-- =========================================================
-- MULLERWALLET DATABASE SCHEMA (cPanel / phpMyAdmin version)
-- Module: Authentication (Register / Login / Email OTP)
--
-- NOTE: Do NOT run CREATE DATABASE here.
-- Create the database first via cPanel > MySQL Databases,
-- then import this file into that database via phpMyAdmin.
-- =========================================================

-- ---------------------------------------------------------
-- USERS TABLE
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name      VARCHAR(60)         NOT NULL,
    last_name       VARCHAR(60)         NOT NULL,
    phone_number    VARCHAR(20)         NOT NULL UNIQUE,
    email           VARCHAR(150)        NOT NULL UNIQUE,
    password_hash   VARCHAR(255)        NOT NULL,
    status          ENUM('unverified','verified','suspended') NOT NULL DEFAULT 'unverified',
    two_fa_enabled  TINYINT(1)          NOT NULL DEFAULT 1,
    failed_attempts TINYINT UNSIGNED    NOT NULL DEFAULT 0,
    locked_until    DATETIME            DEFAULT NULL,
    created_at      DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at      DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP
                                         ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_users_email (email),
    INDEX idx_users_phone (phone_number),
    INDEX idx_users_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- OTPS TABLE
-- Handles OTPs for registration, login (2FA), and password reset
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS otps (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id         INT UNSIGNED        NOT NULL,
    otp_code        VARCHAR(10)         NOT NULL,
    purpose         ENUM('register','login','reset_password') NOT NULL,
    is_used         TINYINT(1)          NOT NULL DEFAULT 0,
    attempts        TINYINT UNSIGNED    NOT NULL DEFAULT 0,
    expires_at      DATETIME            NOT NULL,
    created_at      DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,

    CONSTRAINT fk_otps_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    INDEX idx_otps_user_purpose (user_id, purpose),
    INDEX idx_otps_expires (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------
-- USER_SESSIONS TABLE (optional, for later use)
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_sessions (
    id              CHAR(64)            NOT NULL PRIMARY KEY,
    user_id         INT UNSIGNED        NOT NULL,
    ip_address      VARCHAR(45)         DEFAULT NULL,
    user_agent      VARCHAR(255)        DEFAULT NULL,
    created_at      DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_active_at  DATETIME            NOT NULL DEFAULT CURRENT_TIMESTAMP,
    expires_at      DATETIME            NOT NULL,

    CONSTRAINT fk_sessions_user
        FOREIGN KEY (user_id) REFERENCES users(id)
        ON DELETE CASCADE,

    INDEX idx_sessions_user (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;