-- Novalon管理系统审计日志归档表
-- 版本: V8
-- 描述: 创建审计日志归档表，用于存储历史审计日志

CREATE TABLE IF NOT EXISTS audit_log_archive (
    id BIGSERIAL PRIMARY KEY,
    entity_type VARCHAR(100) NOT NULL,
    entity_id BIGINT,
    operation_type VARCHAR(20) NOT NULL,
    operator VARCHAR(100),
    operation_time TIMESTAMP,
    before_data JSONB,
    after_data JSONB,
    changed_fields TEXT[],
    ip_address VARCHAR(50),
    user_agent TEXT,
    description TEXT,
    created_at TIMESTAMP,
    archived_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_log_archive_entity_type ON audit_log_archive(entity_type);
CREATE INDEX idx_audit_log_archive_entity_id ON audit_log_archive(entity_id);
CREATE INDEX idx_audit_log_archive_operation_type ON audit_log_archive(operation_type);
CREATE INDEX idx_audit_log_archive_operator ON audit_log_archive(operator);
CREATE INDEX idx_audit_log_archive_operation_time ON audit_log_archive(operation_time);
CREATE INDEX idx_audit_log_archive_archived_at ON audit_log_archive(archived_at);

COMMENT ON TABLE audit_log_archive IS '审计日志归档表';
COMMENT ON COLUMN audit_log_archive.id IS '主键ID';
COMMENT ON COLUMN audit_log_archive.entity_type IS '实体类型（如User, Role等）';
COMMENT ON COLUMN audit_log_archive.entity_id IS '实体ID';
COMMENT ON COLUMN audit_log_archive.operation_type IS '操作类型（CREATE, UPDATE, DELETE）';
COMMENT ON COLUMN audit_log_archive.operator IS '操作人';
COMMENT ON COLUMN audit_log_archive.operation_time IS '操作时间';
COMMENT ON COLUMN audit_log_archive.before_data IS '变更前数据（JSON格式）';
COMMENT ON COLUMN audit_log_archive.after_data IS '变更后数据（JSON格式）';
COMMENT ON COLUMN audit_log_archive.changed_fields IS '变更字段列表';
COMMENT ON COLUMN audit_log_archive.ip_address IS 'IP地址';
COMMENT ON COLUMN audit_log_archive.user_agent IS '用户代理';
COMMENT ON COLUMN audit_log_archive.description IS '操作描述';
COMMENT ON COLUMN audit_log_archive.created_at IS '记录创建时间';
COMMENT ON COLUMN audit_log_archive.archived_at IS '归档时间';
