-- =============================================================================
-- 迁移：NS4 租户管理后台 — 新建表
-- 日期：2026-03-20
-- 关联 SPEC：tenant-admin-web
-- 需求：15.1, 15.2, 15.3, 15.4
-- 说明：
--   1. auth.tenant_admins — 租户管理员表（独立认证体系）
--   2. biz.excel_upload_log — Excel 上传记录表
--   3. biz.salary_adjustments — 助教奖罚明细表
--   4. biz.stg_finance_expense — 财务支出暂存表
--   5. biz.stg_platform_income — 团购收入暂存表
--   6. biz.stg_recharge_commission — 充值业绩归属暂存表
--   创建顺序：excel_upload_log 先于 salary_adjustments 和 staging 表（FK 依赖）
-- =============================================================================

-- 确保 schema 存在
CREATE SCHEMA IF NOT EXISTS auth;
CREATE SCHEMA IF NOT EXISTS biz;

-- ---------------------------------------------------------------------------
-- 1. auth.tenant_admins — 租户管理员表
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS auth.tenant_admins (
    id              BIGSERIAL       PRIMARY KEY,
    username        VARCHAR(50)     UNIQUE NOT NULL,        -- 登录用户名
    password_hash   VARCHAR(255)    NOT NULL,               -- bcrypt 哈希
    display_name    VARCHAR(100),                           -- 显示名称
    tenant_id       BIGINT          NOT NULL,               -- 所属租户
    managed_site_ids BIGINT[]       NOT NULL,               -- 管辖门店 ID 列表
    is_active       BOOLEAN         DEFAULT true,           -- 账号状态（启用/禁用）
    deleted_at      TIMESTAMPTZ     DEFAULT NULL,           -- 软删除时间戳：NULL=正常，非 NULL=已删除（2026-03-22 新增，与 is_active 分离）
    created_by      BIGINT,                                 -- 创建者（Operator ID）
    created_at      TIMESTAMPTZ     DEFAULT NOW(),          -- 创建时间
    last_login_at   TIMESTAMPTZ                             -- 最后登录时间
);

COMMENT ON TABLE auth.tenant_admins IS '租户管理员表（NS4 独立认证体系，与小程序 auth.users 隔离）';
COMMENT ON COLUMN auth.tenant_admins.username IS '登录用户名，全局唯一';
COMMENT ON COLUMN auth.tenant_admins.password_hash IS 'bcrypt 密码哈希';
COMMENT ON COLUMN auth.tenant_admins.managed_site_ids IS '管辖门店 ID 数组，用于数据隔离';
COMMENT ON COLUMN auth.tenant_admins.tenant_id IS '所属租户 ID';
COMMENT ON COLUMN auth.tenant_admins.is_active IS '账号状态：false=禁用，登录返回 403（仅控制启用/禁用，与软删除无关）';
COMMENT ON COLUMN auth.tenant_admins.deleted_at IS '软删除时间戳：NULL=正常，非 NULL=已删除。删除与禁用分离：is_active 控制启用/禁用，deleted_at 控制软删除（2026-03-22）';

CREATE INDEX IF NOT EXISTS idx_tenant_admin_tenant
    ON auth.tenant_admins (tenant_id);

-- 部分索引：加速列表和登录查询（仅索引未删除记录）（2026-03-22 新增）
CREATE INDEX IF NOT EXISTS idx_tenant_admins_active_not_deleted
    ON auth.tenant_admins (is_active)
    WHERE deleted_at IS NULL;

-- ---------------------------------------------------------------------------
-- 2. biz.excel_upload_log — Excel 上传记录表（必须先于 salary_adjustments 和 staging 表）
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biz.excel_upload_log (
    id              BIGSERIAL       PRIMARY KEY,
    site_id         BIGINT          NOT NULL,               -- 门店 ID
    upload_type     VARCHAR(30)     NOT NULL
                    CHECK (upload_type IN ('expense', 'platform_income', 'salary_adj', 'recharge_commission')),
    file_name       VARCHAR(255)    NOT NULL,               -- 原始文件名
    uploaded_by     BIGINT          NOT NULL,               -- 上传人（管理员 ID）
    row_count       INTEGER         DEFAULT 0,              -- 数据行数
    conflict_count  INTEGER         DEFAULT 0,              -- 冲突行数
    resolved_count  INTEGER         DEFAULT 0,              -- 已解决冲突数
    status          VARCHAR(20)     NOT NULL
                    CHECK (status IN ('pending', 'confirmed', 'failed')),
    error_detail    JSONB,                                  -- 错误详情
    created_at      TIMESTAMPTZ     DEFAULT NOW(),          -- 上传时间
    confirmed_at    TIMESTAMPTZ                             -- 确认时间
);

COMMENT ON TABLE biz.excel_upload_log IS 'Excel 上传记录表（NS4 租户管理后台）';
COMMENT ON COLUMN biz.excel_upload_log.upload_type IS '模板类型：expense/platform_income/salary_adj/recharge_commission';
COMMENT ON COLUMN biz.excel_upload_log.uploaded_by IS '上传人（auth.tenant_admins.id）';
COMMENT ON COLUMN biz.excel_upload_log.status IS '批次状态：pending=待确认, confirmed=已写入, failed=写入失败';
COMMENT ON COLUMN biz.excel_upload_log.error_detail IS '写入失败时的错误详情 JSON';

CREATE INDEX IF NOT EXISTS idx_excel_log_site
    ON biz.excel_upload_log (site_id, created_at DESC);

-- ---------------------------------------------------------------------------
-- 3. biz.salary_adjustments — 助教奖罚明细表
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biz.salary_adjustments (
    id              BIGSERIAL       PRIMARY KEY,
    site_id         BIGINT          NOT NULL,               -- 门店 ID
    assistant_id    BIGINT,                                 -- 匹配到的助教 ID（可空）
    assistant_name  VARCHAR(100)    NOT NULL,               -- 助教姓名
    assistant_number VARCHAR(50)    NOT NULL,               -- 助教编号
    salary_month    VARCHAR(7)      NOT NULL,               -- 月份 YYYY-MM
    adjustment_type VARCHAR(20)     NOT NULL
                    CHECK (adjustment_type IN ('deduction', 'bonus')),
    amount          NUMERIC(12,2)   NOT NULL
                    CHECK (amount > 0),                     -- 金额（必须 > 0）
    reason          VARCHAR(200)    NOT NULL,               -- 原因
    upload_batch_id BIGINT          REFERENCES biz.excel_upload_log(id),
    created_at      TIMESTAMPTZ     DEFAULT NOW(),          -- 创建时间
    created_by      BIGINT                                  -- 上传人
);

COMMENT ON TABLE biz.salary_adjustments IS '助教奖罚明细表（通过 Excel 上传写入，直接进入业务库）';
COMMENT ON COLUMN biz.salary_adjustments.assistant_id IS '匹配到的助教 ID，匹配失败时为 NULL';
COMMENT ON COLUMN biz.salary_adjustments.adjustment_type IS '类型：deduction=扣款, bonus=奖金';
COMMENT ON COLUMN biz.salary_adjustments.upload_batch_id IS '关联 excel_upload_log.id';

CREATE INDEX IF NOT EXISTS idx_salary_adj_site_month
    ON biz.salary_adjustments (site_id, salary_month);

CREATE INDEX IF NOT EXISTS idx_salary_adj_assistant_month
    ON biz.salary_adjustments (assistant_id, salary_month);

-- ---------------------------------------------------------------------------
-- 4. biz.stg_finance_expense — 财务支出暂存表
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biz.stg_finance_expense (
    id              BIGSERIAL       PRIMARY KEY,
    site_id         BIGINT          NOT NULL,               -- 门店 ID
    expense_month   VARCHAR(7)      NOT NULL,               -- 月份 YYYY-MM
    category        VARCHAR(50)     NOT NULL,               -- 支出类别
    amount          NUMERIC(12,2)   NOT NULL,               -- 金额
    remark          TEXT,                                    -- 备注
    upload_batch_id BIGINT          REFERENCES biz.excel_upload_log(id),
    synced_at       TIMESTAMPTZ,                            -- ETL 同步时间（NULL=未同步）
    created_at      TIMESTAMPTZ     DEFAULT NOW()           -- 创建时间
);

COMMENT ON TABLE biz.stg_finance_expense IS '财务支出暂存表（Excel 上传 → ETL 同步到 DWS）';
COMMENT ON COLUMN biz.stg_finance_expense.synced_at IS 'ETL 同步时间，NULL 表示尚未同步';

-- ---------------------------------------------------------------------------
-- 5. biz.stg_platform_income — 团购收入暂存表
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biz.stg_platform_income (
    id              BIGSERIAL       PRIMARY KEY,
    site_id         BIGINT          NOT NULL,               -- 门店 ID
    income_month    VARCHAR(7)      NOT NULL,               -- 月份 YYYY-MM
    platform_name   VARCHAR(100)    NOT NULL,               -- 平台名称
    amount          NUMERIC(12,2)   NOT NULL,               -- 收入金额
    remark          TEXT,                                    -- 备注
    upload_batch_id BIGINT          REFERENCES biz.excel_upload_log(id),
    synced_at       TIMESTAMPTZ,                            -- ETL 同步时间（NULL=未同步）
    created_at      TIMESTAMPTZ     DEFAULT NOW()           -- 创建时间
);

COMMENT ON TABLE biz.stg_platform_income IS '团购收入暂存表（Excel 上传 → ETL 同步到 DWS）';
COMMENT ON COLUMN biz.stg_platform_income.synced_at IS 'ETL 同步时间，NULL 表示尚未同步';

-- ---------------------------------------------------------------------------
-- 6. biz.stg_recharge_commission — 充值业绩归属暂存表
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biz.stg_recharge_commission (
    id                  BIGSERIAL       PRIMARY KEY,
    site_id             BIGINT          NOT NULL,           -- 门店 ID
    recharge_date       DATE            NOT NULL,           -- 充值日期
    member_name         VARCHAR(100)    NOT NULL,           -- 会员名称
    recharge_amount     NUMERIC(12,2)   NOT NULL,           -- 充值金额
    assigned_assistant  VARCHAR(100)    NOT NULL,           -- 归属助教
    reward_amount       NUMERIC(12,2)   NOT NULL,           -- 奖励金额
    upload_batch_id     BIGINT          REFERENCES biz.excel_upload_log(id),
    synced_at           TIMESTAMPTZ,                        -- ETL 同步时间（NULL=未同步）
    created_at          TIMESTAMPTZ     DEFAULT NOW()       -- 创建时间
);

COMMENT ON TABLE biz.stg_recharge_commission IS '充值业绩归属暂存表（Excel 上传 → ETL 同步到 DWS）';
COMMENT ON COLUMN biz.stg_recharge_commission.synced_at IS 'ETL 同步时间，NULL 表示尚未同步';

-- =============================================================================
-- 回滚
-- =============================================================================
-- DROP TABLE IF EXISTS biz.stg_recharge_commission;
-- DROP TABLE IF EXISTS biz.stg_platform_income;
-- DROP TABLE IF EXISTS biz.stg_finance_expense;
-- DROP TABLE IF EXISTS biz.salary_adjustments;
-- DROP TABLE IF EXISTS biz.excel_upload_log;
-- DROP TABLE IF EXISTS auth.tenant_admins;

-- =============================================================================
-- 验证
-- =============================================================================
-- 1) auth.tenant_admins 表存在性
-- SELECT column_name, data_type, is_nullable
-- FROM information_schema.columns
-- WHERE table_schema = 'auth' AND table_name = 'tenant_admins'
-- ORDER BY ordinal_position;
-- 预期：11 行（含 2026-03-22 新增的 deleted_at）

-- 2) biz.excel_upload_log 表存在性
-- SELECT column_name, data_type
-- FROM information_schema.columns
-- WHERE table_schema = 'biz' AND table_name = 'excel_upload_log'
-- ORDER BY ordinal_position;
-- 预期：12 行

-- 3) biz.salary_adjustments FK 约束
-- SELECT conname, pg_get_constraintdef(oid)
-- FROM pg_constraint
-- WHERE conrelid = 'biz.salary_adjustments'::regclass AND contype = 'f';
-- 预期：1 行（upload_batch_id → excel_upload_log）

-- 4) 索引存在性
-- SELECT indexname FROM pg_indexes
-- WHERE schemaname IN ('auth', 'biz')
--   AND indexname IN (
--     'idx_tenant_admin_tenant',
--     'idx_excel_log_site',
--     'idx_salary_adj_site_month',
--     'idx_salary_adj_assistant_month'
--   );
-- 预期：4 行

-- 5) staging 表存在性
-- SELECT table_schema, table_name
-- FROM information_schema.tables
-- WHERE table_schema = 'biz'
--   AND table_name IN ('stg_finance_expense', 'stg_platform_income', 'stg_recharge_commission');
-- 预期：3 行
