-- 2. billing：套餐、订阅、开通申请、额度（架构 §3.9，FR-M6-03、05）
-- 来源：docs/architecture/schema-draft.sql（2026-11-03 验证过的草案）
-- 全局表：套餐定义带版本，D2 定价之后改数字就是加一个版本
CREATE TABLE plan (
  code          text NOT NULL CHECK (code IN ('free', 'pro', 'enterprise')),
  version       int  NOT NULL,
  name          text NOT NULL,
  quotas        jsonb NOT NULL,                         -- {"parse": 500, "generate": 300}；检查怎么扣见 data-model §4.3
  limits        jsonb NOT NULL,                         -- 单文件大小、页数、项目数上限
  active        boolean NOT NULL DEFAULT true,
  created_at    timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (code, version)
);

CREATE TABLE activation_request (                       -- 专业版开通申请（G8）；注销后按财务要求保留
  id                uuid PRIMARY KEY,
  tenant_id         uuid NOT NULL REFERENCES tenant (id),
  applicant_user_id uuid NOT NULL REFERENCES user_account (id),
  plan_code         text NOT NULL,
  plan_version      int  NOT NULL,
  invoice_info      jsonb NOT NULL,                     -- 抬头、税号、地址电话、开户行账号
  status            text NOT NULL DEFAULT 'submitted'
                    CHECK (status IN ('submitted', 'paid', 'activated', 'rejected', 'cancelled')),
  ops_note          text,
  handled_by_ops    uuid,
  handled_at        timestamptz,
  invoice_no        text,
  invoiced_at       timestamptz,
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id),
  FOREIGN KEY (plan_code, plan_version) REFERENCES plan (code, version)
);

CREATE TABLE subscription (
  id              uuid PRIMARY KEY,
  tenant_id       uuid NOT NULL REFERENCES tenant (id),
  plan_code       text NOT NULL,
  plan_version    int  NOT NULL,
  status          text NOT NULL DEFAULT 'active'
                  CHECK (status IN ('active', 'expired', 'cancelled')),
  source          text NOT NULL CHECK (source IN ('signup', 'manual', 'online')),
  seats           int  NOT NULL DEFAULT 1 CHECK (seats >= 1),   -- MVP 固定 1（AR11）
  starts_at       timestamptz NOT NULL,
  ends_at         timestamptz,                          -- 免费版为空
  activation_request_id uuid,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id),
  FOREIGN KEY (plan_code, plan_version) REFERENCES plan (code, version),
  FOREIGN KEY (tenant_id, activation_request_id) REFERENCES activation_request (tenant_id, id)
);
-- 同一时间只有一个生效的订阅
CREATE UNIQUE INDEX ON subscription (tenant_id) WHERE status = 'active';

CREATE TABLE quota_account (
  id            uuid PRIMARY KEY,
  tenant_id     uuid NOT NULL REFERENCES tenant (id),
  unit          text NOT NULL CHECK (unit IN ('parse', 'check', 'generate', 'dedupe')),
  balance       int  NOT NULL DEFAULT 0,                -- 缓存，和流水在同一个事务里更新，定期对账
  updated_at    timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id),
  UNIQUE (tenant_id, unit)
);

-- 只追加。amount 是对余额的影响：发放 +N、预扣 −1、退回 +1、结算 0、免扣 0、过期 −余量、调整 ±N
CREATE TABLE quota_entry (
  id                uuid PRIMARY KEY,
  tenant_id         uuid NOT NULL,
  account_id        uuid NOT NULL,
  subscription_id   uuid,                               -- 发放和过期挂在订阅周期上；「本期已用」按它汇总
  kind              text NOT NULL
                    CHECK (kind IN ('grant', 'hold', 'settle', 'release', 'expire', 'adjust', 'waive')),
  amount            int  NOT NULL,
  idempotency_key   text NOT NULL,                      -- 如 parse_job:<id>:hold
  hold_entry_id     uuid,                               -- 结算、退回指回对应的预扣
  ref_type          text CHECK (ref_type IN ('parse_job', 'check_run')),
  ref_id            uuid,                               -- 不加外键：删除项目后流水保留（G10）
  project_id        uuid,                               -- 同上
  reason            text,                               -- 调整必填；免扣写规则，如 recheck_24h
  actor_user_id     uuid REFERENCES user_account (id),
  actor_ops_id      uuid,
  created_at        timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, id),
  UNIQUE (tenant_id, idempotency_key),
  FOREIGN KEY (tenant_id, account_id) REFERENCES quota_account (tenant_id, id),
  FOREIGN KEY (tenant_id, subscription_id) REFERENCES subscription (tenant_id, id),
  FOREIGN KEY (tenant_id, hold_entry_id) REFERENCES quota_entry (tenant_id, id),
  CHECK (kind <> 'adjust' OR reason IS NOT NULL),
  CHECK (kind NOT IN ('settle', 'waive') OR amount = 0),
  CHECK (kind <> 'hold' OR amount < 0),
  CHECK (kind NOT IN ('settle', 'release') OR hold_entry_id IS NOT NULL)
);
CREATE INDEX ON quota_entry (tenant_id, account_id, created_at DESC);
CREATE INDEX ON quota_entry (tenant_id, ref_type, ref_id);
-- 一笔预扣只能有一个结局
CREATE UNIQUE INDEX ON quota_entry (tenant_id, hold_entry_id) WHERE kind IN ('settle', 'release');
CREATE TRIGGER quota_entry_append_only BEFORE UPDATE OR DELETE ON quota_entry
  FOR EACH ROW EXECUTE FUNCTION forbid_change();

CREATE TABLE quota_alert (                              -- 80% / 100% 每个周期只提醒一次
  tenant_id         uuid NOT NULL,
  account_id        uuid NOT NULL,
  subscription_id   uuid NOT NULL,
  threshold         int  NOT NULL CHECK (threshold IN (80, 100)),
  sent_at           timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, account_id, subscription_id, threshold),
  FOREIGN KEY (tenant_id, account_id) REFERENCES quota_account (tenant_id, id),
  FOREIGN KEY (tenant_id, subscription_id) REFERENCES subscription (tenant_id, id)
);