License/backend/migrations/002_new_tables.sql

-- ============================================================
-- 域名流水监控 - 新增业务表
-- License 授权 / 访客信息 / 爬取调度配置
-- ============================================================

-- License 授权表（含短信去重字段）
CREATE TABLE IF NOT EXISTS domain_monitor.super_admin_license (
    id SERIAL PRIMARY KEY,
    super_admin_id INTEGER NOT NULL,
    license_key VARCHAR(200) NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'active',
    max_tenants INTEGER,
    max_users_per_tenant INTEGER,
    remark TEXT,
    warning_sent BOOLEAN DEFAULT FALSE,
    expired_sent BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX IF NOT EXISTS idx_sa_license_sa_id ON domain_monitor.super_admin_license(super_admin_id);
CREATE INDEX IF NOT EXISTS idx_sa_license_status ON domain_monitor.super_admin_license(status);
CREATE INDEX IF NOT EXISTS idx_sa_license_expires ON domain_monitor.super_admin_license(expires_at);

-- 访客信息表
CREATE TABLE IF NOT EXISTS domain_monitor.visitor_info (
    id SERIAL PRIMARY KEY,
    domain_id INT NOT NULL UNIQUE,
    name VARCHAR(100),
    phone VARCHAR(50),
    email VARCHAR(200),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE,
    CONSTRAINT fk_visitor_domain FOREIGN KEY (domain_id) REFERENCES domain_monitor.monitored_domains(id) ON DELETE CASCADE
);

-- 爬取调度配置表
CREATE TABLE IF NOT EXISTS domain_monitor.fetch_schedule_config (
    id SERIAL PRIMARY KEY,
    enabled BOOLEAN DEFAULT FALSE,
    schedule_time VARCHAR(5) DEFAULT '02:00'
);

-- 初始化默认配置
INSERT INTO domain_monitor.fetch_schedule_config (enabled, schedule_time)
SELECT FALSE, '02:00'
WHERE NOT EXISTS (SELECT 1 FROM domain_monitor.fetch_schedule_config);