-- =====================================================================
-- CRM PLATFORM — MASTER DATABASE SCHEMA
-- Multi-tenant SaaS: each `business_lines` row is now one paying
-- tenant company (see SECTION 18), reached at <slug>.<APP_DOMAIN>.
-- Engine: MySQL 8.0+  |  Charset: utf8mb4  |  Collation: utf8mb4_unicode_ci
--
-- This file is the single source of truth for the schema. It is split,
-- section by section, into the numbered files under database/migrations/
-- so it can be applied incrementally and tracked. Do not edit the
-- migration files and this file independently — regenerate migrations
-- from this file (see database/build_migrations.php).
--
-- Conventions used throughout:
--   - Every table: InnoDB, utf8mb4_unicode_ci
--   - Every PK:      id BIGINT UNSIGNED AUTO_INCREMENT
--   - Audit fields:  created_by, updated_by (nullable FK -> users.id)
--                    created_at, updated_at (auto-managed)
--   - Soft deletes:  deleted_at DATETIME NULL on records that must be
--                    recoverable / kept for audit and reporting history.
--   - Money:         DECIMAL(12,2) + a CHAR(3) currency column,
--                    defaulting to 'CAD' (workspace default currency).
--   - Polymorphic relations (activities, notes, attachments, tags) use
--     an (entity_type, entity_id) pair. MySQL cannot enforce a foreign
--     key against more than one parent table, so entity_id is NOT a
--     declared FK — integrity for these is enforced in the model layer
--     (see app/core/BaseModel.php + app/models/Concerns). This is a
--     deliberate, documented trade-off, not an oversight.
-- =====================================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- =====================================================================
-- SECTION 0 — MIGRATION TRACKING
-- =====================================================================

CREATE TABLE IF NOT EXISTS schema_migrations (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    migration    VARCHAR(191)    NOT NULL,
    batch        INT UNSIGNED    NOT NULL,
    applied_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_schema_migrations_migration (migration)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- =====================================================================
-- SECTION 2 — CRM CORE: LEADS, CUSTOMERS, COMPANIES
-- =====================================================================

CREATE TABLE IF NOT EXISTS lead_sources (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    slug       VARCHAR(100) NOT NULL,
    is_active  TINYINT(1)   NOT NULL DEFAULT 1,
    sort_order SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    UNIQUE KEY uk_lead_sources_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Configurable pipeline stages (New / Contacted / Quoted / Won / Lost)
-- kept as data, not an ENUM, so the Kanban columns are admin-editable.
CREATE TABLE IF NOT EXISTS lead_stages (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(60)  NOT NULL,
    slug        VARCHAR(60)  NOT NULL,
    color_hex   CHAR(7)      NULL,
    is_won      TINYINT(1)   NOT NULL DEFAULT 0,
    is_lost     TINYINT(1)   NOT NULL DEFAULT 0,
    sort_order  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    is_active   TINYINT(1)   NOT NULL DEFAULT 1,
    UNIQUE KEY uk_lead_stages_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS companies (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NOT NULL,
    name              VARCHAR(191)    NOT NULL,
    industry          VARCHAR(120)    NULL,
    website           VARCHAR(191)    NULL,
    phone             VARCHAR(30)     NULL,
    address_line1     VARCHAR(191)    NULL,
    address_line2     VARCHAR(191)    NULL,
    city              VARCHAR(120)    NULL,
    province          VARCHAR(120)    NULL,
    postal_code       VARCHAR(20)     NULL,
    country           VARCHAR(2)      NOT NULL DEFAULT 'CA',
    contract_status   ENUM('prospect','active','lapsed') NOT NULL DEFAULT 'prospect',
    site_count        SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    annual_value      DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    currency          CHAR(3)         NOT NULL DEFAULT 'CAD',
    owner_id          BIGINT UNSIGNED NULL COMMENT 'Account owner (users.id)',
    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_companies_business_line (business_line_id),
    KEY idx_companies_owner (owner_id),
    KEY idx_companies_status (contract_status),
    KEY idx_companies_deleted_at (deleted_at),
    FULLTEXT KEY ftx_companies_name (name),
    CONSTRAINT fk_companies_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_companies_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_companies_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_companies_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 leads (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NOT NULL,
    lead_source_id    BIGINT UNSIGNED NULL,
    lead_stage_id     BIGINT UNSIGNED NOT NULL,
    company_id        BIGINT UNSIGNED NULL COMMENT 'Set when this lead belongs to a B2B account',
    owner_id          BIGINT UNSIGNED NULL COMMENT 'Sales rep assigned (users.id)',
    first_name        VARCHAR(100)    NOT NULL,
    last_name         VARCHAR(100)    NULL,
    email             VARCHAR(191)    NULL,
    phone             VARCHAR(30)     NULL,
    estimated_value   DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    currency          CHAR(3)         NOT NULL DEFAULT 'CAD',
    notes             TEXT            NULL,
    lost_reason       VARCHAR(255)    NULL,
    converted_customer_id BIGINT UNSIGNED NULL COMMENT 'Set once the lead becomes a customer',
    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_leads_business_line (business_line_id),
    KEY idx_leads_stage (lead_stage_id),
    KEY idx_leads_source (lead_source_id),
    KEY idx_leads_owner (owner_id),
    KEY idx_leads_company (company_id),
    KEY idx_leads_deleted_at (deleted_at),
    FULLTEXT KEY ftx_leads_name_email (first_name, last_name, email),
    CONSTRAINT fk_leads_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_leads_source FOREIGN KEY (lead_source_id) REFERENCES lead_sources(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_leads_stage FOREIGN KEY (lead_stage_id) REFERENCES lead_stages(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_leads_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_leads_owner FOREIGN KEY (owner_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_leads_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_leads_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 lead_tasks (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    lead_id      BIGINT UNSIGNED NOT NULL,
    title        VARCHAR(191)    NOT NULL,
    due_at       DATETIME        NULL,
    assigned_to  BIGINT UNSIGNED NULL,
    is_completed TINYINT(1)      NOT NULL DEFAULT 0,
    completed_at DATETIME        NULL,
    created_by   BIGINT UNSIGNED NULL,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_lead_tasks_lead (lead_id),
    KEY idx_lead_tasks_assigned (assigned_to),
    CONSTRAINT fk_lead_tasks_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_lead_tasks_assigned FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_lead_tasks_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;

-- Service plans customers subscribe to ("Weekly Care Plus", "Nightly
-- Commercial") — drives pricing defaults on appointments/invoices.
CREATE TABLE IF NOT EXISTS service_plans (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NOT NULL,
    name              VARCHAR(120)    NOT NULL,
    billing_frequency ENUM('one_time','weekly','biweekly','monthly','annually') NOT NULL DEFAULT 'monthly',
    base_price        DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    currency          CHAR(3)         NOT NULL DEFAULT 'CAD',
    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,
    KEY idx_service_plans_business_line (business_line_id),
    CONSTRAINT fk_service_plans_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS customers (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NOT NULL,
    company_id        BIGINT UNSIGNED NULL COMMENT 'Set when this customer is billed under a B2B account',
    source_lead_id    BIGINT UNSIGNED NULL COMMENT 'The lead this customer was converted from, if any',
    service_plan_id   BIGINT UNSIGNED NULL,
    assigned_staff_id BIGINT UNSIGNED NULL COMMENT 'Primary caregiver / crew lead (users.id)',
    first_name        VARCHAR(100)    NOT NULL,
    last_name         VARCHAR(100)    NULL,
    email             VARCHAR(191)    NULL,
    phone             VARCHAR(30)     NULL,
    address_line1     VARCHAR(191)    NULL,
    address_line2     VARCHAR(191)    NULL,
    city              VARCHAR(120)    NULL,
    province          VARCHAR(120)    NULL,
    postal_code       VARCHAR(20)     NULL,
    country           VARCHAR(2)      NOT NULL DEFAULT 'CA',
    status            ENUM('active','paused','churned') NOT NULL DEFAULT 'active',
    lifetime_value     DECIMAL(12,2)  NOT NULL DEFAULT 0.00 COMMENT 'Cached rollup, recalculated from invoices/payments',
    currency          CHAR(3)         NOT NULL DEFAULT 'CAD',
    customer_since    DATE            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_customers_business_line (business_line_id),
    KEY idx_customers_company (company_id),
    KEY idx_customers_status (status),
    KEY idx_customers_staff (assigned_staff_id),
    KEY idx_customers_deleted_at (deleted_at),
    FULLTEXT KEY ftx_customers_name_email (first_name, last_name, email),
    CONSTRAINT fk_customers_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_customers_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_customers_source_lead FOREIGN KEY (source_lead_id) REFERENCES leads(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_customers_service_plan FOREIGN KEY (service_plan_id) REFERENCES service_plans(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_customers_staff FOREIGN KEY (assigned_staff_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_customers_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_customers_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;

-- Secondary contacts on a customer record (Customer Details > Contacts tab).
CREATE TABLE IF NOT EXISTS customer_contacts (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id  BIGINT UNSIGNED NOT NULL,
    name         VARCHAR(191)    NOT NULL,
    relationship VARCHAR(100)    NULL COMMENT 'e.g. spouse, facilities manager',
    phone        VARCHAR(30)     NULL,
    email        VARCHAR(191)    NULL,
    is_primary   TINYINT(1)      NOT NULL DEFAULT 0,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    KEY idx_customer_contacts_customer (customer_id),
    CONSTRAINT fk_customer_contacts_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SECTION 3 — SHARED / POLYMORPHIC: ACTIVITY TIMELINE, NOTES, FILES, TAGS
-- =====================================================================

-- Powers every "Activity Timeline" seen on Lead Details, Customer
-- Details, and (filtered) the global Activity Logs page.
CREATE TABLE IF NOT EXISTS activities (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type  VARCHAR(40)     NOT NULL COMMENT 'lead, customer, company, appointment, invoice...',
    entity_id    BIGINT UNSIGNED NOT NULL,
    type         VARCHAR(60)     NOT NULL COMMENT 'call_logged, email_sent, stage_changed, note_added...',
    description  VARCHAR(500)    NOT NULL,
    metadata     JSON            NULL COMMENT 'Structured payload, e.g. {"from":"New","to":"Quoted"}',
    caused_by    BIGINT UNSIGNED NULL COMMENT 'NULL = system/automation generated',
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_activities_entity (entity_type, entity_id, created_at),
    KEY idx_activities_caused_by (caused_by),
    CONSTRAINT fk_activities_caused_by FOREIGN KEY (caused_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 notes (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type  VARCHAR(40)     NOT NULL,
    entity_id    BIGINT UNSIGNED NOT NULL,
    body         TEXT            NOT NULL,
    created_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_notes_entity (entity_type, entity_id),
    CONSTRAINT fk_notes_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 the "Files" tab on Lead Details and any future attachment UI
-- (email attachments, invoice PDFs, imported spreadsheets).
CREATE TABLE IF NOT EXISTS attachments (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type   VARCHAR(40)     NOT NULL,
    entity_id     BIGINT UNSIGNED NOT NULL,
    disk_path     VARCHAR(500)    NOT NULL COMMENT 'Path under /uploads, never web-executable',
    original_name VARCHAR(255)    NOT NULL,
    mime_type     VARCHAR(120)    NOT NULL,
    size_bytes    BIGINT UNSIGNED NOT NULL,
    checksum_sha256 CHAR(64)      NULL,
    uploaded_by   BIGINT UNSIGNED NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at    DATETIME        NULL,
    KEY idx_attachments_entity (entity_type, entity_id),
    CONSTRAINT fk_attachments_uploaded_by FOREIGN KEY (uploaded_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 tags (
    id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name       VARCHAR(80)  NOT NULL,
    slug       VARCHAR(80)  NOT NULL,
    color_hex  CHAR(7)      NULL,
    UNIQUE KEY uk_tags_slug (slug)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS taggables (
    tag_id       BIGINT UNSIGNED NOT NULL,
    entity_type  VARCHAR(40)     NOT NULL,
    entity_id    BIGINT UNSIGNED NOT NULL,
    PRIMARY KEY (tag_id, entity_type, entity_id),
    KEY idx_taggables_entity (entity_type, entity_id),
    CONSTRAINT fk_taggables_tag FOREIGN KEY (tag_id) REFERENCES tags(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SECTION 4 — SCHEDULING
-- =====================================================================

CREATE TABLE IF NOT EXISTS service_types (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NOT NULL,
    name              VARCHAR(120)    NOT NULL,
    default_duration_minutes SMALLINT UNSIGNED NOT NULL DEFAULT 60,
    default_price     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    currency          CHAR(3)         NOT NULL DEFAULT 'CAD',
    is_active         TINYINT(1)      NOT NULL DEFAULT 1,
    KEY idx_service_types_business_line (business_line_id),
    CONSTRAINT fk_service_types_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS appointments (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NOT NULL,
    customer_id       BIGINT UNSIGNED NULL,
    lead_id           BIGINT UNSIGNED NULL COMMENT 'Pre-sale assessments booked before conversion',
    service_type_id   BIGINT UNSIGNED NULL,
    assigned_staff_id BIGINT UNSIGNED NULL,
    scheduled_start   DATETIME        NOT NULL,
    scheduled_end     DATETIME        NOT NULL,
    status            ENUM('scheduled','completed','cancelled','no_show') NOT NULL DEFAULT 'scheduled',
    location_address  VARCHAR(255)    NULL,
    notes             TEXT            NULL,
    cancelled_reason  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_appointments_business_line (business_line_id),
    KEY idx_appointments_customer (customer_id),
    KEY idx_appointments_lead (lead_id),
    KEY idx_appointments_staff_time (assigned_staff_id, scheduled_start),
    KEY idx_appointments_status (status),
    KEY idx_appointments_deleted_at (deleted_at),
    CONSTRAINT fk_appointments_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_service_type FOREIGN KEY (service_type_id) REFERENCES service_types(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_staff FOREIGN KEY (assigned_staff_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_appointments_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT chk_appointments_time_order CHECK (scheduled_end > scheduled_start)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SECTION 5 — BILLING
-- =====================================================================

CREATE TABLE IF NOT EXISTS invoices (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    customer_id    BIGINT UNSIGNED NULL,
    company_id     BIGINT UNSIGNED NULL,
    invoice_number VARCHAR(40)     NOT NULL,
    status         ENUM('draft','sent','paid','overdue','void') NOT NULL DEFAULT 'draft',
    subtotal       DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    tax_total      DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    total          DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    currency       CHAR(3)         NOT NULL DEFAULT 'CAD',
    issue_date     DATE            NOT NULL,
    due_date       DATE            NULL,
    paid_at        DATETIME        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,
    UNIQUE KEY uk_invoices_number (invoice_number),
    KEY idx_invoices_customer (customer_id),
    KEY idx_invoices_company (company_id),
    KEY idx_invoices_status (status),
    CONSTRAINT fk_invoices_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_invoices_company FOREIGN KEY (company_id) REFERENCES companies(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_invoices_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_invoices_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
    -- NOTE: "must have a customer_id OR a company_id" is intentionally NOT a
    -- CHECK constraint here. MySQL forbids a column from being both part of
    -- a CHECK constraint and the child side of a FK with a CASCADE/SET NULL
    -- referential action (error 3823) — and both FKs above need SET NULL
    -- behavior elsewhere in the app. This rule is enforced in
    -- app/models/InvoiceModel.php validation instead.
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoice_items (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id  BIGINT UNSIGNED NOT NULL,
    description VARCHAR(255)    NOT NULL,
    quantity    DECIMAL(10,2)   NOT NULL DEFAULT 1.00,
    unit_price  DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    line_total  DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    sort_order  SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    KEY idx_invoice_items_invoice (invoice_id),
    CONSTRAINT fk_invoice_items_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS payments (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    invoice_id    BIGINT UNSIGNED NOT NULL,
    amount        DECIMAL(12,2)   NOT NULL,
    currency      CHAR(3)         NOT NULL DEFAULT 'CAD',
    method        ENUM('card','bank_transfer','cash','cheque','other') NOT NULL DEFAULT 'card',
    reference     VARCHAR(120)    NULL COMMENT 'Processor transaction id',
    paid_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    recorded_by   BIGINT UNSIGNED NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_payments_invoice (invoice_id),
    CONSTRAINT fk_payments_invoice FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_payments_recorded_by FOREIGN KEY (recorded_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The Hub's OWN subscription to A&B (Settings > Billing tab) — not to
-- be confused with `invoices`, which bills A&B's customers.
CREATE TABLE IF NOT EXISTS billing_accounts (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    plan_name          VARCHAR(80)     NOT NULL DEFAULT 'Enterprise',
    monthly_amount     DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
    currency           CHAR(3)         NOT NULL DEFAULT 'CAD',
    billing_cycle      ENUM('monthly','annually') NOT NULL DEFAULT 'annually',
    card_last_four     CHAR(4)         NULL,
    card_brand         VARCHAR(30)     NULL,
    renews_on          DATE            NULL,
    updated_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SECTION 6 — EMAIL
-- =====================================================================

CREATE TABLE IF NOT EXISTS email_accounts (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id           BIGINT UNSIGNED NULL COMMENT 'NULL = shared/team mailbox',
    business_line_id  BIGINT UNSIGNED NULL,
    email_address     VARCHAR(191)    NOT NULL,
    provider          ENUM('gmail','smtp','outlook') NOT NULL DEFAULT 'smtp',
    credentials_encrypted TEXT        NULL COMMENT 'Encrypted OAuth token or SMTP secret, never plaintext',
    is_active         TINYINT(1)      NOT NULL DEFAULT 1,
    connected_at      DATETIME        NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_email_accounts_address (email_address),
    KEY idx_email_accounts_user (user_id),
    CONSTRAINT fk_email_accounts_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_email_accounts_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_messages (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email_account_id  BIGINT UNSIGNED NOT NULL,
    direction         ENUM('inbound','outbound') NOT NULL,
    related_lead_id   BIGINT UNSIGNED NULL,
    related_customer_id BIGINT UNSIGNED NULL,
    from_address      VARCHAR(191)    NOT NULL,
    to_address        VARCHAR(191)    NOT NULL,
    subject           VARCHAR(255)    NULL,
    body_html         MEDIUMTEXT      NULL,
    status            ENUM('queued','sent','delivered','failed','received','read') NOT NULL DEFAULT 'queued',
    is_starred        TINYINT(1)      NOT NULL DEFAULT 0,
    sent_at           DATETIME        NULL,
    created_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    deleted_at        DATETIME        NULL,
    KEY idx_email_messages_account (email_account_id, created_at),
    KEY idx_email_messages_lead (related_lead_id),
    KEY idx_email_messages_customer (related_customer_id),
    FULLTEXT KEY ftx_email_messages_subject (subject),
    CONSTRAINT fk_email_messages_account FOREIGN KEY (email_account_id) REFERENCES email_accounts(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_email_messages_lead FOREIGN KEY (related_lead_id) REFERENCES leads(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_email_messages_customer FOREIGN KEY (related_customer_id) REFERENCES customers(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS email_templates (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NULL,
    name              VARCHAR(150)    NOT NULL,
    category          ENUM('homecare','janitorial','transactional','general') NOT NULL DEFAULT 'general',
    subject           VARCHAR(255)    NOT NULL,
    body_html         MEDIUMTEXT      NOT 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_email_templates_business_line (business_line_id),
    KEY idx_email_templates_category (category),
    CONSTRAINT fk_email_templates_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_email_templates_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_email_templates_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;

-- =====================================================================
-- SECTION 7 — CAMPAIGNS
-- =====================================================================

CREATE TABLE IF NOT EXISTS campaigns (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NULL COMMENT 'NULL = sent to both brands',
    email_template_id BIGINT UNSIGNED NULL,
    name              VARCHAR(191)    NOT NULL,
    subject           VARCHAR(255)    NOT NULL,
    body_html         MEDIUMTEXT      NOT NULL,
    audience_segment  VARCHAR(120)    NULL COMMENT 'Denormalized label, e.g. "Active Clients"',
    audience_filter   JSON            NULL COMMENT 'Structured segment definition for re-evaluation',
    status            ENUM('draft','scheduled','sending','sent','cancelled') NOT NULL DEFAULT 'draft',
    scheduled_at      DATETIME        NULL,
    sent_at           DATETIME        NULL,
    recipients_count  INT UNSIGNED    NOT NULL DEFAULT 0,
    opens_count       INT UNSIGNED    NOT NULL DEFAULT 0,
    clicks_count      INT UNSIGNED    NOT NULL DEFAULT 0,
    unsubscribes_count INT UNSIGNED   NOT NULL DEFAULT 0,
    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_campaigns_business_line (business_line_id),
    KEY idx_campaigns_status (status),
    CONSTRAINT fk_campaigns_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_campaigns_template FOREIGN KEY (email_template_id) REFERENCES email_templates(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_campaigns_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_campaigns_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 campaign_recipients (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id   BIGINT UNSIGNED NOT NULL,
    customer_id   BIGINT UNSIGNED NULL,
    lead_id       BIGINT UNSIGNED NULL,
    email_address VARCHAR(191)    NOT NULL,
    status        ENUM('pending','sent','delivered','opened','clicked','bounced','unsubscribed') NOT NULL DEFAULT 'pending',
    sent_at       DATETIME        NULL,
    opened_at     DATETIME        NULL,
    clicked_at    DATETIME        NULL,
    KEY idx_campaign_recipients_campaign (campaign_id),
    KEY idx_campaign_recipients_customer (customer_id),
    KEY idx_campaign_recipients_lead (lead_id),
    CONSTRAINT fk_campaign_recipients_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_campaign_recipients_customer FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_campaign_recipients_lead FOREIGN KEY (lead_id) REFERENCES leads(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SECTION 8 — AUTOMATION
-- =====================================================================

CREATE TABLE IF NOT EXISTS automation_workflows (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(191)    NOT NULL,
    trigger_type  VARCHAR(80)     NOT NULL COMMENT 'lead_created, invoice_overdue, appointment_completed...',
    trigger_config JSON           NULL,
    is_active     TINYINT(1)      NOT NULL DEFAULT 1,
    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_automation_workflows_trigger (trigger_type),
    CONSTRAINT fk_automation_workflows_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_automation_workflows_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 automation_steps (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    workflow_id  BIGINT UNSIGNED NOT NULL,
    step_type    ENUM('send_email','wait','condition','assign_owner','update_field','webhook') NOT NULL,
    config       JSON            NULL COMMENT 'Step-specific settings, e.g. {"template_id":12,"delay_hours":24}',
    sort_order   SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    parent_branch VARCHAR(20)    NULL COMMENT 'yes/no branch label for condition steps',
    KEY idx_automation_steps_workflow (workflow_id, sort_order),
    CONSTRAINT fk_automation_steps_workflow FOREIGN KEY (workflow_id) REFERENCES automation_workflows(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS automation_runs (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    workflow_id  BIGINT UNSIGNED NOT NULL,
    entity_type  VARCHAR(40)     NOT NULL,
    entity_id    BIGINT UNSIGNED NOT NULL,
    status       ENUM('running','completed','failed','cancelled') NOT NULL DEFAULT 'running',
    current_step_id BIGINT UNSIGNED NULL,
    started_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME        NULL,
    error_message VARCHAR(500)   NULL,
    KEY idx_automation_runs_workflow (workflow_id),
    KEY idx_automation_runs_entity (entity_type, entity_id),
    CONSTRAINT fk_automation_runs_workflow FOREIGN KEY (workflow_id) REFERENCES automation_workflows(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_automation_runs_step FOREIGN KEY (current_step_id) REFERENCES automation_steps(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SECTION 9 — AI ASSISTANT, CHATBOT & VOICE AGENT (data foundation only)
-- =====================================================================

CREATE TABLE IF NOT EXISTS chatbot_settings (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id BIGINT UNSIGNED NOT NULL,
    bot_name         VARCHAR(80)     NOT NULL DEFAULT 'A&B Assistant',
    greeting_message VARCHAR(500)    NULL,
    is_live          TINYINT(1)      NOT NULL DEFAULT 0,
    handoff_keywords VARCHAR(255)    NULL,
    updated_by       BIGINT UNSIGNED NULL,
    updated_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_chatbot_settings_business_line (business_line_id),
    CONSTRAINT fk_chatbot_settings_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_chatbot_settings_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;

-- Unifies chatbot conversations and voice calls under one "channel"
-- column so Conversation Logs can list both from a single table.
CREATE TABLE IF NOT EXISTS conversations (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NOT NULL,
    channel           ENUM('chatbot','voice_agent') NOT NULL,
    visitor_identifier VARCHAR(191)   NULL COMMENT 'Phone number, session id, or anonymous cookie id',
    related_lead_id   BIGINT UNSIGNED NULL,
    related_customer_id BIGINT UNSIGNED NULL,
    outcome           ENUM('lead_created','booked','escalated','resolved','abandoned') NULL,
    handled_by_user_id BIGINT UNSIGNED NULL COMMENT 'Set if escalated to a human',
    duration_seconds  INT UNSIGNED    NULL,
    recording_url     VARCHAR(500)    NULL COMMENT 'Voice only',
    started_at        DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at          DATETIME        NULL,
    KEY idx_conversations_business_line (business_line_id),
    KEY idx_conversations_channel (channel),
    KEY idx_conversations_lead (related_lead_id),
    KEY idx_conversations_customer (related_customer_id),
    CONSTRAINT fk_conversations_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE RESTRICT ON UPDATE CASCADE,
    CONSTRAINT fk_conversations_lead FOREIGN KEY (related_lead_id) REFERENCES leads(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_conversations_customer FOREIGN KEY (related_customer_id) REFERENCES customers(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_conversations_handled_by FOREIGN KEY (handled_by_user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS conversation_messages (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_id BIGINT UNSIGNED NOT NULL,
    sender          ENUM('visitor','bot','agent') NOT NULL,
    message         MEDIUMTEXT      NOT NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_conversation_messages_conversation (conversation_id, created_at),
    CONSTRAINT fk_conversation_messages_conversation FOREIGN KEY (conversation_id) REFERENCES conversations(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SECTION 10 — NOTIFICATIONS & ACTIVITY LOG (system-wide)
-- =====================================================================

CREATE TABLE IF NOT EXISTS notifications (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id          BIGINT UNSIGNED NOT NULL,
    type             VARCHAR(60)     NOT NULL COMMENT 'new_lead, invoice_overdue, appointment_change, ai_escalation...',
    title            VARCHAR(191)    NOT NULL,
    message          VARCHAR(500)    NOT NULL,
    related_entity_type VARCHAR(40)  NULL,
    related_entity_id   BIGINT UNSIGNED NULL,
    is_read          TINYINT(1)      NOT NULL DEFAULT 0,
    read_at          DATETIME        NULL,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_notifications_user_unread (user_id, is_read, created_at),
    CONSTRAINT fk_notifications_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Per-user, per-type opt in/out (Notifications > Notification Preferences).
CREATE TABLE IF NOT EXISTS notification_preferences (
    id          BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id     BIGINT UNSIGNED NOT NULL,
    type        VARCHAR(60)     NOT NULL,
    channel     ENUM('in_app','email','sms') NOT NULL DEFAULT 'in_app',
    is_enabled  TINYINT(1)      NOT NULL DEFAULT 1,
    UNIQUE KEY uk_notification_prefs (user_id, type, channel),
    CONSTRAINT fk_notification_prefs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- The system-wide audit trail (Activity Logs page: User Action /
-- System / Security / Admin categories).
CREATE TABLE IF NOT EXISTS activity_logs (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id      BIGINT UNSIGNED NULL COMMENT 'NULL = system-generated',
    category     ENUM('user_action','system','security','admin') NOT NULL DEFAULT 'user_action',
    action       VARCHAR(191)    NOT NULL,
    entity_type  VARCHAR(40)     NULL,
    entity_id    BIGINT UNSIGNED NULL,
    ip_address   VARCHAR(45)     NULL,
    user_agent   VARCHAR(255)    NULL,
    created_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_activity_logs_user (user_id),
    KEY idx_activity_logs_category_time (category, created_at),
    KEY idx_activity_logs_entity (entity_type, entity_id),
    CONSTRAINT fk_activity_logs_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SECTION 11 — SETTINGS & INTEGRATIONS
-- =====================================================================

-- Simple, extensible key-value workspace settings (General tab, plus
-- anything future modules need without a schema change).
CREATE TABLE IF NOT EXISTS settings (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    setting_key  VARCHAR(120)    NOT NULL,
    setting_value TEXT           NULL,
    updated_by   BIGINT UNSIGNED NULL,
    updated_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_settings_key (setting_key),
    CONSTRAINT fk_settings_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 integrations (
    id            BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name          VARCHAR(120)    NOT NULL COMMENT 'QuickBooks Online, Twilio, Slack, Google Calendar...',
    slug          VARCHAR(80)     NOT NULL,
    status        ENUM('connected','not_connected','error') NOT NULL DEFAULT 'not_connected',
    credentials_encrypted TEXT   NULL,
    connected_by  BIGINT UNSIGNED NULL,
    connected_at  DATETIME        NULL,
    created_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_integrations_slug (slug),
    CONSTRAINT fk_integrations_connected_by FOREIGN KEY (connected_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

-- =====================================================================
-- SECTION 14 — CRM MODULE EXTENSIONS (Phase 3)
-- Additive only. `comments` is new (lighter-weight, threaded, distinct
-- from the longer-form `notes` table from Phase 1). `appointments`,
-- `leads`, and `customers` gain nullable columns for recurring
-- scheduling, lead priority/duplicate-merge tracking, and CSV import
-- provenance — none of it breaks existing rows.
-- =====================================================================

-- Short, often-threaded remarks on any entity — distinct from `notes`
-- (Phase 1), which is for longer-form structured notes. A lead's
-- "Activity Timeline" mixes both; Comments are what a quick "@Rita can
-- you follow up on this?" becomes.
CREATE TABLE IF NOT EXISTS comments (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    entity_type  VARCHAR(40)     NOT NULL,
    entity_id    BIGINT UNSIGNED NOT NULL,
    parent_id    BIGINT UNSIGNED NULL COMMENT 'Set for a threaded reply to another comment',
    body         VARCHAR(2000)   NOT NULL,
    created_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_comments_entity (entity_type, entity_id),
    KEY idx_comments_parent (parent_id),
    CONSTRAINT fk_comments_parent FOREIGN KEY (parent_id) REFERENCES comments(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_comments_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;

-- Recurring appointments: a "parent" appointment carries the
-- recurrence rule; each generated occurrence is its own real row
-- (so any single occurrence can be individually rescheduled/cancelled
-- without touching the series) linked back via parent_appointment_id.
ALTER TABLE appointments
    ADD COLUMN parent_appointment_id BIGINT UNSIGNED NULL AFTER lead_id,
    ADD COLUMN recurrence_rule JSON NULL COMMENT '{"frequency":"weekly","interval":1,"until":"2026-12-31","days_of_week":[2]} — only set on the parent row',
    ADD COLUMN is_recurring_parent TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN reminder_minutes_before SMALLINT UNSIGNED NULL DEFAULT 60,
    ADD COLUMN reminder_sent_at DATETIME NULL,
    ADD KEY idx_appointments_parent (parent_appointment_id),
    ADD CONSTRAINT fk_appointments_parent FOREIGN KEY (parent_appointment_id) REFERENCES appointments(id) ON DELETE CASCADE ON UPDATE CASCADE;

-- Lead Management: priority (already had stage/status via lead_stages;
-- this is urgency, independent of pipeline position), and duplicate/
-- merge bookkeeping so a merged-away lead stays queryable for audit
-- instead of being hard-deleted.
ALTER TABLE leads
    ADD COLUMN priority ENUM('low','medium','high','urgent') NOT NULL DEFAULT 'medium' AFTER lead_stage_id,
    ADD COLUMN merged_into_lead_id BIGINT UNSIGNED NULL COMMENT 'Set when this lead was merged into another (the survivor)',
    ADD COLUMN import_batch_id CHAR(36) NULL COMMENT 'Groups rows created by the same CSV import run',
    ADD KEY idx_leads_priority (priority),
    ADD KEY idx_leads_merged_into (merged_into_lead_id),
    ADD KEY idx_leads_import_batch (import_batch_id),
    ADD CONSTRAINT fk_leads_merged_into FOREIGN KEY (merged_into_lead_id) REFERENCES leads(id) ON DELETE SET NULL ON UPDATE CASCADE;

ALTER TABLE customers
    ADD COLUMN import_batch_id CHAR(36) NULL AFTER source_lead_id,
    ADD KEY idx_customers_import_batch (import_batch_id);

-- CSV import run log — one row per `php` or admin-triggered import,
-- so Users/Leads/Customers import screens can all show "last import:
-- 214 created, 3 skipped as duplicates, 2 failed" without re-parsing
-- the original file.
CREATE TABLE IF NOT EXISTS import_batches (
    id             CHAR(36)        NOT NULL COMMENT 'UUID, shared with leads.import_batch_id / customers.import_batch_id',
    entity_type    VARCHAR(40)     NOT NULL COMMENT 'leads, customers, companies...',
    original_filename VARCHAR(255) NULL,
    total_rows     INT UNSIGNED    NOT NULL DEFAULT 0,
    created_count  INT UNSIGNED    NOT NULL DEFAULT 0,
    skipped_count  INT UNSIGNED    NOT NULL DEFAULT 0,
    failed_count   INT UNSIGNED    NOT NULL DEFAULT 0,
    error_log      JSON            NULL COMMENT 'Array of {row, message} for failed/skipped rows',
    imported_by    BIGINT UNSIGNED NULL,
    created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    KEY idx_import_batches_entity (entity_type),
    CONSTRAINT fk_import_batches_user FOREIGN KEY (imported_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =====================================================================
-- SECTION 15 — MARKETING SYSTEM (Phase 4)
-- Additive only. `campaigns`/`campaign_recipients` (Phase 1) gain new
-- nullable columns; everything else is a new table. `automation_workflows`/
-- `automation_steps`/`automation_runs` (Phase 1) are reused as-is — the
-- automation ENGINE built in Phase 4 is new application code, not a
-- new table.
-- =====================================================================

-- Recurring campaigns follow the exact same pattern proven for
-- recurring appointments in Phase 3: the parent stays flagged, each
-- occurrence is a REAL child campaign row (own recipients, own stats),
-- never a virtual/computed send.
ALTER TABLE campaigns
    ADD COLUMN parent_campaign_id BIGINT UNSIGNED NULL AFTER email_template_id,
    ADD COLUMN is_recurring_parent TINYINT(1) NOT NULL DEFAULT 0,
    ADD COLUMN recurrence_rule JSON NULL COMMENT 'Same shape as appointments.recurrence_rule: {frequency, interval, until}',
    ADD COLUMN audience_segment_id BIGINT UNSIGNED NULL COMMENT 'Set when the audience was built from a saved segment rather than an ad-hoc filter',
    ADD KEY idx_campaigns_parent (parent_campaign_id);

ALTER TABLE campaign_recipients
    ADD COLUMN bounced_at DATETIME NULL AFTER clicked_at,
    ADD COLUMN replied_at DATETIME NULL AFTER bounced_at,
    ADD COLUMN unsubscribe_token CHAR(64) NULL AFTER replied_at,
    ADD UNIQUE KEY uk_campaign_recipients_unsub_token (unsubscribe_token);

-- Birthday Email automation needs a birthday to fire on.
ALTER TABLE customers
    ADD COLUMN date_of_birth DATE NULL AFTER phone;

-- Global suppression list: once an address unsubscribes, EVERY future
-- campaign (regardless of which one they unsubscribed from) must skip
-- it — this is checked by AudienceSegmentService, not just the
-- originating campaign's recipient list.
CREATE TABLE IF NOT EXISTS email_unsubscribes (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    email             VARCHAR(191)    NOT NULL,
    business_line_id  BIGINT UNSIGNED NULL COMMENT 'NULL = unsubscribed from all A&B communications, not just one brand',
    reason            VARCHAR(255)    NULL,
    unsubscribed_at   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_email_unsubscribes_email_bl (email, business_line_id),
    CONSTRAINT fk_email_unsubscribes_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;

-- Saved, reusable audience definitions ("Filtered Audience") — the
-- filter itself is a small JSON DSL interpreted by
-- AudienceSegmentService::resolve(), e.g.
-- {"type":"leads","business_line":"homecare","stage":"quoted"}.
CREATE TABLE IF NOT EXISTS audience_segments (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name              VARCHAR(150)    NOT NULL,
    description       VARCHAR(255)    NULL,
    filter_definition JSON            NOT NULL,
    created_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,
    CONSTRAINT fk_audience_segments_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;

-- The actual send queue — decouples "the campaign was scheduled/sent"
-- from "the SMTP transaction happened," so a bulk send of 5,000
-- recipients never blocks an HTTP request. bin/process_email_queue.php
-- (a worker, run on a schedule) claims pending rows and sends them.
CREATE TABLE IF NOT EXISTS email_queue (
    id              BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    campaign_id     BIGINT UNSIGNED NULL COMMENT 'NULL = a personal/one-off email, not part of a campaign',
    campaign_recipient_id BIGINT UNSIGNED NULL,
    to_email        VARCHAR(191)    NOT NULL,
    to_name         VARCHAR(191)    NULL,
    from_email      VARCHAR(191)    NULL COMMENT 'NULL = use the workspace default from config/mail.php',
    subject         VARCHAR(255)    NOT NULL,
    body_html       MEDIUMTEXT      NOT NULL,
    status          ENUM('pending','processing','sent','failed') NOT NULL DEFAULT 'pending',
    attempts        SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    last_error      VARCHAR(500)    NULL,
    scheduled_for   DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT 'Worker only sends rows where this is <= NOW()',
    sent_at         DATETIME        NULL,
    created_by      BIGINT UNSIGNED NULL,
    created_at      DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_email_queue_status_schedule (status, scheduled_for),
    KEY idx_email_queue_campaign (campaign_id),
    CONSTRAINT fk_email_queue_campaign FOREIGN KEY (campaign_id) REFERENCES campaigns(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_email_queue_recipient FOREIGN KEY (campaign_recipient_id) REFERENCES campaign_recipients(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_email_queue_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;

-- SMTP Settings: a single-row table (like billing_accounts) so the
-- workspace's mail transport is admin-configurable from the UI instead
-- of only via .env. MailService prefers this row when present and
-- falls back to config/mail.php otherwise — see that class's Phase 4
-- update.
CREATE TABLE IF NOT EXISTS smtp_settings (
    id                    TINYINT UNSIGNED PRIMARY KEY DEFAULT 1 COMMENT 'Always row id=1 — single workspace-wide config',
    host                  VARCHAR(191) NULL,
    port                  SMALLINT UNSIGNED NULL,
    encryption            ENUM('tls','ssl','none') NOT NULL DEFAULT 'tls',
    username              VARCHAR(191) NULL,
    password_encrypted    VARCHAR(500) NULL,
    from_address          VARCHAR(191) NULL,
    from_name             VARCHAR(191) NULL,
    is_configured         TINYINT(1) NOT NULL DEFAULT 0,
    updated_by            BIGINT UNSIGNED NULL,
    updated_at            DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_smtp_settings_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT chk_smtp_settings_single_row CHECK (id = 1)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Automation engine (Phase 4) needs to know WHEN a paused ("wait")
-- step should resume — added here rather than in Section 8 because
-- it's Phase 4 application code (AutomationEngine) that needs it, even
-- though the parent table itself dates to Phase 1.
ALTER TABLE automation_runs
    ADD COLUMN next_step_at DATETIME NULL AFTER current_step_id,
    ADD KEY idx_automation_runs_due (status, next_step_at);

-- =====================================================================
-- SECTION 16 — AI MODULES & INTEGRATIONS (Phase 5)
-- Additive only. `conversations` (Phase 1) already unified chatbot and
-- voice under one table with the right shape (visitor_identifier,
-- outcome, recording_url) — it just gains an 'ai_assistant' channel
-- and a few voice-specific columns. `integrations` (Phase 1) already
-- has slug + encrypted-credentials-blob storage generic enough for
-- every provider in this phase; nothing about it needed to change,
-- only more rows need seeding (see MarketingSeeder's sibling for
-- Phase 5, IntegrationCatalogSeeder).
-- =====================================================================

ALTER TABLE conversations
    MODIFY COLUMN channel ENUM('chatbot','voice_agent','ai_assistant') NOT NULL,
    ADD COLUMN direction ENUM('inbound','outbound') NULL COMMENT 'Voice only' AFTER channel,
    ADD COLUMN external_ref VARCHAR(100) NULL COMMENT 'Twilio Call SID, WhatsApp message id, etc — for correlating with provider dashboards/webhooks' AFTER visitor_identifier,
    ADD COLUMN transcript_text MEDIUMTEXT NULL COMMENT 'Full flattened transcript, separate from the per-turn conversation_messages rows — convenient for voice calls and AI summarization' AFTER recording_url,
    ADD KEY idx_conversations_external_ref (external_ref);

-- Two-way Google Calendar sync bookkeeping — nullable, so appointments
-- created before Google Calendar was connected are unaffected.
ALTER TABLE appointments
    ADD COLUMN google_calendar_event_id VARCHAR(255) NULL AFTER reminder_sent_at;

-- Admin-editable system prompts, one per AI feature (and optionally
-- per business line, since Homecare and Cleaner want a different
-- voice). AiAssistantService/ChatbotService/VoiceAgentService all read
-- their active prompt from here instead of a hardcoded string.
CREATE TABLE IF NOT EXISTS ai_prompts (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    feature           ENUM('ai_assistant','chatbot','voice_agent') NOT NULL,
    business_line_id  BIGINT UNSIGNED NULL COMMENT 'NULL = applies to both brands',
    name              VARCHAR(150)    NOT NULL,
    system_prompt     TEXT            NOT NULL,
    is_active         TINYINT(1)      NOT NULL DEFAULT 1,
    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,
    KEY idx_ai_prompts_feature_active (feature, is_active),
    CONSTRAINT fk_ai_prompts_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_ai_prompts_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
    CONSTRAINT fk_ai_prompts_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;

-- FAQ-style retrieval knowledge base — the Chatbot's "FAQ" feature and
-- the AI Assistant's "Answer Questions" feature both search this
-- before (or instead of) calling out to the LLM.
CREATE TABLE IF NOT EXISTS knowledge_base_articles (
    id                BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id  BIGINT UNSIGNED NULL,
    category          VARCHAR(100)    NULL,
    question          VARCHAR(500)    NOT NULL,
    answer            TEXT            NOT NULL,
    is_active         TINYINT(1)      NOT NULL DEFAULT 1,
    view_count        INT UNSIGNED    NOT NULL DEFAULT 0,
    created_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,
    FULLTEXT KEY ft_knowledge_base_question (question, answer),
    KEY idx_knowledge_base_business_line (business_line_id),
    CONSTRAINT fk_knowledge_base_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_knowledge_base_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;

-- "Training Data": curated example prompt/ideal-response pairs kept
-- for review and export (JSONL) — e.g. for future fine-tuning or for
-- a human to audit what the AI should say in tricky situations. This
-- is deliberately NOT fed automatically into every request (that's
-- what ai_prompts + knowledge_base_articles are for); it's a curated,
-- human-reviewed reference set.
CREATE TABLE IF NOT EXISTS training_examples (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    feature        ENUM('ai_assistant','chatbot','voice_agent') NOT NULL,
    prompt_text    TEXT            NOT NULL,
    ideal_response TEXT            NOT NULL,
    notes          VARCHAR(500)    NULL,
    created_by     BIGINT UNSIGNED NULL,
    created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_training_examples_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;

-- Generic inbound-webhook audit log — every Twilio call-status ping,
-- Meta lead-ad submission, WhatsApp delivery receipt, etc. lands here
-- first (raw payload preserved) before being processed, so a failed
-- or malformed webhook is debuggable from Admin instead of only ever
-- existing in a provider's dashboard.
CREATE TABLE IF NOT EXISTS webhook_events (
    id           BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    provider     VARCHAR(60)     NOT NULL COMMENT 'twilio, meta, whatsapp, google...',
    event_type   VARCHAR(100)    NULL,
    payload      JSON            NOT NULL,
    processed    TINYINT(1)      NOT NULL DEFAULT 0,
    error_message VARCHAR(500)   NULL,
    received_at  DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_webhook_events_provider (provider, received_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

SET FOREIGN_KEY_CHECKS = 1;

-- =====================================================================
-- SECTION 17 — PERFORMANCE INDEXES (Phase 6)
-- Every index below targets a column used in a date-range WHERE clause
-- that had NO index at all — confirmed via EXPLAIN during Phase 6's
-- optimization pass, not added speculatively. Each was a full table
-- scan (`type: ALL`) before this migration on the exact query
-- ReportService/DashboardController runs. Composite indexes are
-- ordered filter-column-first, range-column-second, matching how
-- MySQL actually uses a compound index for "WHERE business_line_id = ?
-- AND created_at > ?" style queries.
-- =====================================================================

ALTER TABLE payments      ADD KEY idx_payments_paid_at (paid_at);
ALTER TABLE conversations ADD KEY idx_conversations_started_at (started_at);
ALTER TABLE leads         ADD KEY idx_leads_business_line_created (business_line_id, created_at);
ALTER TABLE customers     ADD KEY idx_customers_business_line_since (business_line_id, customer_since);
ALTER TABLE appointments  ADD KEY idx_appointments_business_line_scheduled (business_line_id, scheduled_start);
ALTER TABLE campaigns     ADD KEY idx_campaigns_sent_at (sent_at);
ALTER TABLE campaigns     ADD KEY idx_campaigns_created_at (created_at);
ALTER TABLE automation_runs ADD KEY idx_automation_runs_started_at (started_at);
ALTER TABLE email_queue   ADD KEY idx_email_queue_created_at (created_at);


-- =====================================================================
-- SECTION 18 — SAAS PLATFORM (multi-tenant billing)
-- Turns business_lines from "the two brands under one company" into
-- "one row per paying tenant." Every other table already shards by
-- business_line_id, so this section only adds billing state on
-- business_lines itself plus the new platform-level tables. See
-- database/migrations/0018_create_saas_platform.sql for the identical,
-- hand-maintained copy of this section (kept standalone so it applies
-- on top of already-migrated databases without --fresh).
-- =====================================================================

ALTER TABLE business_lines
    ADD COLUMN plan                   VARCHAR(40)  NULL AFTER brand_color,
    ADD COLUMN subscription_status    ENUM('trialing','active','past_due','expired','canceled')
                                                    NOT NULL DEFAULT 'trialing' AFTER plan,
    ADD COLUMN billing_cycle          ENUM('yearly') NOT NULL DEFAULT 'yearly' AFTER subscription_status,
    ADD COLUMN billing_email          VARCHAR(191) NULL AFTER billing_cycle,
    ADD COLUMN trial_ends_at          DATETIME     NULL AFTER billing_email,
    ADD COLUMN subscription_expires_at DATETIME    NULL AFTER trial_ends_at,
    ADD COLUMN stripe_customer_id     VARCHAR(191) NULL AFTER subscription_expires_at,
    ADD COLUMN stripe_subscription_id VARCHAR(191) NULL AFTER stripe_customer_id,
    ADD COLUMN activation_source      ENUM('signup_stripe','signup_code','manual_admin') NULL AFTER stripe_subscription_id,
    ADD COLUMN onboarded_by_admin_id  BIGINT UNSIGNED NULL AFTER activation_source,
    ADD KEY idx_business_lines_subscription_status (subscription_status),
    ADD KEY idx_business_lines_stripe_customer (stripe_customer_id);

CREATE TABLE IF NOT EXISTS platform_admins (
    id             BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name           VARCHAR(191)    NOT NULL,
    email          VARCHAR(191)    NOT NULL,
    password_hash  VARCHAR(255)    NOT NULL,
    is_active      TINYINT(1)      NOT NULL DEFAULT 1,
    last_login_at  DATETIME        NULL,
    created_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at     DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uk_platform_admins_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE business_lines
    ADD CONSTRAINT fk_business_lines_onboarded_by
        FOREIGN KEY (onboarded_by_admin_id) REFERENCES platform_admins(id) ON DELETE SET NULL ON UPDATE CASCADE;

CREATE TABLE IF NOT EXISTS subscription_codes (
    id                 BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code_prefix        VARCHAR(12)     NOT NULL,
    code_hash          CHAR(64)        NOT NULL,
    intent             ENUM('activate_new','renew_existing','either') NOT NULL DEFAULT 'either',
    business_line_id   BIGINT UNSIGNED NULL,
    duration_months    SMALLINT UNSIGNED NOT NULL DEFAULT 12,
    max_uses           SMALLINT UNSIGNED NOT NULL DEFAULT 1,
    use_count          SMALLINT UNSIGNED NOT NULL DEFAULT 0,
    expires_at         DATETIME        NULL,
    revoked_at         DATETIME        NULL,
    created_by_admin_id BIGINT UNSIGNED NOT NULL,
    created_at         DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uk_subscription_codes_hash (code_hash),
    KEY idx_subscription_codes_business_line (business_line_id),
    CONSTRAINT fk_subscription_codes_business_line FOREIGN KEY (business_line_id) REFERENCES business_lines(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_subscription_codes_admin FOREIGN KEY (created_by_admin_id) REFERENCES platform_admins(id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscription_code_redemptions (
    id                  BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    subscription_code_id BIGINT UNSIGNED NOT NULL,
    business_line_id    BIGINT UNSIGNED NOT NULL,
    redeemed_at          DATETIME       NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_scr_code (subscription_code_id),
    KEY idx_scr_business_line (business_line_id),
    CONSTRAINT fk_scr_code FOREIGN KEY (subscription_code_id) REFERENCES subscription_codes(id) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT fk_scr_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;

CREATE TABLE IF NOT EXISTS subscription_events (
    id               BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    business_line_id BIGINT UNSIGNED NOT NULL,
    event_type       VARCHAR(60)     NOT NULL,
    detail           VARCHAR(500)    NULL,
    stripe_event_id  VARCHAR(191)    NULL,
    created_at       DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
    KEY idx_subscription_events_business_line (business_line_id, created_at),
    CONSTRAINT fk_subscription_events_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;