

CREATE TABLE stores (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  slug VARCHAR(120) NOT NULL UNIQUE,
  owner_name VARCHAR(80) NULL,
  phone VARCHAR(40) NULL,
  address VARCHAR(255) NULL,
  business_no VARCHAR(40) NULL,
  service_mode ENUM('order_only','payment') NOT NULL DEFAULT 'order_only',
  payment_provider ENUM('none','mock','kakao','naver','toss') NOT NULL DEFAULT 'none',
  payment_config JSON NULL,
  status ENUM('active','paused') NOT NULL DEFAULT 'active',
  ads_enabled TINYINT(1) NOT NULL DEFAULT 1,
  ad_reward_type ENUM('discount','free_item','coupon','none') NOT NULL DEFAULT 'discount',
  ad_reward_value INT NOT NULL DEFAULT 500,
  theme_color VARCHAR(24) NOT NULL DEFAULT '#111827',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  INDEX idx_stores_status(status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE categories (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  store_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_categories_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE,
  INDEX idx_categories_store(store_id, is_active, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE menu_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  store_id BIGINT UNSIGNED NOT NULL,
  category_id BIGINT UNSIGNED NULL,
  name VARCHAR(140) NOT NULL,
  description TEXT NULL,
  price INT NOT NULL DEFAULT 0,
  image_path VARCHAR(255) NULL,
  is_soldout TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_menu_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE,
  CONSTRAINT fk_menu_category FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL,
  INDEX idx_menu_store(store_id, is_active, is_soldout),
  INDEX idx_menu_category(category_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE menu_option_groups (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  menu_item_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  min_select INT NOT NULL DEFAULT 0,
  max_select INT NOT NULL DEFAULT 1,
  is_required TINYINT(1) NOT NULL DEFAULT 0,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_option_group_menu FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE CASCADE,
  INDEX idx_option_groups_menu(menu_item_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE menu_options (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  option_group_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(120) NOT NULL,
  price_delta INT NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  sort_order INT NOT NULL DEFAULT 0,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_options_group FOREIGN KEY (option_group_id) REFERENCES menu_option_groups(id) ON DELETE CASCADE,
  INDEX idx_options_group(option_group_id, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE order_items (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  menu_item_id BIGINT UNSIGNED NULL,
  menu_name VARCHAR(140) NOT NULL,
  base_price INT NOT NULL DEFAULT 0,
  quantity INT NOT NULL DEFAULT 1,
  line_total INT NOT NULL DEFAULT 0,
  options_json JSON NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_order_items_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  CONSTRAINT fk_order_items_menu FOREIGN KEY (menu_item_id) REFERENCES menu_items(id) ON DELETE SET NULL,
  INDEX idx_order_items_order(order_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE payments (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  order_id BIGINT UNSIGNED NOT NULL,
  provider ENUM('mock','kakao','naver','toss','free') NOT NULL,
  provider_order_id VARCHAR(120) NULL,
  transaction_id VARCHAR(190) NULL,
  amount INT NOT NULL DEFAULT 0,
  status ENUM('ready','widget','pending','approved','failed','canceled','refunded') NOT NULL DEFAULT 'ready',
  request_payload JSON NULL,
  response_payload JSON NULL,
  approved_at DATETIME NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_payments_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
  INDEX idx_payments_order(order_id),
  INDEX idx_payments_transaction(transaction_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE ads (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  store_id BIGINT UNSIGNED NULL,
  title VARCHAR(160) NOT NULL,
  video_url VARCHAR(500) NOT NULL,
  landing_url VARCHAR(500) NOT NULL,
  placement ENUM('customer_reward') NOT NULL DEFAULT 'customer_reward',
  status ENUM('active','paused') NOT NULL DEFAULT 'active',
  starts_at DATETIME NULL,
  ends_at DATETIME NULL,
  weight INT NOT NULL DEFAULT 10,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_ads_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE SET NULL,
  CONSTRAINT fk_ads_user FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL,
  INDEX idx_ads_active(status, starts_at, ends_at),
  INDEX idx_ads_store(store_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE audit_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  store_id BIGINT UNSIGNED NULL,
  user_id BIGINT UNSIGNED NULL,
  actor_role VARCHAR(40) NULL,
  action VARCHAR(120) NOT NULL,
  entity_type VARCHAR(80) NULL,
  entity_id VARCHAR(80) NULL,
  ip_hash CHAR(64) NULL,
  user_agent_hash CHAR(64) NULL,
  metadata JSON NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_audit_store_created(store_id, created_at),
  INDEX idx_audit_action(action),
  CONSTRAINT fk_audit_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE SET NULL,
  CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE system_logs (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  level VARCHAR(20) NOT NULL,
  channel VARCHAR(60) NOT NULL,
  message TEXT NOT NULL,
  context JSON NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_system_logs_created(created_at),
  INDEX idx_system_logs_level(level)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE rate_limits (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  route_key VARCHAR(160) NOT NULL,
  identifier_hash CHAR(64) NOT NULL,
  attempts INT NOT NULL DEFAULT 1,
  window_started_at DATETIME NOT NULL,
  UNIQUE KEY uq_rate(route_key, identifier_hash),
  INDEX idx_rate_window(window_started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE platform_settings (
  setting_key VARCHAR(120) PRIMARY KEY,
  setting_value JSON NULL,
  updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO stores(id, name, slug, owner_name, phone, address, business_no, service_mode, payment_provider, payment_config, status, ads_enabled, ad_reward_type, ad_reward_value, theme_color, created_at, updated_at)
VALUES
(1, '데모 카페', 'demo-cafe', '홍길동', '02-0000-0000', '서울시 강남구 테헤란로 1', '000-00-00000', 'order_only', 'none', NULL, 'active', 1, 'discount', 500, '#111827', NOW(), NOW());

INSERT INTO categories(id, store_id, name, sort_order, is_active, created_at, updated_at)
VALUES
(1, 1, '커피', 1, 1, NOW(), NOW()),
(2, 1, '디저트', 2, 1, NOW(), NOW());

INSERT INTO menu_items(id, store_id, category_id, name, description, price, image_path, is_soldout, is_active, sort_order, created_at, updated_at)
VALUES
(1, 1, 1, '아메리카노', '깔끔한 산미의 데일리 커피', 4500, NULL, 0, 1, 1, NOW(), NOW()),
(2, 1, 1, '카페라떼', '고소한 우유와 에스프레소', 5200, NULL, 0, 1, 2, NOW(), NOW()),
(3, 1, 2, '치즈케이크', '부드러운 뉴욕 스타일 케이크', 6500, NULL, 0, 1, 3, NOW(), NOW());

INSERT INTO menu_option_groups(id, menu_item_id, name, min_select, max_select, is_required, sort_order, created_at, updated_at)
VALUES
(1, 1, '온도', 1, 1, 1, 1, NOW(), NOW()),
(2, 1, '추가 옵션', 0, 3, 0, 2, NOW(), NOW()),
(3, 2, '온도', 1, 1, 1, 1, NOW(), NOW());

INSERT INTO menu_options(option_group_id, name, price_delta, is_active, sort_order, created_at, updated_at)
VALUES
(1, 'HOT', 0, 1, 1, NOW(), NOW()),
(1, 'ICE', 500, 1, 2, NOW(), NOW()),
(2, '샷 추가', 500, 1, 1, NOW(), NOW()),
(2, '바닐라 시럽', 700, 1, 2, NOW(), NOW()),
(3, 'HOT', 0, 1, 1, NOW(), NOW()),
(3, 'ICE', 500, 1, 2, NOW(), NOW());

INSERT INTO ads(id, store_id, title, video_url, landing_url, placement, status, starts_at, ends_at, weight, created_by, created_at, updated_at)
VALUES
(1, NULL, '샘플 10초 광고', 'https://www.w3schools.com/html/mov_bbb.mp4', 'https://example.com', 'customer_reward', 'active', NULL, NULL, 10, 1, NOW(), NOW());

