License/backend/migrations/001_initial_tables.sql

-- ============================================================
-- 初始建表
-- ============================================================

CREATE SCHEMA IF NOT EXISTS domain_monitor;

-- 监控域名表
CREATE TABLE IF NOT EXISTS domain_monitor.monitored_domains (
    id SERIAL PRIMARY KEY,
    domain VARCHAR NOT NULL,
    remark VARCHAR(500),
    super_admin_id INTEGER,
    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,
    remark VARCHAR(500),
    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,
    username VARCHAR,
    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),
    super_admin_discount NUMERIC(10, 4) DEFAULT 1.0000,
    super_admin_actual_price NUMERIC(20, 4),
    original_price NUMERIC(20, 4),
    consumption_date TIMESTAMP WITH TIME ZONE NOT NULL,
    order_no VARCHAR,
    invoiced BOOLEAN DEFAULT FALSE,
    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);