-- Abuse reports from the /abuse form, abuse@ mail and the per-site report endpoint. One queue for admins;
-- reporter_hash is an HMAC of the reporter IP or sender address so repeat reports count once per reporter.
CREATE TABLE abuse_reports (
  id TEXT PRIMARY KEY,
  source TEXT NOT NULL CHECK (source IN ('web', 'email', 'site')),
  target_url TEXT NOT NULL,
  site_id TEXT REFERENCES sites(id) ON DELETE SET NULL,
  category TEXT NOT NULL,
  details TEXT NOT NULL DEFAULT '',
  reporter_email TEXT,
  reporter_hash TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'actioned', 'dismissed')),
  resolution TEXT,
  resolved_by TEXT REFERENCES accounts(id) ON DELETE SET NULL,
  resolved_at INTEGER,
  created_at INTEGER NOT NULL
);

CREATE INDEX abuse_reports_status ON abuse_reports(status, created_at);

CREATE INDEX abuse_reports_site ON abuse_reports(site_id, status, reporter_hash);

-- Set while a site is held for abuse review, so restoring clears only abuse pauses and never other disables.
ALTER TABLE sites ADD COLUMN paused_at INTEGER;

INSERT INTO abuse_reports (id, source, target_url, site_id, category, details, reporter_hash, created_at)
SELECT r.id, 'site', s.label, r.site_id, 'other', r.reason, r.reporter_ip_hash, r.created_at
FROM site_abuse_reports r JOIN sites s ON s.id = r.site_id;

UPDATE sites SET paused_at = updated_at WHERE disabled_reason = 'Disabled after abuse reports';