-- ============================================================================================
-- ABOS -- RESUME migration script (purchases_sp_views.sql onward)
-- Generated 2026-08-06T10:11:27Z.
-- Target database: stacie_Aggie_v1.0
--
-- Use this to resume a run that got through fix_sp_process_ai_bank_statement.sql,
-- attendance_sp_views.sql, payroll_sp_views.sql, digital_assets_sp_views.sql,
-- loans_sp_views.sql, orders_sp_views.sql, and products_sp_views.sql successfully but failed
-- on purchases_sp_views.sql's very first statement (Error 1060, Duplicate column name
-- 'notes'). Root cause: that file hardcoded table_schema = 'db_abos_v0.1' as a string
-- literal instead of using DATABASE() like every other guarded ALTER in this codebase, so
-- the "does this column already exist" check silently never matched the real database name
-- and kept re-attempting the ADD COLUMN. Fixed to use DATABASE() -- now safe to re-run
-- unconditionally, including against a database where the column is already present.
--
-- HOW TO RUN:
--   mysql -u <user> -p 'stacie_Aggie_v1.0' < manual-migration-resume-from-purchases.sql
--
-- SAFE TO RE-RUN: every section guards its own objects, same as manual-full-migration.sql.
-- ============================================================================================

-- -----------------------------------------------------------------------------
-- 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.
-- Uses DATABASE() rather than a hardcoded schema name — a literal '`stacie_Aggie_v1.0`' here
-- silently never matches on any other target database (dev renamed, staging, production),
-- so the guard always reports "column missing" and re-attempts the ADD COLUMN, which then
-- fails with "Duplicate column name" once it already exists. DATABASE() is what every other
-- guarded ALTER in this codebase already uses.
SET @col_exists = (SELECT COUNT(*) FROM information_schema.columns
                   WHERE table_schema = DATABASE() 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;