-- ============================================
-- 审计字段迁移：创建人/更新人 + DNS记录本地审计表
-- 数据库: nginxserver @ 10.1.1.122:3306
-- 日期: 2026-06-19
-- 执行方式: mysql -h 10.1.1.122 -u nginxserver -p nginxserver < audit_migration.sql
-- ============================================

USE nginxserver;

-- ════════════════════════════════════════════════
-- 安全添加审计列（检查是否已存在，避免重复执行报错）
-- ════════════════════════════════════════════════
DROP PROCEDURE IF EXISTS add_audit_columns;

DELIMITER $$
CREATE PROCEDURE add_audit_columns()
BEGIN
    -- ========================================
    -- forwarding_rules: created_by
    -- ========================================
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'nginxserver'
          AND TABLE_NAME = 'forwarding_rules'
          AND COLUMN_NAME = 'created_by'
    ) THEN
        ALTER TABLE forwarding_rules
            ADD COLUMN created_by VARCHAR(100) DEFAULT NULL
            COMMENT '创建人';
    END IF;

    -- forwarding_rules: updated_by
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'nginxserver'
          AND TABLE_NAME = 'forwarding_rules'
          AND COLUMN_NAME = 'updated_by'
    ) THEN
        ALTER TABLE forwarding_rules
            ADD COLUMN updated_by VARCHAR(100) DEFAULT NULL
            COMMENT '更新人';
    END IF;

    -- ========================================
    -- dns_zone_config: created_by
    -- ========================================
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'nginxserver'
          AND TABLE_NAME = 'dns_zone_config'
          AND COLUMN_NAME = 'created_by'
    ) THEN
        ALTER TABLE dns_zone_config
            ADD COLUMN created_by VARCHAR(100) DEFAULT NULL
            COMMENT '创建人';
    END IF;

    -- dns_zone_config: updated_by
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'nginxserver'
          AND TABLE_NAME = 'dns_zone_config'
          AND COLUMN_NAME = 'updated_by'
    ) THEN
        ALTER TABLE dns_zone_config
            ADD COLUMN updated_by VARCHAR(100) DEFAULT NULL
            COMMENT '更新人';
    END IF;

    -- ========================================
    -- certificates: created_by
    -- ========================================
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'nginxserver'
          AND TABLE_NAME = 'certificates'
          AND COLUMN_NAME = 'created_by'
    ) THEN
        ALTER TABLE certificates
            ADD COLUMN created_by VARCHAR(100) DEFAULT NULL
            COMMENT '创建人';
    END IF;

    -- certificates: updated_by
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.COLUMNS
        WHERE TABLE_SCHEMA = 'nginxserver'
          AND TABLE_NAME = 'certificates'
          AND COLUMN_NAME = 'updated_by'
    ) THEN
        ALTER TABLE certificates
            ADD COLUMN updated_by VARCHAR(100) DEFAULT NULL
            COMMENT '更新人';
    END IF;
END$$
DELIMITER ;

-- 执行存储过程
CALL add_audit_columns();

-- 清理
DROP PROCEDURE IF EXISTS add_audit_columns;

-- ════════════════════════════════════════════════
-- 新建 DNS 记录本地审计表
-- ════════════════════════════════════════════════
CREATE TABLE IF NOT EXISTS dns_records (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY COMMENT '主键ID',
    record_id     VARCHAR(128) NOT NULL COMMENT '阿里云解析记录ID',
    domain_zone   VARCHAR(255) NOT NULL COMMENT '主域名（如 zgitm.com）',
    rr            VARCHAR(255) NOT NULL COMMENT '主机记录',
    record_type   VARCHAR(20)  NOT NULL COMMENT '记录类型: A / CNAME / TXT',
    record_value  VARCHAR(500) NOT NULL COMMENT '记录值（IP 或域名）',
    ttl           INT          DEFAULT 600 COMMENT 'TTL 生效时间（秒）',
    status        TINYINT      DEFAULT 1 COMMENT '状态: 0-已删除(逻辑删除), 1-正常',
    created_by    VARCHAR(100) DEFAULT NULL COMMENT '创建人',
    updated_by    VARCHAR(100) DEFAULT NULL COMMENT '更新人',
    created_at    DATETIME     DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
    updated_at    DATETIME     DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
    UNIQUE KEY uk_record_id (record_id),
    INDEX idx_domain_zone (domain_zone),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
  COMMENT='DNS解析记录本地审计表（阿里云为权威数据源，本表仅做审计追踪）';

-- ════════════════════════════════════════════════
-- 验证结果
-- ════════════════════════════════════════════════
SELECT '=== forwarding_rules 审计列 ===' AS '';
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'nginxserver'
  AND TABLE_NAME = 'forwarding_rules'
  AND COLUMN_NAME IN ('created_by', 'updated_by');

SELECT '=== dns_zone_config 审计列 ===' AS '';
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'nginxserver'
  AND TABLE_NAME = 'dns_zone_config'
  AND COLUMN_NAME IN ('created_by', 'updated_by');

SELECT '=== certificates 审计列 ===' AS '';
SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, COLUMN_COMMENT
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'nginxserver'
  AND TABLE_NAME = 'certificates'
  AND COLUMN_NAME IN ('created_by', 'updated_by');

SELECT '=== dns_records 表结构 ===' AS '';
SHOW CREATE TABLE dns_records;