-- Migration 0002: create_rbac_and_auth
-- 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 1 — RBAC, AUTH & MULTI-BUSINESS ACCESS
-- =====================================================================

-- The two companies operated from one Hub. Every business-owning table
-- carries a business_line_id so data (and later, permissions) can be
-- scoped per brand.
CREATE TABLE IF NOT EXISTS business_lines (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(120)    NOT NULL,
    slug         VARCHAR(60)     NOT NULL,
    legal_name   VARCHAR(191)    NULL,
    brand_color  CHAR(7)         NULL COMMENT 'Hex color, e.g. #174890',
    support_email VARCHAR(191)   NULL,
    support_phone VARCHAR(30)    NULL,
    is_active    TINYINT(1)      NOT NULL DEFAULT 1,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_business_lines_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(100)    NOT NULL,
    slug         VARCHAR(100)    NOT NULL,
    description  VARCHAR(255)    NULL,
    is_system    TINYINT(1)      NOT NULL DEFAULT 0 COMMENT '1 = built-in role, cannot be deleted',
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_roles_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Permissions are grouped by "module" to match the permission matrix
-- shown in Roles & Permissions (Leads & Customers, Scheduling, Email &
-- Campaigns, AI & Automation, Reports & Analytics, User Management,
-- Billing & Integrations).
CREATE TABLE IF NOT EXISTS permissions (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    module       VARCHAR(80)     NOT NULL,
    action       VARCHAR(80)     NOT NULL COMMENT 'view, create, update, delete, export, manage...',
    slug         VARCHAR(160)    NOT NULL COMMENT 'module.action, e.g. leads.create',
    description  VARCHAR(255)    NULL,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_permissions_slug (slug),
    KEY idx_permissions_module (module)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS role_permissions (
    role_id        BIGINT UNSIGNED NOT NULL,
    permission_id  BIGINT UNSIGNED NOT NULL,
    created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id, permission_id),
    CONSTRAINT fk_role_permissions_role
        FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_role_permissions_permission
        FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS users (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    role_id           BIGINT UNSIGNED NULL,
    first_name        VARCHAR(100)    NOT NULL,
    last_name         VARCHAR(100)    NOT NULL,
    email             VARCHAR(191)    NOT NULL,
    phone             VARCHAR(30)     NULL,
    job_title         VARCHAR(120)    NULL,
    password_hash     VARCHAR(255)    NOT NULL,
    avatar_path        VARCHAR(255)    NULL,
    status            ENUM('active','invited','suspended') NOT NULL DEFAULT 'invited',
    two_factor_enabled TINYINT(1)     NOT NULL DEFAULT 0,
    two_factor_secret  VARCHAR(191)   NULL,
    last_login_at     DATETIME        NULL,
    last_login_ip     VARCHAR(45)     NULL,
    timezone          VARCHAR(60)     NOT NULL DEFAULT 'America/Toronto',
    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,
    UNIQUE KEY uk_users_email (email),
    KEY idx_users_role (role_id),
    KEY idx_users_status (status),
    KEY idx_users_deleted_at (deleted_at),
    CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_users_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_users_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;

-- Per-user, per-permission allow/deny overrides on top of their role.
-- Lets an Operations Admin grant one Sales Rep an extra permission
-- without creating a whole new role.
CREATE TABLE IF NOT EXISTS user_permission_overrides (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id       BIGINT UNSIGNED NOT NULL,
    permission_id BIGINT UNSIGNED NOT NULL,
    effect        ENUM('allow','deny') NOT NULL DEFAULT 'allow',
    created_by    BIGINT UNSIGNED NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_permission (user_id, permission_id),
    CONSTRAINT fk_upo_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_upo_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_upo_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Which brand(s) a user is allowed to see/act on ("Both Brands",
-- "Homecare" only, "Janitorial" only in the User Management table).
CREATE TABLE IF NOT EXISTS user_business_lines (
    user_id          BIGINT UNSIGNED NOT NULL,
    business_line_id BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (user_id, business_line_id),
    CONSTRAINT fk_ubl_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_ubl_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Token-based invite flow ("Invite User" modal) — a pending user record
-- already exists in `users` with status='invited'; this row stores the
-- one-time token used to let them set their own password.
CREATE TABLE IF NOT EXISTS user_invitations (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT UNSIGNED NOT NULL,
    token_hash   CHAR(64)        NOT NULL COMMENT 'SHA-256 of the raw token sent by email',
    invited_by   BIGINT UNSIGNED NULL,
    expires_at   DATETIME        NOT NULL,
    accepted_at  DATETIME        NULL,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_user_invitations_token (token_hash),
    KEY idx_user_invitations_user (user_id),
    CONSTRAINT fk_invitations_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_invitations_invited_by FOREIGN KEY (invited_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 password_resets (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT UNSIGNED NOT NULL,
    token_hash   CHAR(64)        NOT NULL,
    ip_address   VARCHAR(45)     NULL,
    expires_at   DATETIME        NOT NULL,
    used_at      DATETIME        NULL,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_password_resets_token (token_hash),
    KEY idx_password_resets_user (user_id),
    CONSTRAINT fk_password_resets_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- "Keep me signed in" on the login page — a long-lived, rotating,
-- hashed selector/validator token pair (never a raw session id in a cookie).
CREATE TABLE IF NOT EXISTS remember_tokens (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id       BIGINT UNSIGNED NOT NULL,
    selector      CHAR(24)        NOT NULL,
    validator_hash CHAR(64)       NOT NULL,
    user_agent    VARCHAR(255)    NULL,
    ip_address    VARCHAR(45)     NULL,
    expires_at    DATETIME        NOT NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_remember_tokens_selector (selector),
    KEY idx_remember_tokens_user (user_id),
    CONSTRAINT fk_remember_tokens_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Every login attempt, success or failure — foundation for rate
-- limiting and lockouts (see app/services/RateLimiter.php).
CREATE TABLE IF NOT EXISTS login_attempts (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email        VARCHAR(191)    NOT NULL,
    ip_address   VARCHAR(45)     NOT NULL,
    was_successful TINYINT(1)    NOT NULL DEFAULT 0,
    user_agent   VARCHAR(255)    NULL,
    attempted_at DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_login_attempts_email_time (email, attempted_at),
    KEY idx_login_attempts_ip_time (ip_address, attempted_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Generic sliding-window rate limiting for any named limiter (API
-- endpoints, upload endpoints, etc.) — separate from login_attempts,
-- which is login-specific and also feeds the "Security" activity log
-- category. See app/services/RateLimiter.php.
CREATE TABLE IF NOT EXISTS rate_limit_hits (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    limiter_key VARCHAR(191) NOT NULL COMMENT 'e.g. "api:203.0.113.4" or "upload:user:42"',
    created_at  DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_rate_limit_hits_key_time (limiter_key, created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- API keys shown on the Integrations page ("Live API Key" / Regenerate).
CREATE TABLE IF NOT EXISTS api_keys (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(120)    NOT NULL,
    key_prefix    VARCHAR(12)     NOT NULL COMMENT 'Non-secret prefix shown in UI, e.g. sk_live_9f2a',
    key_hash      CHAR(64)        NOT NULL COMMENT 'SHA-256 of the full secret key; the raw key is shown once',
    scopes        JSON            NULL COMMENT 'Array of permission slugs this key is limited to',
    last_used_at  DATETIME        NULL,
    created_by    BIGINT UNSIGNED NULL,
    revoked_at    DATETIME        NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_api_keys_hash (key_hash),
    KEY idx_api_keys_created_by (created_by),
    CONSTRAINT fk_api_keys_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Backs SESSION_DRIVER=database (App\Core\DatabaseSessionHandler). Using
-- MySQL instead of flat files means sessions survive app restarts and
-- work correctly if the app is ever scaled across multiple servers.
CREATE TABLE IF NOT EXISTS sessions (
    id            CHAR(128)       NOT NULL COMMENT 'PHP session id',
    user_id       BIGINT UNSIGNED NULL,
    ip_address    VARCHAR(45)     NULL,
    user_agent    VARCHAR(255)    NULL,
    payload       MEDIUMTEXT      NOT NULL,
    last_activity INT UNSIGNED    NOT NULL COMMENT 'Unix timestamp, indexed for garbage collection',
    PRIMARY KEY (id),
    KEY idx_sessions_user (user_id),
    KEY idx_sessions_last_activity (last_activity),
    CONSTRAINT fk_sessions_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;
