License/backend/migrations/001_initial.sql

-- ============================================================
-- 域名流水监控 - 初始建表
-- 文件: 001_initial.sql
-- 日期: 2026-05-12
-- ============================================================

-- 创建业务 schema（如不存在）
CREATE SCHEMA IF NOT EXISTS domain_monitor;

-- ============================================================
-- 监控域名表（用于配置需要监控的域名）
-- ============================================================

CREATE TABLE IF NOT EXISTS domain_monitor.monitored_domains (
    id SERIAL PRIMARY KEY,
    domain VARCHAR NOT NULL,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS ix_monitored_domains_domain ON domain_monitor.monitored_domains (domain);
CREATE INDEX IF NOT EXISTS ix_monitored_domains_id ON domain_monitor.monitored_domains (id);

-- ============================================================
-- 监控大屏 - 超级管理员表
-- ============================================================

CREATE TABLE IF NOT EXISTS domain_monitor.super_admin (
    id SERIAL PRIMARY KEY,
    username VARCHAR NOT NULL,
    nickname VARCHAR,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ============================================================
-- 监控大屏 - 超级管理员与租户关联表
-- ============================================================

CREATE TABLE IF NOT EXISTS domain_monitor.super_admin_tenant (
    id SERIAL PRIMARY KEY,
    super_admin_id INTEGER NOT NULL,
    tenant_id INTEGER NOT NULL
);

CREATE INDEX IF NOT EXISTS ix_sat_super_admin ON domain_monitor.super_admin_tenant (super_admin_id);
CREATE INDEX IF NOT EXISTS ix_sat_tenant ON domain_monitor.super_admin_tenant (tenant_id);

-- ============================================================
-- 监控大屏 - 租户（企业）表
-- ============================================================

CREATE TABLE IF NOT EXISTS domain_monitor.tenant (
    id SERIAL PRIMARY KEY,
    company_name VARCHAR,
    subdomain VARCHAR NOT NULL,
    balance NUMERIC(20, 4) DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS ix_tenant_subdomain ON domain_monitor.tenant (subdomain);

-- ============================================================
-- 监控大屏 - 模型信息参考表
-- ============================================================

CREATE TABLE IF NOT EXISTS domain_monitor.models (
    id SERIAL PRIMARY KEY,
    model_code VARCHAR NOT NULL,
    model_name VARCHAR NOT NULL,
    original_price NUMERIC(20, 4)
);

CREATE UNIQUE INDEX IF NOT EXISTS ix_model_code ON domain_monitor.models (model_code);

-- ============================================================
-- 监控大屏 - 用户模型消费明细表
-- 记录每次模型调用的消费数据，包含平台原价、企业折扣、用户折扣等信息
-- ============================================================

CREATE TABLE IF NOT EXISTS domain_monitor.user_consumption_detail (
    id SERIAL PRIMARY KEY,
    user_id VARCHAR NOT NULL,
    tenant_id INTEGER NOT NULL,
    model_code VARCHAR NOT NULL,
    call_count INTEGER DEFAULT 0,
    -- 用户侧：用户实际支付
    user_actual_total NUMERIC(20, 4) DEFAULT 0,
    user_discount NUMERIC(10, 4) DEFAULT 1.0000,
    user_actual_price NUMERIC(20, 4),
    -- 租户侧：平台向企业收取
    tenant_actual_total NUMERIC(20, 4) DEFAULT 0,
    tenant_discount NUMERIC(10, 4) DEFAULT 1.0000,
    tenant_actual_price NUMERIC(20, 4),
    -- 平台原价
    original_price NUMERIC(20, 4),
    -- 消费时间（用于时间范围过滤）
    consumption_date TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS ix_ucd_user ON domain_monitor.user_consumption_detail (user_id);
CREATE INDEX IF NOT EXISTS ix_ucd_tenant ON domain_monitor.user_consumption_detail (tenant_id);
CREATE INDEX IF NOT EXISTS ix_ucd_model ON domain_monitor.user_consumption_detail (model_code);
CREATE INDEX IF NOT EXISTS ix_ucd_consumption_date ON domain_monitor.user_consumption_detail (consumption_date);