-- =============================================
-- 素材校验日志表 (material_verify_log) - cid数据库
-- =============================================

CREATE TABLE IF NOT EXISTS material_verify_log
(
    id              BIGSERIAL PRIMARY KEY,
    tenant_id       BIGINT DEFAULT 0,
    material_type   VARCHAR(20) NOT NULL,
    material_id     VARCHAR(100) NOT NULL,
    source_table    VARCHAR(50) NOT NULL,
    source_id       BIGINT NOT NULL,
    account_id      BIGINT NOT NULL,
    task_id         VARCHAR(100),
    request_params  TEXT,
    response_result TEXT,
    verify_status   VARCHAR(20) DEFAULT 'PENDING' NOT NULL,
    suggestion      INT DEFAULT -1,
    label           INT DEFAULT -1,
    result_type     INT DEFAULT -1,
    error_msg       TEXT,
    check_time      BIGINT,
    duration_ms     BIGINT DEFAULT 0,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_by      VARCHAR(100) DEFAULT 'system'
);

CREATE INDEX IF NOT EXISTS idx_material_verify_log_material_id ON material_verify_log(material_id);
CREATE INDEX IF NOT EXISTS idx_material_verify_log_source ON material_verify_log(source_table, source_id);
CREATE INDEX IF NOT EXISTS idx_material_verify_log_status ON material_verify_log(verify_status);
CREATE INDEX IF NOT EXISTS idx_material_verify_log_account ON material_verify_log(account_id);
CREATE INDEX IF NOT EXISTS idx_material_verify_log_task ON material_verify_log(task_id);
CREATE INDEX IF NOT EXISTS idx_material_verify_log_created ON material_verify_log(created_at);

COMMENT ON TABLE material_verify_log IS '素材校验日志表';
COMMENT ON COLUMN material_verify_log.material_type IS '素材类型 IMAGE/VIDEO';
COMMENT ON COLUMN material_verify_log.material_id IS '素材ID(image_id/video_id)';
COMMENT ON COLUMN material_verify_log.verify_status IS '校验状态: PENDING=待校验, VERIFIED=通过, REJECTED=不通过';
COMMENT ON COLUMN material_verify_log.suggestion IS '易盾处置建议: 0=通过, 1=嫌疑, 2=不通过';
COMMENT ON COLUMN material_verify_log.error_msg IS '错误信息';