-- Migration: 008_create_scores
-- Description: 考核评分结果表
-- Date: 2026-08-01
-- Refs: pmdocs/changes/2026-08-01-001-V0.2考核规则配置.md

CREATE TABLE IF NOT EXISTS scores (
  id               INTEGER PRIMARY KEY AUTOINCREMENT,
  code             TEXT    NOT NULL UNIQUE,
  rule_id          INTEGER NOT NULL REFERENCES rules(id),
  reporter         TEXT    NOT NULL,
  station          TEXT    NOT NULL,
  period           TEXT    NOT NULL,
  period_type      TEXT    NOT NULL,
  total_score      REAL,
  quality_score    REAL,
  quantity_score   REAL,
  efficiency_score REAL,
  compliance_score REAL,
  item_details     TEXT,
  computed_at      TEXT,
  created_at       TEXT    NOT NULL DEFAULT (datetime('now', 'localtime')),
  updated_at       TEXT    NOT NULL DEFAULT (datetime('now', 'localtime'))
);

CREATE INDEX IF NOT EXISTS idx_scores_reporter ON scores(reporter);
CREATE INDEX IF NOT EXISTS idx_scores_station ON scores(station);
CREATE INDEX IF NOT EXISTS idx_scores_period ON scores(period);
CREATE INDEX IF NOT EXISTS idx_scores_rule ON scores(rule_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_scores_unique ON scores(reporter, station, period);