-- Migration: 005_create_notices
-- Description: 通知公告及回执表
-- Date: 2026-08-01

CREATE TABLE IF NOT EXISTS notices (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  code         TEXT    NOT NULL UNIQUE,
  title        TEXT    NOT NULL,
  content      TEXT,
  priority     TEXT    NOT NULL DEFAULT 'normal',
  scope        TEXT    NOT NULL DEFAULT 'all',
  stations     TEXT,
  roles        TEXT,
  attachment   TEXT,
  published_by TEXT    NOT NULL,
  published_at TEXT    NOT NULL DEFAULT (datetime('now', 'localtime')),
  created_at   TEXT    NOT NULL DEFAULT (datetime('now', 'localtime')),
  updated_at   TEXT    NOT NULL DEFAULT (datetime('now', 'localtime'))
);

CREATE TABLE IF NOT EXISTS notice_receipts (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  notice_id     INTEGER NOT NULL REFERENCES notices(id),
  receiver_name TEXT    NOT NULL,
  receiver_role TEXT    NOT NULL,
  read          INTEGER NOT NULL DEFAULT 0,
  confirmed     INTEGER NOT NULL DEFAULT 0,
  read_at       TEXT,
  confirmed_at  TEXT,
  created_at    TEXT    NOT NULL DEFAULT (datetime('now', 'localtime'))
);

INSERT OR IGNORE INTO notices
  (code, title, content, priority, scope, published_by, published_at)
VALUES
  ('NOTICE_20260801_001', '关于开展第三季度考核工作的通知', '各记者站请于8月15日前完成第三季度工作记录提交，总部将在8月底完成评分。', 'high', 'all', '林致远', '2026-08-01 09:00:00'),
  ('NOTICE_20260801_002', '记者站年度考核评分标准调整说明', '自本通知发布之日起，新提交的稿件将按更新后的评分标准执行。详见附件。', 'normal', 'station', '林致远', '2026-08-01 10:30:00'),
  ('NOTICE_20260720_001', '暑期安全生产宣传报道提示', '请各站注意收集暑期出行、安全生产等相关素材，及时投稿。', 'normal', 'all', '林致远', '2026-07-20 14:00:00');