-- SafeGuard — Phase 3 additions (admin panel)
-- Run this AFTER schema.sql + schema_phase2.sql.

SET NAMES utf8mb4;

-- ---------------------------------------------------------------
-- Admins — separate table from `users` on purpose. Admin accounts
-- are provisioned by a Super Admin, never through public registration.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admins (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name            VARCHAR(120) NOT NULL,
    email           VARCHAR(150) NOT NULL UNIQUE,
    password_hash   VARCHAR(255) NOT NULL,
    role            ENUM('super_admin','moderator','finance_admin','support_admin','reviewer','read_only') NOT NULL DEFAULT 'reviewer',
    is_active       TINYINT(1) NOT NULL DEFAULT 1,
    failed_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0,
    locked_until    DATETIME NULL,
    last_login_at   DATETIME NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------
-- Audit log — every moderation/admin action, per spec section 33/45.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS admin_activity_logs (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    admin_id    INT UNSIGNED NULL,          -- nullable: kept even if the admin account is later removed
    admin_name  VARCHAR(120) NOT NULL,      -- denormalized snapshot for a stable audit trail
    action      VARCHAR(60)  NOT NULL,      -- e.g. 'user.approve', 'report.reject', 'recharge.approve'
    target_type VARCHAR(30)  NOT NULL,      -- 'user' | 'report' | 'recharge' | 'admin'
    target_id   INT UNSIGNED NOT NULL,
    description VARCHAR(255) NULL,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (admin_id) REFERENCES admins(id) ON DELETE SET NULL,
    INDEX idx_target (target_type, target_id),
    INDEX idx_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------
-- Small extras admin screens need
-- ---------------------------------------------------------------
ALTER TABLE users
    ADD COLUMN admin_note TEXT NULL AFTER account_status;

ALTER TABLE reports
    ADD COLUMN admin_note TEXT NULL AFTER description,
    ADD COLUMN assigned_moderator_id INT UNSIGNED NULL AFTER admin_note;

-- NOTE: no admin account is seeded here on purpose (a hard-coded password
-- hash in a SQL file is a bad habit). After running this file, visit
-- /admin/setup once — it only works while the `admins` table is empty and
-- lets you create the first Super Admin account through a normal form.