FranklinWH-Toolbox/deploy/sql/init_pgsql.sql

-- =============================================================================
-- FranklinWH Toolbox - PostgreSQL 从零初始化脚本
-- =============================================================================
-- 作用：
--   1. 创建业务角色 fwh_toolbox
--   2. 创建业务数据库 fwh_toolbox_db（owner = fwh_toolbox）
--   3. 创建 schema：original / alarm_snapshot / public
--   4. 创建后端“显式管理”的两张表（alarm_snapshot.*）
--
-- 说明：
--   * original schema 下的“站点原始数据表”由后端在运行时按需动态建表
--     （表名为雪花ID的十六进制串，如 6901c3b1d05017860aaf87e3），
--     因此本脚本只创建 schema，不预建这些动态表。
--   * alarm_snapshot 下两张表结构来自线上库 pg_dump，后端不做 AutoMigrate，
--     需要本脚本预先建好。
--
-- 使用方式（作为 postgres 超级用户执行）：
--   sudo -u postgres psql -v fwh_pwd=你的密码 -f init_pgsql.sql
--   密码直接传入、无需加引号（脚本内部用 %L 做安全转义）。
--   若不传 fwh_pwd，默认使用 fwh_toolbox（仅供本地开发，请勿用于生产）。
-- =============================================================================

\set ON_ERROR_STOP on

-- 默认密码（可通过 -v fwh_pwd=xxx 覆盖，无需自行加引号）
\if :{?fwh_pwd}
\else
  \set fwh_pwd fwh_toolbox
\endif

-- ---------------------------------------------------------------------------
-- 1. 角色
--    使用 format(... %L ...) 对密码做安全的字面量转义，支持含特殊字符的密码。
-- ---------------------------------------------------------------------------
SELECT format('CREATE ROLE fwh_toolbox LOGIN PASSWORD %L', :'fwh_pwd')
WHERE NOT EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'fwh_toolbox')
\gexec

-- ---------------------------------------------------------------------------
-- 2. 数据库（owner = fwh_toolbox）
--    CREATE DATABASE 不能在事务块或 DO 块中执行，用 \gexec 条件创建
-- ---------------------------------------------------------------------------
SELECT 'CREATE DATABASE fwh_toolbox_db OWNER fwh_toolbox ENCODING ''UTF8'''
WHERE NOT EXISTS (SELECT 1 FROM pg_database WHERE datname = 'fwh_toolbox_db')
\gexec

-- 切换到业务库继续初始化
\connect fwh_toolbox_db

-- ---------------------------------------------------------------------------
-- 3. Schema
-- ---------------------------------------------------------------------------
CREATE SCHEMA IF NOT EXISTS original       AUTHORIZATION fwh_toolbox;
CREATE SCHEMA IF NOT EXISTS alarm_snapshot AUTHORIZATION fwh_toolbox;
-- public schema 默认存在，owner 为 postgres，授予业务角色使用权
GRANT USAGE, CREATE ON SCHEMA public TO fwh_toolbox;

-- ---------------------------------------------------------------------------
-- 4. alarm_snapshot 表（后端不做 AutoMigrate，需预建）
-- ---------------------------------------------------------------------------

-- 4.1 站点告警快照数据
CREATE TABLE IF NOT EXISTS alarm_snapshot.site_alarm_snapshot_data (
    id             bigint                          NOT NULL,
    sn             character varying(32)           NOT NULL,
    file_record_id bigint                          NOT NULL,
    log_time       timestamp(0) without time zone  NOT NULL,
    json_data      jsonb                           NOT NULL,
    zone_info      text
);

COMMENT ON TABLE  alarm_snapshot.site_alarm_snapshot_data                IS '站点告警快照数据';
COMMENT ON COLUMN alarm_snapshot.site_alarm_snapshot_data.sn             IS '站点SN';
COMMENT ON COLUMN alarm_snapshot.site_alarm_snapshot_data.file_record_id IS '文件记录表的id';
COMMENT ON COLUMN alarm_snapshot.site_alarm_snapshot_data.log_time       IS '日志记录时间(UTC时间)';
COMMENT ON COLUMN alarm_snapshot.site_alarm_snapshot_data.json_data      IS 'json数据(二进制)';
COMMENT ON COLUMN alarm_snapshot.site_alarm_snapshot_data.zone_info      IS '时区信息';

-- 自增身份列（幂等：仅在未定义 identity 时添加）
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_attribute
        WHERE attrelid = 'alarm_snapshot.site_alarm_snapshot_data'::regclass
          AND attname  = 'id'
          AND attidentity <> ''
    ) THEN
        ALTER TABLE alarm_snapshot.site_alarm_snapshot_data
            ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
                SEQUENCE NAME alarm_snapshot.site_alarm_snapshot_data_id_seq
                START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1
            );
    END IF;
END$$;

-- 约束（幂等）
DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'site_alarm_snapshot_data_id_pkey') THEN
        ALTER TABLE ONLY alarm_snapshot.site_alarm_snapshot_data
            ADD CONSTRAINT site_alarm_snapshot_data_id_pkey PRIMARY KEY (id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'site_alarm_snapshot_data_sn_file_record_id_log_time_unique') THEN
        ALTER TABLE ONLY alarm_snapshot.site_alarm_snapshot_data
            ADD CONSTRAINT site_alarm_snapshot_data_sn_file_record_id_log_time_unique
            UNIQUE (sn, file_record_id, log_time);
    END IF;
END$$;

-- 4.2 站点告警快照文件处理记录
CREATE TABLE IF NOT EXISTS alarm_snapshot.site_alarm_snapshot_file_record (
    id            bigint                          NOT NULL,
    code_type     integer,
    file_name     text                            NOT NULL,
    file_url      text                            NOT NULL,
    sn            character varying(32)           NOT NULL,
    log_time      date                            NOT NULL,
    start_time    timestamp(0) without time zone  NOT NULL,
    end_time      timestamp(0) without time zone  NOT NULL,
    complete_time timestamp(0) without time zone  NOT NULL,
    state         smallint DEFAULT 0              NOT NULL
);

COMMENT ON TABLE  alarm_snapshot.site_alarm_snapshot_file_record               IS '站点告警快照的文件处理记录';
COMMENT ON COLUMN alarm_snapshot.site_alarm_snapshot_file_record.sn            IS 'site SN';
COMMENT ON COLUMN alarm_snapshot.site_alarm_snapshot_file_record.log_time      IS '日志记录日期(format YYYY-MM-DD)';
COMMENT ON COLUMN alarm_snapshot.site_alarm_snapshot_file_record.start_time    IS '记录开始时间(format YYYY-MM-DD HH:mm:SS)';
COMMENT ON COLUMN alarm_snapshot.site_alarm_snapshot_file_record.end_time      IS '记录结束时间(format YYYY-MM-DD HH:mm:SS)';
COMMENT ON COLUMN alarm_snapshot.site_alarm_snapshot_file_record.complete_time IS '记录上传完成时间(format YYYY-MM-DD HH:mm:SS)';
COMMENT ON COLUMN alarm_snapshot.site_alarm_snapshot_file_record.state         IS '文件分析状态(0-分析中；1-完成；-1-失败)';

DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM pg_attribute
        WHERE attrelid = 'alarm_snapshot.site_alarm_snapshot_file_record'::regclass
          AND attname  = 'id'
          AND attidentity <> ''
    ) THEN
        ALTER TABLE alarm_snapshot.site_alarm_snapshot_file_record
            ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
                SEQUENCE NAME alarm_snapshot.site_alarm_snapshot_file_record_id_seq
                START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1
            );
    END IF;
END$$;

DO $$
BEGIN
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'site_alarm_snapshot_file_record_id_pkey') THEN
        ALTER TABLE ONLY alarm_snapshot.site_alarm_snapshot_file_record
            ADD CONSTRAINT site_alarm_snapshot_file_record_id_pkey PRIMARY KEY (id);
    END IF;
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'site_alarm_snapshot_file_record_file_url_unique') THEN
        ALTER TABLE ONLY alarm_snapshot.site_alarm_snapshot_file_record
            ADD CONSTRAINT site_alarm_snapshot_file_record_file_url_unique UNIQUE (file_url);
    END IF;
END$$;

-- ---------------------------------------------------------------------------
-- 5. 归属与授权
-- ---------------------------------------------------------------------------
ALTER TABLE alarm_snapshot.site_alarm_snapshot_data        OWNER TO fwh_toolbox;
ALTER TABLE alarm_snapshot.site_alarm_snapshot_file_record OWNER TO fwh_toolbox;

GRANT ALL ON ALL TABLES    IN SCHEMA alarm_snapshot TO fwh_toolbox;
GRANT ALL ON ALL SEQUENCES IN SCHEMA alarm_snapshot TO fwh_toolbox;
GRANT ALL ON ALL TABLES    IN SCHEMA original       TO fwh_toolbox;
GRANT ALL ON ALL SEQUENCES IN SCHEMA original       TO fwh_toolbox;

\echo '✅ PostgreSQL 初始化完成：数据库 fwh_toolbox_db / schema original,alarm_snapshot / alarm_snapshot 表已就绪'