-- =========================================================
-- MULLERWALLET DATABASE SCHEMA
-- Module: Authentication (Register / Login / Email OTP)
-- =========================================================

CREATE DATABASE IF NOT EXISTS mullerwallet
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE mullerwallet;

-- ---------------------------------------------------------
-- USERS TABLE
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    full_name       VARCHAR(100)        NOT NULL,
    email           VARCHAR(150)        NOT NULL UNIQUE,
    password_hash   VARCHAR(255)        NOT NULL,
    status          ENUM('unverified','verified','suspended') NOT NULL DEFAULT 'unverified',
    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_status (status)
) ENGINE=InnoDB;

-- ---------------------------------------------------------
-- 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;

-- ---------------------------------------------------------
-- SESSIONS TABLE (optional but recommended for a wallet app)
-- Lets you track/revoke logins instead of relying only on PHP session
-- ---------------------------------------------------------
CREATE TABLE IF NOT EXISTS user_sessions (
    id              CHAR(64)            NOT NULL PRIMARY KEY,   -- random token
    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;