SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS=0;

DROP TABLE IF EXISTS rate_limits;
DROP TABLE IF EXISTS system_logs;
DROP TABLE IF EXISTS audit_logs;
DROP TABLE IF EXISTS ad_events;
DROP TABLE IF EXISTS ads;
DROP TABLE IF EXISTS payments;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS menu_options;
DROP TABLE IF EXISTS menu_option_groups;
DROP TABLE IF EXISTS menu_items;
DROP TABLE IF EXISTS categories;
DROP TABLE IF EXISTS restaurant_tables;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS stores;
DROP TABLE IF EXISTS platform_settings;

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 users (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  store_id BIGINT UNSIGNED NULL,
  role ENUM('super_admin','store_admin','staff') NOT NULL,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  failed_login_count INT NOT NULL DEFAULT 0,
  locked_until DATETIME NULL,
  last_login_at DATETIME NULL,
  status ENUM('active','paused') NOT NULL DEFAULT 'active',
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_users_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE SET NULL,
  INDEX idx_users_store(store_id),
  INDEX idx_users_role(role)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE restaurant_tables (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  store_id BIGINT UNSIGNED NOT NULL,
  label VARCHAR(60) NOT NULL,
  token VARCHAR(64) NOT NULL UNIQUE,
  capacity 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_tables_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE,
  INDEX idx_tables_store(store_id)
) 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 orders (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  store_id BIGINT UNSIGNED NOT NULL,
  table_id BIGINT UNSIGNED NOT NULL,
  table_label VARCHAR(60) NOT NULL,
  order_no VARCHAR(40) NOT NULL UNIQUE,
  customer_note TEXT NULL,
  status ENUM('pending','accepted','preparing','ready','served','canceled','paid') NOT NULL DEFAULT 'pending',
  payment_status ENUM('not_required','pending','approved','failed','refunded','canceled') NOT NULL DEFAULT 'not_required',
  payment_provider ENUM('none','mock','kakao','naver','toss','free') NOT NULL DEFAULT 'none',
  subtotal INT NOT NULL DEFAULT 0,
  discount_amount INT NOT NULL DEFAULT 0,
  total_amount INT NOT NULL DEFAULT 0,
  ad_reward_applied TINYINT(1) NOT NULL DEFAULT 0,
  ad_session_id VARCHAR(80) NULL,
  source_ip_hash CHAR(64) NULL,
  user_agent_hash CHAR(64) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NOT NULL,
  CONSTRAINT fk_orders_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE CASCADE,
  CONSTRAINT fk_orders_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE RESTRICT,
  INDEX idx_orders_store_created(store_id, created_at),
  INDEX idx_orders_status(status),
  INDEX idx_orders_payment(payment_status)
) 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 ad_events (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  ad_id BIGINT UNSIGNED NOT NULL,
  store_id BIGINT UNSIGNED NULL,
  table_id BIGINT UNSIGNED NULL,
  order_id BIGINT UNSIGNED NULL,
  event_type ENUM('impression','complete','click','reward') NOT NULL,
  ip_hash CHAR(64) NULL,
  user_agent_hash CHAR(64) NULL,
  session_id VARCHAR(80) NULL,
  created_at DATETIME NOT NULL,
  CONSTRAINT fk_ad_events_ad FOREIGN KEY (ad_id) REFERENCES ads(id) ON DELETE CASCADE,
  CONSTRAINT fk_ad_events_store FOREIGN KEY (store_id) REFERENCES stores(id) ON DELETE SET NULL,
  CONSTRAINT fk_ad_events_table FOREIGN KEY (table_id) REFERENCES restaurant_tables(id) ON DELETE SET NULL,
  CONSTRAINT fk_ad_events_order FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE SET NULL,
  INDEX idx_ad_events_store_type(store_id, event_type, created_at),
  INDEX idx_ad_events_session(session_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 users(id, store_id, role, name, email, password_hash, status, created_at, updated_at)
VALUES
(1, NULL, 'super_admin', '플랫폼 관리자', 'admin@qr.test', '$2y$12$WbyLNXNtstRqrXiPQNQjSupLZufQv2gNs/vUh0Dw1dyV3a7jHePNy', 'active', NOW(), NOW()),
(2, 1, 'store_admin', '데모 카페 관리자', 'store@qr.test', '$2y$12$VDmub7.w4uzJn7fId8teSOrBs6a5nLiJLWFYacJkijjX/XcJaa.Si', 'active', NOW(), NOW());

INSERT INTO restaurant_tables(id, store_id, label, token, capacity, is_active, created_at, updated_at)
VALUES
(1, 1, 'A-01', 'demo-table-a01-token-change-me', 2, 1, NOW(), NOW()),
(2, 1, 'A-02', 'demo-table-a02-token-change-me', 4, 1, 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());

SET FOREIGN_KEY_CHECKS=1;