-- 共享主機：請在 phpMyAdmin 選好資料庫後再匯入（勿 CREATE DATABASE／USE；精靈亦會略過）。
-- 若需含建庫語句的完整腳本，可改匯入 sql/install.sql。

CREATE TABLE IF NOT EXISTS `members` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(80) NOT NULL,
  `password` VARCHAR(255) NOT NULL COMMENT 'password_hash bcrypt 等',
  `display_name` VARCHAR(120) NOT NULL DEFAULT '',
  `email` VARCHAR(254) NULL DEFAULT NULL,
  `email_verified_at` DATETIME NULL DEFAULT NULL,
  `member_tier` VARCHAR(16) NOT NULL DEFAULT 'register' COMMENT 'register／formal／blacklist',
  `approved_at` DATETIME NULL DEFAULT NULL COMMENT '管理員人工審核通過時間（NULL=待審核）',
  `approved_by` INT UNSIGNED NULL DEFAULT NULL COMMENT '審核通過之管理員 members.id',
  `status` TINYINT NOT NULL DEFAULT 1 COMMENT '1=可登入',
  `role` VARCHAR(32) NOT NULL DEFAULT '' COMMENT 'admin 等',
  `ncoin` INT NOT NULL DEFAULT 0 COMMENT '站內貨幣餘額',
  `last_login_at` DATETIME DEFAULT NULL,
  `last_login_ip` VARCHAR(45) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_members_username` (`username`),
  UNIQUE KEY `uq_members_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_member_email_verify_tokens` (
  `member_id` INT UNSIGNED NOT NULL,
  `token_hash` CHAR(64) NOT NULL COMMENT 'SHA-256 hex',
  `code_hash` CHAR(64) NULL DEFAULT NULL COMMENT 'SHA-256 hex of 6-digit verify code',
  `expires_at` DATETIME NOT NULL,
  `sent_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`),
  UNIQUE KEY `uq_fevt_hash` (`token_hash`),
  KEY `idx_fevt_exp` (`expires_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_site_member_profiles` (
  `member_id` INT UNSIGNED NOT NULL,
  `avatar_rel` VARCHAR(512) NOT NULL DEFAULT '',
  `masseur_favorites_public` TINYINT NOT NULL DEFAULT 0,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `spa_store_snapshots` (
  `spa_store_id` INT UNSIGNED NOT NULL COMMENT 'spa_stores.id 或 manual 自增 id',
  `synced_at` DATETIME NOT NULL COMMENT '本站最後寫入時間',
  `source_data_as_of_unix` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT 'API meta.data_as_of_unix',
  `source_data_as_of_iso` VARCHAR(40) NULL DEFAULT NULL COMMENT 'API meta.data_as_of_iso',
  `meta_store_updated_at` VARCHAR(64) NULL DEFAULT NULL,
  `meta_place_last_updated` VARCHAR(64) NULL DEFAULT NULL,
  `google_reviews_source` VARCHAR(32) NULL DEFAULT NULL COMMENT 'place_data / google_reviews_table',
  `google_reviews_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `google_reviews_table_rows` INT UNSIGNED NULL DEFAULT NULL,
  `google_reviews_table_max_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `store_name` VARCHAR(255) NOT NULL DEFAULT '' COMMENT '店面名稱',
  `display_address` TEXT NULL COMMENT '組合或格式化地址',
  `city_name` VARCHAR(120) NULL DEFAULT NULL,
  `district_name` VARCHAR(120) NULL DEFAULT NULL,
  `street_address` VARCHAR(512) NULL DEFAULT NULL,
  `place_formatted_address` TEXT NULL,
  `google_place_id` VARCHAR(255) NULL DEFAULT NULL,
  `google_map_location` VARCHAR(255) NULL DEFAULT NULL,
  `latitude` DECIMAL(10,7) NULL DEFAULT NULL,
  `longitude` DECIMAL(10,7) NULL DEFAULT NULL,
  `place_rating` DECIMAL(4,2) NULL DEFAULT NULL,
  `place_ratings_total` INT UNSIGNED NULL DEFAULT NULL,
  `phone` VARCHAR(80) NULL DEFAULT NULL,
  `mobile` VARCHAR(80) NULL DEFAULT NULL,
  `line` VARCHAR(255) NULL DEFAULT NULL,
  `line_id` VARCHAR(255) NULL DEFAULT NULL,
  `wechat` VARCHAR(255) NULL DEFAULT NULL,
  `wechat_id` VARCHAR(255) NULL DEFAULT NULL,
  `telegram_contact` VARCHAR(255) NULL DEFAULT NULL,
  `tiktok_contact` VARCHAR(255) NULL DEFAULT NULL,
  `email` VARCHAR(255) NULL DEFAULT NULL,
  `website` VARCHAR(512) NULL DEFAULT NULL,
  `facebook` VARCHAR(512) NULL DEFAULT NULL,
  `cover_photo_path` VARCHAR(512) NULL DEFAULT NULL,
  `photos_json` MEDIUMTEXT NULL COMMENT 'API photos[] JSON',
  `place_opening_hours_raw` MEDIUMTEXT NULL,
  `business_hours_json` TEXT NULL COMMENT 'business_hours 結構 JSON',
  `business_hours_start` VARCHAR(64) NULL DEFAULT NULL,
  `business_hours_end` VARCHAR(64) NULL DEFAULT NULL,
  `business_type` VARCHAR(128) NULL DEFAULT NULL,
  `appointment_required` VARCHAR(64) NULL DEFAULT NULL,
  `parking` VARCHAR(255) NULL DEFAULT NULL,
  `payment_method` VARCHAR(255) NULL DEFAULT NULL,
  `spa_type` VARCHAR(128) NULL DEFAULT NULL,
  `basic_charge` DECIMAL(12,2) NULL DEFAULT NULL,
  `prev_store_id` INT UNSIGNED NULL DEFAULT NULL,
  `next_store_id` INT UNSIGNED NULL DEFAULT NULL,
  `public_detail_url_path` VARCHAR(255) NULL DEFAULT NULL,
  `payload_json` LONGTEXT NULL COMMENT '選填：完整 meta+store JSON 或 manual listing_kind',
  `source_type` VARCHAR(20) NOT NULL DEFAULT 'api' COMMENT 'api / manual',
  `is_approved` TINYINT NOT NULL DEFAULT 1 COMMENT 'manual 推薦店家審核',
  `created_member_id` INT UNSIGNED NULL DEFAULT NULL COMMENT 'manual 推薦人',
  PRIMARY KEY (`spa_store_id`),
  KEY `idx_source_ts` (`source_data_as_of_unix`),
  KEY `idx_synced` (`synced_at`),
  KEY `idx_store_name` (`store_name`(128)),
  KEY `idx_city` (`city_name`),
  KEY `idx_is_approved` (`is_approved`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_visit_log` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `viewed_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `client_ip` VARCHAR(45) NOT NULL DEFAULT '',
  `page_key` VARCHAR(128) NOT NULL,
  `page_title` VARCHAR(255) NULL DEFAULT NULL,
  `member_id` BIGINT UNSIGNED NULL,
  PRIMARY KEY (`id`),
  KEY `idx_page_key` (`page_key`),
  KEY `idx_viewed_at` (`viewed_at`),
  KEY `idx_page_viewed` (`page_key`, `viewed_at`),
  KEY `idx_ip_viewed` (`client_ip`, `viewed_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_visitor_settings` (
  `id` TINYINT UNSIGNED NOT NULL PRIMARY KEY,
  `online_window_minutes` SMALLINT UNSIGNED NOT NULL DEFAULT 10,
  `peak_online` INT UNSIGNED NOT NULL DEFAULT 0,
  `peak_online_at` DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `fate_visitor_settings` (`id`, `online_window_minutes`, `peak_online`, `peak_online_at`)
VALUES (1, 10, 0, NULL);

CREATE TABLE IF NOT EXISTS `fate_reward_ledger` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `member_id` INT UNSIGNED NOT NULL,
  `action` VARCHAR(32) NOT NULL,
  `ref_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `amount` INT NOT NULL DEFAULT 0,
  `applied_local` TINYINT NOT NULL DEFAULT 0,
  `applied_spa` TINYINT NOT NULL DEFAULT 0,
  `reverted` TINYINT NOT NULL DEFAULT 0,
  `reverted_at` DATETIME NULL DEFAULT NULL,
  `note` VARCHAR(255) NOT NULL DEFAULT '',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_action_ref_member` (`action`, `ref_id`, `member_id`),
  KEY `idx_member` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_ncoin_transfers` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `from_member_id` INT UNSIGNED NOT NULL,
  `to_member_id` INT UNSIGNED NOT NULL,
  `amount_gross` INT UNSIGNED NOT NULL,
  `fee_amount` INT UNSIGNED NOT NULL DEFAULT 0,
  `amount_net` INT UNSIGNED NOT NULL,
  `fee_pct` TINYINT UNSIGNED NOT NULL DEFAULT 0,
  `note` VARCHAR(120) NOT NULL DEFAULT '',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_from` (`from_member_id`),
  KEY `idx_to` (`to_member_id`),
  KEY `idx_created` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_store_masseurs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `spa_store_id` INT UNSIGNED NOT NULL,
  `name` VARCHAR(80) NOT NULL,
  `name_norm` VARCHAR(80) NOT NULL,
  `gender` ENUM('female','male','other') NOT NULL DEFAULT 'female',
  `photo_rel` VARCHAR(512) NOT NULL DEFAULT '',
  `created_by_member_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `like_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `dislike_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `favorite_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_hidden` TINYINT NOT NULL DEFAULT 0,
  `birth_year` SMALLINT UNSIGNED NULL DEFAULT NULL,
  `measure_bust` SMALLINT UNSIGNED NULL DEFAULT NULL,
  `measure_waist` SMALLINT UNSIGNED NULL DEFAULT NULL,
  `measure_hip` SMALLINT UNSIGNED NULL DEFAULT NULL,
  `cup_size` VARCHAR(8) NULL DEFAULT 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 `uq_store_name` (`spa_store_id`, `name_norm`),
  KEY `idx_store` (`spa_store_id`, `is_hidden`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_masseur_votes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `masseur_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED NOT NULL,
  `vote` TINYINT NOT NULL DEFAULT 1,
  `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 `uq_vote` (`masseur_id`, `member_id`),
  KEY `idx_masseur` (`masseur_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_masseur_reviews` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `masseur_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED NOT NULL,
  `body` TEXT NOT NULL,
  `is_hidden` TINYINT NOT NULL DEFAULT 0,
  `edited_by_admin` TINYINT 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`),
  KEY `idx_masseur` (`masseur_id`, `is_hidden`, `id`),
  KEY `idx_member_masseur` (`member_id`, `masseur_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_masseur_tag_topics` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(32) NOT NULL,
  `title` VARCHAR(80) NOT NULL,
  `description` VARCHAR(2000) NOT NULL DEFAULT '',
  `allow_multiple` TINYINT NOT NULL DEFAULT 0,
  `sort` INT NOT NULL DEFAULT 0,
  `is_hidden` TINYINT 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`),
  UNIQUE KEY `uq_slug` (`slug`),
  KEY `idx_visible` (`is_hidden`, `sort`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_masseur_tags` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `topic_id` INT UNSIGNED NOT NULL,
  `label` VARCHAR(48) NOT NULL,
  `sort` INT NOT NULL DEFAULT 0,
  `is_hidden` TINYINT 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`),
  KEY `idx_topic` (`topic_id`, `is_hidden`, `sort`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_masseur_tag_picks` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `masseur_id` INT UNSIGNED NOT NULL,
  `tag_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_pick` (`masseur_id`, `tag_id`, `member_id`),
  KEY `idx_masseur` (`masseur_id`),
  KEY `idx_tag` (`tag_id`),
  KEY `idx_member` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `fate_masseur_tag_topics` (`slug`, `title`, `allow_multiple`, `sort`)
VALUES
  ('info', '技師情報', 1, 10),
  ('service', '服務', 0, 20);

CREATE TABLE IF NOT EXISTS `fate_masseur_weekly_recommends` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `masseur_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED NOT NULL,
  `week_start` DATE NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_member_masseur_week` (`member_id`, `masseur_id`, `week_start`),
  KEY `idx_week_masseur` (`week_start`, `masseur_id`),
  KEY `idx_member_week` (`member_id`, `week_start`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_masseur_favorites` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `member_id` INT UNSIGNED NOT NULL,
  `masseur_id` INT UNSIGNED NOT NULL,
  `favorited_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `is_experienced` TINYINT NOT NULL DEFAULT 0,
  `experienced_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_member_masseur` (`member_id`, `masseur_id`),
  KEY `idx_masseur` (`masseur_id`),
  KEY `idx_member_fav` (`member_id`, `favorited_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_store_reviews` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `spa_store_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED NOT NULL,
  `body` TEXT NOT NULL,
  `is_hidden` TINYINT NOT NULL DEFAULT 0,
  `edited_by_admin` TINYINT 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`),
  KEY `idx_store` (`spa_store_id`, `is_hidden`, `id`),
  KEY `idx_member_store` (`member_id`, `spa_store_id`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_crowd_reports` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `entity_type` VARCHAR(16) NOT NULL,
  `entity_id` INT UNSIGNED NOT NULL,
  `report_key` VARCHAR(32) NOT NULL,
  `member_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_crowd_report` (`entity_type`, `entity_id`, `report_key`, `member_id`),
  KEY `idx_crowd_entity` (`entity_type`, `entity_id`, `report_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_forum_boards` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `slug` VARCHAR(48) NOT NULL,
  `kind` VARCHAR(16) NOT NULL DEFAULT 'general',
  `title` VARCHAR(80) NOT NULL,
  `description` VARCHAR(255) NOT NULL DEFAULT '',
  `sort` INT NOT NULL DEFAULT 0,
  `is_hidden` TINYINT NOT NULL DEFAULT 0,
  `rules_html` MEDIUMTEXT NULL,
  `announcement_html` MEDIUMTEXT NULL,
  `announcement_updated_at` DATETIME NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_forum_board_moderators` (
  `board_id` INT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED NOT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`board_id`, `member_id`),
  KEY `idx_member` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_forum_threads` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `board_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `type` VARCHAR(16) NOT NULL DEFAULT 'general',
  `ref_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `title` VARCHAR(255) NOT NULL,
  `author_member_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `reply_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `view_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `last_post_at` DATETIME NULL DEFAULT NULL,
  `last_post_member_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_pinned` TINYINT NOT NULL DEFAULT 0,
  `is_locked` TINYINT NOT NULL DEFAULT 0,
  `is_hidden` TINYINT NOT NULL DEFAULT 0,
  `is_orphan` TINYINT 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`),
  UNIQUE KEY `uq_type_ref` (`type`, `ref_id`),
  KEY `idx_board` (`board_id`, `is_pinned`, `last_post_at`),
  KEY `idx_last` (`last_post_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_forum_posts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `thread_id` INT UNSIGNED NOT NULL,
  `author_member_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `parent_post_id` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `body` MEDIUMTEXT NOT NULL,
  `floor_no` INT UNSIGNED NOT NULL DEFAULT 0,
  `like_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `favorite_count` INT UNSIGNED NOT NULL DEFAULT 0,
  `is_hidden` TINYINT NOT NULL DEFAULT 0,
  `is_paid` TINYINT NOT NULL DEFAULT 0,
  `paid_price` INT UNSIGNED NOT NULL DEFAULT 0,
  `paid_preview_chars` INT UNSIGNED NOT NULL DEFAULT 200,
  `edited_by_admin` TINYINT NOT NULL DEFAULT 0,
  `edited_at` DATETIME NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_thread` (`thread_id`, `floor_no`),
  KEY `idx_author` (`author_member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_forum_post_votes` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id` BIGINT UNSIGNED NOT NULL,
  `member_id` INT UNSIGNED NOT NULL,
  `vote` TINYINT NOT NULL DEFAULT 1,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_post_member` (`post_id`, `member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_forum_post_favorites` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `member_id` INT UNSIGNED NOT NULL,
  `post_id` BIGINT UNSIGNED NOT NULL,
  `favorited_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_member_post` (`member_id`, `post_id`),
  KEY `idx_post` (`post_id`),
  KEY `idx_member_fav` (`member_id`, `favorited_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_forum_reports` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `target_type` VARCHAR(8) NOT NULL DEFAULT 'post',
  `target_id` BIGINT UNSIGNED NOT NULL,
  `reporter_member_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `reason` VARCHAR(255) NOT NULL DEFAULT '',
  `status` VARCHAR(12) NOT NULL DEFAULT 'open',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_forum_paid_purchases` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `post_id` BIGINT UNSIGNED NOT NULL,
  `buyer_member_id` INT UNSIGNED NOT NULL,
  `price_paid` INT UNSIGNED NOT NULL DEFAULT 0,
  `author_payout` INT UNSIGNED NOT NULL DEFAULT 0,
  `platform_fee` INT UNSIGNED NOT NULL DEFAULT 0,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_post_buyer` (`post_id`, `buyer_member_id`),
  KEY `idx_post` (`post_id`),
  KEY `idx_buyer` (`buyer_member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_blog_categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(64) NOT NULL,
  `slug` VARCHAR(64) NOT NULL DEFAULT '',
  `sort_order` INT NOT NULL DEFAULT 0,
  `is_hidden` TINYINT 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`),
  UNIQUE KEY `uq_slug` (`slug`),
  KEY `idx_sort` (`sort_order`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `fate_blog_posts` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT UNSIGNED NULL DEFAULT NULL,
  `author_member_id` INT UNSIGNED NOT NULL DEFAULT 0,
  `title` VARCHAR(200) NOT NULL,
  `slug` VARCHAR(120) NOT NULL DEFAULT '',
  `body_html` MEDIUMTEXT NOT NULL,
  `excerpt` VARCHAR(400) NOT NULL DEFAULT '',
  `is_announcement` TINYINT NOT NULL DEFAULT 0,
  `is_published` TINYINT NOT NULL DEFAULT 1,
  `is_hidden` TINYINT NOT NULL DEFAULT 0,
  `published_at` DATETIME NULL DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_list` (`is_hidden`, `is_published`, `published_at`, `id`),
  KEY `idx_category` (`category_id`, `is_hidden`, `is_published`, `published_at`),
  KEY `idx_announcement` (`is_announcement`, `is_hidden`, `is_published`, `published_at`),
  KEY `idx_slug` (`slug`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO `fate_forum_boards` (`slug`, `kind`, `title`, `description`, `sort`)
VALUES
  ('general', 'general', '綜合討論', '不分店家的閒聊、心得與情報交流。', 10),
  ('store', 'store', '店家討論', '各店家的專屬討論串。', 20),
  ('masseur', 'masseur', '技師討論', '針對個別推薦技師的討論串。', 30);
