-- ============================================
-- Finders Database Schema (MySQL 8.0+)
-- ============================================
CREATE
DATABASE IF NOT EXISTS finders
DEFAULT CHARACTER SET utf8mb4
COLLATE utf8mb4_unicode_ci;
USE
finders;
-- ============================================
-- 1. MEMBER
-- ============================================
CREATE TABLE member
( -- Base ํ
์ด๋ธ (๊ณตํต ํ๋๋ง)
id BIGINT NOT NULL AUTO_INCREMENT,
role VARCHAR(20) NOT NULL, -- Discriminator: USER, OWNER, ADMIN (JPA๊ฐ ์๋ ๊ด๋ฆฌ)
name VARCHAR(20) NOT NULL, -- ์ค๋ช
email VARCHAR(100) NULL, -- ์นด์นด์ค ๋น์ฆ์ฑ ํตํด์ ๊ฐ์ ธ์ด
phone VARCHAR(20) NULL, -- ์นด์นด์ค ๋น์ฆ์ฑ ํตํด์ ๊ฐ์ ธ์ด
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
refresh_token_hash VARCHAR(500) NULL, -- JWT Refresh Token Hash (๋ชจ๋ ์ญํ ๊ณตํต)
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
PRIMARY KEY (id),
INDEX idx_member_role (role),
CONSTRAINT chk_member_role CHECK (role IN ('USER', 'OWNER', 'ADMIN')),
CONSTRAINT chk_member_status CHECK (status IN ('ACTIVE', 'SUSPENDED', 'WITHDRAWN'))
) ENGINE=InnoDB COMMENT='ํ์ Base (Joined Table ์์)';
CREATE TABLE member_user
( -- User ์ ์ฉ ํ
์ด๋ธ (์์
๋ก๊ทธ์ธ ์ฌ์ฉ์)
member_id BIGINT NOT NULL, -- PK & FK โ member.id
nickname VARCHAR(20) NOT NULL, -- ์นด์นด์ค ๋น์ฆ์ฑ ํตํด์ ๊ฐ์ ธ์ค๊ฑฐ๋ ์
๋ ฅ (๋๋ค์)
profile_image VARCHAR(500) NULL, -- ์นด์นด์ค ๋น์ฆ์ฑ ํตํด์ ๊ฐ์ ธ์ด
credit_balance INT UNSIGNED NOT NULL DEFAULT 3, -- ๋ณด์ ํฌ๋ ๋ง (ํ์๊ฐ์
์ 3๊ฐ ์ง๊ธ)
last_credit_refresh_at DATETIME NULL, -- ๋ง์ง๋ง ๋ฌด๋ฃ ํฌ๋ ๋ง ๋ฆฌํ๋ ์ ์ผ์
PRIMARY KEY (member_id),
UNIQUE KEY uk_member_user_nickname (nickname),
CONSTRAINT fk_member_user FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='User ์ ์ฉ (์์
๋ก๊ทธ์ธ, ํฌ๋ ๋ง)';
CREATE TABLE member_owner
( -- Owner ์ ์ฉ ํ
์ด๋ธ (ํ์์ ์ฌ์ฅ๋)
member_id BIGINT NOT NULL, -- PK & FK โ member.id
password_hash VARCHAR(255) NOT NULL, -- BCrypt ํด์ (์ด๋ฉ์ผ/๋น๋ฐ๋ฒํธ ๋ก๊ทธ์ธ)
business_number VARCHAR(20) NULL, -- ์ฌ์
์ ๋ฒํธ
bank_name VARCHAR(50) NULL, -- ์ํ๋ช
bank_account_number VARCHAR(50) NULL, -- ๊ณ์ข๋ฒํธ
bank_account_holder VARCHAR(50) NULL, -- ์๊ธ์ฃผ
PRIMARY KEY (member_id),
CONSTRAINT fk_member_owner FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Owner ์ ์ฉ (์ฌ์
์ ์ ๋ณด, ์ ์ฐ ๊ณ์ข)';
CREATE TABLE member_admin
( -- Admin ์ ์ฉ ํ
์ด๋ธ (๊ด๋ฆฌ์)
member_id BIGINT NOT NULL, -- PK & FK โ member.id
password_hash VARCHAR(255) NOT NULL, -- BCrypt ํด์ (์ด๋ฉ์ผ/๋น๋ฐ๋ฒํธ ๋ก๊ทธ์ธ)
PRIMARY KEY (member_id),
CONSTRAINT fk_member_admin FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='Admin ์ ์ฉ';
CREATE TABLE social_account
( -- User(role='USER') ์ ์ฉ
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL, -- FK (User๋ง ์ฐ๊ฒฐ, Owner/Admin์ ์์
๋ก๊ทธ์ธ ๋ฏธ์ฌ์ฉ)
provider VARCHAR(20) NOT NULL, -- KAKAO, APPLE
provider_id VARCHAR(100) NOT NULL,
social_email VARCHAR(100) NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_social_provider (provider, provider_id),
INDEX idx_social_member (member_id),
CONSTRAINT fk_social_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT chk_social_provider CHECK (provider IN ('KAKAO', 'APPLE'))
) ENGINE=InnoDB COMMENT='์์
๊ณ์ (User ์ ์ฉ)';
CREATE TABLE member_address
(
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL, -- FK โ member_user
address_name VARCHAR(50) NOT NULL, -- ๋ฐฐ์ก์ง ์ด๋ฆ
zipcode VARCHAR(10) NOT NULL, -- ์ฐํธ๋ฒํธ
address VARCHAR(200) NOT NULL, -- 'ex) ์์ธ ์์ด๊ตฌ ๊ณ ๋ฌด๋๋ก 89
address_detail VARCHAR(100) NULL, -- 'ex) 3003๋ 303ํธ
is_default BOOLEAN NOT NULL DEFAULT FALSE, -- ๊ธฐ๋ณธ ๋ฐฐ์ก์ง ์ฌ๋ถ
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
PRIMARY KEY (id),
INDEX idx_address_member (member_id, is_default),
CONSTRAINT fk_address_member FOREIGN KEY (member_id) REFERENCES member (id)
) ENGINE=InnoDB COMMENT='๋ฐฐ์ก์ง';
CREATE TABLE terms
( -- ์ฝ๊ด ๋ฒ์ ๊ด๋ฆฌ
id BIGINT NOT NULL AUTO_INCREMENT,
type VARCHAR(20) NOT NULL, -- TERMS, PRIVACY, MARKETING, LOCATION
version VARCHAR(20) NOT NULL, -- ๋ฒ์ (์: 1.0, 1.1, 2.0)
title VARCHAR(200) NOT NULL, -- ์ฝ๊ด ์ ๋ชฉ
content TEXT NOT NULL, -- ์ฝ๊ด ๋ด์ฉ
is_required BOOLEAN NOT NULL DEFAULT TRUE, -- ํ์ ๋์ ์ฌ๋ถ
is_active BOOLEAN NOT NULL DEFAULT TRUE, -- ํ์ฌ ํ์ฑ ๋ฒ์ ์ฌ๋ถ
effective_date DATE NOT NULL, -- ์ํ์ผ
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_terms_version (type, version),
INDEX idx_terms_active (type, is_active),
CONSTRAINT chk_terms_type CHECK (type IN ('SERVICE', 'PRIVACY', 'LOCATION', 'SERVICE_INFO', 'MARKETING'))
) ENGINE=InnoDB COMMENT='์ฝ๊ด ๋ฒ์ ';
CREATE TABLE member_agreement
( -- ํ์๋ณ ์ฝ๊ด ๋์ ์ด๋ ฅ
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL, -- FK
terms_id BIGINT NOT NULL, -- FK (์ฝ๊ด ๋ฒ์ ์ฐธ์กฐ)
is_agreed BOOLEAN NOT NULL,
agreed_at DATETIME NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_agreement_member (member_id),
INDEX idx_agreement_terms (terms_id),
CONSTRAINT fk_agreement_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT fk_agreement_terms FOREIGN KEY (terms_id) REFERENCES terms (id)
) ENGINE=InnoDB COMMENT='์ฝ๊ด ๋์ ์ด๋ ฅ';
CREATE TABLE terms_social_mapping
( -- ์ฝ๊ด๊ณผ ์์
๋ก๊ทธ์ธ ํ๊ทธ ๋งคํ (์นด์นด์ค ๋์ ํญ๋ชฉ ์ฐ๋)
id BIGINT NOT NULL AUTO_INCREMENT,
terms_id BIGINT NOT NULL, -- FK โ terms
provider VARCHAR(20) NOT NULL, -- KAKAO, APPLE (SocialProvider)
social_tag VARCHAR(100) NOT NULL, -- ์์
์ธก ํ๊ทธ (์: "service_policy", "privacy_policy")
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
CONSTRAINT fk_tsm_terms FOREIGN KEY (terms_id) REFERENCES terms (id)
) ENGINE=InnoDB COMMENT='์ฝ๊ด-์์
ํ๊ทธ ๋งคํ';
CREATE TABLE member_device
( -- FCM(Firebase) ๋๋ฐ์ด์ค ํ ํฐ -- ๋ฐ๋ชจ๋ฐ์ด ์ ๊น์ง ์ ๋ ๊ฐ๋ฐ ๊ธ์ง
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL, -- FK
device_token VARCHAR(500) NOT NULL, -- FCM ํ ํฐ
device_type VARCHAR(20) NOT NULL, -- IOS, ANDROID, WEB
device_name VARCHAR(100) NULL, -- ๋๋ฐ์ด์ค ์ด๋ฆ (์: iPhone 15, Galaxy S24)
is_active BOOLEAN NOT NULL DEFAULT TRUE,
last_used_at DATETIME NULL, -- ๋ง์ง๋ง ์ฌ์ฉ ์๊ฐ
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_device_token (device_token),
INDEX idx_device_member (member_id, is_active),
CONSTRAINT fk_device_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT chk_device_type CHECK (device_type IN ('IOS', 'ANDROID', 'WEB'))
) ENGINE=InnoDB COMMENT='FCM ๋๋ฐ์ด์ค ํ ํฐ';
CREATE TABLE credit_history
( -- AI ํฌ๋ ๋ง ์ถฉ์ /์ฌ์ฉ ๋ด์ญ
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL,
type VARCHAR(20) NOT NULL, -- SIGNUP_BONUS, REFRESH, PURCHASE, USE, REFUND
amount INT NOT NULL, -- ๋ณ๋๋ (+3, -1 ๋ฑ)
balance_after INT UNSIGNED NOT NULL, -- ๋ณ๋ ํ ์์ก
related_type VARCHAR(50) NULL, -- PHOTO_RESTORATION, PAYMENT
related_id BIGINT NULL, -- ๊ด๋ จ ์ํฐํฐ ID
description VARCHAR(200) NULL, -- ์ค๋ช
(์: "ํ์๊ฐ์
๋ณด๋์ค", "AI ๋ณต์ ์ฌ์ฉ")
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_credit_history_member (member_id, created_at DESC),
INDEX idx_credit_history_type (type),
CONSTRAINT fk_credit_history_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT chk_credit_type CHECK (type IN ('SIGNUP_BONUS', 'REFRESH', 'PURCHASE', 'USE', 'REFUND'))
) ENGINE=InnoDB COMMENT='ํฌ๋ ๋ง ๋ด์ญ';
CREATE TABLE favorite_photo_lab
( -- ๋ณ ํ์
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL,
photo_lab_id BIGINT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_favorite_lab (member_id, photo_lab_id),
CONSTRAINT fk_fav_lab_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT fk_fav_lab_lab FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id)
) ENGINE=InnoDB COMMENT='๊ด์ฌ ํ์์';
-- ============================================
-- 2. STORE (ํ์์)
-- ============================================
CREATE TABLE photo_lab
(
id BIGINT NOT NULL AUTO_INCREMENT,
owner_id BIGINT NOT NULL, -- FK โ member_owner.member_id (Owner ์ ์ฉ)
region_id BIGINT NOT NULL, -- FK region.id (์/๊ตฐ/๊ตฌ)
name VARCHAR(100) NOT NULL,
description TEXT NULL,
phone VARCHAR(20) NULL,
zipcode VARCHAR(10) NULL, -- ์ฐํธ๋ฒํธ
address VARCHAR(200) NOT NULL, -- 'ex) ์์ธ ์์ด๊ตฌ ๊ณ ๋ฌด๋๋ก 89
address_detail VARCHAR(100) NULL, -- 'ex) 3003๋ 303ํธ
latitude DECIMAL(10, 8) NULL,
longitude DECIMAL(11, 8) NULL,
work_count INT UNSIGNED NOT NULL DEFAULT 0, -- ์์
์ด ํ์
post_count INT UNSIGNED NOT NULL DEFAULT 0, -- ์์
๊ฒฐ๊ณผ๋ฌผ์ ํ์ํจ
reservation_count INT UNSIGNED NOT NULL DEFAULT 0, -- ์์ฝ ์๋ฃ ์์ ์ ์
๋ฐ์ดํธ
review_count INT UNSIGNED NOT NULL DEFAULT 0,
avg_work_time INT UNSIGNED NULL, -- ์ฃผ๋ฌธ ์๋ฃ ์์ ์(development_order.status = 'COMPLETED') ๋ฐฑ์๋์์ ์ง์ ๊ณ์ฐํด์ ์ ์ฅ
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
is_delivery_available BOOLEAN NOT NULL DEFAULT FALSE, -- TRUE: ๋ฐฐ์ก๊ฐ๋ฅ, FALSE: ๋ฐฐ์ก ๋ถ๊ฐ๋ฅ
max_reservations_per_hour INT UNSIGNED NOT NULL DEFAULT 3, -- ์๊ฐ๋น ์ต๋ ์์ฝ ๊ฐ๋ฅ ์ (Owner ์กฐ์ ๊ฐ๋ฅ)
load_base_roll INT UNSIGNED NOT NULL DEFAULT 1, -- ๋ถํ ๊ณ์ฐ ๊ธฐ์ค ๋กค ์
load_add_minutes INT UNSIGNED NOT NULL DEFAULT 120, -- ์ถ๊ฐ ๋กค๋น ๋ถ (์์ฝ ๋๊ธฐ ์๊ฐ ๊ณ์ฐ)
qr_code_url VARCHAR(500) NULL, -- QR ์ฝ๋ ์ด๋ฏธ์ง URL (ํ์ฅ ์ฃผ๋ฌธ์ฉ)
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
PRIMARY KEY (id),
INDEX idx_lab_status (status),
INDEX idx_lab_location (latitude, longitude),
FULLTEXT INDEX ft_lab_name (name),
CONSTRAINT fk_lab_owner FOREIGN KEY (owner_id) REFERENCES member (id),
CONSTRAINT fk_lab_region FOREIGN KEY (region_id) REFERENCES region (id),
CONSTRAINT chk_lab_status CHECK (status IN ('PENDING', 'ACTIVE', 'SUSPENDED', 'CLOSED'))
) ENGINE=InnoDB COMMENT='ํ์์';
CREATE TABLE photo_lab_image
( -- ํ์์๋ง๋ค ์ด๋ฏธ์ง ๊ฐ์๊ฐ ์ฌ๋ฌ ๊ฐ์ด๋ฏ๋ก ๋ฐ๋ก ๋ถ๋ฆฌ
id BIGINT NOT NULL AUTO_INCREMENT,
photo_lab_id BIGINT NOT NULL, -- FK
object_path VARCHAR(500) NOT NULL, -- GCS object path (์: photo-labs/123/images/abc.jpg)
display_order INT NOT NULL DEFAULT 0,
is_main BOOLEAN NOT NULL DEFAULT FALSE, -- ๋ํ ์ด๋ฏธ์ง ์ฌ๋ถ
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_lab_image (photo_lab_id, is_main),
CONSTRAINT fk_lab_image FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='ํ์์ ์ด๋ฏธ์ง';
CREATE TABLE photo_lab_tag
( -- join table
id BIGINT NOT NULL AUTO_INCREMENT,
photo_lab_id BIGINT NOT NULL, -- FK
tag_id BIGINT NOT NULL, -- FK
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_lab_tag (photo_lab_id, tag_id),
INDEX idx_photo_lab_tag_photo_lab_id (photo_lab_id),
INDEX idx_photo_lab_tag_tag_id (tag_id),
CONSTRAINT fk_photo_lab_tag_photo_lab_id
FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id) ON DELETE CASCADE,
CONSTRAINT fk_photo_lab_tag_tag_id
FOREIGN KEY (tag_id) REFERENCES tag (id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='ํ์์-ํ๊ทธ ์กฐ์ธ ํ
์ด๋ธ';
CREATE TABLE tag
(
id BIGINT NOT NULL AUTO_INCREMENT,
name VARCHAR(50) NOT NULL, -- ํ๊ทธ ์ด๋ฆ
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_tag_name (name)
) ENGINE=InnoDB COMMENT='ํ์์ ํ๊ทธ';
CREATE TABLE photo_lab_notice
(
id BIGINT NOT NULL AUTO_INCREMENT,
photo_lab_id BIGINT NOT NULL, -- FK
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
notice_type VARCHAR(20) NOT NULL DEFAULT 'GENERAL', -- ENUM ์ฒ๋ฆฌ GENERAL: ์ผ๋ฐ๊ณต์ง, EVENT: ์ด๋ฒคํธํ์ฌ๊ณต์ง
start_date DATE NULL, -- yyyy-MM-dd, 2025-12-23
end_date DATE NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_lab_notice (photo_lab_id),
CONSTRAINT fk_lab_notice FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id) ON DELETE CASCADE,
CONSTRAINT chk_lab_notice_type CHECK (notice_type IN ('GENERAL', 'EVENT', 'POLICY'))
) ENGINE=InnoDB COMMENT='ํ์์ ๊ณต์ง';
CREATE TABLE photo_lab_business_hour
( -- ํ์์์ ๊ฐ ์์ผ๋ง๋ค์ ์ผ์ ๋ฑ๋ก 1:N ๊ด๊ณ
id BIGINT NOT NULL AUTO_INCREMENT,
photo_lab_id BIGINT NOT NULL,
day_of_week VARCHAR(10) NOT NULL, -- MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY, SUNDAY (java.time.DayOfWeek)
open_time TIME NULL, -- HH:mm:ss (์: 09:00:00)
close_time TIME NULL,
is_closed BOOLEAN NOT NULL DEFAULT FALSE, -- FALSE: ์์
์ผ, TRUE: ํด๋ฌด์ผ
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_lab_hour (photo_lab_id, day_of_week),
CONSTRAINT fk_lab_hour FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id) ON DELETE CASCADE,
CONSTRAINT chk_day_of_week CHECK (day_of_week IN
('SUNDAY', 'MONDAY', 'TUESDAY', 'WEDNESDAY', 'THURSDAY', 'FRIDAY', 'SATURDAY'))
) ENGINE=InnoDB COMMENT='์์
์๊ฐ';
CREATE TABLE photo_lab_document
( -- GCP Cloud Storage์ bucket ๋ง๋ค์ด์ ์ ์ฅํด์ผ ํ ๋ฏ
id BIGINT NOT NULL AUTO_INCREMENT,
photo_lab_id BIGINT NOT NULL,
document_type VARCHAR(30) NOT NULL, -- BUSINESS_LICENSE, BUSINESS_PERMIT
object_path VARCHAR(500) NOT NULL, -- GCS object path (์: photo-labs/123/documents/BUSINESS_LICENSE/abc.pdf)
-- ํ์ผ์ ๋ฒ์ ๊ด๋ฆฌ๊ฐ ํ์ํ๋ค. ๊ด๋ฆฌ์ ์
์ฅ์์ ์๊ฐํด๋ณด๋ฉด, approved, rejeceted, pending
file_name VARCHAR(200) NULL,
verified_at DATETIME NULL, -- ๊ฒ์ฆ ์๋ฃ ์ผ์
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_lab_doc (photo_lab_id, document_type),
CONSTRAINT fk_lab_doc FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id) ON DELETE CASCADE,
CONSTRAINT chk_doc_type CHECK (document_type IN ('BUSINESS_LICENSE', 'BUSINESS_PERMIT'))
) ENGINE=InnoDB COMMENT='์ฌ์
์ ์ฆ๋น ์๋ฅ';
CREATE TABLE region
(
id BIGINT NOT NULL AUTO_INCREMENT,
region_name VARCHAR(50) NOT NULL COMMENT '์ง์ญ๋ช
',
parent_region_id BIGINT NULL COMMENT '์์ ์ง์ญ ID (์ต์์ ์ง์ญ์ NULL)',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
PRIMARY KEY (id),
INDEX idx_region_parent (parent_region_id),
UNIQUE KEY uk_region_name_parent (region_name, parent_region_id),
CONSTRAINT fk_region_parent_region
FOREIGN KEY (parent_region_id) REFERENCES region (id)
) ENGINE=InnoDB COMMENT='์ง์ญ (๊ณ์ธต ๊ตฌ์กฐ: ์์/ํ์)';
-- ============================================
-- 3. RESERVATION
-- ============================================
CREATE TABLE reservation
(
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL, -- FK โ member_user
slot_id BIGINT NOT NULL, -- FK โ reservation_slot (๋ ์ง/์๊ฐ์ ์ฌ๋กฏ์ ํตํด ์ฐธ์กฐ)
photo_lab_id BIGINT NOT NULL, -- FK โ photo_lab
status VARCHAR(20) NOT NULL DEFAULT 'RESERVED',
-- ์์
๋ด์ฉ ์ ํ (๋ค์ค ์ ํ ๊ฐ๋ฅ)
is_develop BOOLEAN NOT NULL DEFAULT FALSE, -- ํ์
is_scan BOOLEAN NOT NULL DEFAULT FALSE, -- ์ค์บ
is_print BOOLEAN NOT NULL DEFAULT FALSE, -- ์ธํ
roll_count INT UNSIGNED NOT NULL DEFAULT 1,
request_message VARCHAR(500) NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
PRIMARY KEY (id),
INDEX idx_reservation_lab_status (photo_lab_id, status),
CONSTRAINT fk_reservation_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT fk_reservation_slot FOREIGN KEY (slot_id) REFERENCES reservation_slot (id),
CONSTRAINT fk_reservation_lab FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id),
CONSTRAINT chk_reservation_status CHECK (status IN ('RESERVED', 'COMPLETED', 'CANCELED'))
) ENGINE=InnoDB COMMENT='์์ฝ';
CREATE TABLE reservation_slot
(
id BIGINT NOT NULL AUTO_INCREMENT,
photo_lab_id BIGINT NOT NULL,
reservation_date DATE NOT NULL,
reservation_time TIME NOT NULL,
max_capacity INT NOT NULL,
reserved_count INT NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
PRIMARY KEY (id),
UNIQUE KEY uk_slot_lab_date_time (photo_lab_id, reservation_date, reservation_time),
INDEX idx_slot_lab_date (photo_lab_id, reservation_date),
CONSTRAINT fk_slot_lab
FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='์์ฝ ์ฌ๋กฏ(์ ์ ๊ด๋ฆฌ)';
-- ============================================
-- 4. PHOTO (ํ์/์ค์บ/์ธํ)
-- ============================================
CREATE TABLE development_order
( -- ํ์/์ค์บ ์ธํธ๋ก
id BIGINT NOT NULL AUTO_INCREMENT,
reservation_id BIGINT NULL, -- ์์ฝ ์์ด ํ์ฅ ์ฃผ๋ฌธ ๊ฐ๋ฅ (QR ์ค์บ)
photo_lab_id BIGINT NOT NULL, -- ํ์์ (์์ฝ ์์ ๋ ํ์)
member_id BIGINT NOT NULL,
order_code VARCHAR(20) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'RECEIVED',
total_photos INT UNSIGNED NOT NULL DEFAULT 0, -- ์ค์บ ์งํ๋ฅ ์ scanned_photo COUNT๋ก ๊ณ์ฐ
total_price INT UNSIGNED NOT NULL DEFAULT 0, -- ์ฃผ๋ฌธ ์์ ๊ฐ๊ฒฉ ์ค๋
์ท (ํ์+์ค์บ ํจํค์ง)
completed_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_dev_order_code (order_code),
INDEX idx_dev_order_member (member_id, status),
INDEX idx_dev_order_lab (photo_lab_id, status),
CONSTRAINT fk_dev_order_reservation FOREIGN KEY (reservation_id) REFERENCES reservation (id),
CONSTRAINT fk_dev_order_lab FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id),
CONSTRAINT fk_dev_order_member FOREIGN KEY (member_id) REFERENCES member (id),
is_develop BOOLEAN NOT NULL DEFAULT FALSE,
is_scan BOOLEAN NOT NULL DEFAULT FALSE,
is_print BOOLEAN NOT NULL DEFAULT FALSE,
roll_count INT UNSIGNED NOT NULL DEFAULT 1,
CONSTRAINT chk_dev_status CHECK (status IN ('RECEIVED', 'DEVELOPING', 'SCANNING', 'COMPLETED'))
) ENGINE=InnoDB COMMENT='ํ์ ์ฃผ๋ฌธ';
CREATE TABLE scanned_photo
( -- 1:N ๊ด๊ณ
id BIGINT NOT NULL AUTO_INCREMENT,
order_id BIGINT NOT NULL, -- FK development_order
object_path VARCHAR(500) NOT NULL, -- GCS object path (์: temp/orders/123/scans/abc.jpg)
file_name VARCHAR(200) NULL,
display_order INT NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_scanned_order (order_id),
CONSTRAINT fk_scanned_order FOREIGN KEY (order_id) REFERENCES development_order (id)
) ENGINE=InnoDB COMMENT='์ค์บ ์ฌ์ง';
CREATE TABLE print_order
(
id BIGINT NOT NULL AUTO_INCREMENT,
dev_order_id BIGINT NULL, -- ํ์ ์์ด ์ธํ๋ง ์์ฒญ ๊ฐ๋ฅ
photo_lab_id BIGINT NOT NULL, -- ํ์์
member_id BIGINT NOT NULL,
order_code VARCHAR(20) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
total_price INT UNSIGNED NOT NULL DEFAULT 0,
receipt_method VARCHAR(20) NOT NULL DEFAULT 'PICKUP',
estimated_at DATETIME NULL,
completed_at DATETIME NULL,
deposit_receipt_object_path VARCHAR(500) NULL,
depositor_name VARCHAR(50) NULL,
deposit_bank_name VARCHAR(50) NULL,
payment_submitted_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_print_order_code (order_code),
INDEX idx_print_order_member (member_id, status),
INDEX idx_print_order_lab (photo_lab_id, status),
CONSTRAINT fk_print_order_dev FOREIGN KEY (dev_order_id) REFERENCES development_order (id),
CONSTRAINT fk_print_order_lab FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id),
CONSTRAINT fk_print_order_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT chk_print_status CHECK (status IN ('PENDING', 'CONFIRMED', 'PRINTING', 'READY', 'SHIPPED', 'COMPLETED')),
CONSTRAINT chk_receipt_method CHECK (receipt_method IN ('PICKUP', 'DELIVERY'))
) ENGINE=InnoDB COMMENT='์ธํ ์ฃผ๋ฌธ';
CREATE TABLE print_order_item
(
id BIGINT NOT NULL AUTO_INCREMENT,
print_order_id BIGINT NOT NULL, -- FK
film_type VARCHAR(20) NOT NULL,
paper_type VARCHAR(30) NOT NULL,
print_method VARCHAR(20) NOT NULL,
size VARCHAR(20) NOT NULL,
frame_type VARCHAR(20) NOT NULL,
unit_price INT UNSIGNED NOT NULL,
total_price INT UNSIGNED NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_print_item_order (print_order_id),
CONSTRAINT fk_print_item_order FOREIGN KEY (print_order_id) REFERENCES print_order (id)
) ENGINE=InnoDB COMMENT='์ธํ ์ต์
/๊ฐ๊ฒฉ ์์ธ';
CREATE TABLE print_order_photo
(
id BIGINT NOT NULL AUTO_INCREMENT,
print_order_id BIGINT NOT NULL,
scanned_photo_id BIGINT NOT NULL,
quantity INT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_pop_order_photo (print_order_id, scanned_photo_id),
CONSTRAINT fk_pop_print_order FOREIGN KEY (print_order_id) REFERENCES print_order (id),
CONSTRAINT fk_pop_scanned_photo FOREIGN KEY (scanned_photo_id) REFERENCES scanned_photo (id)
) ENGINE=InnoDB COMMENT='์ธํ ์ฃผ๋ฌธ-์ค์บ์ฌ์ง ๋งคํ(์๋ ํฌํจ)';
CREATE TABLE delivery
(
id BIGINT NOT NULL AUTO_INCREMENT,
print_order_id BIGINT NOT NULL,
recipient_name VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL,
zipcode VARCHAR(10) NOT NULL,
address VARCHAR(200) NOT NULL,
address_detail VARCHAR(100) NULL,
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
tracking_number VARCHAR(50) NULL,
carrier VARCHAR(50) NULL,
delivery_fee INT UNSIGNED NOT NULL DEFAULT 0,
shipped_at DATETIME NULL,
delivered_at DATETIME NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_delivery_order (print_order_id),
INDEX idx_delivery_status (status),
CONSTRAINT fk_delivery_order FOREIGN KEY (print_order_id) REFERENCES print_order (id),
CONSTRAINT chk_delivery_status CHECK (status IN ('PENDING', 'PREPARING', 'SHIPPED', 'IN_TRANSIT', 'DELIVERED'))
) ENGINE=InnoDB COMMENT='๋ฐฐ์ก';
CREATE TABLE photo_restoration
( -- Replicate AI ์ฌ์ฉ
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL,
original_path VARCHAR(500) NOT NULL, -- ์๋ณธ ์ด๋ฏธ์ง GCS ๊ฒฝ๋ก
mask_path VARCHAR(500) NULL, -- ๋ง์คํฌ ์ด๋ฏธ์ง GCS ๊ฒฝ๋ก (๋ชจ๋ธ์ ๋ฐ๋ผ ๋ถํ์)
restored_path VARCHAR(500) NULL, -- ๋ณต์๋ ์ด๋ฏธ์ง GCS ๊ฒฝ๋ก
restored_width INT NULL, -- ๋ณต์๋ ์ด๋ฏธ์ง ๋๋น (ํฝ์
)
restored_height INT NULL, -- ๋ณต์๋ ์ด๋ฏธ์ง ๋์ด (ํฝ์
)
status VARCHAR(20) NOT NULL DEFAULT 'PENDING', -- PENDING, PROCESSING, COMPLETED, FAILED
replicate_prediction_id VARCHAR(100) NULL, -- Replicate API prediction ID (ํ์ํธํ)
provider_job_id VARCHAR(100) NULL, -- AI ์ ๊ณต์ ์์
ID (๋ฒ์ฉ)
provider_name VARCHAR(30) NULL, -- AI ์ ๊ณต์ ์ด๋ฆ (REPLICATE ๋ฑ)
-- ํฌ๋ ๋ง ๊ด๋ จ
credit_used INT UNSIGNED NOT NULL DEFAULT 1, -- ์ฌ์ฉ๋ ํฌ๋ ๋ง ์ (๋ณต์ ์๋ฃ ์ ์ฐจ๊ฐ)
-- ์๋ฌ ์ ๋ณด
error_message VARCHAR(500) NULL, -- ์คํจ ์ ์๋ฌ ๋ฉ์์ง
-- ํผ๋๋ฐฑ (AI ํ์ง ๊ฐ์ ์ฉ)
feedback_rating VARCHAR(10) NULL, -- GOOD, BAD
feedback_comment VARCHAR(500) NULL, -- ํผ๋๋ฐฑ ์ฝ๋ฉํธ (์ ํ)
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_restoration_member (member_id, status),
INDEX idx_restoration_prediction (replicate_prediction_id),
CONSTRAINT fk_restoration_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT chk_restoration_status CHECK (status IN ('PENDING', 'PROCESSING', 'COMPLETED', 'FAILED')),
CONSTRAINT chk_feedback_rating CHECK (feedback_rating IS NULL OR feedback_rating IN ('GOOD', 'BAD'))
) ENGINE=InnoDB COMMENT='AI ์ฌ์ง ๋ณต์';
-- ============================================
-- 5. COMMUNITY
-- ============================================
CREATE TABLE post
(
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL, -- FK
photo_lab_id BIGINT NULL, -- FK (ํ์์ ์ด์ฉ ์)
is_self_developed BOOLEAN NOT NULL DEFAULT FALSE, -- TRUE: ์๊ฐํ์, FALSE: ํ์์ ์ด์ฉ
title VARCHAR(30) NOT NULL, -- ๊ฒ์๊ธ ์ ๋ชฉ
content TEXT NOT NULL,
lab_review VARCHAR(300) NULL, -- ํ์์ ๋ฆฌ๋ทฐ (ํ์์ ์ด์ฉ ์)
like_count INT UNSIGNED NOT NULL DEFAULT 0,
comment_count INT UNSIGNED NOT NULL DEFAULT 0,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
PRIMARY KEY (id),
INDEX idx_post_member (member_id, status),
INDEX idx_post_lab (photo_lab_id),
INDEX idx_post_created (created_at DESC),
FULLTEXT INDEX ft_post_content (title, content),
CONSTRAINT fk_post_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT fk_post_lab FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id),
CONSTRAINT chk_post_status CHECK (status IN ('ACTIVE', 'HIDDEN', 'DELETED')), -- CommunityStatus enum
CONSTRAINT chk_post_lab_required CHECK (
(is_self_developed = TRUE AND photo_lab_id IS NULL) OR
(is_self_developed = FALSE AND photo_lab_id IS NOT NULL)
)
) ENGINE=InnoDB COMMENT='๊ฒ์๊ธ/๋ฆฌ๋ทฐ';
CREATE TABLE post_image
( -- 1:N
id BIGINT NOT NULL AUTO_INCREMENT,
post_id BIGINT NOT NULL, -- FK
object_path VARCHAR(500) NOT NULL, -- GCS object path (์: posts/123/abc.jpg)
display_order INT NOT NULL DEFAULT 0,
width INT NOT NULL, -- ์ด๋ฏธ์ง ๋๋น (ํฝ์
)
height INT NOT NULL, -- ์ด๋ฏธ์ง ๋์ด (ํฝ์
)
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_post_image (post_id),
CONSTRAINT fk_post_image FOREIGN KEY (post_id) REFERENCES post (id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='๊ฒ์๊ธ ์ด๋ฏธ์ง';
CREATE TABLE comments
( -- ๋๊ธ
id BIGINT NOT NULL AUTO_INCREMENT,
post_id BIGINT NOT NULL,
member_id BIGINT NOT NULL,
content VARCHAR(1000) NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'ACTIVE',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
PRIMARY KEY (id),
INDEX idx_comments_post (post_id, created_at),
CONSTRAINT fk_comments_post FOREIGN KEY (post_id) REFERENCES post (id),
CONSTRAINT fk_comments_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT chk_comments_status CHECK (status IN ('ACTIVE', 'HIDDEN', 'DELETED')) -- CommunityStatus enum
) ENGINE=InnoDB COMMENT='๋๊ธ';
CREATE TABLE post_like
( -- ํํธ ํ์
id BIGINT NOT NULL AUTO_INCREMENT,
post_id BIGINT NOT NULL,
member_id BIGINT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_post_like (post_id, member_id),
CONSTRAINT fk_like_post FOREIGN KEY (post_id) REFERENCES post (id) ON DELETE CASCADE,
CONSTRAINT fk_like_member FOREIGN KEY (member_id) REFERENCES member (id)
) ENGINE=InnoDB COMMENT='์ข์์';
CREATE TABLE search_history (
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL,
keyword VARCHAR(100) NOT NULL,
object_path VARCHAR(500) NULL,
width INT NULL,
height INT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_search_history_member (member_id, updated_at DESC),
CONSTRAINT fk_search_history_member FOREIGN KEY (member_id) REFERENCES member (id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='์ต๊ทผ ๊ฒ์์ด ์ด๋ ฅ';
-- ============================================
-- 6. INQUIRY
-- ============================================
CREATE TABLE inquiry
(
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL,
photo_lab_id BIGINT NULL,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_inquiry_member (member_id, status),
INDEX idx_inquiry_lab (photo_lab_id),
CONSTRAINT fk_inquiry_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT fk_inquiry_lab FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id),
CONSTRAINT chk_inquiry_status CHECK (status IN ('PENDING', 'ANSWERED', 'CLOSED'))
) ENGINE=InnoDB COMMENT='1:1 ๋ฌธ์';
CREATE TABLE inquiry_image
( -- ๋ฌธ์ ์ฒจ๋ถ ์ด๋ฏธ์ง (์ต๋ 5๊ฐ)
id BIGINT NOT NULL AUTO_INCREMENT,
inquiry_id BIGINT NOT NULL, -- FK
object_path VARCHAR(500) NOT NULL, -- GCS object path (์: inquiries/123/abc.jpg)
display_order INT NOT NULL DEFAULT 0,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_inquiry_image (inquiry_id),
CONSTRAINT fk_inquiry_image FOREIGN KEY (inquiry_id) REFERENCES inquiry (id) ON DELETE CASCADE
) ENGINE=InnoDB COMMENT='๋ฌธ์ ์ด๋ฏธ์ง';
CREATE TABLE inquiry_reply
(
id BIGINT NOT NULL AUTO_INCREMENT,
inquiry_id BIGINT NOT NULL,
replier_id BIGINT NOT NULL, -- ๋ต๋ณ์ (ADMIN: ์๋น์ค ๋ฌธ์, OWNER: ํ์์ ๋ฌธ์)
content TEXT NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_reply_inquiry (inquiry_id),
CONSTRAINT fk_reply_inquiry FOREIGN KEY (inquiry_id) REFERENCES inquiry (id),
CONSTRAINT fk_reply_replier FOREIGN KEY (replier_id) REFERENCES member (id)
) ENGINE=InnoDB COMMENT='๋ฌธ์ ๋ต๋ณ';
-- ============================================
-- 7. COMMON
-- ============================================
CREATE TABLE notice
( -- ์ ์ฒด ํ์ ๊ณต์ง์ฌํญ ๊ฒ์ํ -- โ ๋ฐ๋ชจ๋ฐ์ด ์ ๋ฏธ๊ตฌํ
id BIGINT NOT NULL AUTO_INCREMENT,
title VARCHAR(200) NOT NULL,
content TEXT NOT NULL,
notice_type VARCHAR(20) NOT NULL DEFAULT 'GENERAL',
is_pinned BOOLEAN NOT NULL DEFAULT FALSE, -- ๊ณ ์ ๋์๋์ง
view_count INT UNSIGNED NOT NULL DEFAULT 0, -- ๊ณต์ง์ view count๊ฐ ํ์ํ ๊น..?
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL,
PRIMARY KEY (id),
INDEX idx_notice_pinned (is_pinned DESC, created_at DESC),
CONSTRAINT chk_notice_type CHECK (notice_type IN ('GENERAL', 'EVENT', 'POLICY'))
) ENGINE=InnoDB COMMENT='๊ณต์ง์ฌํญ';
CREATE TABLE promotion
( -- ๋ฉ์ธํ์ด์ง ํ๋ก๋ชจ์
๋ฐฐ๋ ๋ถ๋ถ -- ๋ฐ๋ชจ๋ฐ์ด ์ ๊น์ง ์ ๋ ๊ฐ๋ฐ ๊ธ์ง
id BIGINT NOT NULL AUTO_INCREMENT,
photo_lab_id BIGINT NOT NULL,
title VARCHAR(200) NOT NULL,
description VARCHAR(500) NULL,
object_path VARCHAR(500) NOT NULL, -- GCS object path (์: promotions/123/abc.jpg)
promotion_type VARCHAR(20) NOT NULL DEFAULT 'BANNER',
display_order INT NOT NULL DEFAULT 0,
start_date DATETIME NOT NULL,
end_date DATETIME NOT NULL,
is_active BOOLEAN NOT NULL DEFAULT TRUE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_promotion_active (is_active, start_date, end_date),
CONSTRAINT fk_promotion_lab FOREIGN KEY (photo_lab_id) REFERENCES photo_lab (id),
CONSTRAINT chk_promotion_type CHECK (promotion_type IN ('BANNER', 'POPUP', 'EVENT'))
) ENGINE=InnoDB COMMENT='ํ๋ก๋ชจ์
';
CREATE TABLE notification
( -- ํ์๋ณ ๊ฐ์ธ ์๋ฆผ(์ฑ ํธ์/ ์๋ฆผ์ผํฐ) -- ๋ฐ๋ชจ๋ฐ์ด ์ ๊น์ง ์ ๋ ๊ฐ๋ฐ ๊ธ์ง
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL,
title VARCHAR(100) NOT NULL,
content VARCHAR(500) NOT NULL,
notification_type VARCHAR(20) NOT NULL,
related_id BIGINT NULL COMMENT '๊ด๋ จ ์ํฐํฐ ID',
related_type VARCHAR(50) NULL COMMENT '๊ด๋ จ ์ํฐํฐ ํ์
',
is_read BOOLEAN NOT NULL DEFAULT FALSE,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_notification_member (member_id, is_read, created_at DESC),
CONSTRAINT fk_notification_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT chk_notification_type CHECK (notification_type IN
('ORDER', 'RESERVATION', 'COMMUNITY', 'MARKETING', 'NOTICE'))
) ENGINE=InnoDB COMMENT='์๋ฆผ';
CREATE TABLE payment
( -- ํฌํธ์ V2 ๊ฒฐ์ ์ฐ๋
id BIGINT NOT NULL AUTO_INCREMENT,
member_id BIGINT NOT NULL,
-- ์ฃผ๋ฌธ ์ ๋ณด
order_type VARCHAR(20) NOT NULL, -- CREDIT_PURCHASE, DEVELOPMENT_ORDER, PRINT_ORDER
related_order_id BIGINT NULL, -- development_order.id ๋๋ print_order.id (ํฌ๋ ๋ง ๊ตฌ๋งค ์ NULL)
payment_id VARCHAR(64) NOT NULL, -- ๊ฒฐ์ ๊ฑด ID (์ฐ๋ฆฌ๊ฐ ์์ฑ, ํฌํธ์์ ์ ๋ฌ)
order_name VARCHAR(100) NOT NULL, -- ์ฃผ๋ฌธ๋ช
(์: "AI ๋ณต์ ํฌ๋ ๋ง 10๊ฐ")
-- ๊ธ์ก ์ ๋ณด
amount INT UNSIGNED NOT NULL, -- ๊ฒฐ์ ์์ฒญ ๊ธ์ก
credit_amount INT UNSIGNED NULL, -- ๊ตฌ๋งคํ ํฌ๋ ๋ง ์ (CREDIT_PURCHASE ์)
-- ํฌํธ์ V2 (์น์ธ ํ ์ ์ฅ)
transaction_id VARCHAR(100) NULL, -- ํฌํธ์ ์ฑ๋ฒ ID (V1์ imp_uid์ ํด๋น)
pg_tx_id VARCHAR(100) NULL, -- PG์ฌ ๊ฑฐ๋ ID
pg_provider VARCHAR(30) NULL, -- PG์ฌ (TOSSPAYMENTS, KCP, KAKAOPAY ๋ฑ)
-- ๊ฒฐ์ ์๋จ
method VARCHAR(30) NULL, -- CARD, TRANSFER, VIRTUAL_ACCOUNT, MOBILE, EASY_PAY
status VARCHAR(30) NOT NULL DEFAULT 'READY',
-- ์นด๋ ์ ๋ณด (CARD, EASY_PAY ๊ฒฐ์ ์)
card_company VARCHAR(20) NULL, -- ์นด๋์ฌ (์ผ์ฑ, ํ๋ ๋ฑ)
card_number VARCHAR(20) NULL, -- ๋ง์คํน๋ ์นด๋๋ฒํธ (1234****5678)
approve_no VARCHAR(20) NULL, -- ์น์ธ๋ฒํธ (ํ๋ถ/๋ถ์ ์ ํ์)
installment_months INT NULL, -- ํ ๋ถ ๊ฐ์์ (0=์ผ์๋ถ)
receipt_url VARCHAR(500) NULL, -- ์์์ฆ URL
-- ์๊ฐ ์ ๋ณด
requested_at DATETIME NULL, -- ๊ฒฐ์ ์์ฒญ ์๊ฐ
paid_at DATETIME NULL, -- ๊ฒฐ์ ์๋ฃ ์๊ฐ
-- ์คํจ/์ทจ์ ์ ๋ณด
fail_code VARCHAR(50) NULL,
fail_message VARCHAR(200) NULL,
cancelled_at DATETIME NULL,
cancel_reason VARCHAR(200) NULL,
cancel_amount INT UNSIGNED NULL, -- ์ทจ์ ๊ธ์ก (๋ถ๋ถ์ทจ์ ๋์)
-- ๊ณตํต
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (id),
UNIQUE KEY uk_payment_id (payment_id),
UNIQUE KEY uk_transaction_id (transaction_id),
INDEX idx_payment_member (member_id, status),
INDEX idx_payment_order_type (order_type, related_order_id),
INDEX idx_payment_status (status),
CONSTRAINT fk_payment_member FOREIGN KEY (member_id) REFERENCES member (id),
CONSTRAINT chk_payment_status CHECK (status IN (
'READY', 'PENDING', 'VIRTUAL_ACCOUNT_ISSUED', 'PAID',
'FAILED', 'PARTIAL_CANCELLED', 'CANCELLED'
)),
CONSTRAINT chk_order_type CHECK (order_type IN ('CREDIT_PURCHASE', 'DEVELOPMENT_ORDER', 'PRINT_ORDER')),
CONSTRAINT chk_payment_method CHECK (method IS NULL OR method IN (
'CARD', 'TRANSFER', 'VIRTUAL_ACCOUNT', 'EASY_PAY'
)),
CONSTRAINT chk_payment_data CHECK (
(order_type = 'CREDIT_PURCHASE' AND credit_amount IS NOT NULL AND related_order_id IS NULL) OR
(order_type IN ('DEVELOPMENT_ORDER', 'PRINT_ORDER') AND related_order_id IS NOT NULL AND credit_amount IS NULL)
)
) ENGINE=InnoDB COMMENT='๊ฒฐ์ (ํฌํธ์ V2)';