/*
 ============================================================
  TechStackBase – Full Database Schema
  Version: v1.0.0
  Target: MySQL 8+ / MariaDB 10.4+
 ============================================================

  IMPORTANT:
  • Run on a CLEAN database
  • Uses InnoDB everywhere
  • UTF8MB4 everywhere
  • Foreign keys ordered correctly
 ============================================================
*/

SET NAMES utf8mb4;
SET time_zone = '+00:00';

SET FOREIGN_KEY_CHECKS = 0;

/* ============================================================
   CORE TABLES
   ============================================================ */

/* ----------------------------
   COMPANIES
---------------------------- */
CREATE TABLE companies (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    contact_name VARCHAR(255) NULL,
    contact_email VARCHAR(255) NULL,
    contact_phone VARCHAR(64) NULL,
    status TINYINT(1) NOT NULL DEFAULT 1,

    /* Reserved future fields */
    field_01 VARCHAR(255) NULL,
    field_02 VARCHAR(255) NULL,
    field_03 VARCHAR(255) NULL,
    field_04 VARCHAR(255) NULL,
    field_05 VARCHAR(255) NULL,
    field_06 VARCHAR(255) NULL,
    field_07 VARCHAR(255) NULL,
    field_08 VARCHAR(255) NULL,
    field_09 VARCHAR(255) NULL,
    field_10 VARCHAR(255) NULL,
    field_11 VARCHAR(255) NULL,
    field_12 VARCHAR(255) NULL,
    field_13 VARCHAR(255) NULL,
    field_14 VARCHAR(255) NULL,
    field_15 VARCHAR(255) NULL,
    field_16 VARCHAR(255) NULL,
    field_17 VARCHAR(255) NULL,
    field_18 VARCHAR(255) NULL,
    field_19 VARCHAR(255) NULL,
    field_20 VARCHAR(255) NULL,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

/* ----------------------------
   USERS
---------------------------- */
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    email VARCHAR(255) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    first_name VARCHAR(128) NOT NULL,
    last_name VARCHAR(128) NOT NULL,
    role ENUM('user','company','support','admin') NOT NULL DEFAULT 'user',
    status TINYINT(1) NOT NULL DEFAULT 1,
    email_verified TINYINT(1) NOT NULL DEFAULT 0,

    /* Reserved future fields */
    field_01 VARCHAR(255) NULL,
    field_02 VARCHAR(255) NULL,
    field_03 VARCHAR(255) NULL,
    field_04 VARCHAR(255) NULL,
    field_05 VARCHAR(255) NULL,
    field_06 VARCHAR(255) NULL,
    field_07 VARCHAR(255) NULL,
    field_08 VARCHAR(255) NULL,
    field_09 VARCHAR(255) NULL,
    field_10 VARCHAR(255) NULL,
    field_11 VARCHAR(255) NULL,
    field_12 VARCHAR(255) NULL,
    field_13 VARCHAR(255) NULL,
    field_14 VARCHAR(255) NULL,
    field_15 VARCHAR(255) NULL,
    field_16 VARCHAR(255) NULL,
    field_17 VARCHAR(255) NULL,
    field_18 VARCHAR(255) NULL,
    field_19 VARCHAR(255) NULL,
    field_20 VARCHAR(255) NULL,

    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_users_email (email),
    INDEX idx_users_company (company_id),

    CONSTRAINT fk_users_company
        FOREIGN KEY (company_id)
        REFERENCES companies(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

/* ============================================================
   AUTH & SECURITY
   ============================================================ */

/* ----------------------------
   AUTH TOKENS
---------------------------- */
CREATE TABLE auth_tokens (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_auth_token (token_hash),
    INDEX idx_auth_user (user_id),
    INDEX idx_auth_expiry (expires_at),

    CONSTRAINT fk_auth_tokens_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

/* ----------------------------
   EMAIL VERIFICATIONS
---------------------------- */
CREATE TABLE email_verifications (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_email_ver_user (user_id),
    UNIQUE KEY uq_email_ver_token (token_hash),

    CONSTRAINT fk_email_ver_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

/* ----------------------------
   PASSWORD RESETS
---------------------------- */
CREATE TABLE password_resets (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    token_hash CHAR(64) NOT NULL,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_pass_reset_user (user_id),
    UNIQUE KEY uq_pass_reset_token (token_hash),

    CONSTRAINT fk_pass_reset_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE CASCADE
) ENGINE=InnoDB;

/* ----------------------------
   RATE LIMITS
---------------------------- */
CREATE TABLE rate_limits (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rate_key CHAR(64) NOT NULL,
    endpoint VARCHAR(128) NOT NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_id BIGINT UNSIGNED NULL,
    attempts INT UNSIGNED NOT NULL DEFAULT 1,
    expires_at DATETIME NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_rate_key (rate_key),
    INDEX idx_rate_ip (ip_address),
    INDEX idx_rate_user (user_id),
    INDEX idx_rate_expiry (expires_at),

    CONSTRAINT fk_rate_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL
) ENGINE=InnoDB;

/* ============================================================
   SYSTEM CONFIGURATION
   ============================================================ */

/* ----------------------------
   MODULES
---------------------------- */
CREATE TABLE modules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(64) NOT NULL,
    name VARCHAR(128) NOT NULL,
    description TEXT NULL,
    icon VARCHAR(64) NULL,
    sort_order INT NOT NULL DEFAULT 0,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_modules_code (code)
) ENGINE=InnoDB;

/* ----------------------------
   PERMISSIONS
---------------------------- */
CREATE TABLE permissions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role ENUM('user','company','support','admin') NOT NULL,
    module_code VARCHAR(64) NOT NULL,
    can_read TINYINT(1) NOT NULL DEFAULT 0,
    can_write TINYINT(1) NOT NULL DEFAULT 0,
    can_delete TINYINT(1) NOT NULL DEFAULT 0,

    UNIQUE KEY uq_perm_role_module (role, module_code),

    CONSTRAINT fk_perm_module
        FOREIGN KEY (module_code)
        REFERENCES modules(code)
        ON DELETE CASCADE
) ENGINE=InnoDB;

/* ============================================================
   BILLING & SUBSCRIPTIONS
   ============================================================ */

/* ----------------------------
   SUBSCRIPTION PLANS
---------------------------- */
CREATE TABLE subscription_plans (
    code VARCHAR(64) PRIMARY KEY,
    name VARCHAR(128) NOT NULL,
    description TEXT NULL,
    price DECIMAL(10,2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'ZAR',
    billing_cycle ENUM('monthly','yearly') NOT NULL,
    active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

/* ----------------------------
   PLAN MODULES
---------------------------- */
CREATE TABLE plan_modules (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plan_code VARCHAR(64) NOT NULL,
    module_code VARCHAR(64) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    UNIQUE KEY uq_plan_module (plan_code, module_code),

    CONSTRAINT fk_plan_modules_plan
        FOREIGN KEY (plan_code)
        REFERENCES subscription_plans(code)
        ON DELETE CASCADE,

    CONSTRAINT fk_plan_modules_module
        FOREIGN KEY (module_code)
        REFERENCES modules(code)
        ON DELETE CASCADE
) ENGINE=InnoDB;

/* ----------------------------
   COMPANY SUBSCRIPTIONS
---------------------------- */
CREATE TABLE company_subscriptions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    plan_code VARCHAR(64) NOT NULL,
    status ENUM('pending','active','cancelled','expired') NOT NULL DEFAULT 'pending',
    started_at DATETIME NULL,
    expires_at DATETIME NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_company_subs_company (company_id),
    INDEX idx_company_subs_status (status),

    CONSTRAINT fk_company_subs_company
        FOREIGN KEY (company_id)
        REFERENCES companies(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_company_subs_plan
        FOREIGN KEY (plan_code)
        REFERENCES subscription_plans(code)
        ON DELETE RESTRICT
) ENGINE=InnoDB;

/* ----------------------------
   PAYMENTS
---------------------------- */
CREATE TABLE payments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    company_id BIGINT UNSIGNED NOT NULL,
    subscription_id BIGINT UNSIGNED NOT NULL,
    reference VARCHAR(64) NOT NULL,
    plan_code VARCHAR(64) NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency CHAR(3) NOT NULL DEFAULT 'ZAR',
    gateway VARCHAR(32) NOT NULL DEFAULT 'payfast',
    status ENUM('pending','paid','failed','cancelled','reversed') NOT NULL DEFAULT 'pending',
    gateway_payload JSON NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,

    UNIQUE KEY uq_payment_reference (reference),

    CONSTRAINT fk_payment_company
        FOREIGN KEY (company_id)
        REFERENCES companies(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_payment_subscription
        FOREIGN KEY (subscription_id)
        REFERENCES company_subscriptions(id)
        ON DELETE CASCADE,

    CONSTRAINT fk_payment_plan
        FOREIGN KEY (plan_code)
        REFERENCES subscription_plans(code)
        ON DELETE RESTRICT
) ENGINE=InnoDB;

/* ============================================================
   AUDIT LOGS (IMMUTABLE)
   ============================================================ */

CREATE TABLE audit_logs (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NULL,
    company_id BIGINT UNSIGNED NULL,
    module_code VARCHAR(64) NOT NULL,
    action VARCHAR(64) NOT NULL,
    payload JSON NULL,
    ip_address VARCHAR(45) NOT NULL,
    user_agent VARCHAR(255) NOT NULL,
    prev_hash CHAR(64) NULL,
    hash CHAR(64) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    INDEX idx_audit_user (user_id),
    INDEX idx_audit_company (company_id),
    INDEX idx_audit_created (created_at),

    CONSTRAINT fk_audit_user
        FOREIGN KEY (user_id)
        REFERENCES users(id)
        ON DELETE SET NULL,

    CONSTRAINT fk_audit_company
        FOREIGN KEY (company_id)
        REFERENCES companies(id)
        ON DELETE SET NULL
) ENGINE=InnoDB;

SET FOREIGN_KEY_CHECKS = 1;
