55 lines
2.6 KiB
SQL
55 lines
2.6 KiB
SQL
CREATE TABLE IF NOT EXISTS sections (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
sort_order INT NOT NULL DEFAULT 1000,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_sections_name (name)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS photos (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
section_id BIGINT UNSIGNED NOT NULL,
|
|
code_name VARCHAR(191) NOT NULL,
|
|
description TEXT NULL,
|
|
sort_order INT NOT NULL DEFAULT 1000,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_photos_section FOREIGN KEY (section_id) REFERENCES sections(id) ON DELETE CASCADE,
|
|
UNIQUE KEY uq_photos_code_name (code_name),
|
|
KEY idx_photos_section_sort (section_id, sort_order)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS photo_files (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
photo_id BIGINT UNSIGNED NOT NULL,
|
|
kind ENUM('before','after') NOT NULL,
|
|
file_path VARCHAR(500) NOT NULL,
|
|
mime_type VARCHAR(100) NOT NULL,
|
|
size_bytes INT UNSIGNED NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_photo_files_photo FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
|
|
UNIQUE KEY uq_photo_files_kind (photo_id, kind)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS comment_users (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
display_name VARCHAR(191) NOT NULL,
|
|
token_hash CHAR(64) NOT NULL,
|
|
is_active TINYINT(1) NOT NULL DEFAULT 1,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
UNIQUE KEY uq_comment_users_token_hash (token_hash)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
CREATE TABLE IF NOT EXISTS photo_comments (
|
|
id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
|
|
photo_id BIGINT UNSIGNED NOT NULL,
|
|
user_id BIGINT UNSIGNED NULL,
|
|
comment_text TEXT NOT NULL,
|
|
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
|
|
CONSTRAINT fk_photo_comments_photo FOREIGN KEY (photo_id) REFERENCES photos(id) ON DELETE CASCADE,
|
|
CONSTRAINT fk_photo_comments_user FOREIGN KEY (user_id) REFERENCES comment_users(id) ON DELETE SET NULL,
|
|
KEY idx_photo_comments_photo_created (photo_id, created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|