-- =============================================================
-- CET324 Advanced CyberSecurity -- Assignment 2
-- Database Schema: Secure Registration System (ACS 1)
-- Run once: mysql -u root < setup.sql
-- =============================================================

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

USE acs_secure_reg;

-- -------------------------------------------------------------
-- Table: users
-- Stores registered user accounts.
-- Passwords are stored as bcrypt hashes ONLY -- never plaintext.
-- email_verified = 1 only after the registration OTP is confirmed.
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id             INT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    username       VARCHAR(50)   NOT NULL,
    email          VARCHAR(255)  NOT NULL,
    password_hash  VARCHAR(255)  NOT NULL,              -- bcrypt hash (cost 12)
    email_verified TINYINT(1)    NOT NULL DEFAULT 0,    -- 1 after OTP confirmed
    created_at     TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    last_pw_change TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP
                                          ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_username (username),
    UNIQUE KEY uq_email    (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- Table: password_history
-- Keeps previous password hashes to prevent password reuse.
-- Policy: last 5 passwords cannot be reused.
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS password_history (
    id            INT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id       INT UNSIGNED  NOT NULL,
    password_hash VARCHAR(255)  NOT NULL,
    changed_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_pw_history_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- Table: registration_attempts
-- Tracks registration attempts per IP for rate limiting.
-- Records older than 10 minutes are ignored by the application.
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS registration_attempts (
    id           INT UNSIGNED  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ip_address   VARCHAR(45)   NOT NULL,               -- supports IPv4 and IPv6
    attempted_at TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ip_time (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -------------------------------------------------------------
-- Table: verification_codes
-- Stores bcrypt-hashed email OTP codes for:
--   purpose = 'register' : post-registration email verification
--   purpose = 'login'    : second factor at login
-- Codes expire after 10 minutes and are deleted on first use.
-- -------------------------------------------------------------
CREATE TABLE IF NOT EXISTS verification_codes (
    code_id    INT UNSIGNED               NOT NULL AUTO_INCREMENT PRIMARY KEY,
    user_id    INT UNSIGNED               NOT NULL,
    code_hash  VARCHAR(255)               NOT NULL,  -- bcrypt hash (cost 10)
    purpose    ENUM('register', 'login')  NOT NULL DEFAULT 'login',
    expires_at DATETIME                   NOT NULL,
    created_at TIMESTAMP                  NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_verif_code_user
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_purpose (user_id, purpose)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;