ReporterStationManagementSystem/migrations/001_init_schema.sql

-- Migration: 001_init_schema
-- Description: 初始化基础表结构（从 server/index.js 迁移）
-- Date: 2026-08-01

PRAGMA journal_mode = WAL;
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS schema_migrations (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  name       TEXT    NOT NULL UNIQUE,
  applied_at TEXT    NOT NULL DEFAULT (datetime('now', 'localtime'))
);

CREATE TABLE IF NOT EXISTS work_records (
  id             TEXT PRIMARY KEY,
  title          TEXT NOT NULL,
  type           TEXT NOT NULL,
  reporter       TEXT NOT NULL,
  station        TEXT NOT NULL,
  occurred_date  TEXT NOT NULL,
  platform       TEXT NOT NULL,
  status         TEXT NOT NULL,
  score          INTEGER,
  description    TEXT,
  review_note    TEXT,
  updated_at     TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  record_id  TEXT    NOT NULL REFERENCES work_records(id),
  actor_role TEXT    NOT NULL,
  actor_name TEXT    NOT NULL,
  action     TEXT    NOT NULL,
  from_status TEXT,
  to_status  TEXT    NOT NULL,
  score      INTEGER,
  note       TEXT,
  created_at TEXT    NOT NULL DEFAULT CURRENT_TIMESTAMP
);