-- Migration 0013: create_org_structure_and_auth_hardening
-- Auto-generated from database/schema.sql by database/build_migrations.php
-- Do not hand-edit this file — edit schema.sql and re-run that script instead.

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 13 — ORG STRUCTURE & AUTH/ADMIN HARDENING (Phase 2)
-- Additive only — no existing table from Sections 0–12 is altered
-- except `users`, which gains new NULLABLE columns (safe on a table
-- that already has data) for account-lock, password-expiry, email
-- verification, and department membership.
-- =====================================================================

ALTER TABLE users
    ADD COLUMN department_id BIGINT UNSIGNED NULL AFTER role_id,
    ADD COLUMN email_verified_at DATETIME NULL AFTER email,
    ADD COLUMN password_changed_at DATETIME NULL AFTER password_hash,
    ADD COLUMN failed_login_count SMALLINT UNSIGNED NOT NULL DEFAULT 0 AFTER password_changed_at,
    ADD COLUMN locked_until DATETIME NULL AFTER failed_login_count,
    ADD KEY idx_users_department (department_id);

CREATE TABLE IF NOT EXISTS departments (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NULL COMMENT 'NULL = spans both brands (e.g. Finance, IT)',
    name              VARCHAR(120)    NOT NULL,
    description       VARCHAR(255)    NULL,
    created_by        BIGINT UNSIGNED NULL,
    updated_by        BIGINT UNSIGNED NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at        DATETIME        NULL,
    KEY idx_departments_business_line (business_line_id),
    KEY idx_departments_deleted_at (deleted_at),
    CONSTRAINT fk_departments_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_departments_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_departments_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Now that `departments` exists, users.department_id can be constrained.
ALTER TABLE users
    ADD CONSTRAINT fk_users_department FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS teams (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    department_id     BIGINT UNSIGNED NULL,
    business_line_id  BIGINT UNSIGNED NULL,
    name              VARCHAR(120)    NOT NULL,
    description       VARCHAR(255)    NULL,
    lead_user_id      BIGINT UNSIGNED NULL,
    created_by        BIGINT UNSIGNED NULL,
    updated_by        BIGINT UNSIGNED NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at        DATETIME        NULL,
    KEY idx_teams_department (department_id),
    KEY idx_teams_business_line (business_line_id),
    KEY idx_teams_lead (lead_user_id),
    KEY idx_teams_deleted_at (deleted_at),
    CONSTRAINT fk_teams_department FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_teams_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_teams_lead_user FOREIGN KEY (lead_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_teams_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_teams_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS team_members (
    team_id    BIGINT UNSIGNED NOT NULL,
    user_id    BIGINT UNSIGNED NOT NULL,
    joined_at  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (team_id, user_id),
    KEY idx_team_members_user (user_id),
    CONSTRAINT fk_team_members_team FOREIGN KEY (team_id) REFERENCES teams(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_team_members_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Prevents password reuse — PasswordPolicyService checks the last N
-- (configurable) hashes here before allowing a change to succeed.
CREATE TABLE IF NOT EXISTS password_histories (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id       BIGINT UNSIGNED NOT NULL,
    password_hash VARCHAR(255)    NOT NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_password_histories_user (user_id, created_at),
    CONSTRAINT fk_password_histories_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Email verification AND email-change re-verification both use this
-- table; `new_email` is NULL for a first-time verification of the
-- address already on the account, and set when verifying a change.
CREATE TABLE IF NOT EXISTS email_verification_tokens (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT UNSIGNED NOT NULL,
    token_hash   CHAR(64)        NOT NULL,
    new_email    VARCHAR(191)    NULL,
    expires_at   DATETIME        NOT NULL,
    verified_at  DATETIME        NULL,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_email_verification_token (token_hash),
    KEY idx_email_verification_user (user_id),
    CONSTRAINT fk_email_verification_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Single-use backup codes for 2FA account recovery (lost authenticator
-- device). Generated once when 2FA is enabled; each code is consumed
-- (used_at set) the first time it's used and never accepted again.
CREATE TABLE IF NOT EXISTS two_factor_recovery_codes (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id    BIGINT UNSIGNED NOT NULL,
    code_hash  CHAR(64)        NOT NULL,
    used_at    DATETIME        NULL,
    created_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_2fa_recovery_user (user_id),
    CONSTRAINT fk_2fa_recovery_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Freeform per-user preferences (theme, density, default landing page,
-- language...) — same key-value shape as `settings`, scoped to a user
-- instead of the whole workspace, so a new preference never needs a
-- migration.
CREATE TABLE IF NOT EXISTS user_preferences (
    user_id     BIGINT UNSIGNED NOT NULL,
    pref_key    VARCHAR(80)     NOT NULL,
    pref_value  TEXT            NULL,
    updated_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, pref_key),
    CONSTRAINT fk_user_preferences_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


SET FOREIGN_KEY_CHECKS = 1;
