-- AyaPolaris schema v3 (2026-07-08)
-- 全新庫，不碰 sdlp/sdlp2/sdlp3。型別對齊 unidata（class SMALLINT / seatno TINYINT / sid VARCHAR(10)）
CREATE DATABASE IF NOT EXISTS ayapolaris
  DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE ayapolaris;

-- ── 計畫主檔（一列＝一個計畫的當前版）────────────────────────
CREATE TABLE plans (
  plan_id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,          -- 計畫序號
  sid            VARCHAR(10)  NOT NULL,                            -- 學號（unidata.t_stddata.sid）
  class          SMALLINT UNSIGNED,                                -- 建檔快照，不回刷（101/201/301）
  seatno         TINYINT UNSIGNED,
  stdname        VARCHAR(20),
  grade_at_apply TINYINT UNSIGNED NOT NULL,                        -- 申請當下年級 1/2
  target_term    ENUM('G1','G2S1','G2S2','G3S1') NOT NULL,        -- 計畫歸屬
  school_year    SMALLINT UNSIGNED NOT NULL,                       -- 學年（民國）如 115
  status         ENUM('pending','revise','approved_unsigned','approved','rejected','expired')
                 NOT NULL DEFAULT 'pending',
  expired_from   ENUM('pending','revise','approved_unsigned') NULL, -- 過期原因（hover 顯示）
  version        INT UNSIGNED NOT NULL DEFAULT 1,
  apply_start    DATE NULL,
  apply_end      DATE NULL,
  exec_end_date  DATE NULL,                                        -- 通過時寫入
  -- 申請內容（當前版）
  planname       VARCHAR(100),
  refsubject     VARCHAR(100),                                     -- 相關學科/領域（G1 也填）
  costd          VARCHAR(200) NOT NULL DEFAULT '無',               -- 共學同學顯示文字（姓名快照）
  costd_sids     JSON NULL,                                        -- 共學同學學號陣列（結構化）
  copro          VARCHAR(100),                                     -- 協助專家
  eq             VARCHAR(500),                                     -- 使用設備
  plancontent    TEXT,                                             -- 計畫摘要
  presentation   VARCHAR(500),                                     -- 預期成果
  studyhour      TINYINT UNSIGNED NOT NULL DEFAULT 1,              -- 申請節數（G1 固定 1）
  reinforce      TINYINT UNSIGNED NULL,                            -- G3S1 充補課 bitmask 國1/英2/數4
  special_venue_id  INT UNSIGNED NULL,                             -- 申請的特殊場地
  assigned_venue_id INT UNSIGNED NULL,                             -- 管理端分配的學習場地
  share          ENUM('private','public') NOT NULL DEFAULT 'private',
  approved_at    DATETIME NULL,
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY uq_one_per_window (sid, target_term, school_year),    -- 一窗一計畫
  KEY idx_status (status),
  KEY idx_term_year (target_term, school_year),
  KEY idx_class (class),
  KEY idx_special_venue (special_venue_id),
  KEY idx_assigned_venue (assigned_venue_id)
) ENGINE=InnoDB;

-- ── 週次（固定 1..18，無日期）────────────────────────────────
CREATE TABLE plan_weeks (
  plan_id   INT UNSIGNED NOT NULL,
  week_no   TINYINT UNSIGNED NOT NULL,
  schedule  TEXT,                          -- 預計進度（隨版本編輯）
  note      TEXT,                          -- 備註說明（隨版本編輯）
  progress  TEXT,                          -- 通過後實際執行進度
  filled_at DATE NULL,
  PRIMARY KEY (plan_id, week_no),
  CONSTRAINT fk_weeks_plan FOREIGN KEY (plan_id) REFERENCES plans(plan_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── 成果（通過後）──────────────────────────────────────────
CREATE TABLE plan_outcomes (
  plan_id     INT UNSIGNED PRIMARY KEY,
  outcome_url VARCHAR(600),
  impression  TEXT,
  updated_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  CONSTRAINT fk_outcome_plan FOREIGN KEY (plan_id) REFERENCES plans(plan_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── 版本備份（升版時搬入）──────────────────────────────────
CREATE TABLE plan_history (
  hist_id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  plan_id            INT UNSIGNED NOT NULL,
  version_no         INT UNSIGNED NOT NULL,
  content            JSON NOT NULL,               -- 申請欄位＋18 週 schedule/note 快照
  version_started_at DATETIME,                    -- 該版起始（v1=建檔 / vN=升版）
  review_decision    ENUM('revise','approved','rejected') NULL,
  review_opinion     TEXT,
  review_comments    JSON NULL,
  reviewer           VARCHAR(20),
  reviewed_at        DATETIME NULL,
  archived_at        DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY uq_plan_ver (plan_id, version_no),
  CONSTRAINT fk_hist_plan FOREIGN KEY (plan_id) REFERENCES plans(plan_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── 當前版審查 ────────────────────────────────────────────
CREATE TABLE reviews (
  plan_id     INT UNSIGNED PRIMARY KEY,
  version_no  INT UNSIGNED,
  decision    ENUM('revise','approved','rejected') NULL,
  opinion     TEXT,
  reviewer    VARCHAR(20),
  reviewed_at DATETIME NULL,
  CONSTRAINT fk_review_plan FOREIGN KEY (plan_id) REFERENCES plans(plan_id) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE review_comments (
  id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  plan_id   INT UNSIGNED NOT NULL,
  field_key VARCHAR(30) NOT NULL,     -- 'planname' / 'plancontent' / 'week:3' …
  comment   TEXT,
  KEY idx_plan (plan_id),
  CONSTRAINT fk_rc_plan FOREIGN KEY (plan_id) REFERENCES plans(plan_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── 線上簽名（落地存檔）────────────────────────────────────
CREATE TABLE signatures (
  plan_id    INT UNSIGNED NOT NULL,
  role       ENUM('student','parent') NOT NULL,
  image_path VARCHAR(200) NOT NULL,
  image_hash CHAR(64),
  signed_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (plan_id, role),
  CONSTRAINT fk_sign_plan FOREIGN KEY (plan_id) REFERENCES plans(plan_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── 場地（特殊場地＋學習場地同一組）────────────────────────
CREATE TABLE venues (
  venue_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  name     VARCHAR(50) NOT NULL,
  capacity INT UNSIGNED NULL,          -- 容量上限（NULL=不限）
  active   TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB;

-- ── 審核教師（來源 unidata.t_member）────────────────────────
CREATE TABLE reviewers (
  reviewer_id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  account     VARCHAR(20) NOT NULL,    -- t_member.ldap（Google email 前綴）
  name        VARCHAR(41),             -- lastname+firstname 快照
  UNIQUE KEY uq_account (account)
) ENGINE=InnoDB;

CREATE TABLE reviewer_scopes (
  reviewer_id INT UNSIGNED NOT NULL,
  scope_type  ENUM('class','venue') NOT NULL,
  scope_value VARCHAR(50) NOT NULL,    -- class：班級號字串；venue：venue_id 字串
  PRIMARY KEY (reviewer_id, scope_type, scope_value),
  CONSTRAINT fk_scope_reviewer FOREIGN KEY (reviewer_id) REFERENCES reviewers(reviewer_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── 他人計畫互動 ──────────────────────────────────────────
CREATE TABLE share_ratings (
  plan_id    INT UNSIGNED NOT NULL,
  rater_sid  VARCHAR(20) NOT NULL,     -- 學生 sid 或教師 account
  stars      TINYINT UNSIGNED NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (plan_id, rater_sid),
  CONSTRAINT fk_rating_plan FOREIGN KEY (plan_id) REFERENCES plans(plan_id) ON DELETE CASCADE,
  CONSTRAINT chk_stars CHECK (stars BETWEEN 1 AND 5)
) ENGINE=InnoDB;

CREATE TABLE share_comments (
  comment_id     INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  plan_id        INT UNSIGNED NOT NULL,
  commenter_sid  VARCHAR(20) NOT NULL,
  commenter_name VARCHAR(41),
  body           TEXT,
  status         ENUM('published','pending_ai','removed') NOT NULL DEFAULT 'published',
  created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_plan (plan_id),
  CONSTRAINT fk_comment_plan FOREIGN KEY (plan_id) REFERENCES plans(plan_id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ── 檔案登記 ──────────────────────────────────────────────
CREATE TABLE files (
  fid         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  plan_id     INT UNSIGNED NULL,
  kind        ENUM('sign','other') NOT NULL DEFAULT 'sign',
  file_name   VARCHAR(300),
  file_size   INT UNSIGNED,
  web_path    VARCHAR(200),
  system_path VARCHAR(200),
  uploaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_plan (plan_id)
) ENGINE=InnoDB;

-- ── 系統參數 ──────────────────────────────────────────────
CREATE TABLE settings (
  skey   VARCHAR(50) PRIMARY KEY,
  svalue VARCHAR(200),
  note   VARCHAR(200)
) ENGINE=InnoDB;

-- ── 申請窗規則（可調日期；程式預設值見 §3.1）──────────────
CREATE TABLE apply_windows (
  grade         TINYINT UNSIGNED NOT NULL,
  window_start  CHAR(5) NOT NULL,        -- 'MM-DD'
  window_end    CHAR(5) NOT NULL,
  target_term   ENUM('G1','G2S1','G2S2','G3S1') NOT NULL,
  exec_end_rule VARCHAR(20) NOT NULL,    -- '06-30' / '+1Y02-28' / '07-31'
  PRIMARY KEY (grade, window_start)
) ENGINE=InnoDB;

-- ── 審計 ──────────────────────────────────────────────────
CREATE TABLE audit_log (
  id         INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  actor      VARCHAR(50),
  action     VARCHAR(30),
  plan_id    INT UNSIGNED NULL,
  detail     JSON NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_actor (actor), KEY idx_plan (plan_id), KEY idx_created (created_at)
) ENGINE=InnoDB;

-- ── 種子資料 ──────────────────────────────────────────────
INSERT INTO settings (skey, svalue, note) VALUES
  ('weeknum', '18', '每個計畫的週次數（固定 18 週，無日期）'),
  ('planword', '100', '計畫摘要最少字數'),
  ('impressionword', '600', '成果心得最少字數'),
  ('current_school_year', '115', '目前學年（民國）');

-- 申請窗規則（§3.1）：exec_end_rule = 執行結束日規則
INSERT INTO apply_windows (grade, window_start, window_end, target_term, exec_end_rule) VALUES
  (1, '09-01', '03-31', 'G1',   '06-30'),      -- 一年級 9/1~隔年3/31 → G1，執行至當學年 6/30
  (1, '04-01', '06-30', 'G2S1', '+1Y02-28'),   -- 一年級 4/1~6/30 → 二上，執行至隔年 2/28
  (2, '09-01', '02-28', 'G2S2', '07-31'),      -- 二年級 9/1~隔年2/28 → 二下，執行至當學年 7/31
  (2, '04-01', '06-30', 'G3S1', '+1Y02-28');   -- 二年級 4/1~6/30 → 三上，執行至隔年 2/28
