-- Migration: 013_add_appeals
-- Description: 申诉复议表
-- Date: 2026-08-01

CREATE TABLE IF NOT EXISTS appeals (
  id         INTEGER PRIMARY KEY AUTOINCREMENT,
  code       TEXT    NOT NULL UNIQUE,
  record_id  TEXT    NOT NULL REFERENCES work_records(id),
  appellant  TEXT    NOT NULL,
  station    TEXT    NOT NULL,
  reason     TEXT    NOT NULL,
  status     TEXT    NOT NULL DEFAULT 'pending',
  handler    TEXT,
  handler_role TEXT,
  response   TEXT,
  created_at TEXT    NOT NULL DEFAULT (datetime('now', 'localtime')),
  updated_at TEXT    NOT NULL DEFAULT (datetime('now', 'localtime')),
  handled_at TEXT
);

CREATE INDEX IF NOT EXISTS idx_appeals_record ON appeals(record_id);
CREATE INDEX IF NOT EXISTS idx_appeals_status ON appeals(status);
CREATE INDEX IF NOT EXISTS idx_appeals_appellant ON appeals(appellant);