-- Migration: 007_create_rules
-- Description: 考核规则及指标项表
-- Date: 2026-08-01
-- Refs: pmdocs/changes/2026-08-01-001-V0.2考核规则配置.md

CREATE TABLE IF NOT EXISTS rules (
  id           INTEGER PRIMARY KEY AUTOINCREMENT,
  code         TEXT    NOT NULL UNIQUE,
  name         TEXT    NOT NULL,
  description  TEXT,
  period_type  TEXT    NOT NULL,
  period_start TEXT,
  period_end   TEXT,
  status       TEXT    NOT NULL DEFAULT 'draft',
  version      INTEGER NOT NULL DEFAULT 1,
  parent_id    INTEGER REFERENCES rules(id),
  created_by   TEXT,
  created_at   TEXT    NOT NULL DEFAULT (datetime('now', 'localtime')),
  updated_at   TEXT    NOT NULL DEFAULT (datetime('now', 'localtime'))
);

CREATE TABLE IF NOT EXISTS rule_items (
  id            INTEGER PRIMARY KEY AUTOINCREMENT,
  rule_id       INTEGER NOT NULL REFERENCES rules(id) ON DELETE CASCADE,
  category      TEXT    NOT NULL,
  name          TEXT    NOT NULL,
  metric_key    TEXT    NOT NULL,
  weight        REAL    NOT NULL,
  min_score     REAL    NOT NULL DEFAULT 0,
  max_score     REAL    NOT NULL DEFAULT 100,
  formula_type  TEXT    NOT NULL,
  formula_params TEXT,
  display_order INTEGER NOT NULL DEFAULT 0,
  enabled       INTEGER NOT NULL DEFAULT 1,
  created_at    TEXT    NOT NULL DEFAULT (datetime('now', 'localtime')),
  updated_at    TEXT    NOT NULL DEFAULT (datetime('now', 'localtime'))
);

CREATE INDEX IF NOT EXISTS idx_rules_status ON rules(status);
CREATE INDEX IF NOT EXISTS idx_rules_period ON rules(period_start, period_end);
CREATE INDEX IF NOT EXISTS idx_rule_items_rule ON rule_items(rule_id);
CREATE INDEX IF NOT EXISTS idx_rule_items_category ON rule_items(category);