-- =============================================================================
-- 迁移：NS4.1 注册体系 — 连接器/租户/店铺/简写ID历史 四张新表
-- 日期：2026-03-22
-- 关联 SPEC：admin-web-enhancement
-- 需求：A1.1, A1.2, A1.3, A1.4, A1.5
-- 说明：
--   1. biz.connectors    — 连接器注册表（上游 SaaS 系统）
--   2. biz.tenants       — 租户注册表（连接器下的租户）
--   3. biz.sites         — 店铺注册表（合并原 auth.site_code_mapping）
--   4. biz.site_code_history — 简写ID 变更历史
--   创建顺序：connectors → tenants → sites → site_code_history（FK 依赖链）
--   种子数据：飞球连接器 + 朗朗桌球租户
--   数据迁移：auth.site_code_mapping → biz.sites + biz.site_code_history
-- =============================================================================

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

-- ---------------------------------------------------------------------------
-- 1. biz.connectors — 连接器注册表
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biz.connectors (
    id              SERIAL          PRIMARY KEY,
    connector_key   VARCHAR(50)     NOT NULL UNIQUE,        -- 连接器标识（如 feiqiu）
    display_name    VARCHAR(100)    NOT NULL,               -- 显示名称
    is_active       BOOLEAN         NOT NULL DEFAULT true,  -- 是否启用
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()  -- 创建时间
);

COMMENT ON TABLE biz.connectors IS '连接器注册表：记录本项目接入的上游 SaaS 系统';

-- ---------------------------------------------------------------------------
-- 2. biz.tenants — 租户注册表
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biz.tenants (
    id              SERIAL          PRIMARY KEY,
    connector_id    INTEGER         NOT NULL REFERENCES biz.connectors(id),  -- 所属连接器
    tenant_id       BIGINT          NOT NULL,               -- 上游系统租户 ID
    tenant_name     VARCHAR(200),                           -- 租户名称
    is_active       BOOLEAN         NOT NULL DEFAULT true,  -- 是否启用
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(), -- 创建时间
    updated_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(), -- 更新时间
    UNIQUE (connector_id, tenant_id)
);

COMMENT ON TABLE biz.tenants IS '租户注册表：连接器下的租户，tenant_id 来自上游系统';

-- ---------------------------------------------------------------------------
-- 3. biz.sites — 店铺注册表
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biz.sites (
    id              SERIAL          PRIMARY KEY,
    tenant_id       INTEGER         NOT NULL REFERENCES biz.tenants(id),    -- 所属租户（biz.tenants.id）
    site_id         BIGINT          NOT NULL UNIQUE,        -- 上游系统店铺 ID
    site_name       VARCHAR(200),                           -- 店铺名称
    site_code       VARCHAR(6)      UNIQUE,                 -- 当前生效的简写ID（6位，3+3格式）
    site_label      VARCHAR(50),                            -- 店铺标签
    is_active       BOOLEAN         NOT NULL DEFAULT true,  -- 是否启用
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(), -- 创建时间
    updated_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW()  -- 更新时间
);

COMMENT ON TABLE biz.sites IS '店铺注册表：合并原 auth.site_code_mapping，增加租户关联和简写ID管理';
COMMENT ON COLUMN biz.sites.site_code IS '当前生效的简写ID，6位字符（3+3格式），全局唯一';

-- ---------------------------------------------------------------------------
-- 4. biz.site_code_history — 简写ID 变更历史
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS biz.site_code_history (
    id              SERIAL          PRIMARY KEY,
    site_id         BIGINT          NOT NULL,               -- 店铺 ID（biz.sites.site_id）
    site_code       VARCHAR(6)      NOT NULL UNIQUE,        -- 简写ID（全局唯一，含历史）
    is_current      BOOLEAN         NOT NULL DEFAULT false, -- 是否当前生效
    created_at      TIMESTAMPTZ     NOT NULL DEFAULT NOW(), -- 创建时间
    retired_at      TIMESTAMPTZ                             -- 退役时间（NULL=未退役）
);

COMMENT ON TABLE biz.site_code_history IS '简写ID变更历史：增量记录所有使用过的简写ID，全局 UNIQUE 保护已提交申请';
COMMENT ON COLUMN biz.site_code_history.is_current IS 'true=当前生效的简写ID，每个 site_id 最多一条 is_current=true';

-- =============================================================================
-- 种子数据
-- =============================================================================

-- 连接器：飞球
INSERT INTO biz.connectors (connector_key, display_name)
VALUES ('feiqiu', '飞球');

-- 租户：朗朗桌球
INSERT INTO biz.tenants (connector_id, tenant_id, tenant_name)
VALUES (1, 2790683160709957, '朗朗桌球');

-- =============================================================================
-- 数据迁移：auth.site_code_mapping → biz.sites
-- =============================================================================

-- 从 auth.site_code_mapping 迁移真实数据到 biz.sites
INSERT INTO biz.sites (tenant_id, site_id, site_name, site_code)
SELECT t.id, scm.site_id, scm.site_name, scm.site_code
FROM auth.site_code_mapping scm
JOIN biz.tenants t ON t.tenant_id = scm.tenant_id
WHERE scm.tenant_id IS NOT NULL;

-- 为已有 site_code 创建历史记录（标记为当前生效）
INSERT INTO biz.site_code_history (site_id, site_code, is_current)
SELECT site_id, site_code, true
FROM biz.sites
WHERE site_code IS NOT NULL;

-- =============================================================================
-- === ROLLBACK ===
-- 按依赖逆序删除
-- DROP TABLE IF EXISTS biz.site_code_history;
-- DROP TABLE IF EXISTS biz.sites;
-- DROP TABLE IF EXISTS biz.tenants;
-- DROP TABLE IF EXISTS biz.connectors;
-- =============================================================================

-- =============================================================================
-- 验证
-- =============================================================================
-- 1) biz.connectors 表存在性
-- SELECT column_name, data_type, is_nullable
-- FROM information_schema.columns
-- WHERE table_schema = 'biz' AND table_name = 'connectors'
-- ORDER BY ordinal_position;
-- 预期：5 行（id, connector_key, display_name, is_active, created_at）

-- 2) biz.tenants 表存在性
-- SELECT column_name, data_type, is_nullable
-- FROM information_schema.columns
-- WHERE table_schema = 'biz' AND table_name = 'tenants'
-- ORDER BY ordinal_position;
-- 预期：7 行（id, connector_id, tenant_id, tenant_name, is_active, created_at, updated_at）

-- 3) biz.sites 表存在性及字段
-- SELECT column_name, data_type, is_nullable
-- FROM information_schema.columns
-- WHERE table_schema = 'biz' AND table_name = 'sites'
-- ORDER BY ordinal_position;
-- 预期：9 行（id, tenant_id, site_id, site_name, site_code, site_label, is_active, created_at, updated_at）

-- 4) biz.site_code_history 表存在性
-- SELECT column_name, data_type, is_nullable
-- FROM information_schema.columns
-- WHERE table_schema = 'biz' AND table_name = 'site_code_history'
-- ORDER BY ordinal_position;
-- 预期：6 行（id, site_id, site_code, is_current, created_at, retired_at）

-- 5) 种子数据验证
-- SELECT connector_key, display_name FROM biz.connectors;
-- 预期：1 行（feiqiu, 飞球）
-- SELECT tenant_id, tenant_name FROM biz.tenants;
-- 预期：1 行（2790683160709957, 朗朗桌球）

-- 6) 数据迁移验证 — biz.sites 行数应 ≥ auth.site_code_mapping 中 tenant_id IS NOT NULL 的行数
-- SELECT COUNT(*) FROM biz.sites;
-- SELECT COUNT(*) FROM auth.site_code_mapping WHERE tenant_id IS NOT NULL;

-- 7) site_code_history 记录数应等于 biz.sites 中 site_code IS NOT NULL 的行数
-- SELECT COUNT(*) FROM biz.site_code_history;
-- SELECT COUNT(*) FROM biz.sites WHERE site_code IS NOT NULL;

-- 8) UNIQUE 约束验证
-- SELECT conname, pg_get_constraintdef(oid)
-- FROM pg_constraint
-- WHERE conrelid IN (
--     'biz.connectors'::regclass,
--     'biz.tenants'::regclass,
--     'biz.sites'::regclass,
--     'biz.site_code_history'::regclass
-- ) AND contype IN ('u', 'p')
-- ORDER BY conrelid::text, conname;
