-- ============================================================================================
-- ABOS -- RESUME migration script (bank_account_branch_fields_and_banks.sql onward)
-- Generated 2026-08-05T22:19:38Z.
-- Target database: stacie_Aggie_v1.0
--
-- Use this to resume a run that got through finance_views.sql successfully but failed on the
-- entity_type UPDATE in bank_account_branch_fields_and_banks.sql (Error 1175, safe update
-- mode) -- now fixed by toggling SQL_SAFE_UPDATES off around that statement. Also carries the
-- same fix applied to two later sections (projects_foundation_refactor.sql,
-- projects_hierarchy_and_ai.sql) that update non-key columns the same way and would have
-- hit the identical error further down this same run.
--
-- HOW TO RUN:
--   mysql -u <user> -p 'stacie_Aggie_v1.0' < manual-migration-resume-from-bank-fields.sql
--
-- SAFE TO RE-RUN: every section guards its own objects, same as manual-full-migration.sql.
-- ============================================================================================

-- -----------------------------------------------------------------------------
-- SECTION: bank_account_branch_fields_and_banks.sql
-- -----------------------------------------------------------------------------
-- =============================================================================
-- Bank account form: add representative + branch fields to dim_financial_entities,
-- and a dim_banks reference table (seeded with major US banks) for the Bank Name
-- dropdown on /finance/business-accounts/bank-accounts.
--
-- Idempotent: column adds are guarded via information_schema; dim_banks uses
-- CREATE TABLE IF NOT EXISTS + UNIQUE(name) + INSERT IGNORE.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/bank_account_branch_fields_and_banks.sql
-- =============================================================================
USE `stacie_Aggie_v1.0`;

-- ── 1. New columns on dim_financial_entities ────────────────────────────────
SET @db := DATABASE();

-- business_id: the JPA entity maps a Business relation and list/create queries filter
-- on it; some older dev databases predate this column. Guarded add so prod (which
-- already has it) is unaffected.
SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE dim_financial_entities ADD COLUMN business_id INT NULL AFTER tenant_id',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_financial_entities' AND COLUMN_NAME='business_id');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE dim_financial_entities ADD COLUMN bank_representative VARCHAR(255) NULL AFTER bank_email',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_financial_entities' AND COLUMN_NAME='bank_representative');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE dim_financial_entities ADD COLUMN bank_branch VARCHAR(255) NULL AFTER bank_representative',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_financial_entities' AND COLUMN_NAME='bank_branch');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE dim_financial_entities ADD COLUMN bank_branch_number VARCHAR(100) NULL AFTER bank_branch',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_financial_entities' AND COLUMN_NAME='bank_branch_number');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := (SELECT IF(COUNT(*)=0,
  'ALTER TABLE dim_financial_entities ADD COLUMN bank_branch_address VARCHAR(500) NULL AFTER bank_branch_number',
  'SELECT 1') FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_financial_entities' AND COLUMN_NAME='bank_branch_address');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 1b. Align entity_type enum with the Java enum names ─────────────────────
-- The JPA enum is stored as its constant name ('Bank','CreditCard'), but some dev
-- DBs define the column as enum('Bank','Credit Card') with a space, which truncates
-- on CreditCard inserts. Expand → migrate legacy value → collapse to canonical.
--
-- entity_type carries no index, so this UPDATE is rejected by MySQL Workbench/
-- phpMyAdmin's client-side "safe update mode" (Error 1175) even though it has a
-- WHERE clause — that mode specifically requires a KEY column in the WHERE.
-- Toggle it off for just this one statement rather than relying on every client's
-- local preference being configured a particular way.
SET SQL_SAFE_UPDATES = 0;
ALTER TABLE dim_financial_entities
  MODIFY COLUMN entity_type ENUM('Bank','Credit Card','CreditCard') NOT NULL;
UPDATE dim_financial_entities SET entity_type='CreditCard' WHERE entity_type='Credit Card';
SET SQL_SAFE_UPDATES = 1;
ALTER TABLE dim_financial_entities
  MODIFY COLUMN entity_type ENUM('Bank','CreditCard') NOT NULL;

-- ── 2. Reference table for the Bank Name dropdown ───────────────────────────
CREATE TABLE IF NOT EXISTS dim_banks (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(255) NOT NULL,
    country     VARCHAR(10)  NOT NULL DEFAULT 'US',
    is_deleted  TINYINT(1)   NOT NULL DEFAULT 0,
    created_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP    DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_dim_banks_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 3. Seed major US banks (curated ~100; INSERT IGNORE = safe to re-run) ────
INSERT IGNORE INTO dim_banks (name) VALUES
('JPMorgan Chase Bank'),('Bank of America'),('Wells Fargo Bank'),('Citibank'),
('U.S. Bank'),('PNC Bank'),('Truist Bank'),('Goldman Sachs Bank USA'),
('TD Bank'),('Capital One'),('The Bank of New York Mellon'),('State Street Bank and Trust'),
('Citizens Bank'),('Fifth Third Bank'),('Morgan Stanley Bank'),('HSBC Bank USA'),
('Ally Bank'),('KeyBank'),('Regions Bank'),('M&T Bank'),
('Huntington National Bank'),('American Express National Bank'),('Discover Bank'),('BMO Bank'),
('First Republic Bank'),('Silicon Valley Bank'),('Charles Schwab Bank'),('Comerica Bank'),
('Zions Bank'),('First Citizens Bank'),('Synchrony Bank'),('Santander Bank'),
('Flagstar Bank'),('Western Alliance Bank'),('Valley National Bank'),('Webster Bank'),
('East West Bank'),('First Horizon Bank'),('Frost Bank'),('Bank of Oklahoma'),
('Pinnacle Bank'),('Wintrust Bank'),('Associated Bank'),('Old National Bank'),
('UMB Bank'),('Bank OZK'),('Prosperity Bank'),('Cadence Bank'),
('South State Bank'),('Fulton Bank'),('Simmons Bank'),('Umpqua Bank'),
('Texas Capital Bank'),('Glacier Bank'),('Centennial Bank'),('Commerce Bank'),
('First National Bank of Omaha'),('Arvest Bank'),('WesBanco'),('Renasant Bank'),
('Ameris Bank'),('Bank of Hawaii'),('First Hawaiian Bank'),('Rockland Trust'),
('Banner Bank'),('Cathay Bank'),('Bank of Hope'),('Pacific Western Bank'),
('Trustmark National Bank'),('Independent Bank'),('Atlantic Union Bank'),('TowneBank'),
('Sandy Spring Bank'),('Eastern Bank'),('Berkshire Bank'),('NBT Bank'),
('Provident Bank'),('Dime Community Bank'),('ConnectOne Bank'),('First Interstate Bank'),
('Hancock Whitney Bank'),('BankUnited'),('Axos Bank'),('Live Oak Bank'),
('EverBank'),('USAA Federal Savings Bank'),('Navy Federal Credit Union'),('PenFed Credit Union'),
('Varo Bank'),('SoFi Bank'),('Mercury'),('Brex'),
('City National Bank'),('Signature Bank'),('First National Bank'),('Bremer Bank'),
('Great Southern Bank'),('Columbia Bank'),('Customers Bank'),('Amerant Bank'),
('Beneficial State Bank'),('Nicolet National Bank');

-- -----------------------------------------------------------------------------
-- SECTION: user_business_access.sql
-- -----------------------------------------------------------------------------
-- =============================================================================
-- user_business_access: which businesses (under a tenant) a user may access.
-- Replaces per-module READ/WRITE grants for tenant managers/users with a simple
-- per-business on/off grant. SUPER_ADMIN / TENANT_ADMIN are not restricted.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/user_business_access.sql
-- =============================================================================
USE `stacie_Aggie_v1.0`;

CREATE TABLE IF NOT EXISTS user_business_access (
    id          BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id   INT NOT NULL,
    user_id     INT NOT NULL,
    business_id INT NOT NULL,
    is_deleted  TINYINT(1) NOT NULL DEFAULT 0,
    created_at  TIMESTAMP  DEFAULT CURRENT_TIMESTAMP,
    created_by  INT NULL,
    updated_at  TIMESTAMP  DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY uq_uba_user_business (user_id, business_id),
    KEY idx_uba_user (user_id),
    KEY idx_uba_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- SECTION: sp_process_ai_bank_statement.sql
-- -----------------------------------------------------------------------------
-- SP-24 (v2): Atomic persist of human-verified AI statement extraction.
-- Rewritten for the current unified finance schema:
--   dim_financial_entities  (Bank / Credit Card entity, resolved or created)
--   fact_statements         (statement header, keyed by entity_id)
--   fact_statement_transactions (line items)
-- Contract matches FinanceProcedureRepository (7 params: 5 IN + 2 OUT).
-- Uses JSON_VALUE (MySQL 8.0.21+) so JSON null maps to SQL NULL, not the string "null".
-- Requires session var @current_tenant_id (set via RlsSessionHelper / TenantContextFilter).
--
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/sp_process_ai_bank_statement.sql

USE `stacie_Aggie_v1.0`;

DROP PROCEDURE IF EXISTS sp_process_ai_bank_statement;

DELIMITER $$

CREATE PROCEDURE sp_process_ai_bank_statement(
    IN  p_statement_json     LONGTEXT,
    IN  p_transactions_json  LONGTEXT,
    IN  p_created_by         INT,
    IN  p_entity_type        VARCHAR(50),
    IN  p_entity_id          BIGINT,
    OUT p_statement_id       BIGINT,
    OUT p_transaction_count  INT
)
proc: BEGIN
    DECLARE v_tenant_id     INT;
    DECLARE v_entity_id     BIGINT;
    DECLARE v_entity_type   VARCHAR(20);
    DECLARE v_account_no    VARCHAR(100);
    DECLARE v_bank_name     VARCHAR(255);
    DECLARE v_entity_label  VARCHAR(255);
    DECLARE v_business_id   INT;

    SET v_tenant_id = @current_tenant_id;
    IF v_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant context (@current_tenant_id) is required';
    END IF;

    -- Selected business scope (nullable). Persisted on the entity + statement so
    -- business-filtered list screens can find these imported records.
    SET v_business_id = @current_business_id;

    IF p_statement_json IS NULL OR JSON_VALID(p_statement_json) = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid statement JSON';
    END IF;
    IF p_transactions_json IS NULL OR JSON_VALID(p_transactions_json) = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid transactions JSON';
    END IF;

    -- Normalise entity type to the dim_financial_entities enum ('Bank' | 'Credit Card')
    SET v_entity_type = CASE
        WHEN p_entity_type IN ('CreditCard', 'Credit Card', 'CREDIT_CARD') THEN 'Credit Card'
        ELSE 'Bank'
    END;

    SET v_account_no = JSON_VALUE(p_statement_json, '$.accountNumber');

    -- ── Ensure the bank exists in the global reference list (dim_banks) ──────
    -- The AI extraction may surface a bank/issuer not yet in the Bank Name
    -- dropdown. Register it so it becomes selectable for future entities.
    -- INSERT IGNORE + UNIQUE(name) makes this an idempotent "add if missing".
    SET v_bank_name = NULLIF(TRIM(JSON_VALUE(p_statement_json, '$.bankName')), '');
    IF v_bank_name IS NOT NULL THEN
        INSERT IGNORE INTO dim_banks (name) VALUES (v_bank_name);
    END IF;

    -- User-supplied account label/nickname (falls back to bank/holder name below).
    SET v_entity_label = NULLIF(TRIM(JSON_VALUE(p_statement_json, '$.entityLabel')), '');

    -- ── Resolve the financial entity ────────────────────────────────────────
    -- 1) explicit entity_id, 2) existing tenant entity by account number, 3) create.
    IF p_entity_id IS NOT NULL THEN
        SELECT id INTO v_entity_id
        FROM dim_financial_entities
        WHERE id = p_entity_id AND tenant_id = v_tenant_id AND is_deleted = 0
        LIMIT 1;
    END IF;

    IF v_entity_id IS NULL AND v_account_no IS NOT NULL AND v_account_no <> '' THEN
        SELECT id INTO v_entity_id
        FROM dim_financial_entities
        WHERE tenant_id = v_tenant_id
          AND account_number = v_account_no
          AND entity_type = v_entity_type
          AND is_deleted = 0
        ORDER BY id LIMIT 1;
    END IF;

    IF v_entity_id IS NULL THEN
        INSERT INTO dim_financial_entities (
            tenant_id, business_id, entity_name, entity_type, account_holder_name, account_number,
            bank_or_issuer_name, currency, status, created_by, updated_by, is_deleted
        ) VALUES (
            v_tenant_id,
            v_business_id,
            COALESCE(
                v_entity_label,
                v_bank_name,
                NULLIF(JSON_VALUE(p_statement_json, '$.accountHolderName'), ''),
                'Imported Entity'),
            v_entity_type,
            JSON_VALUE(p_statement_json, '$.accountHolderName'),
            v_account_no,
            v_bank_name,
            COALESCE(NULLIF(JSON_VALUE(p_statement_json, '$.currency'), ''), 'USD'),
            'Active',
            p_created_by, p_created_by, 0
        );
        SET v_entity_id = LAST_INSERT_ID();
    END IF;

    -- Backfill business scope on a pre-existing entity that was imported without one
    -- (e.g. before business_id propagation existed), so it appears in scoped lists.
    IF v_business_id IS NOT NULL AND v_entity_id IS NOT NULL THEN
        UPDATE dim_financial_entities
        SET business_id = v_business_id
        WHERE id = v_entity_id AND tenant_id = v_tenant_id AND business_id IS NULL;
    END IF;

    -- ── Insert the statement header ─────────────────────────────────────────
    INSERT INTO fact_statements (
        tenant_id, business_id, entity_id, statement_period, beginning_balance, current_balance,
        current_interest_rate, created_by, updated_by, is_deleted
    ) VALUES (
        v_tenant_id,
        v_business_id,
        v_entity_id,
        JSON_VALUE(p_statement_json, '$.statementPeriod'),
        JSON_VALUE(p_statement_json, '$.beginningBalance' RETURNING DECIMAL(15,2)),
        JSON_VALUE(p_statement_json, '$.currentBalance'  RETURNING DECIMAL(15,2)),
        JSON_VALUE(p_statement_json, '$.interestRate'    RETURNING DECIMAL(5,2)),
        p_created_by, p_created_by, 0
    );

    SET p_statement_id = LAST_INSERT_ID();

    -- ── Insert the transactions ─────────────────────────────────────────────
    INSERT INTO fact_statement_transactions (
        tenant_id, statement_id, transaction_date, transaction_type, description, vendor,
        amount, cash_in_or_out, personal_or_business, associated_card_number, home_tax_percent,
        person_or_project_1, expense_labels, pdf_link, created_by, updated_by, is_deleted
    )
    SELECT
        v_tenant_id,
        p_statement_id,
        jt.transaction_date,
        CASE
            WHEN jt.transaction_type IN ('Credit','Debit','Transfer','Fee','Interest','Reversal')
                THEN jt.transaction_type
            WHEN LOWER(COALESCE(jt.cash_in_or_out, '')) LIKE '%in%' THEN 'Credit'
            ELSE 'Debit'
        END,
        jt.description,
        jt.vendor,
        jt.amount,
        CASE WHEN LOWER(COALESCE(jt.cash_in_or_out, '')) LIKE '%in%' THEN 'Cash In' ELSE 'Cash Out' END,
        CASE WHEN jt.personal_or_business IN ('Personal','Business') THEN jt.personal_or_business ELSE NULL END,
        jt.associated_card_number,
        jt.home_tax_percent,
        jt.person_or_project_1,
        jt.expense_labels,
        jt.pdf_link,
        p_created_by, p_created_by, 0
    FROM JSON_TABLE(
        p_transactions_json,
        '$[*]' COLUMNS (
            transaction_date      DATE           PATH '$.transactionDate',
            transaction_type      VARCHAR(50)    PATH '$.transactionType',
            description           TEXT           PATH '$.description',
            vendor                VARCHAR(255)   PATH '$.vendor',
            amount                DECIMAL(15,2)  PATH '$.amount',
            cash_in_or_out        VARCHAR(20)    PATH '$.cashInOrOut',
            personal_or_business  VARCHAR(20)    PATH '$.personalOrBusiness',
            associated_card_number VARCHAR(100)  PATH '$.associatedCardNumber',
            home_tax_percent      DECIMAL(5,2)   PATH '$.homeTaxPercent',
            person_or_project_1   VARCHAR(255)   PATH '$.personOrProject1',
            expense_labels        TEXT           PATH '$.expenseLabels',
            pdf_link              VARCHAR(512)   PATH '$.pdfLink'
        )
    ) AS jt;

    SET p_transaction_count = ROW_COUNT();
END proc$$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: fix_sp_process_ai_bank_statement.sql
-- -----------------------------------------------------------------------------
-- ============================================================================================
-- FIX: sp_process_ai_bank_statement — MariaDB compatibility
-- The target server is MariaDB, whose JSON_VALUE() does not support MySQL 8's
-- "RETURNING <type>" clause (it always returns text). Replaced with CAST(JSON_VALUE(...) AS ...)
-- for the three numeric fields (beginningBalance, currentBalance, interestRate).
-- Safe to re-run: DROP PROCEDURE IF EXISTS + CREATE PROCEDURE.
-- This same fix has also been applied in full_schema_snapshot.sql.
-- ============================================================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_process_ai_bank_statement`$$
CREATE PROCEDURE `sp_process_ai_bank_statement`(
    IN  p_statement_json     LONGTEXT,
    IN  p_transactions_json  LONGTEXT,
    IN  p_created_by         INT,
    IN  p_entity_type        VARCHAR(50),
    IN  p_entity_id          BIGINT,
    OUT p_statement_id       BIGINT,
    OUT p_transaction_count  INT
)
proc: BEGIN
    DECLARE v_tenant_id     INT;
    DECLARE v_entity_id     BIGINT;
    DECLARE v_entity_type   VARCHAR(20);
    DECLARE v_account_no    VARCHAR(100);
    DECLARE v_bank_name     VARCHAR(255);
    DECLARE v_entity_label  VARCHAR(255);
    DECLARE v_business_id   INT;

    SET v_tenant_id = @current_tenant_id;
    IF v_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant context (@current_tenant_id) is required';
    END IF;

    -- Selected business scope (nullable). Persisted on the entity + statement so
    -- business-filtered list screens can find these imported records.
    SET v_business_id = @current_business_id;

    IF p_statement_json IS NULL OR JSON_VALID(p_statement_json) = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid statement JSON';
    END IF;
    IF p_transactions_json IS NULL OR JSON_VALID(p_transactions_json) = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid transactions JSON';
    END IF;

    -- Normalise entity type to the dim_financial_entities enum ('Bank' | 'Credit Card')
    SET v_entity_type = CASE
        WHEN p_entity_type IN ('CreditCard', 'Credit Card', 'CREDIT_CARD') THEN 'Credit Card'
        ELSE 'Bank'
    END;

    SET v_account_no = JSON_VALUE(p_statement_json, '$.accountNumber');

    -- ── Ensure the bank exists in the global reference list (dim_banks) ──────
    -- The AI extraction may surface a bank/issuer not yet in the Bank Name
    -- dropdown. Register it so it becomes selectable for future entities.
    -- INSERT IGNORE + UNIQUE(name) makes this an idempotent "add if missing".
    SET v_bank_name = NULLIF(TRIM(JSON_VALUE(p_statement_json, '$.bankName')), '');
    IF v_bank_name IS NOT NULL THEN
        INSERT IGNORE INTO dim_banks (name) VALUES (v_bank_name);
    END IF;

    -- User-supplied account label/nickname (falls back to bank/holder name below).
    SET v_entity_label = NULLIF(TRIM(JSON_VALUE(p_statement_json, '$.entityLabel')), '');

    -- ── Resolve the financial entity ────────────────────────────────────────
    -- 1) explicit entity_id, 2) existing tenant entity by account number, 3) create.
    IF p_entity_id IS NOT NULL THEN
        SELECT id INTO v_entity_id
        FROM dim_financial_entities
        WHERE id = p_entity_id AND tenant_id = v_tenant_id AND is_deleted = 0
        LIMIT 1;
    END IF;

    IF v_entity_id IS NULL AND v_account_no IS NOT NULL AND v_account_no <> '' THEN
        SELECT id INTO v_entity_id
        FROM dim_financial_entities
        WHERE tenant_id = v_tenant_id
          AND account_number = v_account_no
          AND entity_type = v_entity_type
          AND is_deleted = 0
        ORDER BY id LIMIT 1;
    END IF;

    IF v_entity_id IS NULL THEN
        INSERT INTO dim_financial_entities (
            tenant_id, business_id, entity_name, entity_type, account_holder_name, account_number,
            bank_or_issuer_name, currency, status, created_by, updated_by, is_deleted
        ) VALUES (
            v_tenant_id,
            v_business_id,
            COALESCE(
                v_entity_label,
                v_bank_name,
                NULLIF(JSON_VALUE(p_statement_json, '$.accountHolderName'), ''),
                'Imported Entity'),
            v_entity_type,
            JSON_VALUE(p_statement_json, '$.accountHolderName'),
            v_account_no,
            v_bank_name,
            COALESCE(NULLIF(JSON_VALUE(p_statement_json, '$.currency'), ''), 'USD'),
            'Active',
            p_created_by, p_created_by, 0
        );
        SET v_entity_id = LAST_INSERT_ID();
    END IF;

    -- Backfill business scope on a pre-existing entity that was imported without one
    -- (e.g. before business_id propagation existed), so it appears in scoped lists.
    IF v_business_id IS NOT NULL AND v_entity_id IS NOT NULL THEN
        UPDATE dim_financial_entities
        SET business_id = v_business_id
        WHERE id = v_entity_id AND tenant_id = v_tenant_id AND business_id IS NULL;
    END IF;

    -- ── Insert the statement header ─────────────────────────────────────────
    INSERT INTO fact_statements (
        tenant_id, business_id, entity_id, statement_period, beginning_balance, current_balance,
        current_interest_rate, created_by, updated_by, is_deleted
    ) VALUES (
        v_tenant_id,
        v_business_id,
        v_entity_id,
        JSON_VALUE(p_statement_json, '$.statementPeriod'),
        CAST(JSON_VALUE(p_statement_json, '$.beginningBalance') AS DECIMAL(15,2)),
        CAST(JSON_VALUE(p_statement_json, '$.currentBalance') AS DECIMAL(15,2)),
        CAST(JSON_VALUE(p_statement_json, '$.interestRate') AS DECIMAL(5,2)),
        p_created_by, p_created_by, 0
    );

    SET p_statement_id = LAST_INSERT_ID();

    -- ── Insert the transactions ─────────────────────────────────────────────
    INSERT INTO fact_statement_transactions (
        tenant_id, statement_id, transaction_date, transaction_type, description, vendor,
        amount, cash_in_or_out, personal_or_business, associated_card_number, home_tax_percent,
        person_or_project_1, expense_labels, pdf_link, created_by, updated_by, is_deleted
    )
    SELECT
        v_tenant_id,
        p_statement_id,
        jt.transaction_date,
        CASE
            WHEN jt.transaction_type IN ('Credit','Debit','Transfer','Fee','Interest','Reversal')
                THEN jt.transaction_type
            WHEN LOWER(COALESCE(jt.cash_in_or_out, '')) LIKE '%in%' THEN 'Credit'
            ELSE 'Debit'
        END,
        jt.description,
        jt.vendor,
        jt.amount,
        CASE WHEN LOWER(COALESCE(jt.cash_in_or_out, '')) LIKE '%in%' THEN 'Cash In' ELSE 'Cash Out' END,
        CASE WHEN jt.personal_or_business IN ('Personal','Business') THEN jt.personal_or_business ELSE NULL END,
        jt.associated_card_number,
        jt.home_tax_percent,
        jt.person_or_project_1,
        jt.expense_labels,
        jt.pdf_link,
        p_created_by, p_created_by, 0
    FROM JSON_TABLE(
        p_transactions_json,
        '$[*]' COLUMNS (
            transaction_date      DATE           PATH '$.transactionDate',
            transaction_type      VARCHAR(50)    PATH '$.transactionType',
            description           TEXT           PATH '$.description',
            vendor                VARCHAR(255)   PATH '$.vendor',
            amount                DECIMAL(15,2)  PATH '$.amount',
            cash_in_or_out        VARCHAR(20)    PATH '$.cashInOrOut',
            personal_or_business  VARCHAR(20)    PATH '$.personalOrBusiness',
            associated_card_number VARCHAR(100)  PATH '$.associatedCardNumber',
            home_tax_percent      DECIMAL(5,2)   PATH '$.homeTaxPercent',
            person_or_project_1   VARCHAR(255)   PATH '$.personOrProject1',
            expense_labels        TEXT           PATH '$.expenseLabels',
            pdf_link              VARCHAR(512)   PATH '$.pdfLink'
        )
    ) AS jt;

    SET p_transaction_count = ROW_COUNT();
END proc $$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: attendance_sp_views.sql
-- -----------------------------------------------------------------------------
-- HRM Attendance: SP-write / View-read stack for fact_attendance.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/attendance_sp_views.sql

USE `stacie_Aggie_v1.0`;

CREATE OR REPLACE VIEW vw_attendance AS
SELECT
    a.id            AS id,
    a.id            AS attendanceId,
    a.tenant_id     AS tenant_id,
    a.employee_id   AS employeeId,
    a.employee_name AS employeeName,
    a.date          AS date,
    a.status        AS status,
    a.hours_worked  AS hours,
    a.check_in      AS checkIn,
    a.check_out     AS checkOut,
    a.notes         AS notes,
    a.created_at    AS createdAt,
    a.updated_at    AS updatedAt
FROM fact_attendance a
WHERE a.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_attendance;
DROP PROCEDURE IF EXISTS sp_update_attendance;
DROP PROCEDURE IF EXISTS sp_soft_delete_attendance;

DELIMITER $$

CREATE PROCEDURE sp_create_attendance(
    IN  p_tenant_id     INT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_date          DATE,
    IN  p_status        VARCHAR(50),
    IN  p_hours         DECIMAL(5,2),
    IN  p_check_in      VARCHAR(20),
    IN  p_check_out     VARCHAR(20),
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_attendance_id BIGINT
)
proc: BEGIN
    DECLARE v_name VARCHAR(255);
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_date IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Attendance date is required';
    END IF;

    SET v_name = NULLIF(TRIM(COALESCE(p_employee_name, '')), '');
    IF v_name IS NULL AND p_employee_id IS NOT NULL THEN
        SELECT NULLIF(TRIM(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, ''))), '')
          INTO v_name
          FROM dim_employees
         WHERE id = p_employee_id AND tenant_id = p_tenant_id
         LIMIT 1;
    END IF;
    SET v_name = COALESCE(v_name, 'Unknown');

    INSERT INTO fact_attendance (
        tenant_id, employee_id, employee_name, date, status, hours_worked,
        check_in, check_out, notes, created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_employee_id, v_name, p_date,
        COALESCE(p_status, 'Present'), p_hours,
        NULLIF(p_check_in, ''), NULLIF(p_check_out, ''), p_notes,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_attendance_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_attendance(
    IN  p_tenant_id     INT,
    IN  p_attendance_id BIGINT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_date          DATE,
    IN  p_status        VARCHAR(50),
    IN  p_hours         DECIMAL(5,2),
    IN  p_check_in      VARCHAR(20),
    IN  p_check_out     VARCHAR(20),
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_attendance
        WHERE id = p_attendance_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Attendance record not found';
    END IF;

    UPDATE fact_attendance SET
        employee_id   = COALESCE(p_employee_id, employee_id),
        employee_name = COALESCE(NULLIF(TRIM(COALESCE(p_employee_name, '')), ''), employee_name),
        date          = COALESCE(p_date, date),
        status        = COALESCE(p_status, status),
        hours_worked  = COALESCE(p_hours, hours_worked),
        check_in      = COALESCE(NULLIF(p_check_in, ''), check_in),
        check_out     = COALESCE(NULLIF(p_check_out, ''), check_out),
        notes         = COALESCE(p_notes, notes),
        updated_at    = NOW(),
        updated_by    = p_updated_by
    WHERE id = p_attendance_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

CREATE PROCEDURE sp_soft_delete_attendance(
    IN  p_tenant_id     INT,
    IN  p_attendance_id BIGINT,
    IN  p_deleted_by    INT
)
proc: BEGIN
    UPDATE fact_attendance SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_attendance_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: payroll_sp_views.sql
-- -----------------------------------------------------------------------------
-- HRM Payroll: SP-write / View-read stack for fact_payroll.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/payroll_sp_views.sql

USE `stacie_Aggie_v1.0`;

CREATE OR REPLACE VIEW vw_payroll AS
SELECT
    p.id            AS id,
    p.id            AS payrollId,
    p.tenant_id     AS tenant_id,
    p.employee_id   AS employeeId,
    p.employee_name AS employeeName,
    p.month         AS month,
    p.basic_salary  AS basicSalary,
    p.allowances    AS allowances,
    p.deductions    AS deductions,
    p.tax           AS tax,
    p.net_pay       AS netPay,
    p.currency      AS currency,
    p.status        AS status,
    p.pay_date      AS payDate,
    p.created_at    AS createdAt,
    p.updated_at    AS updatedAt
FROM fact_payroll p
WHERE p.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_payroll;
DROP PROCEDURE IF EXISTS sp_update_payroll;
DROP PROCEDURE IF EXISTS sp_soft_delete_payroll;

DELIMITER $$

CREATE PROCEDURE sp_create_payroll(
    IN  p_tenant_id     INT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_month         VARCHAR(20),
    IN  p_basic_salary  DECIMAL(15,2),
    IN  p_allowances    DECIMAL(15,2),
    IN  p_deductions    DECIMAL(15,2),
    IN  p_tax           DECIMAL(15,2),
    IN  p_net_pay       DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_status        VARCHAR(20),
    IN  p_pay_date      DATE,
    IN  p_created_by    INT,
    OUT p_payroll_id    BIGINT
)
proc: BEGIN
    DECLARE v_name VARCHAR(255);
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_basic_salary IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Basic salary is required';
    END IF;
    IF p_net_pay IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Net pay is required';
    END IF;

    SET v_name = NULLIF(TRIM(COALESCE(p_employee_name, '')), '');
    IF v_name IS NULL AND p_employee_id IS NOT NULL THEN
        SELECT NULLIF(TRIM(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, ''))), '')
          INTO v_name
          FROM dim_employees
         WHERE id = p_employee_id AND tenant_id = p_tenant_id
         LIMIT 1;
    END IF;
    SET v_name = COALESCE(v_name, 'Unknown');

    INSERT INTO fact_payroll (
        tenant_id, employee_id, employee_name, month, basic_salary, allowances,
        deductions, tax, net_pay, currency, status, pay_date,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_employee_id, v_name, p_month, p_basic_salary, COALESCE(p_allowances, 0),
        COALESCE(p_deductions, 0), COALESCE(p_tax, 0), p_net_pay, COALESCE(p_currency, 'USD'),
        COALESCE(p_status, 'Draft'), p_pay_date,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_payroll_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_payroll(
    IN  p_tenant_id     INT,
    IN  p_payroll_id    BIGINT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_month         VARCHAR(20),
    IN  p_basic_salary  DECIMAL(15,2),
    IN  p_allowances    DECIMAL(15,2),
    IN  p_deductions    DECIMAL(15,2),
    IN  p_tax           DECIMAL(15,2),
    IN  p_net_pay       DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_status        VARCHAR(20),
    IN  p_pay_date      DATE,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_payroll
        WHERE id = p_payroll_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Payroll record not found';
    END IF;

    UPDATE fact_payroll SET
        employee_id   = COALESCE(p_employee_id, employee_id),
        employee_name = COALESCE(NULLIF(TRIM(COALESCE(p_employee_name, '')), ''), employee_name),
        month         = COALESCE(p_month, month),
        basic_salary  = COALESCE(p_basic_salary, basic_salary),
        allowances    = COALESCE(p_allowances, allowances),
        deductions    = COALESCE(p_deductions, deductions),
        tax           = COALESCE(p_tax, tax),
        net_pay       = COALESCE(p_net_pay, net_pay),
        currency      = COALESCE(p_currency, currency),
        status        = COALESCE(p_status, status),
        pay_date      = COALESCE(p_pay_date, pay_date),
        updated_at    = NOW(),
        updated_by    = p_updated_by
    WHERE id = p_payroll_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

CREATE PROCEDURE sp_soft_delete_payroll(
    IN  p_tenant_id     INT,
    IN  p_payroll_id    BIGINT,
    IN  p_deleted_by    INT
)
proc: BEGIN
    UPDATE fact_payroll SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_payroll_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: digital_assets_sp_views.sql
-- -----------------------------------------------------------------------------
-- Inventory Digital Assets: purpose-fit table + SP-write / View-read stack.
-- The legacy dim_assets (asset_tag/serial_number/asset_type_id, Available/Allocated status)
-- models physical assets and does not match the frontend DigitalAsset shape (licenses/domains
-- with vendor, cost, expiry, assignee). We introduce inv_digital_assets for the UI contract.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/digital_assets_sp_views.sql

USE `stacie_Aggie_v1.0`;

CREATE TABLE IF NOT EXISTS inv_digital_assets (
    id          INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id   INT NOT NULL,
    name        VARCHAR(255) NOT NULL,
    category    VARCHAR(150) NULL,
    vendor      VARCHAR(255) NULL,
    cost        DECIMAL(15,2) NULL,
    currency    VARCHAR(10) NULL DEFAULT 'USD',
    status      VARCHAR(50) NULL DEFAULT 'Active',
    expiry_date DATE NULL,
    assigned_to VARCHAR(255) NULL,
    created_at  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by  INT NULL,
    updated_at  TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by  INT NULL,
    is_deleted  TINYINT(1) NULL DEFAULT 0,
    KEY idx_inv_digital_assets_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_digital_assets AS
SELECT
    d.id          AS id,
    d.id          AS assetId,
    d.tenant_id   AS tenant_id,
    d.name        AS name,
    d.category    AS category,
    d.vendor      AS vendor,
    d.cost        AS cost,
    d.currency    AS currency,
    d.status      AS status,
    d.expiry_date AS expiryDate,
    d.assigned_to AS assignedTo,
    d.created_at  AS createdAt,
    d.updated_at  AS updatedAt
FROM inv_digital_assets d
WHERE d.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_digital_asset;
DROP PROCEDURE IF EXISTS sp_update_digital_asset;
DROP PROCEDURE IF EXISTS sp_soft_delete_digital_asset;

DELIMITER $$

CREATE PROCEDURE sp_create_digital_asset(
    IN  p_tenant_id   INT,
    IN  p_name        VARCHAR(255),
    IN  p_category    VARCHAR(150),
    IN  p_vendor      VARCHAR(255),
    IN  p_cost        DECIMAL(15,2),
    IN  p_currency    VARCHAR(10),
    IN  p_status      VARCHAR(50),
    IN  p_expiry_date DATE,
    IN  p_assigned_to VARCHAR(255),
    IN  p_created_by  INT,
    OUT p_asset_id    INT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_name IS NULL OR p_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Asset name is required';
    END IF;

    INSERT INTO inv_digital_assets (
        tenant_id, name, category, vendor, cost, currency, status, expiry_date, assigned_to,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_name, p_category, p_vendor, p_cost, COALESCE(p_currency, 'USD'),
        COALESCE(p_status, 'Active'), p_expiry_date, p_assigned_to,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_asset_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_digital_asset(
    IN  p_tenant_id   INT,
    IN  p_asset_id    INT,
    IN  p_name        VARCHAR(255),
    IN  p_category    VARCHAR(150),
    IN  p_vendor      VARCHAR(255),
    IN  p_cost        DECIMAL(15,2),
    IN  p_currency    VARCHAR(10),
    IN  p_status      VARCHAR(50),
    IN  p_expiry_date DATE,
    IN  p_assigned_to VARCHAR(255),
    IN  p_updated_by  INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM inv_digital_assets
        WHERE id = p_asset_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Digital asset not found';
    END IF;

    UPDATE inv_digital_assets SET
        name        = COALESCE(p_name, name),
        category    = COALESCE(p_category, category),
        vendor      = COALESCE(p_vendor, vendor),
        cost        = COALESCE(p_cost, cost),
        currency    = COALESCE(p_currency, currency),
        status      = COALESCE(p_status, status),
        expiry_date = COALESCE(p_expiry_date, expiry_date),
        assigned_to = COALESCE(p_assigned_to, assigned_to),
        updated_at  = NOW(),
        updated_by  = p_updated_by
    WHERE id = p_asset_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

CREATE PROCEDURE sp_soft_delete_digital_asset(
    IN  p_tenant_id  INT,
    IN  p_asset_id   INT,
    IN  p_deleted_by INT
)
proc: BEGIN
    UPDATE inv_digital_assets SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_asset_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: loans_sp_views.sql
-- -----------------------------------------------------------------------------
-- HRM Loans: SP-write / View-read stack for fact_loans.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/loans_sp_views.sql

USE `stacie_Aggie_v1.0`;

CREATE OR REPLACE VIEW vw_loans AS
SELECT
    l.id                 AS id,
    l.id                 AS loanId,
    l.tenant_id          AS tenant_id,
    l.employee_id        AS employeeId,
    l.employee_name      AS employeeName,
    l.loan_amount        AS loanAmount,
    l.currency           AS currency,
    l.no_of_installments AS installments,
    l.monthly_amount     AS monthlyAmount,
    l.start_date         AS startDate,
    l.status             AS status,
    l.purpose            AS purpose,
    l.notes              AS notes,
    l.created_at         AS createdAt,
    l.updated_at         AS updatedAt
FROM fact_loans l
WHERE l.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_loan;
DROP PROCEDURE IF EXISTS sp_update_loan;
DROP PROCEDURE IF EXISTS sp_soft_delete_loan;

DELIMITER $$

CREATE PROCEDURE sp_create_loan(
    IN  p_tenant_id     INT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_loan_amount   DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_installments  INT,
    IN  p_monthly       DECIMAL(15,2),
    IN  p_start_date    DATE,
    IN  p_status        VARCHAR(50),
    IN  p_purpose       TEXT,
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_loan_id       BIGINT
)
proc: BEGIN
    DECLARE v_name VARCHAR(255);
    DECLARE v_installments INT;
    DECLARE v_monthly DECIMAL(15,2);
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_loan_amount IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Loan amount is required';
    END IF;

    SET v_name = NULLIF(TRIM(COALESCE(p_employee_name, '')), '');
    IF v_name IS NULL AND p_employee_id IS NOT NULL THEN
        SELECT NULLIF(TRIM(CONCAT(COALESCE(first_name, ''), ' ', COALESCE(last_name, ''))), '')
          INTO v_name FROM dim_employees
         WHERE id = p_employee_id AND tenant_id = p_tenant_id LIMIT 1;
    END IF;
    SET v_name = COALESCE(v_name, 'Unknown');

    SET v_installments = COALESCE(NULLIF(p_installments, 0), 1);
    SET v_monthly = COALESCE(p_monthly, ROUND(p_loan_amount / v_installments, 2));

    INSERT INTO fact_loans (
        tenant_id, employee_id, employee_name, loan_amount, currency, no_of_installments,
        monthly_amount, start_date, status, purpose, notes,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_employee_id, v_name, p_loan_amount, COALESCE(p_currency, 'USD'), v_installments,
        v_monthly, p_start_date, COALESCE(p_status, 'Pending'), p_purpose, p_notes,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_loan_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_loan(
    IN  p_tenant_id     INT,
    IN  p_loan_id       BIGINT,
    IN  p_employee_id   INT,
    IN  p_employee_name VARCHAR(255),
    IN  p_loan_amount   DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_installments  INT,
    IN  p_monthly       DECIMAL(15,2),
    IN  p_start_date    DATE,
    IN  p_status        VARCHAR(50),
    IN  p_purpose       TEXT,
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_loans
        WHERE id = p_loan_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Loan not found';
    END IF;

    UPDATE fact_loans SET
        employee_id        = COALESCE(p_employee_id, employee_id),
        employee_name      = COALESCE(NULLIF(TRIM(COALESCE(p_employee_name, '')), ''), employee_name),
        loan_amount        = COALESCE(p_loan_amount, loan_amount),
        currency           = COALESCE(p_currency, currency),
        no_of_installments = COALESCE(NULLIF(p_installments, 0), no_of_installments),
        monthly_amount     = COALESCE(p_monthly, monthly_amount),
        start_date         = COALESCE(p_start_date, start_date),
        status             = COALESCE(p_status, status),
        purpose            = COALESCE(p_purpose, purpose),
        notes              = COALESCE(p_notes, notes),
        updated_at         = NOW(),
        updated_by         = p_updated_by
    WHERE id = p_loan_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

CREATE PROCEDURE sp_soft_delete_loan(
    IN  p_tenant_id  INT,
    IN  p_loan_id    BIGINT,
    IN  p_deleted_by INT
)
proc: BEGIN
    UPDATE fact_loans SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_loan_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: orders_sp_views.sql
-- -----------------------------------------------------------------------------
-- CRM Orders: SP-write / View-read stack for fact_orders.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/orders_sp_views.sql

USE `stacie_Aggie_v1.0`;

-- ---------------------------------------------------------------------------
-- Read model: view exposes camelCase aliases matching the frontend orderFromApi
-- ---------------------------------------------------------------------------
CREATE OR REPLACE VIEW vw_orders AS
SELECT
    o.id                        AS id,
    o.id                        AS orderId,
    o.tenant_id                 AS tenant_id,
    o.order_no                  AS orderNo,
    o.client_name               AS clientName,
    o.status                    AS status,
    o.total_amount              AS totalAmount,
    o.total_amount              AS total,
    o.currency                  AS currency,
    o.no_of_items               AS items,
    o.order_date                AS orderDate,
    o.delivery_date             AS deliveryDate,
    o.notes                     AS notes,
    o.created_at                AS createdAt,
    o.updated_at                AS updatedAt
FROM fact_orders o
WHERE o.is_deleted = 0;

-- ---------------------------------------------------------------------------
-- Write model: create / update / soft-delete stored procedures
-- ---------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS sp_create_order;
DROP PROCEDURE IF EXISTS sp_update_order;
DROP PROCEDURE IF EXISTS sp_soft_delete_order;

DELIMITER $$

CREATE PROCEDURE sp_create_order(
    IN  p_tenant_id     INT,
    IN  p_order_no      VARCHAR(100),
    IN  p_client_name   VARCHAR(255),
    IN  p_status        VARCHAR(50),
    IN  p_total_amount  DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_no_of_items   INT,
    IN  p_order_date    DATE,
    IN  p_delivery_date DATE,
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_order_id      BIGINT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_order_no IS NULL OR p_order_no = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order number is required';
    END IF;
    IF p_client_name IS NULL OR p_client_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Client name is required';
    END IF;

    INSERT INTO fact_orders (
        tenant_id, order_no, client_name, status, total_amount, currency,
        no_of_items, order_date, delivery_date, notes,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_order_no, p_client_name,
        COALESCE(p_status, 'Pending'),
        COALESCE(p_total_amount, 0),
        COALESCE(p_currency, 'USD'),
        COALESCE(p_no_of_items, 0),
        p_order_date, p_delivery_date, p_notes,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );

    SET p_order_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_order(
    IN  p_tenant_id     INT,
    IN  p_order_id      BIGINT,
    IN  p_order_no      VARCHAR(100),
    IN  p_client_name   VARCHAR(255),
    IN  p_status        VARCHAR(50),
    IN  p_total_amount  DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_no_of_items   INT,
    IN  p_order_date    DATE,
    IN  p_delivery_date DATE,
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_orders
        WHERE id = p_order_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Order not found';
    END IF;

    UPDATE fact_orders SET
        order_no      = COALESCE(p_order_no, order_no),
        client_name   = COALESCE(p_client_name, client_name),
        status        = COALESCE(p_status, status),
        total_amount  = COALESCE(p_total_amount, total_amount),
        currency      = COALESCE(p_currency, currency),
        no_of_items   = COALESCE(p_no_of_items, no_of_items),
        order_date    = COALESCE(p_order_date, order_date),
        delivery_date = COALESCE(p_delivery_date, delivery_date),
        notes         = COALESCE(p_notes, notes),
        updated_at    = NOW(),
        updated_by    = p_updated_by
    WHERE id = p_order_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

CREATE PROCEDURE sp_soft_delete_order(
    IN  p_tenant_id  INT,
    IN  p_order_id   BIGINT,
    IN  p_deleted_by INT
)
proc: BEGIN
    UPDATE fact_orders SET
        is_deleted = 1,
        updated_at = NOW(),
        updated_by = p_deleted_by
    WHERE id = p_order_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: products_sp_views.sql
-- -----------------------------------------------------------------------------
-- Inventory Products: purpose-fit table + SP-write / View-read stack.
-- The legacy dim_products (category_id FK, base_cost/base_retail) does not match the
-- frontend Product shape and is unused, so we introduce inv_products keyed to the UI contract.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/products_sp_views.sql

USE `stacie_Aggie_v1.0`;

CREATE TABLE IF NOT EXISTS inv_products (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT NOT NULL,
    name          VARCHAR(255) NOT NULL,
    sku           VARCHAR(100) NULL,
    category      VARCHAR(150) NULL,
    unit_price    DECIMAL(15,2) NOT NULL DEFAULT 0,
    cost_price    DECIMAL(15,2) NULL,
    currency      VARCHAR(10) NULL DEFAULT 'USD',
    stock_qty     INT NULL DEFAULT 0,
    reorder_level INT NULL DEFAULT 0,
    unit          VARCHAR(50) NULL,
    status        ENUM('Active','Inactive','Discontinued') NULL DEFAULT 'Active',
    created_at    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by    INT NULL,
    updated_at    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by    INT NULL,
    is_deleted    TINYINT(1) NULL DEFAULT 0,
    KEY idx_inv_products_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_products AS
SELECT
    p.id            AS id,
    p.id            AS productId,
    p.tenant_id     AS tenant_id,
    p.name          AS name,
    p.sku           AS sku,
    p.category      AS category,
    p.unit_price    AS unitPrice,
    p.cost_price    AS costPrice,
    p.currency      AS currency,
    p.stock_qty     AS stockQty,
    p.reorder_level AS reorderLevel,
    p.unit          AS unit,
    p.status        AS status,
    p.created_at    AS createdAt,
    p.updated_at    AS updatedAt
FROM inv_products p
WHERE p.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_product;
DROP PROCEDURE IF EXISTS sp_update_product;
DROP PROCEDURE IF EXISTS sp_soft_delete_product;

DELIMITER $$

CREATE PROCEDURE sp_create_product(
    IN  p_tenant_id     INT,
    IN  p_name          VARCHAR(255),
    IN  p_sku           VARCHAR(100),
    IN  p_category      VARCHAR(150),
    IN  p_unit_price    DECIMAL(15,2),
    IN  p_cost_price    DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_stock_qty     INT,
    IN  p_reorder_level INT,
    IN  p_unit          VARCHAR(50),
    IN  p_status        VARCHAR(50),
    IN  p_created_by    INT,
    OUT p_product_id    INT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_name IS NULL OR p_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product name is required';
    END IF;

    INSERT INTO inv_products (
        tenant_id, name, sku, category, unit_price, cost_price, currency,
        stock_qty, reorder_level, unit, status,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_name, p_sku, p_category, COALESCE(p_unit_price, 0), p_cost_price,
        COALESCE(p_currency, 'USD'), COALESCE(p_stock_qty, 0), COALESCE(p_reorder_level, 0),
        p_unit, COALESCE(p_status, 'Active'),
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_product_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_product(
    IN  p_tenant_id     INT,
    IN  p_product_id    INT,
    IN  p_name          VARCHAR(255),
    IN  p_sku           VARCHAR(100),
    IN  p_category      VARCHAR(150),
    IN  p_unit_price    DECIMAL(15,2),
    IN  p_cost_price    DECIMAL(15,2),
    IN  p_currency      VARCHAR(10),
    IN  p_stock_qty     INT,
    IN  p_reorder_level INT,
    IN  p_unit          VARCHAR(50),
    IN  p_status        VARCHAR(50),
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM inv_products
        WHERE id = p_product_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product not found';
    END IF;

    UPDATE inv_products SET
        name          = COALESCE(p_name, name),
        sku           = COALESCE(p_sku, sku),
        category      = COALESCE(p_category, category),
        unit_price    = COALESCE(p_unit_price, unit_price),
        cost_price    = COALESCE(p_cost_price, cost_price),
        currency      = COALESCE(p_currency, currency),
        stock_qty     = COALESCE(p_stock_qty, stock_qty),
        reorder_level = COALESCE(p_reorder_level, reorder_level),
        unit          = COALESCE(p_unit, unit),
        status        = COALESCE(p_status, status),
        updated_at    = NOW(),
        updated_by    = p_updated_by
    WHERE id = p_product_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

CREATE PROCEDURE sp_soft_delete_product(
    IN  p_tenant_id  INT,
    IN  p_product_id INT,
    IN  p_deleted_by INT
)
proc: BEGIN
    UPDATE inv_products SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_product_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: purchases_sp_views.sql
-- -----------------------------------------------------------------------------
-- Procurement Purchases: SP-write / View-read stack for fact_purchases.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/purchases_sp_views.sql

USE `stacie_Aggie_v1.0`;

-- Add notes column (frontend sends purchase notes) if not present.
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns
                   WHERE table_schema = '`stacie_Aggie_v1.0`' AND table_name = 'fact_purchases' AND column_name = 'notes');
SET @ddl = IF(@col_exists = 0, 'ALTER TABLE fact_purchases ADD COLUMN notes TEXT NULL AFTER purchase_date', 'SELECT 1');
PREPARE stmt FROM @ddl; EXECUTE stmt; DEALLOCATE PREPARE stmt;

CREATE OR REPLACE VIEW vw_purchases AS
SELECT
    p.id                     AS id,
    p.id                     AS purchaseId,
    p.tenant_id              AS tenant_id,
    p.supplier_id            AS supplierId,
    s.vendor_name            AS supplierName,
    s.vendor_name            AS vendorName,
    p.wallet_id              AS walletId,
    w.wallet_name            AS walletName,
    p.amount                 AS amount,
    p.is_capital_expenditure AS isCapitalExpenditure,
    p.purchase_date          AS purchaseDate,
    p.notes                  AS notes,
    p.created_at             AS createdAt,
    p.updated_at             AS updatedAt
FROM fact_purchases p
LEFT JOIN dim_suppliers s ON s.id = p.supplier_id
LEFT JOIN dim_wallets   w ON w.id = p.wallet_id
WHERE p.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_purchase;
DROP PROCEDURE IF EXISTS sp_update_purchase;
DROP PROCEDURE IF EXISTS sp_soft_delete_purchase;

DELIMITER $$

CREATE PROCEDURE sp_create_purchase(
    IN  p_tenant_id     INT,
    IN  p_supplier_id   INT,
    IN  p_wallet_id     INT,
    IN  p_is_capex      TINYINT,
    IN  p_amount        DECIMAL(15,2),
    IN  p_purchase_date DATE,
    IN  p_notes         TEXT,
    IN  p_created_by    INT,
    OUT p_purchase_id   BIGINT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_supplier_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Supplier is required';
    END IF;
    IF p_wallet_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Wallet is required';
    END IF;

    INSERT INTO fact_purchases (
        tenant_id, supplier_id, wallet_id, is_capital_expenditure, amount,
        purchase_date, notes, created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_supplier_id, p_wallet_id, COALESCE(p_is_capex, 0), COALESCE(p_amount, 0),
        p_purchase_date, p_notes, NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_purchase_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_purchase(
    IN  p_tenant_id     INT,
    IN  p_purchase_id   BIGINT,
    IN  p_supplier_id   INT,
    IN  p_wallet_id     INT,
    IN  p_is_capex      TINYINT,
    IN  p_amount        DECIMAL(15,2),
    IN  p_purchase_date DATE,
    IN  p_notes         TEXT,
    IN  p_updated_by    INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM fact_purchases
        WHERE id = p_purchase_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Purchase not found';
    END IF;

    UPDATE fact_purchases SET
        supplier_id            = COALESCE(p_supplier_id, supplier_id),
        wallet_id              = COALESCE(p_wallet_id, wallet_id),
        is_capital_expenditure = COALESCE(p_is_capex, is_capital_expenditure),
        amount                 = COALESCE(p_amount, amount),
        purchase_date          = COALESCE(p_purchase_date, purchase_date),
        notes                  = COALESCE(p_notes, notes),
        updated_at             = NOW(),
        updated_by             = p_updated_by
    WHERE id = p_purchase_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

CREATE PROCEDURE sp_soft_delete_purchase(
    IN  p_tenant_id   INT,
    IN  p_purchase_id BIGINT,
    IN  p_deleted_by  INT
)
proc: BEGIN
    UPDATE fact_purchases SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_purchase_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: suppliers_sp_views.sql
-- -----------------------------------------------------------------------------
-- Inventory/Procurement Suppliers: SP-write / View-read stack for dim_suppliers.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/suppliers_sp_views.sql

USE `stacie_Aggie_v1.0`;

CREATE OR REPLACE VIEW vw_suppliers AS
SELECT
    s.id          AS id,
    s.id          AS supplierId,
    s.tenant_id   AS tenant_id,
    s.vendor_name AS vendorName,
    s.vendor_name AS name,
    s.category    AS category,
    s.tax_id      AS taxId,
    s.created_at  AS createdAt,
    s.updated_at  AS updatedAt
FROM dim_suppliers s
WHERE s.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_supplier;
DROP PROCEDURE IF EXISTS sp_update_supplier;
DROP PROCEDURE IF EXISTS sp_soft_delete_supplier;

DELIMITER $$

CREATE PROCEDURE sp_create_supplier(
    IN  p_tenant_id   INT,
    IN  p_vendor_name VARCHAR(255),
    IN  p_category    VARCHAR(100),
    IN  p_tax_id      VARCHAR(50),
    IN  p_created_by  INT,
    OUT p_supplier_id INT
)
proc: BEGIN
    IF p_tenant_id IS NULL THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Tenant id is required';
    END IF;
    IF p_vendor_name IS NULL OR p_vendor_name = '' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Vendor name is required';
    END IF;

    INSERT INTO dim_suppliers (
        tenant_id, vendor_name, category, tax_id,
        created_at, created_by, updated_at, updated_by, is_deleted
    ) VALUES (
        p_tenant_id, p_vendor_name, p_category, p_tax_id,
        NOW(), p_created_by, NOW(), p_created_by, 0
    );
    SET p_supplier_id = LAST_INSERT_ID();
END$$

CREATE PROCEDURE sp_update_supplier(
    IN  p_tenant_id   INT,
    IN  p_supplier_id INT,
    IN  p_vendor_name VARCHAR(255),
    IN  p_category    VARCHAR(100),
    IN  p_tax_id      VARCHAR(50),
    IN  p_updated_by  INT
)
proc: BEGIN
    DECLARE v_exists INT;
    SELECT COUNT(*) INTO v_exists FROM dim_suppliers
        WHERE id = p_supplier_id AND tenant_id = p_tenant_id AND is_deleted = 0;
    IF v_exists = 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Supplier not found';
    END IF;

    UPDATE dim_suppliers SET
        vendor_name = COALESCE(p_vendor_name, vendor_name),
        category    = COALESCE(p_category, category),
        tax_id      = COALESCE(p_tax_id, tax_id),
        updated_at  = NOW(),
        updated_by  = p_updated_by
    WHERE id = p_supplier_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

CREATE PROCEDURE sp_soft_delete_supplier(
    IN  p_tenant_id   INT,
    IN  p_supplier_id INT,
    IN  p_deleted_by  INT
)
proc: BEGIN
    UPDATE dim_suppliers SET
        is_deleted = 1, updated_at = NOW(), updated_by = p_deleted_by
    WHERE id = p_supplier_id AND tenant_id = p_tenant_id AND is_deleted = 0;
END$$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: orphan_domains_sp_views.sql
-- -----------------------------------------------------------------------------
-- Orphan-stub domains (no prior schema/UI): Materials, Liabilities, Sprints, Resources.
-- New purpose-designed tables + SP-write / View-read stacks. Schemas are our design
-- (no existing contract) and are expected to be reviewed/adjusted by the product owner.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/orphan_domains_sp_views.sql

USE `stacie_Aggie_v1.0`;

-- =========================================================================
-- 1) Inventory Materials  (/api/inventory/materials)
-- =========================================================================
CREATE TABLE IF NOT EXISTS inv_materials (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT NOT NULL,
    name          VARCHAR(255) NOT NULL,
    code          VARCHAR(100) NULL,
    category      VARCHAR(150) NULL,
    unit          VARCHAR(50) NULL,
    stock_qty     DECIMAL(15,2) NULL DEFAULT 0,
    unit_cost     DECIMAL(15,2) NULL,
    currency      VARCHAR(10) NULL DEFAULT 'USD',
    reorder_level DECIMAL(15,2) NULL DEFAULT 0,
    status        ENUM('Active','Inactive','Discontinued') NULL DEFAULT 'Active',
    notes         TEXT NULL,
    created_at    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by    INT NULL,
    updated_at    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by    INT NULL,
    is_deleted    TINYINT(1) NULL DEFAULT 0,
    KEY idx_inv_materials_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_materials AS
SELECT id AS id, id AS materialId, tenant_id, name, code, category, unit,
       stock_qty AS stockQty, unit_cost AS unitCost, currency, reorder_level AS reorderLevel,
       status, notes, created_at AS createdAt, updated_at AS updatedAt
FROM inv_materials WHERE is_deleted = 0;

-- =========================================================================
-- 2) Finance Liabilities  (/api/inventory/liabilities  — stub path retained)
-- =========================================================================
CREATE TABLE IF NOT EXISTS fin_liabilities (
    id           INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id    INT NOT NULL,
    name         VARCHAR(255) NOT NULL,
    type         VARCHAR(50) NULL,
    counterparty VARCHAR(255) NULL,
    principal    DECIMAL(15,2) NOT NULL DEFAULT 0,
    outstanding  DECIMAL(15,2) NULL,
    currency     VARCHAR(10) NULL DEFAULT 'USD',
    interest_rate DECIMAL(6,3) NULL,
    start_date   DATE NULL,
    due_date     DATE NULL,
    status       ENUM('Active','Settled','Defaulted') NULL DEFAULT 'Active',
    notes        TEXT NULL,
    created_at   TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by   INT NULL,
    updated_at   TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by   INT NULL,
    is_deleted   TINYINT(1) NULL DEFAULT 0,
    KEY idx_fin_liabilities_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_liabilities AS
SELECT id AS id, id AS liabilityId, tenant_id, name, type, counterparty,
       principal, COALESCE(outstanding, principal) AS outstanding, currency,
       interest_rate AS interestRate, start_date AS startDate, due_date AS dueDate,
       status, notes, created_at AS createdAt, updated_at AS updatedAt
FROM fin_liabilities WHERE is_deleted = 0;

-- =========================================================================
-- 3) Project Sprints  (/api/projects/sprints)
-- =========================================================================
CREATE TABLE IF NOT EXISTS proj_sprints (
    id         INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id  INT NOT NULL,
    name       VARCHAR(255) NOT NULL,
    project_id INT NULL,
    goal       TEXT NULL,
    start_date DATE NULL,
    end_date   DATE NULL,
    status     ENUM('Planning','Active','Completed','Cancelled') NULL DEFAULT 'Planning',
    capacity   INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by INT NULL,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by INT NULL,
    is_deleted TINYINT(1) NULL DEFAULT 0,
    KEY idx_proj_sprints_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_sprints AS
SELECT s.id AS id, s.id AS sprintId, s.tenant_id, s.name, s.project_id AS projectId,
       p.name AS projectName, s.goal, s.start_date AS startDate, s.end_date AS endDate,
       s.status, s.capacity, s.created_at AS createdAt, s.updated_at AS updatedAt
FROM proj_sprints s
LEFT JOIN dim_projects p ON p.id = s.project_id
WHERE s.is_deleted = 0;

-- =========================================================================
-- 4) Project Resources  (/api/projects/resources)
-- =========================================================================
CREATE TABLE IF NOT EXISTS proj_resources (
    id            INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id     INT NOT NULL,
    name          VARCHAR(255) NOT NULL,
    type          VARCHAR(50) NULL,
    project_id    INT NULL,
    role          VARCHAR(150) NULL,
    allocation_pct INT NULL,
    cost_rate     DECIMAL(15,2) NULL,
    currency      VARCHAR(10) NULL DEFAULT 'USD',
    availability  ENUM('Available','Allocated','Unavailable') NULL DEFAULT 'Available',
    notes         TEXT NULL,
    created_at    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    created_by    INT NULL,
    updated_at    TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_by    INT NULL,
    is_deleted    TINYINT(1) NULL DEFAULT 0,
    KEY idx_proj_resources_tenant (tenant_id, is_deleted)
);

CREATE OR REPLACE VIEW vw_resources AS
SELECT r.id AS id, r.id AS resourceId, r.tenant_id, r.name, r.type, r.project_id AS projectId,
       p.name AS projectName, r.role, r.allocation_pct AS allocationPct,
       r.cost_rate AS costRate, r.currency, r.availability,
       r.notes, r.created_at AS createdAt, r.updated_at AS updatedAt
FROM proj_resources r
LEFT JOIN dim_projects p ON p.id = r.project_id
WHERE r.is_deleted = 0;

DROP PROCEDURE IF EXISTS sp_create_material;
DROP PROCEDURE IF EXISTS sp_update_material;
DROP PROCEDURE IF EXISTS sp_soft_delete_material;
DROP PROCEDURE IF EXISTS sp_create_liability;
DROP PROCEDURE IF EXISTS sp_update_liability;
DROP PROCEDURE IF EXISTS sp_soft_delete_liability;
DROP PROCEDURE IF EXISTS sp_create_sprint;
DROP PROCEDURE IF EXISTS sp_update_sprint;
DROP PROCEDURE IF EXISTS sp_soft_delete_sprint;
DROP PROCEDURE IF EXISTS sp_create_resource;
DROP PROCEDURE IF EXISTS sp_update_resource;
DROP PROCEDURE IF EXISTS sp_soft_delete_resource;

DELIMITER $$

-- ---- Materials ----
CREATE PROCEDURE sp_create_material(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_code VARCHAR(100), IN p_category VARCHAR(150),
    IN p_unit VARCHAR(50), IN p_stock_qty DECIMAL(15,2), IN p_unit_cost DECIMAL(15,2), IN p_currency VARCHAR(10),
    IN p_reorder_level DECIMAL(15,2), IN p_status VARCHAR(50), IN p_notes TEXT, IN p_created_by INT, OUT p_material_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Material name is required'; END IF;
    INSERT INTO inv_materials (tenant_id,name,code,category,unit,stock_qty,unit_cost,currency,reorder_level,status,notes,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_code,p_category,p_unit,COALESCE(p_stock_qty,0),p_unit_cost,COALESCE(p_currency,'USD'),COALESCE(p_reorder_level,0),COALESCE(p_status,'Active'),p_notes,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_material_id = LAST_INSERT_ID();
END$$
CREATE PROCEDURE sp_update_material(
    IN p_tenant_id INT, IN p_material_id INT, IN p_name VARCHAR(255), IN p_code VARCHAR(100), IN p_category VARCHAR(150),
    IN p_unit VARCHAR(50), IN p_stock_qty DECIMAL(15,2), IN p_unit_cost DECIMAL(15,2), IN p_currency VARCHAR(10),
    IN p_reorder_level DECIMAL(15,2), IN p_status VARCHAR(50), IN p_notes TEXT, IN p_updated_by INT)
proc: BEGIN
    UPDATE inv_materials SET name=COALESCE(p_name,name),code=COALESCE(p_code,code),category=COALESCE(p_category,category),
        unit=COALESCE(p_unit,unit),stock_qty=COALESCE(p_stock_qty,stock_qty),unit_cost=COALESCE(p_unit_cost,unit_cost),
        currency=COALESCE(p_currency,currency),reorder_level=COALESCE(p_reorder_level,reorder_level),status=COALESCE(p_status,status),
        notes=COALESCE(p_notes,notes),updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_material_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$
CREATE PROCEDURE sp_soft_delete_material(IN p_tenant_id INT, IN p_material_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE inv_materials SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_material_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$

-- ---- Liabilities ----
CREATE PROCEDURE sp_create_liability(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_counterparty VARCHAR(255),
    IN p_principal DECIMAL(15,2), IN p_outstanding DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_interest_rate DECIMAL(6,3),
    IN p_start_date DATE, IN p_due_date DATE, IN p_status VARCHAR(50), IN p_notes TEXT, IN p_created_by INT, OUT p_liability_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Liability name is required'; END IF;
    INSERT INTO fin_liabilities (tenant_id,name,type,counterparty,principal,outstanding,currency,interest_rate,start_date,due_date,status,notes,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_type,p_counterparty,COALESCE(p_principal,0),COALESCE(p_outstanding,p_principal),COALESCE(p_currency,'USD'),p_interest_rate,p_start_date,p_due_date,COALESCE(p_status,'Active'),p_notes,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_liability_id = LAST_INSERT_ID();
END$$
CREATE PROCEDURE sp_update_liability(
    IN p_tenant_id INT, IN p_liability_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_counterparty VARCHAR(255),
    IN p_principal DECIMAL(15,2), IN p_outstanding DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_interest_rate DECIMAL(6,3),
    IN p_start_date DATE, IN p_due_date DATE, IN p_status VARCHAR(50), IN p_notes TEXT, IN p_updated_by INT)
proc: BEGIN
    UPDATE fin_liabilities SET name=COALESCE(p_name,name),type=COALESCE(p_type,type),counterparty=COALESCE(p_counterparty,counterparty),
        principal=COALESCE(p_principal,principal),outstanding=COALESCE(p_outstanding,outstanding),currency=COALESCE(p_currency,currency),
        interest_rate=COALESCE(p_interest_rate,interest_rate),start_date=COALESCE(p_start_date,start_date),due_date=COALESCE(p_due_date,due_date),
        status=COALESCE(p_status,status),notes=COALESCE(p_notes,notes),updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_liability_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$
CREATE PROCEDURE sp_soft_delete_liability(IN p_tenant_id INT, IN p_liability_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE fin_liabilities SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_liability_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$

-- ---- Sprints ----
CREATE PROCEDURE sp_create_sprint(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_project_id INT, IN p_goal TEXT,
    IN p_start_date DATE, IN p_end_date DATE, IN p_status VARCHAR(50), IN p_capacity INT, IN p_created_by INT, OUT p_sprint_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Sprint name is required'; END IF;
    INSERT INTO proj_sprints (tenant_id,name,project_id,goal,start_date,end_date,status,capacity,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_project_id,p_goal,p_start_date,p_end_date,COALESCE(p_status,'Planning'),p_capacity,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_sprint_id = LAST_INSERT_ID();
END$$
CREATE PROCEDURE sp_update_sprint(
    IN p_tenant_id INT, IN p_sprint_id INT, IN p_name VARCHAR(255), IN p_project_id INT, IN p_goal TEXT,
    IN p_start_date DATE, IN p_end_date DATE, IN p_status VARCHAR(50), IN p_capacity INT, IN p_updated_by INT)
proc: BEGIN
    UPDATE proj_sprints SET name=COALESCE(p_name,name),project_id=COALESCE(p_project_id,project_id),goal=COALESCE(p_goal,goal),
        start_date=COALESCE(p_start_date,start_date),end_date=COALESCE(p_end_date,end_date),status=COALESCE(p_status,status),
        capacity=COALESCE(p_capacity,capacity),updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_sprint_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$
CREATE PROCEDURE sp_soft_delete_sprint(IN p_tenant_id INT, IN p_sprint_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE proj_sprints SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_sprint_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$

-- ---- Resources ----
CREATE PROCEDURE sp_create_resource(
    IN p_tenant_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_project_id INT, IN p_role VARCHAR(150),
    IN p_allocation_pct INT, IN p_cost_rate DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_availability VARCHAR(50),
    IN p_notes TEXT, IN p_created_by INT, OUT p_resource_id INT)
proc: BEGIN
    IF p_name IS NULL OR p_name = '' THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Resource name is required'; END IF;
    INSERT INTO proj_resources (tenant_id,name,type,project_id,role,allocation_pct,cost_rate,currency,availability,notes,created_at,created_by,updated_at,updated_by,is_deleted)
    VALUES (p_tenant_id,p_name,p_type,p_project_id,p_role,p_allocation_pct,p_cost_rate,COALESCE(p_currency,'USD'),COALESCE(p_availability,'Available'),p_notes,NOW(),p_created_by,NOW(),p_created_by,0);
    SET p_resource_id = LAST_INSERT_ID();
END$$
CREATE PROCEDURE sp_update_resource(
    IN p_tenant_id INT, IN p_resource_id INT, IN p_name VARCHAR(255), IN p_type VARCHAR(50), IN p_project_id INT, IN p_role VARCHAR(150),
    IN p_allocation_pct INT, IN p_cost_rate DECIMAL(15,2), IN p_currency VARCHAR(10), IN p_availability VARCHAR(50),
    IN p_notes TEXT, IN p_updated_by INT)
proc: BEGIN
    UPDATE proj_resources SET name=COALESCE(p_name,name),type=COALESCE(p_type,type),project_id=COALESCE(p_project_id,project_id),
        role=COALESCE(p_role,role),allocation_pct=COALESCE(p_allocation_pct,allocation_pct),cost_rate=COALESCE(p_cost_rate,cost_rate),
        currency=COALESCE(p_currency,currency),availability=COALESCE(p_availability,availability),notes=COALESCE(p_notes,notes),
        updated_at=NOW(),updated_by=p_updated_by
    WHERE id=p_resource_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$
CREATE PROCEDURE sp_soft_delete_resource(IN p_tenant_id INT, IN p_resource_id INT, IN p_deleted_by INT)
proc: BEGIN
    UPDATE proj_resources SET is_deleted=1,updated_at=NOW(),updated_by=p_deleted_by WHERE id=p_resource_id AND tenant_id=p_tenant_id AND is_deleted=0;
END$$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: fix_opportunity_stage.sql
-- -----------------------------------------------------------------------------
USE `stacie_Aggie_v1.0`;

-- Fixes sp_create_opportunity to persist fact_opportunities.stage (2026-07-28).
-- fact_opportunities already has a `stage` enum('Discovery','Proposal','Negotiation',
-- 'Closed Won','Closed Lost') column distinct from `status` enum('New','Contacted','Qualified',
-- 'Lost','Won') — but the stored procedure never accepted or persisted it, so the Deals UI's
-- pipeline stage was silently dropped on create (and the frontend was additionally sending the
-- stage value into `status`, which fails validation since the two enums don't overlap).
-- Safe to re-run: DROP PROCEDURE IF EXISTS + CREATE.

DROP PROCEDURE IF EXISTS sp_create_opportunity;

DELIMITER $$

CREATE PROCEDURE sp_create_opportunity(
    IN p_tenant_id INT,
    IN p_company_id INT,
    IN p_contact_id INT,
    IN p_title VARCHAR(255),
    IN p_status VARCHAR(50),
    IN p_stage VARCHAR(50),
    IN p_source VARCHAR(100),
    IN p_probability INT,
    IN p_deal_value DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_assigned_to INT,
    IN p_expected_close_date DATE,
    IN p_notes TEXT,
    IN p_created_by INT,
    OUT p_opportunity_id BIGINT
)
BEGIN
    DECLARE v_full_name VARCHAR(255);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SET v_full_name = p_title;
    IF p_contact_id IS NOT NULL THEN
        SELECT CONCAT(first_name, ' ', last_name) INTO v_full_name
        FROM dim_contacts
        WHERE id = p_contact_id AND tenant_id = p_tenant_id;
        IF v_full_name IS NULL THEN
            SET v_full_name = p_title;
        END IF;
    END IF;

    INSERT INTO fact_opportunities (
        tenant_id, company_id, contact_id, full_name, title, deal_title, status, stage, source,
        probability, deal_value, currency, assigned_to, expected_close_date, notes, created_by
    ) VALUES (
        p_tenant_id, p_company_id, p_contact_id, v_full_name, p_title, p_title, p_status, p_stage, p_source,
        p_probability, p_deal_value, p_currency, p_assigned_to, p_expected_close_date, p_notes, p_created_by
    );

    SET p_opportunity_id = LAST_INSERT_ID();
    COMMIT;
END$$

DELIMITER ;

-- -----------------------------------------------------------------------------
-- SECTION: fix_vw_user_module_access_detail.sql
-- -----------------------------------------------------------------------------
USE `stacie_Aggie_v1.0`;

-- Re-fixes vw_user_module_access_detail (2026-07-28). This view previously broke with
-- MySQL error 1356 ("references invalid table(s) or column(s)...") because its stored
-- definition referenced `uma.module_name`, but user_module_access's actual column is `module`
-- (see [[db_name_dot_quoting_bug]]/[[broken_views_definer_issue]] memory — this is the same
-- view that broke mysqldump entirely once before; it had drifted back out of sync with the
-- real column name since). Recreated with SQL SECURITY INVOKER (no DEFINER dependency) and
-- the correct source column, aliased back to `module_name` so existing consumers are unaffected.
-- Safe to re-run: CREATE OR REPLACE VIEW.

CREATE OR REPLACE SQL SECURITY INVOKER VIEW vw_user_module_access_detail AS
SELECT uma.id, uma.tenant_id, uma.user_id, u.email, uma.module AS module_name, uma.access_level
FROM user_module_access uma
JOIN users u ON uma.user_id = u.id
WHERE u.is_deleted = 0;

-- -----------------------------------------------------------------------------
-- SECTION: projects_foundation_refactor.sql
-- -----------------------------------------------------------------------------
USE `stacie_Aggie_v1.0`;

-- =============================================================================
-- Projects module — Foundation refactor (Phase 1).
--
-- Safe to re-run: apply-migrations.sh has no history table and re-applies every
-- script on every deploy, so each statement is information_schema-guarded.
-- Portable MySQL 8 / MariaDB: no JSON_VALUE(...RETURNING), no functional indexes.
--
-- Widens tinytext -> varchar (tinytext cannot be indexed without a prefix length
-- and cannot carry a DEFAULT, yet type/status/priority are NOT NULL and are
-- filtered in every list query), adds real user FK columns alongside the legacy
-- free-text ones, backfills business_id (decision: business_id = 1 for every
-- existing row, business scoping now filters strictly), and adds the composite
-- indexes every query already needs.
--
-- See PROJECTS-REFACTOR-PLAN.md at the repo root for full rationale.
-- =============================================================================

SET @db := DATABASE();

-- ── 1. dim_projects — widen the vocabulary columns ──────────────────────────
SET @sql := IFNULL((SELECT IF(DATA_TYPE='tinytext',
    'ALTER TABLE dim_projects MODIFY COLUMN type VARCHAR(50) NOT NULL', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_projects' AND COLUMN_NAME='type'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT IF(DATA_TYPE='tinytext',
    'ALTER TABLE dim_projects MODIFY COLUMN status VARCHAR(50) NOT NULL', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_projects' AND COLUMN_NAME='status'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT IF(DATA_TYPE='tinytext',
    'ALTER TABLE dim_projects MODIFY COLUMN priority VARCHAR(20) NOT NULL', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_projects' AND COLUMN_NAME='priority'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT IF(DATA_TYPE='tinytext',
    'ALTER TABLE dim_projects MODIFY COLUMN description TEXT NULL', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_projects' AND COLUMN_NAME='description'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 2. dim_projects — real owner FK column (legacy `owner` text retained) ────
-- `owner` stays as the denormalized display name and the fallback for legacy
-- rows whose text never matches a user, so no existing screen loses data.
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_projects' AND COLUMN_NAME='owner_user_id'),
  'ALTER TABLE dim_projects ADD COLUMN owner_user_id INT NULL AFTER owner');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 3. dim_projects — indexes ───────────────────────────────────────────────
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_projects' AND INDEX_NAME='idx_dim_projects_tenant_deleted'),
  'ALTER TABLE dim_projects ADD INDEX idx_dim_projects_tenant_deleted (tenant_id, is_deleted)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_projects' AND INDEX_NAME='idx_dim_projects_business'),
  'ALTER TABLE dim_projects ADD INDEX idx_dim_projects_business (tenant_id, business_id, is_deleted)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_projects' AND INDEX_NAME='idx_dim_projects_status'),
  'ALTER TABLE dim_projects ADD INDEX idx_dim_projects_status (tenant_id, is_deleted, status)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='dim_projects' AND INDEX_NAME='idx_dim_projects_owner_user'),
  'ALTER TABLE dim_projects ADD INDEX idx_dim_projects_owner_user (tenant_id, owner_user_id)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 4. fact_project_tasks — widen vocabulary columns ────────────────────────
SET @sql := IFNULL((SELECT IF(DATA_TYPE='tinytext',
    'ALTER TABLE fact_project_tasks MODIFY COLUMN type VARCHAR(50) NOT NULL', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND COLUMN_NAME='type'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT IF(DATA_TYPE='tinytext',
    'ALTER TABLE fact_project_tasks MODIFY COLUMN status VARCHAR(50) NOT NULL', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND COLUMN_NAME='status'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT IF(DATA_TYPE='tinytext',
    'ALTER TABLE fact_project_tasks MODIFY COLUMN priority VARCHAR(20) NOT NULL', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND COLUMN_NAME='priority'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT IF(DATA_TYPE='tinytext',
    'ALTER TABLE fact_project_tasks MODIFY COLUMN description TEXT NULL', 'SELECT 1')
  FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND COLUMN_NAME='description'), 'SELECT 1');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 5. fact_project_tasks — real assignee FK column ─────────────────────────
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND COLUMN_NAME='assigned_user_id'),
  'ALTER TABLE fact_project_tasks ADD COLUMN assigned_user_id INT NULL AFTER assigned_to');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 6. fact_project_tasks — indexes ─────────────────────────────────────────
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND INDEX_NAME='idx_fact_project_tasks_tenant_deleted'),
  'ALTER TABLE fact_project_tasks ADD INDEX idx_fact_project_tasks_tenant_deleted (tenant_id, is_deleted)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND INDEX_NAME='idx_fact_project_tasks_project'),
  'ALTER TABLE fact_project_tasks ADD INDEX idx_fact_project_tasks_project (tenant_id, is_deleted, project_id)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND INDEX_NAME='idx_fact_project_tasks_status'),
  'ALTER TABLE fact_project_tasks ADD INDEX idx_fact_project_tasks_status (tenant_id, is_deleted, status)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND INDEX_NAME='idx_fact_project_tasks_assignee'),
  'ALTER TABLE fact_project_tasks ADD INDEX idx_fact_project_tasks_assignee (tenant_id, is_deleted, assigned_user_id)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Due-date index: needed now for overdue/date sorting, and is the access path
-- Phase 2's Today/Upcoming views will run on.
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.STATISTICS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_project_tasks' AND INDEX_NAME='idx_fact_project_tasks_due'),
  'ALTER TABLE fact_project_tasks ADD INDEX idx_fact_project_tasks_due (tenant_id, is_deleted, due_date)');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Sections 7-9 below update on non-key columns (owner_user_id, business_id, status,
-- priority, type, progress) via literal UPDATEs and dynamic PREPARE/EXECUTE SQL alike.
-- MySQL Workbench/phpMyAdmin's client-side "safe update mode" (sql_safe_updates=1)
-- rejects any UPDATE whose WHERE doesn't reference a KEY column — which applies to the
-- session regardless of how the statement text arrives, so it also blocks the
-- PREPARE/EXECUTE ones. Toggle it off for this whole run of backfills/normalizations.
SET SQL_SAFE_UPDATES = 0;

-- ── 7. Best-effort backfill of the new user FK columns from legacy free text ─
-- Idempotent: the `IS NULL` guard means already-resolved rows are never retouched,
-- and rows whose text matches no user simply stay NULL and keep displaying the
-- legacy `owner`/`assigned_to` text.
UPDATE dim_projects p
  JOIN users u ON u.tenant_id = p.tenant_id AND u.is_deleted = 0
              AND TRIM(CONCAT(u.first_name, ' ', u.last_name)) = TRIM(p.owner)
   SET p.owner_user_id = u.id
 WHERE p.owner_user_id IS NULL AND p.owner IS NOT NULL AND TRIM(p.owner) <> '';

UPDATE fact_project_tasks t
  JOIN users u ON u.tenant_id = t.tenant_id AND u.is_deleted = 0
              AND TRIM(CONCAT(u.first_name, ' ', u.last_name)) = TRIM(t.assigned_to)
   SET t.assigned_user_id = u.id
 WHERE t.assigned_user_id IS NULL AND t.assigned_to IS NOT NULL AND TRIM(t.assigned_to) <> '';

-- ── 7b. Backfill business_id — REQUIRED before business filtering goes live ──
-- Every existing row predates business scoping and has business_id NULL. Once
-- findForList filters strictly on business_id, those rows become invisible.
-- Decision: assign business_id = 1 to all of them.
--
-- Idempotent (IS NULL guard) and non-destructive — this only fills blanks, it
-- never overwrites a business_id that is already set.
--
-- Guarded on business 1 actually existing, so a mistargeted environment fails
-- closed (prints a warning) rather than writing a dangling business_id.
SET @b1 := (SELECT COUNT(*) FROM businesses WHERE id = 1 AND is_deleted = 0);

SET @sql := IF(@b1 = 1,
  'UPDATE dim_projects SET business_id = 1 WHERE business_id IS NULL',
  'SELECT ''SKIPPED: business id 1 not found — project business_id backfill did not run'' AS warning');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- Tasks inherit from their parent project rather than being set independently,
-- so a task can never end up scoped to a different business than its project.
-- Must run after the project backfill above.
UPDATE fact_project_tasks t
  JOIN dim_projects p ON p.id = t.project_id
   SET t.business_id = p.business_id
 WHERE t.business_id IS NULL AND p.business_id IS NOT NULL;

-- Catches tasks whose parent project row is missing entirely (orphaned FK).
SET @sql := IF(@b1 = 1,
  'UPDATE fact_project_tasks SET business_id = 1 WHERE business_id IS NULL',
  'SELECT ''SKIPPED: business id 1 not found — task business_id backfill did not run'' AS warning');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- NOTE: tenant_id is intentionally NOT touched here. Unlike business_id (NULL on
-- every row today, so filling it is additive), tenant_id is NOT NULL and already
-- populated on every row — forcibly setting it to 1 would MOVE any non-tenant-1
-- project into tenant 1, merging tenants' data with no record of where a row
-- came from. See PROJECTS-REFACTOR-PLAN.md §3 step 7c for the pre-flight query
-- to confirm whether this even applies before considering it.

-- ── 8. Normalize out-of-vocabulary values written before validation existed ──
-- Free-text tinytext columns let arbitrary strings persist. Anything outside
-- the app's vocabulary is parked on the safe default so the new NOT NULL +
-- application-level whitelist cannot reject/misdisplay existing rows.
UPDATE dim_projects SET status = 'Planning'
 WHERE status NOT IN ('Planning','Active','On Hold','Completed','Cancelled');
UPDATE dim_projects SET priority = 'Medium'
 WHERE priority NOT IN ('Low','Medium','High','Critical');
UPDATE dim_projects SET type = 'Other'
 WHERE type NOT IN ('Tech','Ops','Admin','Training','Marketing','Other');

UPDATE fact_project_tasks SET status = 'Todo'
 WHERE status NOT IN ('Backlog','Todo','In Progress','Review','Done','Blocked');
UPDATE fact_project_tasks SET priority = 'Medium'
 WHERE priority NOT IN ('Low','Medium','High','Critical');
UPDATE fact_project_tasks SET type = 'Task'
 WHERE type NOT IN ('Feature','Bug','Task','Backlog','Epic');

-- ── 9. Clamp progress into range (no prior validation allowed anything) ─────
UPDATE dim_projects SET progress = 0   WHERE progress < 0;
UPDATE dim_projects SET progress = 100 WHERE progress > 100;

SET SQL_SAFE_UPDATES = 1;

-- =============================================================================
-- Web module — Foundation (Phase 1): sites, content collections, entries,
-- navigation, redirects, media.
--
-- Bundled into this same (not-yet-applied) migration file rather than a new
-- db/*.sql, so both land on the server in a single deploy. No ordering
-- dependency on the Projects section above — appended after it purely to keep
-- this file in commit order.
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS throughout (no history table —
-- apply-migrations.sh re-applies every script on every run).
--
-- Portable MySQL 8 / MariaDB: no JSON_VALUE(...RETURNING), no functional or
-- JSON indexes. Every column the public read path filters or sorts on is
-- promoted out of data_json into a real indexed column.
--
-- See WEB-MODULE-PLAN.md §3 at the repo root for full rationale, including the
-- standardisation contract (§3a) and the LarCare-table -> ABOS-table map (§3b).
-- =============================================================================

-- ── 10. dim_web_sites ────────────────────────────────────────────────────────
-- The root of the module and the ONLY thing that maps an anonymous public
-- request to a tenant. public_site_key is the credential the Next.js app sends
-- as X-Site-Key; it is public-read-safe but must be unique and unguessable.
CREATE TABLE IF NOT EXISTS dim_web_sites (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    primary_domain VARCHAR(255) NOT NULL,
    additional_domains_json JSON NULL,
    public_site_key CHAR(48) NOT NULL,
    default_locale VARCHAR(10) NOT NULL DEFAULT 'en',
    is_published TINYINT NOT NULL DEFAULT 0,
    seo_title_template VARCHAR(255) NULL,
    default_meta_description VARCHAR(500) NULL,
    default_og_image VARCHAR(500) NULL,
    default_robots VARCHAR(100) NULL DEFAULT 'index,follow',
    favicon_url VARCHAR(500) NULL,
    google_analytics_id VARCHAR(50) NULL,
    google_tag_manager_id VARCHAR(50) NULL,
    google_site_verification VARCHAR(100) NULL,
    company_name VARCHAR(255) NULL,
    tagline VARCHAR(500) NULL,
    contact_phone VARCHAR(50) NULL,
    contact_email VARCHAR(255) NULL,
    address VARCHAR(500) NULL,
    regions_served_json JSON NULL,
    social_links_json JSON NULL,
    notify_emails VARCHAR(500) NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_web_sites_key (public_site_key),
    UNIQUE KEY uq_dim_web_sites_domain (primary_domain),
    UNIQUE KEY uq_dim_web_sites_slug (tenant_id, slug),
    INDEX idx_dim_web_sites_tenant (tenant_id),
    INDEX idx_dim_web_sites_business (tenant_id, business_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 11. dim_web_collections ──────────────────────────────────────────────────
-- A user-defined content type. kind='collection' -> many entries (blog posts);
-- kind='singleton' -> exactly one (the About page's stat block).
CREATE TABLE IF NOT EXISTS dim_web_collections (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    site_id BIGINT NOT NULL,
    collection_key VARCHAR(100) NOT NULL,
    label VARCHAR(255) NOT NULL,
    label_singular VARCHAR(255) NULL,
    kind VARCHAR(20) NOT NULL DEFAULT 'collection',
    route_pattern VARCHAR(255) NULL,
    has_seo_fields TINYINT NOT NULL DEFAULT 1,
    is_system TINYINT NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_web_collections_key (site_id, collection_key),
    INDEX idx_dim_web_collections_tenant (tenant_id),
    INDEX idx_dim_web_collections_site (site_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 12. dim_web_collection_fields ────────────────────────────────────────────
-- The schema of a collection. Drives BOTH the admin editor's field rendering
-- and server-side validation of data_json on write.
CREATE TABLE IF NOT EXISTS dim_web_collection_fields (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    site_id BIGINT NOT NULL,
    collection_id BIGINT NOT NULL,
    field_key VARCHAR(100) NOT NULL,
    label VARCHAR(255) NOT NULL,
    field_type VARCHAR(40) NOT NULL,
    help_text VARCHAR(500) NULL,
    is_required TINYINT NOT NULL DEFAULT 0,
    is_localized TINYINT NOT NULL DEFAULT 0,
    max_length INT NULL,
    config_json JSON NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_web_collection_fields (collection_id, field_key),
    INDEX idx_dim_web_collection_fields_tenant (tenant_id),
    INDEX idx_dim_web_collection_fields_coll (collection_id, is_deleted, sort_order)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 13. fact_web_entries ─────────────────────────────────────────────────────
-- One content row. Everything the public read path filters or sorts on is a
-- real column; everything else lives in data_json, shaped by
-- dim_web_collection_fields.
CREATE TABLE IF NOT EXISTS fact_web_entries (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    site_id BIGINT NOT NULL,
    collection_id BIGINT NOT NULL,
    slug VARCHAR(255) NULL,
    title VARCHAR(500) NOT NULL,
    locale VARCHAR(10) NOT NULL DEFAULT 'en',
    status VARCHAR(20) NOT NULL DEFAULT 'draft',
    sort_order INT NOT NULL DEFAULT 0,
    is_featured TINYINT NOT NULL DEFAULT 0,
    published_at DATETIME NULL,
    data_json JSON NULL,
    meta_title VARCHAR(255) NULL,
    meta_description VARCHAR(500) NULL,
    meta_keywords VARCHAR(500) NULL,
    canonical_url VARCHAR(500) NULL,
    og_title VARCHAR(255) NULL,
    og_description VARCHAR(500) NULL,
    og_image VARCHAR(500) NULL,
    twitter_card VARCHAR(50) NULL,
    robots VARCHAR(100) NULL,
    structured_data_json JSON NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_fact_web_entries_slug (collection_id, locale, slug),
    INDEX idx_fact_web_entries_tenant (tenant_id),
    INDEX idx_fact_web_entries_public (site_id, collection_id, status, is_deleted, sort_order),
    INDEX idx_fact_web_entries_published (site_id, status, published_at),
    INDEX idx_fact_web_entries_featured (site_id, collection_id, is_featured, status),
    INDEX idx_fact_web_entries_business (tenant_id, business_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 14. dim_web_navigation_items ─────────────────────────────────────────────
-- Replaces LarCare's nav_items (29 rows). Self-referencing for dropdowns.
CREATE TABLE IF NOT EXISTS dim_web_navigation_items (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    site_id BIGINT NOT NULL,
    parent_id BIGINT NULL,
    location VARCHAR(20) NOT NULL,
    column_title VARCHAR(255) NULL,
    label VARCHAR(255) NOT NULL,
    href VARCHAR(500) NOT NULL,
    open_in_new_tab TINYINT NOT NULL DEFAULT 0,
    sort_order INT NOT NULL DEFAULT 0,
    is_active TINYINT NOT NULL DEFAULT 1,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_web_nav_tenant (tenant_id),
    INDEX idx_dim_web_nav_public (site_id, location, is_active, is_deleted, sort_order),
    INDEX idx_dim_web_nav_parent (parent_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 15. dim_web_redirects ────────────────────────────────────────────────────
-- SEO essential: every slug change must be able to leave a 301 behind.
CREATE TABLE IF NOT EXISTS dim_web_redirects (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    site_id BIGINT NOT NULL,
    from_path VARCHAR(500) NOT NULL,
    to_path VARCHAR(500) NOT NULL,
    status_code SMALLINT NOT NULL DEFAULT 301,
    is_active TINYINT NOT NULL DEFAULT 1,
    hit_count BIGINT NOT NULL DEFAULT 0,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_web_redirects_from (site_id, from_path),
    INDEX idx_dim_web_redirects_tenant (tenant_id),
    INDEX idx_dim_web_redirects_active (site_id, is_active, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 16. dim_web_media ────────────────────────────────────────────────────────
-- Minimal asset registry: alt text is the point. Files live on disk under
-- abos-api's configured upload root; only metadata is stored here.
CREATE TABLE IF NOT EXISTS dim_web_media (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    site_id BIGINT NOT NULL,
    storage_path VARCHAR(500) NOT NULL,
    public_url VARCHAR(500) NOT NULL,
    original_filename VARCHAR(255) NULL,
    mime_type VARCHAR(100) NULL,
    size_bytes BIGINT NULL,
    width_px INT NULL,
    height_px INT NULL,
    alt_text VARCHAR(500) NULL,
    caption VARCHAR(500) NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_web_media_tenant (tenant_id),
    INDEX idx_dim_web_media_site (site_id, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- Cross-module Labels — free-form, multi-select tags shared across CRM (Leads,
-- Deals), Projects (Tasks), and HRM (Employees).
--
-- Bundled into this same (not-yet-applied) migration file rather than a new
-- db/*.sql, so both land on the server in a single deploy — same rationale as
-- the Web module section above.
--
-- One shared, tenant-wide label catalog (dim_labels) rather than one per
-- module: a label like "Hot" or "Follow-up" is equally meaningful on a Lead,
-- a Task, or an Employee, and a shared catalog is what lets a user create it
-- once and reuse it everywhere, matching the "create label options" ask.
--
-- fact_entity_labels is a generic polymorphic junction (entity_type +
-- entity_id) rather than a join table per module (fact_lead_labels,
-- fact_task_labels, ...) — one table serves every current and future
-- labelable entity, and entity_id is a plain BIGINT independent of whether
-- the underlying record is JPA-backed (Leads, Tasks, Employees) or
-- stored-procedure-backed (Deals/Opportunities), so the label system never
-- needs to know or care how a given module persists its own records.
--
-- Safe to re-run: CREATE TABLE IF NOT EXISTS (no history table — every script
-- re-runs on every apply-migrations.sh run).
-- =============================================================================

-- ── 17. dim_labels ───────────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_labels (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    name VARCHAR(100) NOT NULL,
    color VARCHAR(20) NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_labels_tenant_name (tenant_id, name),
    INDEX idx_dim_labels_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 18. fact_entity_labels ───────────────────────────────────────────────────
-- entity_type is an application-level vocabulary (LabelableEntityType), not an
-- FK-checked enum column — matches the "no FK constraints, vocabulary lives in
-- Java" convention used throughout db/ (see fact_leads.sql's status column).
CREATE TABLE IF NOT EXISTS fact_entity_labels (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    entity_type VARCHAR(30) NOT NULL,
    entity_id BIGINT NOT NULL,
    label_id BIGINT NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    UNIQUE KEY uq_fact_entity_labels (entity_type, entity_id, label_id),
    INDEX idx_fact_entity_labels_tenant (tenant_id),
    INDEX idx_fact_entity_labels_lookup (tenant_id, entity_type, entity_id),
    INDEX idx_fact_entity_labels_by_label (tenant_id, entity_type, label_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- Communication — Mail module. Replaces the previously client-only/localStorage
-- mock (features/communication/components/Mail.tsx) with real, multi-tenant
-- mailbox connections (SMTP/POP3 generic, Gmail/Outlook OAuth2) plus agent
-- assignment and reply guidelines. Bundled into this same migration file for
-- the same single-deploy reason as the sections above.
--
-- dim_mail_account: one row per connected mailbox. Non-secret parameters live
-- in config_json (host/port/username/ssl-flag/etc, following DimWebSite's
-- existing JSON-blob-in-column pattern); the one secret a connection ever
-- needs (SMTP/POP3 password, or an OAuth refresh token) lives in
-- secret_encrypted, AES-256-GCM via the existing SecretDecryptionService (same
-- pattern already used for AI agent keys) — OAuth access tokens are never
-- persisted, only refreshed from the refresh token at point of use.
--
-- fact_mail_account_access: many-to-many grant table (mailbox <-> user), same
-- shape as the existing user_business_access grant table. owner_user_id on
-- dim_mail_account is implicit access and is not duplicated as a grant row
-- here — this table is only for *additional* users.
--
-- fact_mail_message: one row per sent/received message. sent_by_user_id
-- records who actually performed an outbound send (may differ from the
-- mailbox's owner_user_id when a granted user replies on the mailbox's
-- behalf — always recorded, not conditional). linked_entity_type/
-- linked_entity_id are a single optional pointer to a CRM record (Lead/Deal/
-- Contact/Client) — not a many-to-many junction, since a message either
-- points at one CRM record or it doesn't.
--
-- dim_communication_settings: one row per tenant (business_id NULL = tenant-
-- wide default) holding the "reply guidelines" persona/instructions text
-- shown to human agents while drafting a reply.
-- =============================================================================

-- ── 19. dim_mail_account ─────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_mail_account (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    owner_user_id INT NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    email_address VARCHAR(255) NOT NULL,
    provider_type VARCHAR(20) NOT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    config_json LONGTEXT NULL,
    secret_encrypted LONGTEXT NULL,
    sync_cursor VARCHAR(255) NULL,
    last_synced_at TIMESTAMP(6) NULL,
    last_error LONGTEXT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_mail_account_tenant (tenant_id),
    INDEX idx_dim_mail_account_status (status, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 20. fact_mail_account_access ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS fact_mail_account_access (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    mail_account_id BIGINT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_fact_mail_account_access (mail_account_id, user_id),
    INDEX idx_fact_mail_account_access_account (tenant_id, mail_account_id),
    INDEX idx_fact_mail_account_access_user (tenant_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 21. fact_mail_message ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS fact_mail_message (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    mail_account_id BIGINT NOT NULL,
    folder VARCHAR(20) NOT NULL,
    provider_message_id VARCHAR(500) NULL,
    thread_id VARCHAR(255) NULL,
    from_address VARCHAR(255) NULL,
    to_addresses LONGTEXT NULL,
    cc_addresses LONGTEXT NULL,
    subject VARCHAR(998) NULL,
    body_text LONGTEXT NULL,
    body_html LONGTEXT NULL,
    sent_at TIMESTAMP(6) NULL,
    sent_by_user_id INT NULL,
    is_read TINYINT NOT NULL DEFAULT 0,
    is_starred TINYINT NOT NULL DEFAULT 0,
    assigned_user_id INT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'OPEN',
    linked_entity_type VARCHAR(20) NULL,
    linked_entity_id BIGINT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_fact_mail_message_account_folder (tenant_id, mail_account_id, folder),
    INDEX idx_fact_mail_message_dedup (tenant_id, provider_message_id),
    INDEX idx_fact_mail_message_assignment (tenant_id, assigned_user_id, status),
    INDEX idx_fact_mail_message_link (tenant_id, linked_entity_type, linked_entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 22. dim_communication_settings ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_communication_settings (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    reply_persona LONGTEXT NULL,
    reply_instructions LONGTEXT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_communication_settings_tenant (tenant_id, business_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- Communication — Fireflies (meeting transcripts) + WhatsApp (inbox + /task
-- and /lead command bot). fact_meetings already exists (created by an earlier
-- migration) so its new columns use the guarded ALTER TABLE idiom from §2
-- above rather than CREATE TABLE; the new WhatsApp/Fireflies tables are new
-- and use the same CREATE TABLE IF NOT EXISTS style as the Mail tables above.
-- =============================================================================

-- ── 23. fact_meetings — Fireflies transcript columns ─────────────────────────
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_meetings' AND COLUMN_NAME='fireflies_transcript_id'),
  'ALTER TABLE fact_meetings ADD COLUMN fireflies_transcript_id VARCHAR(255) NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_meetings' AND COLUMN_NAME='fireflies_transcript_url'),
  'ALTER TABLE fact_meetings ADD COLUMN fireflies_transcript_url VARCHAR(500) NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_meetings' AND COLUMN_NAME='fireflies_summary'),
  'ALTER TABLE fact_meetings ADD COLUMN fireflies_summary LONGTEXT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_meetings' AND COLUMN_NAME='fireflies_action_items'),
  'ALTER TABLE fact_meetings ADD COLUMN fireflies_action_items LONGTEXT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_meetings' AND COLUMN_NAME='fireflies_synced_at'),
  'ALTER TABLE fact_meetings ADD COLUMN fireflies_synced_at TIMESTAMP(6) NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 24. dim_fireflies_account ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_fireflies_account (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    owner_user_id INT NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    api_key_encrypted LONGTEXT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    last_synced_at TIMESTAMP(6) NULL,
    last_error LONGTEXT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_fireflies_account_tenant (tenant_id),
    INDEX idx_dim_fireflies_account_status (status, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 25. dim_whatsapp_account ─────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_whatsapp_account (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    owner_user_id INT NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    phone_number VARCHAR(30) NULL,
    phone_number_id VARCHAR(100) NOT NULL,
    waba_id VARCHAR(100) NULL,
    access_token_encrypted LONGTEXT NULL,
    app_secret_encrypted LONGTEXT NULL,
    default_project_id BIGINT NULL,
    default_client_id BIGINT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    last_synced_at TIMESTAMP(6) NULL,
    last_error LONGTEXT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_whatsapp_account_tenant (tenant_id),
    UNIQUE KEY uq_dim_whatsapp_account_phone_number_id (phone_number_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 26. fact_whatsapp_account_access ─────────────────────────────────────────
CREATE TABLE IF NOT EXISTS fact_whatsapp_account_access (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    whatsapp_account_id BIGINT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_fact_whatsapp_account_access (whatsapp_account_id, user_id),
    INDEX idx_fact_whatsapp_account_access_account (tenant_id, whatsapp_account_id),
    INDEX idx_fact_whatsapp_account_access_user (tenant_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 27. dim_whatsapp_known_sender ─────────────────────────────────────────────
-- Allow-list of phone numbers permitted to run the /task command (see
-- WhatsAppCommandService) — /lead is deliberately open to any sender since an
-- inbound prospect texting in IS the intended CRM-capture behavior, but /task
-- creates internal work items and must be restricted to known staff numbers.
CREATE TABLE IF NOT EXISTS dim_whatsapp_known_sender (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    whatsapp_account_id BIGINT NOT NULL,
    wa_id VARCHAR(30) NOT NULL,
    mapped_user_id INT NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_dim_whatsapp_known_sender (whatsapp_account_id, wa_id),
    INDEX idx_dim_whatsapp_known_sender_account (tenant_id, whatsapp_account_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 28. fact_whatsapp_message ────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS fact_whatsapp_message (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    whatsapp_account_id BIGINT NOT NULL,
    direction VARCHAR(10) NOT NULL,
    wa_message_id VARCHAR(255) NULL,
    contact_wa_id VARCHAR(30) NOT NULL,
    contact_name VARCHAR(255) NULL,
    body_text LONGTEXT NULL,
    sent_at TIMESTAMP(6) NULL,
    sent_by_user_id INT NULL,
    is_read TINYINT NOT NULL DEFAULT 0,
    assigned_user_id INT NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'OPEN',
    linked_entity_type VARCHAR(20) NULL,
    linked_entity_id BIGINT NULL,
    command_result LONGTEXT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_fact_whatsapp_message_account_contact (tenant_id, whatsapp_account_id, contact_wa_id),
    INDEX idx_fact_whatsapp_message_dedup (tenant_id, wa_message_id),
    INDEX idx_fact_whatsapp_message_assignment (tenant_id, assigned_user_id, status),
    INDEX idx_fact_whatsapp_message_link (tenant_id, linked_entity_type, linked_entity_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- =============================================================================
-- Campaigns — fixes a live gap (channel/currency/impressions/clicks/
-- conversions/ctr/roas/manager already exist on the frontend Campaign type
-- and were silently dropped on every save since fact_campaigns never had
-- them), then adds social platform connections (Facebook/Instagram/Twitter/
-- LinkedIn/TikTok — organic posts + paid ad campaigns, tokens pasted by the
-- user, no shared-app OAuth) and a full email-marketing tool as the "Email"
-- channel's implementation, plus the launch + scheduled-tracking plumbing
-- that ties a Campaign to whichever channel actually published/sent it.
-- =============================================================================

-- ── 29. fact_campaigns — the dropped fields, for real ────────────────────────
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='channel'),
  'ALTER TABLE fact_campaigns ADD COLUMN channel VARCHAR(30) NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='currency'),
  'ALTER TABLE fact_campaigns ADD COLUMN currency VARCHAR(10) NULL DEFAULT ''USD''');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='impressions'),
  'ALTER TABLE fact_campaigns ADD COLUMN impressions INT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='clicks'),
  'ALTER TABLE fact_campaigns ADD COLUMN clicks INT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='conversions'),
  'ALTER TABLE fact_campaigns ADD COLUMN conversions INT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='ctr'),
  'ALTER TABLE fact_campaigns ADD COLUMN ctr DECIMAL(6,4) NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='roas'),
  'ALTER TABLE fact_campaigns ADD COLUMN roas DECIMAL(8,2) NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='manager'),
  'ALTER TABLE fact_campaigns ADD COLUMN manager VARCHAR(255) NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 30. fact_campaigns — launch + tracking columns ───────────────────────────
SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='social_account_id'),
  'ALTER TABLE fact_campaigns ADD COLUMN social_account_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='campaign_mode'),
  'ALTER TABLE fact_campaigns ADD COLUMN campaign_mode VARCHAR(10) NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='post_content'),
  'ALTER TABLE fact_campaigns ADD COLUMN post_content LONGTEXT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='post_media_url'),
  'ALTER TABLE fact_campaigns ADD COLUMN post_media_url VARCHAR(500) NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='external_campaign_id'),
  'ALTER TABLE fact_campaigns ADD COLUMN external_campaign_id VARCHAR(255) NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='launched_at'),
  'ALTER TABLE fact_campaigns ADD COLUMN launched_at TIMESTAMP(6) NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='email_template_id'),
  'ALTER TABLE fact_campaigns ADD COLUMN email_template_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='email_recipient_list_id'),
  'ALTER TABLE fact_campaigns ADD COLUMN email_recipient_list_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

SET @sql := IFNULL((SELECT 'SELECT 1' FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA=@db AND TABLE_NAME='fact_campaigns' AND COLUMN_NAME='mail_account_id'),
  'ALTER TABLE fact_campaigns ADD COLUMN mail_account_id BIGINT NULL');
PREPARE s FROM @sql; EXECUTE s; DEALLOCATE PREPARE s;

-- ── 31. dim_social_account ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_social_account (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    owner_user_id INT NOT NULL,
    platform VARCHAR(20) NOT NULL,
    display_name VARCHAR(255) NOT NULL,
    access_token_encrypted LONGTEXT NULL,
    account_ref_id VARCHAR(255) NULL,
    ad_account_id VARCHAR(255) NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'PENDING',
    last_synced_at TIMESTAMP(6) NULL,
    last_error LONGTEXT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_social_account_tenant (tenant_id),
    INDEX idx_dim_social_account_platform (tenant_id, platform, is_deleted)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 32. fact_social_account_access ───────────────────────────────────────────
CREATE TABLE IF NOT EXISTS fact_social_account_access (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    social_account_id BIGINT NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    UNIQUE KEY uq_fact_social_account_access (social_account_id, user_id),
    INDEX idx_fact_social_account_access_account (tenant_id, social_account_id),
    INDEX idx_fact_social_account_access_user (tenant_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 33. dim_email_template ────────────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_email_template (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    name VARCHAR(255) NOT NULL,
    subject VARCHAR(998) NULL,
    body_html LONGTEXT NULL,
    body_text LONGTEXT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_email_template_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 34. dim_email_recipient_list ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS dim_email_recipient_list (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6),
    updated_by INT NULL,
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_dim_email_recipient_list_tenant (tenant_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 35. fact_email_recipient_list_member ─────────────────────────────────────
CREATE TABLE IF NOT EXISTS fact_email_recipient_list_member (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    list_id BIGINT NOT NULL,
    email VARCHAR(255) NOT NULL,
    name VARCHAR(255) NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    is_deleted TINYINT NOT NULL DEFAULT 0,
    INDEX idx_fact_email_recipient_list_member_list (tenant_id, list_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 36. dim_email_suppression ─────────────────────────────────────────────────
-- Tenant-wide unsubscribe/bounce list, checked before every send — not per
-- recipient-list, since an unsubscribe should stick regardless of which list
-- the address is later added to.
CREATE TABLE IF NOT EXISTS dim_email_suppression (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    email VARCHAR(255) NOT NULL,
    reason VARCHAR(50) NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    UNIQUE KEY uq_dim_email_suppression (tenant_id, email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ── 37. fact_email_campaign_send ─────────────────────────────────────────────
-- One row per recipient per campaign — the source of truth for an Email
-- campaign's impressions (=opens)/clicks/ctr, rolled up by CampaignSyncScheduler.
CREATE TABLE IF NOT EXISTS fact_email_campaign_send (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    campaign_id BIGINT NOT NULL,
    mail_account_id BIGINT NOT NULL,
    recipient_email VARCHAR(255) NOT NULL,
    sent_at TIMESTAMP(6) NULL,
    opened_at TIMESTAMP(6) NULL,
    first_clicked_at TIMESTAMP(6) NULL,
    click_count INT NOT NULL DEFAULT 0,
    unsubscribed_at TIMESTAMP(6) NULL,
    bounced_at TIMESTAMP(6) NULL,
    INDEX idx_fact_email_campaign_send_campaign (tenant_id, campaign_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- SECTION: projects_hierarchy_and_ai.sql
-- -----------------------------------------------------------------------------
-- Projects: type-specific work-item hierarchy (parent_id self-join) + Action Item
-- cross-project linking. Idempotent (information_schema-guarded), same pattern as
-- projects_foundation_refactor.sql.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/projects_hierarchy_and_ai.sql

USE `stacie_Aggie_v1.0`;

SET @col_exists = (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND COLUMN_NAME = 'parent_id');
SET @sql = IF(@col_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD COLUMN `parent_id` BIGINT NULL', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

SET @idx_exists = (SELECT COUNT(*) FROM information_schema.STATISTICS WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_project_tasks' AND INDEX_NAME = 'idx_fact_project_tasks_parent');
SET @sql = IF(@idx_exists = 0, 'ALTER TABLE `fact_project_tasks` ADD INDEX `idx_fact_project_tasks_parent` (`tenant_id`, `is_deleted`, `parent_id`)', 'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Normalize any legacy type values to the new vocabulary's closest root type, so old
-- rows don't become orphaned/invalid under the new hierarchy validation.
-- `type` carries no index, so this trips client-side "safe update mode" (Error 1175)
-- even with a WHERE clause present — toggle it off for just this one statement.
SET SQL_SAFE_UPDATES = 0;
UPDATE fact_project_tasks SET type = 'Task' WHERE type IN ('Backlog', 'Epic') AND is_deleted = 0;
SET SQL_SAFE_UPDATES = 1;

CREATE TABLE IF NOT EXISTS fact_action_item_projects (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    action_item_id BIGINT NOT NULL,
    project_id BIGINT NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    created_by INT NULL,
    UNIQUE KEY uq_action_item_project (action_item_id, project_id),
    INDEX idx_action_item_projects_item (tenant_id, action_item_id),
    INDEX idx_action_item_projects_project (tenant_id, project_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- -----------------------------------------------------------------------------
-- SECTION: fact_audit_logs.sql
-- -----------------------------------------------------------------------------
-- =============================================================================
-- fact_audit_logs.sql — audit trail for the passcode-gated System Logs feature
-- (Settings > System Logs, unlocked with "5421"). Records one row per
-- successful mutating event call (POST/PUT .../event with a 2xx response),
-- written generically by ActivityLoggingFilter for every module that uses the
-- event/webhook convention — see DiagnosticLogService for the companion
-- file-based error/warning/success log (logs/system-activity.json).
-- Safe to re-run: CREATE TABLE IF NOT EXISTS.
-- =============================================================================

CREATE TABLE IF NOT EXISTS fact_audit_logs (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    module VARCHAR(50) NOT NULL,
    action VARCHAR(80) NOT NULL,
    user_id INT NULL,
    user_email VARCHAR(150) NULL,
    summary VARCHAR(300) NOT NULL,
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    INDEX idx_audit_tenant_created (tenant_id, created_at),
    INDEX idx_audit_module (tenant_id, module),
    INDEX idx_audit_user (tenant_id, user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;