009_reports.sql

CREATE TABLE IF NOT EXISTS reports (
	id INT PRIMARY KEY AUTO_INCREMENT,
	reporter_id INT NOT NULL,
	reported_id INT NOT NULL,
	reason ENUM(
    	'spam',
    	'harassment',
    	'inappropriate_content',
    	'fake_profile',
    	'hate_speech',
    	'scam_or_fraud',
    	'other'
  	) NOT NULL DEFAULT 'other',
	description TEXT DEFAULT NULL,
	status ENUM(
		'pending',
		'reviewed',
		'action_taken',
		'dismissed'
	) NOT NULL DEFAULT 'pending',
	reviewed_by_id INT DEFAULT NULL,
	created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
	updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,


  	INDEX idx_reports_status (status),
  	INDEX idx_reports_reported (reported_id),
  	INDEX idx_reports_reporter (reporter_id),

	CONSTRAINT fk_reports_reporter
		FOREIGN KEY (reporter_id)
		REFERENCES users(id)
		ON DELETE CASCADE,

	CONSTRAINT fk_reports_reported
		FOREIGN KEY (reported_id)
		REFERENCES users(id)
		ON DELETE CASCADE,

	CONSTRAINT fk_reports_reviewer
    	FOREIGN KEY (reviewed_by_id)
    	REFERENCES users(id)
    	ON DELETE SET NULL
);