-- +goose Up
-- +goose StatementBegin
CREATE TABLE IF NOT EXISTS content_reports (
    id BIGSERIAL PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    content_id BIGINT NOT NULL,
    reporter_id BIGINT NOT NULL,
    reason VARCHAR(64) NOT NULL DEFAULT '',
    detail TEXT NOT NULL DEFAULT '',
    status VARCHAR(32) NOT NULL DEFAULT 'pending',
    handled_by BIGINT NOT NULL DEFAULT 0,
    handled_action VARCHAR(32) NOT NULL DEFAULT '',
    handled_reason VARCHAR(255) NOT NULL DEFAULT '',
    handled_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

COMMENT ON TABLE content_reports IS '内容举报：记录用户对内容的举报与超管处置结果，用于内容治理。';
COMMENT ON COLUMN content_reports.id IS '主键ID。';
COMMENT ON COLUMN content_reports.tenant_id IS '租户ID；用途：跨租户筛选与归属；约束：必须存在。';
COMMENT ON COLUMN content_reports.content_id IS '内容ID；用途：定位被举报内容；约束：必须存在。';
COMMENT ON COLUMN content_reports.reporter_id IS '举报用户ID；用途：追溯举报来源；约束：必须存在。';
COMMENT ON COLUMN content_reports.reason IS '举报原因；用途：快速分类；约束：建议前端枚举。';
COMMENT ON COLUMN content_reports.detail IS '举报描述；用途：补充说明与证据描述；可为空字符串。';
COMMENT ON COLUMN content_reports.status IS '处置状态；用途：pending/approved/rejected；约束：默认 pending。';
COMMENT ON COLUMN content_reports.handled_by IS '处理人ID；用途：审计追踪；默认 0 表示未处理。';
COMMENT ON COLUMN content_reports.handled_action IS '处理动作；用途：记录处置动作如 block/unpublish/ignore；可为空字符串。';
COMMENT ON COLUMN content_reports.handled_reason IS '处理说明；用途：超管处理备注；可为空字符串。';
COMMENT ON COLUMN content_reports.handled_at IS '处理时间；用途：追踪处理时效；未处理为空。';
COMMENT ON COLUMN content_reports.created_at IS '创建时间；用途：统计与排序。';
COMMENT ON COLUMN content_reports.updated_at IS '更新时间；用途：状态变化追踪。';

CREATE INDEX IF NOT EXISTS content_reports_tenant_id_idx ON content_reports(tenant_id);
CREATE INDEX IF NOT EXISTS content_reports_content_id_idx ON content_reports(content_id);
CREATE INDEX IF NOT EXISTS content_reports_reporter_id_idx ON content_reports(reporter_id);
CREATE INDEX IF NOT EXISTS content_reports_status_idx ON content_reports(status);
CREATE INDEX IF NOT EXISTS content_reports_created_at_idx ON content_reports(created_at);
CREATE INDEX IF NOT EXISTS content_reports_handled_at_idx ON content_reports(handled_at);
-- +goose StatementEnd

-- +goose Down
-- +goose StatementBegin
DROP TABLE IF EXISTS content_reports;
-- +goose StatementEnd