selfrelease
38 lines
1.9 KiB
SQL
38 lines
1.9 KiB
SQL
-- 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'); |