License/backend/migrations/008_sa_balance.sql

-- 超级管理员余额管理
-- 1. super_admin 表新增余额、手机号、预警标记字段
ALTER TABLE domain_monitor.super_admin ADD COLUMN IF NOT EXISTS balance NUMERIC(20,4) DEFAULT 0;
ALTER TABLE domain_monitor.super_admin ADD COLUMN IF NOT EXISTS phone VARCHAR(20);
ALTER TABLE domain_monitor.super_admin ADD COLUMN IF NOT EXISTS balance_warning_sent BOOLEAN DEFAULT FALSE;
ALTER TABLE domain_monitor.super_admin ADD COLUMN IF NOT EXISTS balance_depleted_sent BOOLEAN DEFAULT FALSE;

-- 2. 超管余额变动日志表
CREATE TABLE IF NOT EXISTS domain_monitor.super_admin_balance_log (
    id SERIAL PRIMARY KEY,
    super_admin_id INTEGER NOT NULL,
    change_amount NUMERIC(20,4) NOT NULL,
    balance_after NUMERIC(20,4) NOT NULL,
    biz_type VARCHAR(50) NOT NULL,
    biz_order_no VARCHAR(100),
    remark TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_sa_balance_log_sa_id
    ON domain_monitor.super_admin_balance_log(super_admin_id);

CREATE UNIQUE INDEX IF NOT EXISTS idx_sa_balance_log_unique
    ON domain_monitor.super_admin_balance_log(super_admin_id, biz_type, biz_order_no)
    WHERE biz_order_no IS NOT NULL;