-- Novalon管理系统审计日志表
-- 版本: V7
-- 描述: 创建审计日志表，记录数据变更前后的完整对比

CREATE TABLE IF NOT EXISTS audit_log (
    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 DEFAULT CURRENT_TIMESTAMP,
    before_data JSONB,
    after_data JSONB,
    changed_fields TEXT[],
    ip_address VARCHAR(50),
    user_agent TEXT,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_audit_log_entity_type ON audit_log(entity_type);
CREATE INDEX idx_audit_log_entity_id ON audit_log(entity_id);
CREATE INDEX idx_audit_log_operation_type ON audit_log(operation_type);
CREATE INDEX idx_audit_log_operator ON audit_log(operator);
CREATE INDEX idx_audit_log_operation_time ON audit_log(operation_time);
CREATE INDEX idx_audit_log_entity ON audit_log(entity_type, entity_id);

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