-- SafeGuard — Phase 4 additions (appeal system)
-- Run this AFTER schema.sql + schema_phase2.sql + schema_phase3.sql.

SET NAMES utf8mb4;

CREATE TABLE IF NOT EXISTS appeals (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    report_id           INT UNSIGNED NOT NULL,
    opened_by_user_id   INT UNSIGNED NOT NULL,   -- the respondent — person disputing the report
    deposit_amount      DECIMAL(12,2) NOT NULL DEFAULT 500,
    deposit_status      ENUM('held','refunded','forfeited') NOT NULL DEFAULT 'held',
    status              ENUM('open','evidence_review','decision_pending','resolved') NOT NULL DEFAULT 'open',
    decision            ENUM('report_removed','report_upheld') NULL,
    decision_reason     TEXT NULL,
    resolved_by_admin_id INT UNSIGNED NULL,
    resolved_at         DATETIME NULL,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE,
    FOREIGN KEY (opened_by_user_id) REFERENCES users(id),
    FOREIGN KEY (resolved_by_admin_id) REFERENCES admins(id) ON DELETE SET NULL,
    INDEX idx_report (report_id),
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS appeal_messages (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    appeal_id       INT UNSIGNED NOT NULL,
    sender_type     ENUM('reporter','respondent','admin','system') NOT NULL,
    sender_user_id  INT UNSIGNED NULL,
    sender_admin_id INT UNSIGNED NULL,
    message         TEXT NOT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (appeal_id) REFERENCES appeals(id) ON DELETE CASCADE,
    INDEX idx_appeal (appeal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS appeal_evidence (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    appeal_id       INT UNSIGNED NOT NULL,
    uploaded_by_user_id INT UNSIGNED NULL,
    file_path       VARCHAR(255) NOT NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (appeal_id) REFERENCES appeals(id) ON DELETE CASCADE,
    INDEX idx_appeal (appeal_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;