-- SafeGuard — Phase 1 Schema (Public Directory)
-- Additional tables (wallets, appeals, admin, notifications) ship in later phases.

SET NAMES utf8mb4;
SET time_zone = '+06:00';

-- ---------------------------------------------------------------
-- Users (minimal — enough to show "Reported by" attribution).
-- Full registration/verification fields land in Phase 2.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    display_code    VARCHAR(20)  NOT NULL UNIQUE,   -- e.g. "User-4821", never the real name
    full_name       VARCHAR(120) NOT NULL,
    phone           VARCHAR(20)  NOT NULL UNIQUE,
    email           VARCHAR(150) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    nid_number_enc  VARBINARY(255) NULL,            -- encrypted at rest, never exposed publicly
    is_verified     TINYINT(1)   NOT NULL DEFAULT 0,
    account_status  ENUM('pending','approved','rejected','suspended') NOT NULL DEFAULT 'pending',
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------
-- Categories
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS report_categories (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    slug        VARCHAR(60)  NOT NULL UNIQUE,
    name        VARCHAR(80)  NOT NULL,
    icon        VARCHAR(40)  NULL,           -- lucide icon name
    sort_order  INT UNSIGNED NOT NULL DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------
-- Reports
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS reports (
    id                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    report_code         VARCHAR(30)  NOT NULL UNIQUE,     -- e.g. SCM-2026-000123
    user_id             INT UNSIGNED NOT NULL,            -- reporter
    category_id         INT UNSIGNED NOT NULL,
    title               VARCHAR(160) NOT NULL,
    reported_name       VARCHAR(120) NOT NULL,
    reported_phone      VARCHAR(20)  NULL,
    reported_company    VARCHAR(150) NULL,
    scam_amount         DECIMAL(12,2) NOT NULL DEFAULT 0,
    scam_date           DATE NULL,
    description         TEXT NOT NULL,
    status              ENUM(
                            'draft','payment_pending','pending_review','under_investigation',
                            'approved','rejected','needs_evidence','appealed','archived','removed'
                        ) NOT NULL DEFAULT 'draft',
    fee_paid            TINYINT(1) NOT NULL DEFAULT 0,
    views               INT UNSIGNED NOT NULL DEFAULT 0,
    created_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id)     REFERENCES users(id),
    FOREIGN KEY (category_id) REFERENCES report_categories(id),
    FULLTEXT KEY ft_search (title, reported_name, reported_company, description),
    INDEX idx_status (status),
    INDEX idx_phone (reported_phone),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------
-- Report images (gallery — the main visual evidence shown up top)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS report_images (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    report_id   INT UNSIGNED NOT NULL,
    file_path   VARCHAR(255) NOT NULL,
    is_cover    TINYINT(1) NOT NULL DEFAULT 0,
    sort_order  INT UNSIGNED NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE,
    INDEX idx_report (report_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------
-- Report evidence (separate from gallery — receipts, chat logs, etc.)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS report_evidence (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    report_id       INT UNSIGNED NOT NULL,
    file_path       VARCHAR(255) NOT NULL,
    evidence_type   ENUM('screenshot','receipt','chat','profile','other') NOT NULL DEFAULT 'other',
    is_redacted     TINYINT(1) NOT NULL DEFAULT 0,
    sort_order      INT UNSIGNED NOT NULL DEFAULT 0,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE,
    INDEX idx_report (report_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------
-- Status history (audit trail — required by section 33/45 of the spec)
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS report_status_history (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    report_id   INT UNSIGNED NOT NULL,
    old_status  VARCHAR(30) NULL,
    new_status  VARCHAR(30) NOT NULL,
    changed_by  VARCHAR(60) NOT NULL,   -- "system" or admin display name
    note        VARCHAR(255) NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (report_id) REFERENCES reports(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;