-- Migration 0003: create_crm_core
-- 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 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;


SET FOREIGN_KEY_CHECKS = 1;
