-- 创建资产操作审计日志表
CREATE TABLE IF NOT EXISTS tb_asset_operation_log (
    id BIGSERIAL PRIMARY KEY,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    operator_id BIGINT,
    operator_type VARCHAR(32) NOT NULL,
    operator_name VARCHAR(255) NOT NULL DEFAULT '',

    asset_type VARCHAR(32) NOT NULL,
    asset_id BIGINT NOT NULL,
    asset_identifier VARCHAR(128) NOT NULL DEFAULT '',

    operation_type VARCHAR(64) NOT NULL,
    operation_desc TEXT NOT NULL,

    before_data JSONB,
    after_data JSONB,

    request_id VARCHAR(255),
    ip_address VARCHAR(64),
    user_agent TEXT,
    request_path VARCHAR(255),
    request_method VARCHAR(16),

    result_status VARCHAR(16) NOT NULL,
    error_code VARCHAR(64),
    error_msg TEXT,

    batch_total INT NOT NULL DEFAULT 0,
    success_count INT NOT NULL DEFAULT 0,
    fail_count INT NOT NULL DEFAULT 0
);

CREATE INDEX IF NOT EXISTS idx_asset_log_asset_created
    ON tb_asset_operation_log(asset_type, asset_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_asset_log_identifier_created
    ON tb_asset_operation_log(asset_identifier, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_asset_log_operator_created
    ON tb_asset_operation_log(operator_id, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_asset_log_operation_created
    ON tb_asset_operation_log(operation_type, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_asset_log_result_created
    ON tb_asset_operation_log(result_status, created_at DESC);

COMMENT ON TABLE tb_asset_operation_log IS '资产操作审计日志表';
COMMENT ON COLUMN tb_asset_operation_log.id IS '主键ID';
COMMENT ON COLUMN tb_asset_operation_log.created_at IS '创建时间';
COMMENT ON COLUMN tb_asset_operation_log.operator_id IS '操作人ID（系统触发为空）';
COMMENT ON COLUMN tb_asset_operation_log.operator_type IS '操作人类型(system/admin_user/agent_user/enterprise_user/personal_customer)';
COMMENT ON COLUMN tb_asset_operation_log.operator_name IS '操作人名称快照';
COMMENT ON COLUMN tb_asset_operation_log.asset_type IS '资产类型(iot_card/device)';
COMMENT ON COLUMN tb_asset_operation_log.asset_id IS '资产ID';
COMMENT ON COLUMN tb_asset_operation_log.asset_identifier IS '资产标识(ICCID/虚拟号)';
COMMENT ON COLUMN tb_asset_operation_log.operation_type IS '操作类型';
COMMENT ON COLUMN tb_asset_operation_log.operation_desc IS '操作描述（中文）';
COMMENT ON COLUMN tb_asset_operation_log.before_data IS '变更前数据（JSONB）';
COMMENT ON COLUMN tb_asset_operation_log.after_data IS '变更后数据（JSONB）';
COMMENT ON COLUMN tb_asset_operation_log.request_id IS '请求ID';
COMMENT ON COLUMN tb_asset_operation_log.ip_address IS '请求IP';
COMMENT ON COLUMN tb_asset_operation_log.user_agent IS '用户代理';
COMMENT ON COLUMN tb_asset_operation_log.request_path IS '请求路径';
COMMENT ON COLUMN tb_asset_operation_log.request_method IS '请求方法';
COMMENT ON COLUMN tb_asset_operation_log.result_status IS '执行结果(success/failed/denied)';
COMMENT ON COLUMN tb_asset_operation_log.error_code IS '错误码';
COMMENT ON COLUMN tb_asset_operation_log.error_msg IS '错误摘要';
COMMENT ON COLUMN tb_asset_operation_log.batch_total IS '批量总数';
COMMENT ON COLUMN tb_asset_operation_log.success_count IS '批量成功数';
COMMENT ON COLUMN tb_asset_operation_log.fail_count IS '批量失败数';