-- SafeGuard — Phase 2 additions (accounts, wallet, notifications)
-- Run this AFTER schema.sql on an existing Phase 1 database.
-- (If starting fresh, just run schema.sql then this file, then seed.sql.)

SET NAMES utf8mb4;

-- ---------------------------------------------------------------
-- Extend users with fields the registration form actually needs
-- ---------------------------------------------------------------
ALTER TABLE users
    ADD COLUMN nid_name        VARCHAR(120) NULL AFTER full_name,
    ADD COLUMN failed_attempts TINYINT UNSIGNED NOT NULL DEFAULT 0 AFTER account_status,
    ADD COLUMN locked_until    DATETIME NULL AFTER failed_attempts,
    ADD COLUMN last_login_at   DATETIME NULL AFTER locked_until;

-- ---------------------------------------------------------------
-- Wallets — one per user. Bonus and cash tracked separately so
-- promotional balance restrictions can be enforced (bonus can only
-- pay report fees, not appeal deposits, etc.).
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS wallets (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL UNIQUE,
    bonus_balance   DECIMAL(12,2) NOT NULL DEFAULT 0,
    cash_balance    DECIMAL(12,2) NOT NULL DEFAULT 0,
    updated_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

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

CREATE TABLE IF NOT EXISTS wallet_transactions (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL,
    type            ENUM('welcome_bonus','report_submission','recharge','refund','appeal_deposit','appeal_settlement','admin_adjustment') NOT NULL,
    amount          DECIMAL(12,2) NOT NULL,       -- positive = credit, negative = debit
    source          ENUM('bonus','cash') NOT NULL DEFAULT 'cash',
    balance_after   DECIMAL(12,2) NOT NULL,       -- total balance (bonus+cash) after this transaction
    description     VARCHAR(180) NULL,
    reference_type  VARCHAR(30) NULL,             -- e.g. 'report'
    reference_id    INT UNSIGNED NULL,
    created_at      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------
-- Notifications
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notifications (
    id          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     INT UNSIGNED NOT NULL,
    category    ENUM('account','report','appeal','wallet') NOT NULL DEFAULT 'account',
    title       VARCHAR(150) NOT NULL,
    message     VARCHAR(255) NOT NULL,
    link        VARCHAR(255) NULL,
    is_read     TINYINT(1) NOT NULL DEFAULT 0,
    created_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_unread (user_id, is_read)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ---------------------------------------------------------------
-- Recharges — manual/offline payment requests until a gateway is wired up.
-- Admin approval (Phase 3) moves 'pending' -> 'completed' and credits cash_balance.
-- ---------------------------------------------------------------
CREATE TABLE IF NOT EXISTS recharges (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED NOT NULL,
    amount          DECIMAL(12,2) NOT NULL,
    method          VARCHAR(40) NULL,           -- e.g. 'bkash', 'nagad', 'manual'
    transaction_ref VARCHAR(80) NULL,           -- customer-supplied payment reference
    status          ENUM('pending','processing','completed','failed','refunded') NOT NULL DEFAULT 'pending',
    admin_note      VARCHAR(255) NULL,
    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) ON DELETE CASCADE,
    INDEX idx_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;