-- MySQL 8.0+ schema for ct8 BO (backoffice)
-- 使用現有資料庫 `cs`，在其中建立後台管理所需表

-- Users for BO login
CREATE TABLE IF NOT EXISTS bo_users (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL UNIQUE,
  password_hash VARBINARY(255) NOT NULL,
  display_name VARCHAR(100) NULL,
  role ENUM('admin','editor','viewer') NOT NULL DEFAULT 'editor',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  last_login_at DATETIME NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Refresh tokens for session management
CREATE TABLE IF NOT EXISTS bo_refresh_tokens (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NOT NULL,
  token_hash VARBINARY(255) NOT NULL,
  expires_at DATETIME NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  revoked_at DATETIME NULL,
  user_agent VARCHAR(255) NULL,
  ip_address VARCHAR(45) NULL,
  CONSTRAINT fk_bo_refresh_tokens_user
    FOREIGN KEY (user_id) REFERENCES bo_users(id)
    ON DELETE CASCADE,
  UNIQUE KEY ux_bo_refresh_token_hash (token_hash),
  KEY idx_bo_refresh_user (user_id, expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Batch header: a paste/import session with start/end date and raw text
CREATE TABLE IF NOT EXISTS bo_statement_batches (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NULL,
  start_date DATE NOT NULL,
  end_date DATE NOT NULL,
  note TEXT NULL,
  raw_text MEDIUMTEXT NULL,
  status ENUM('draft','published','archived') NOT NULL DEFAULT 'draft',
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_bo_batches_user
    FOREIGN KEY (created_by) REFERENCES bo_users(id)
    ON DELETE SET NULL,
  KEY idx_bo_batch_date (start_date, end_date),
  KEY idx_bo_batch_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Batch lines: parsed rows with computed total
CREATE TABLE IF NOT EXISTS bo_statement_lines (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  batch_id BIGINT UNSIGNED NOT NULL,
  row_order INT UNSIGNED NULL,
  member VARCHAR(64) NOT NULL,
  bf DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  win DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  lose DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  receive DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  payment DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total DECIMAL(15,2) GENERATED ALWAYS AS (bf + win + lose + receive + payment) STORED,
  remarks VARCHAR(255) NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by BIGINT UNSIGNED NULL,
  CONSTRAINT fk_bo_lines_batch
    FOREIGN KEY (batch_id) REFERENCES bo_statement_batches(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_bo_lines_user
    FOREIGN KEY (updated_by) REFERENCES bo_users(id)
    ON DELETE SET NULL,
  KEY idx_bo_lines_batch_member (batch_id, member),
  KEY idx_bo_lines_member (member)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Convenient view
DROP VIEW IF EXISTS v_bo_statement_lines;
CREATE VIEW v_bo_statement_lines AS
SELECT
  l.id AS line_id,
  l.batch_id,
  b.title,
  b.start_date,
  b.end_date,
  l.row_order,
  l.member,
  l.bf, l.win, l.lose, l.receive, l.payment, l.total,
  l.remarks,
  b.status,
  l.created_at, l.updated_at
FROM bo_statement_lines l
JOIN bo_statement_batches b ON b.id = l.batch_id;

-- Seed an admin (請改為你的雜湊值)
-- INSERT INTO bo_users (username, password_hash, display_name, role) VALUES ('admin', 0x..., 'Administrator', 'admin');

-- Member Groups
CREATE TABLE IF NOT EXISTS bo_member_groups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE,
  description VARCHAR(255) NULL,
  bank_account_text TEXT NULL,
  template_positive MEDIUMTEXT NULL,
  template_negative MEDIUMTEXT NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_bo_member_groups_user
    FOREIGN KEY (created_by) REFERENCES bo_users(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Migration helpers (run once if columns missing)
-- ALTER TABLE bo_member_groups ADD COLUMN bank_account_text TEXT NULL;
-- ALTER TABLE bo_member_groups ADD COLUMN template_positive MEDIUMTEXT NULL;
-- ALTER TABLE bo_member_groups ADD COLUMN template_negative MEDIUMTEXT NULL;

-- NEW: Banks (独立的银行实体，用于分配)
CREATE TABLE IF NOT EXISTS bo_banks (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  code VARCHAR(20) NULL,
  name VARCHAR(120) NOT NULL,
  type ENUM('normal','return') NOT NULL DEFAULT 'normal',
  bank_account_text TEXT NULL,
  template_positive MEDIUMTEXT NULL,
  template_negative MEDIUMTEXT NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  KEY idx_bank_active (is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- NEW: Member Sets（成员集合，行代表 G1/G2/...）
CREATE TABLE IF NOT EXISTS bo_member_sets (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(120) NOT NULL,
  description VARCHAR(255) NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bo_member_set_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  set_id BIGINT UNSIGNED NOT NULL,
  member_name VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_bo_set_member_set FOREIGN KEY (set_id) REFERENCES bo_member_sets(id) ON DELETE CASCADE,
  UNIQUE KEY ux_set_member (set_id, member_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- NEW: 分配（每批次将成员集合分配到某银行）
CREATE TABLE IF NOT EXISTS bo_batch_set_assignment (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  batch_id BIGINT UNSIGNED NOT NULL,
  set_id BIGINT UNSIGNED NOT NULL,
  bank_id BIGINT UNSIGNED NOT NULL,
  assigned_by BIGINT UNSIGNED NULL,
  assigned_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_assign_batch FOREIGN KEY (batch_id) REFERENCES bo_statement_batches(id) ON DELETE CASCADE,
  CONSTRAINT fk_assign_set FOREIGN KEY (set_id) REFERENCES bo_member_sets(id) ON DELETE CASCADE,
  CONSTRAINT fk_assign_bank FOREIGN KEY (bank_id) REFERENCES bo_banks(id) ON DELETE RESTRICT,
  UNIQUE KEY ux_batch_set (batch_id, set_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- NEW: 批次-集合-银行 聚合缓存（可按需重算）
CREATE TABLE IF NOT EXISTS bo_batch_set_totals (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  batch_id BIGINT UNSIGNED NOT NULL,
  set_id BIGINT UNSIGNED NOT NULL,
  bank_id BIGINT UNSIGNED NOT NULL,
  total_winlose DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total_bf DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  total_balance DECIMAL(15,2) NOT NULL DEFAULT 0.00,
  computed_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_tot_batch FOREIGN KEY (batch_id) REFERENCES bo_statement_batches(id) ON DELETE CASCADE,
  CONSTRAINT fk_tot_set FOREIGN KEY (set_id) REFERENCES bo_member_sets(id) ON DELETE CASCADE,
  CONSTRAINT fk_tot_bank FOREIGN KEY (bank_id) REFERENCES bo_banks(id) ON DELETE RESTRICT,
  UNIQUE KEY ux_tot_batch_set_bank (batch_id, set_id, bank_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bo_group_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  group_id BIGINT UNSIGNED NOT NULL,
  member_name VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_bo_group_members_group
    FOREIGN KEY (group_id) REFERENCES bo_member_groups(id)
    ON DELETE CASCADE,
  UNIQUE KEY ux_group_member (group_id, member_name),
  KEY idx_group_id (group_id),
  KEY idx_member_name (member_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


-- Subgroups under a bank group
CREATE TABLE IF NOT EXISTS bo_member_subgroups (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  group_id BIGINT UNSIGNED NOT NULL,
  name VARCHAR(100) NULL,
  sort_order INT UNSIGNED NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_bo_member_subgroups_group
    FOREIGN KEY (group_id) REFERENCES bo_member_groups(id)
    ON DELETE CASCADE,
  KEY idx_subgroups_group (group_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS bo_subgroup_members (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  subgroup_id BIGINT UNSIGNED NOT NULL,
  member_name VARCHAR(100) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_bo_subgroup_members_subgroup
    FOREIGN KEY (subgroup_id) REFERENCES bo_member_subgroups(id)
    ON DELETE CASCADE,
  UNIQUE KEY ux_subgroup_member (subgroup_id, member_name),
  KEY idx_subgroup_id (subgroup_id),
  KEY idx_sub_member_name (member_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


