-- ============================================================================================
-- ABOS -- Consolidated manual production migration script
-- Generated 2026-08-06T10:11:05Z from abos-api/db/apply-migrations.sh's FILES list.
-- Target database: stacie_Aggie_v1.0
--
-- HOW TO RUN:
--   1. BACK UP FIRST (this script does not do it for you):
--        mysqldump -u <user> -p --single-transaction --routines --triggers --no-tablespaces \
--          'stacie_Aggie_v1.0' | gzip > 'stacie_Aggie_v1.0'-$(date +%Y%m%d-%H%M%S).sql.gz
--
--   2. Run this script against that exact database, selecting it via the command-line
--      argument (NOT relying on a leading USE statement):
--        mysql -u <user> -p 'stacie_Aggie_v1.0' < manual-full-migration.sql
--
--      Every "USE db_abos_v0.1;" line from the original per-feature scripts has been
--      rewritten below to a backtick-quoted USE statement against stacie_Aggie_v1.0, e.g.:
--        USE `stacie_Aggie_v1.0`;
--      Backtick-quoting is required because the literal dot in "stacie_Aggie_v1.0" would
--      otherwise be parsed as a schema.table separator and break the statement.
--
-- SAFE TO RE-RUN: every section below guards its own objects (CREATE ... IF NOT EXISTS,
-- information_schema-guarded ALTERs, DROP PROCEDURE/VIEW IF EXISTS + CREATE OR REPLACE).
-- Order matters -- do not reorder sections; later sections depend on earlier ones.
-- ============================================================================================

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

-- Global application branding/settings — single row, no tenant_id (unlike most
-- tables in this schema) since these values apply platform-wide, not per-tenant.
CREATE TABLE IF NOT EXISTS app_settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    app_title VARCHAR(255) NOT NULL,
    app_subtitle VARCHAR(255),
    document_title VARCHAR(255),
    copyright_holder VARCHAR(255),
    login_welcome_message VARCHAR(500),
    login_sign_in_label VARCHAR(100),
    logo_url VARCHAR(500),
    favicon_url VARCHAR(500),
    primary_color VARCHAR(20),
    login_background_color VARCHAR(20),
    footer_text VARCHAR(500),
    support_email VARCHAR(255),
    created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6),
    updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)
);

INSERT INTO app_settings (
    app_title, app_subtitle, document_title, copyright_holder,
    login_welcome_message, login_sign_in_label, logo_url, favicon_url,
    primary_color, login_background_color, footer_text, support_email
)
SELECT
    'ABOS V1', 'Agentic ERP', 'ABOS — TechMAC Agentic ERP', 'NSP Foundation',
    'Sign in to your ERP workspace', 'Sign In', '', '',
    '#1a237e', '#f0f4ff', '© 2026 TechMAC. All rights reserved.', 'dev@techmac.co.uk'
WHERE NOT EXISTS (SELECT 1 FROM app_settings);

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

CREATE TABLE IF NOT EXISTS dim_physical_assets (
    id INT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    asset_tag VARCHAR(100) NOT NULL,
    name VARCHAR(255) NOT NULL,
    category VARCHAR(100) NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'Available',
    purchase_date DATE NULL,
    purchase_cost DECIMAL(15,2) NULL,
    location VARCHAR(255) NULL,
    notes TEXT 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_physical_assets_tenant (tenant_id),
    INDEX idx_dim_physical_assets_status (tenant_id, status),
    INDEX idx_dim_physical_assets_asset_tag (tenant_id, asset_tag)
);

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

CREATE TABLE IF NOT EXISTS fact_leads (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NULL,
    phone VARCHAR(50) NULL,
    company_name VARCHAR(255) NULL,
    source VARCHAR(100) NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'New',
    score INT NULL,
    assigned_to INT NULL,
    notes TEXT 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_leads_tenant (tenant_id),
    INDEX idx_fact_leads_status (tenant_id, status),
    INDEX idx_fact_leads_assigned (assigned_to)
);

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

CREATE TABLE IF NOT EXISTS fact_meetings (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    meeting_date DATE NOT NULL,
    start_time TIME NULL,
    end_time TIME NULL,
    location VARCHAR(255) NULL,
    organizer_id INT NULL,
    attendees TEXT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'Scheduled',
    notes TEXT 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_meetings_tenant (tenant_id),
    INDEX idx_fact_meetings_status (tenant_id, status),
    INDEX idx_fact_meetings_organizer (organizer_id)
);

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

CREATE TABLE IF NOT EXISTS fact_appointments (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    appointment_date DATE NOT NULL,
    start_time TIME NULL,
    duration_minutes INT NULL,
    location VARCHAR(255) NULL,
    client_id INT NULL,
    assigned_to INT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'Scheduled',
    notes TEXT 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_appointments_tenant (tenant_id),
    INDEX idx_fact_appointments_status (tenant_id, status),
    INDEX idx_fact_appointments_client (client_id),
    INDEX idx_fact_appointments_assigned (assigned_to)
);

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

CREATE TABLE IF NOT EXISTS fact_campaigns (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(100) NOT NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'Draft',
    start_date DATE NULL,
    end_date DATE NULL,
    budget DECIMAL(15,2) NULL,
    actual_spend DECIMAL(15,2) NULL DEFAULT 0.00,
    target_audience VARCHAR(255) NULL,
    owner_id INT NULL,
    notes TEXT 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_campaigns_tenant (tenant_id),
    INDEX idx_fact_campaigns_status (tenant_id, status),
    INDEX idx_fact_campaigns_owner (owner_id)
);

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

CREATE TABLE IF NOT EXISTS fact_asset_allocations (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    business_id INT NULL,
    asset_id INT NOT NULL,
    employee_id INT NULL,
    assigned_to_name VARCHAR(255) NULL,
    allocation_date DATE NOT NULL,
    return_date DATE NULL,
    status VARCHAR(50) NOT NULL DEFAULT 'Active',
    notes TEXT 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_asset_allocations_tenant (tenant_id),
    INDEX idx_fact_asset_allocations_asset (tenant_id, asset_id),
    INDEX idx_fact_asset_allocations_employee (employee_id),
    INDEX idx_fact_asset_allocations_status (tenant_id, status)
);

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

CREATE TABLE IF NOT EXISTS fact_vat (
    id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id INT NOT NULL,
    period VARCHAR(20) NOT NULL,
    currency VARCHAR(10) NOT NULL DEFAULT 'USD',
    sales_vat DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    purchase_vat DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    net_vat DECIMAL(15,2) NOT NULL DEFAULT 0.00,
    due_date DATE NULL,
    status VARCHAR(20) NOT NULL DEFAULT 'Draft',
    notes TEXT 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_vat_tenant (tenant_id),
    INDEX idx_fact_vat_period (tenant_id, period)
);

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

-- Adds bank/credit-card contact & address fields to dim_financial_entities (2026-07-16).
-- Purely additive — all columns NULL, no existing column/type touched, no view or stored
-- procedure references these fields today (checked vw_statement_reconciliation_detail and
-- sp_process_ai_bank_statement, neither selects/inserts them).

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'bank_contact_number'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN bank_contact_number VARCHAR(50) NULL AFTER bank_or_issuer_name',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'bank_address'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN bank_address VARCHAR(500) NULL AFTER bank_contact_number',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'bank_fax_number'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN bank_fax_number VARCHAR(50) NULL AFTER bank_address',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'connected_phone_no'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN connected_phone_no VARCHAR(50) NULL AFTER bank_email',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'connected_email'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN connected_email VARCHAR(255) NULL AFTER connected_phone_no',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_financial_entities' AND COLUMN_NAME = 'given_address'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_financial_entities ADD COLUMN given_address VARCHAR(500) NULL AFTER connected_email',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

-- Adds business code, tax/registration id, notes, and active-processes fields to
-- `businesses` (2026-07-25). The frontend edit form already reads/writes these
-- fields; they were missing from the table, so they were silently dropped on
-- both save and load. Purely additive — all columns NULL, no existing column
-- touched. `active_processes` stores a comma-separated list of process keys
-- (CRM, FINANCE, HRM, PROJECT, PROCUREMENT, REAL_ESTATE, LEGAL), matching the
-- existing `dim_subscription_plans.modules text` convention.

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'businesses' AND COLUMN_NAME = 'code'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE businesses ADD COLUMN code VARCHAR(20) NULL AFTER name',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'businesses' AND COLUMN_NAME = 'tax_id'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE businesses ADD COLUMN tax_id VARCHAR(50) NULL AFTER address',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'businesses' AND COLUMN_NAME = 'notes'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE businesses ADD COLUMN notes VARCHAR(1000) NULL AFTER status',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'businesses' AND COLUMN_NAME = 'active_processes'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE businesses ADD COLUMN active_processes VARCHAR(255) NULL AFTER notes',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

-- Links a Lead to a Contact (2026-07-25). Mirrors the existing fact_opportunities.contact_id
-- FK-to-dim_contacts pattern. Purely additive — nullable column, no existing column touched.

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_leads' AND COLUMN_NAME = 'contact_id'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE fact_leads ADD COLUMN contact_id INT NULL AFTER company_name',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @fk_exists = (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_leads' AND CONSTRAINT_NAME = 'fk_fact_leads_contact'
);
SET @sql = IF(@fk_exists = 0,
    'ALTER TABLE fact_leads ADD CONSTRAINT fk_fact_leads_contact FOREIGN KEY (contact_id) REFERENCES dim_contacts(id)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

-- Adds a Lead -> Service link (dim_services) and a Lead description field (2026-07-25).
-- dim_services/dim_service_types/dim_service_packages already exist in the schema but were
-- never wired to any entity/API/UI — this activates dim_services as a lookup source for
-- leads. Purely additive — nullable columns, no existing column touched.

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_leads' AND COLUMN_NAME = 'service_id'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE fact_leads ADD COLUMN service_id INT NULL AFTER contact_id',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @fk_exists = (
    SELECT COUNT(*) FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_leads' AND CONSTRAINT_NAME = 'fk_fact_leads_service'
);
SET @sql = IF(@fk_exists = 0,
    'ALTER TABLE fact_leads ADD CONSTRAINT fk_fact_leads_service FOREIGN KEY (service_id) REFERENCES dim_services(id)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'fact_leads' AND COLUMN_NAME = 'description'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE fact_leads ADD COLUMN description TEXT NULL AFTER notes',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- -----------------------------------------------------------------------------
-- SECTION: fix_all_business_id_columns.sql
-- -----------------------------------------------------------------------------
-- =============================================================================
-- Adds the business_id column to every table whose JPA entity maps a Business
-- relation (@JoinColumn(name="business_id")) but whose column is absent in older
-- dev databases created from the pre-business_id schema dump. Missing columns
-- cause runtime errors like:
--   "Unknown column 'c1_0.business_id' in 'field list'"
-- on finance list endpoints (wallets, invoices, etc.).
--
-- Idempotent: each ALTER is guarded on information_schema, so re-running is a
-- no-op on databases that already have the column.
--
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/fix_all_business_id_columns.sql
-- =============================================================================
USE `stacie_Aggie_v1.0`;
SET @db := DATABASE();

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

-- -----------------------------------------------------------------------------
-- SECTION: fix_finance_business_id_columns.sql
-- -----------------------------------------------------------------------------
-- =============================================================================
-- Adds the business_id column to finance fact tables whose JPA entities map a
-- Business relation and whose list/create queries filter on business_id. Some
-- older dev databases predate this column, causing
--   "Unknown column 'fs1_0.business_id'"  on bank/CC statement + transaction lists.
-- Guarded so environments that already have the column are unaffected.
-- Deploy: mysql -u root -p `stacie_Aggie_v1.0` < db/fix_finance_business_id_columns.sql
-- =============================================================================
USE `stacie_Aggie_v1.0`;
SET @db := DATABASE();

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

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

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

-- Fixes QA Bug #3: the UserModuleAccess JPA entity expects columns `module`,
-- `is_deleted`, `created_at`, `updated_at` on user_module_access, but the table
-- as originally created only had (id, user_id, module_name, access_level,
-- tenant_id). Every existsBy.../findBy...IsDeletedFalse query threw
-- "Unknown column" and TENANT_MANAGER was locked out of every module-gated
-- endpoint as a result. Align the table to the entity rather than the other
-- way around, since grant/revoke logic in TenantModuleService depends on
-- soft-delete semantics (revoke = is_deleted=1, not a hard delete).

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'module_name'
);
SET @sql = IF(@col_exists > 0,
    'ALTER TABLE user_module_access CHANGE COLUMN module_name module VARCHAR(50) NOT NULL',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'created_at'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE user_module_access ADD COLUMN created_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'updated_at'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE user_module_access ADD COLUMN updated_at TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP(6) ON UPDATE CURRENT_TIMESTAMP(6)',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user_module_access' AND COLUMN_NAME = 'is_deleted'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE user_module_access ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

-- Found during Finance re-test (2026-07-12): DimExpenseCategory entity declares an is_deleted
-- field (used by 5 of 8 DimExpenseCategoryRepository query methods, e.g.
-- findByIdAndTenantIdAndIsDeletedFalse), but dim_expense_categories never had that column —
-- same schema-drift pattern as Bug #3 (user_module_access). Every soft-delete-aware query against
-- this table failed with "Unknown column 'is_deleted' in 'field list'", including transitively
-- whenever FactTransaction/FactLedgerEntry eagerly fetched their linked category/subcategory.

SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_expense_categories' AND COLUMN_NAME = 'is_deleted'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_expense_categories ADD COLUMN is_deleted TINYINT NOT NULL DEFAULT 0',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

-- Fixes QA Bug #4: sp_bulk_create_tenant_users' JSON contract (first/last/hash/
-- role/scope/dept) never matched what UserService.createUserViaSp() actually
-- sends (firstName/lastName/passwordHash/passwordSalt/userType/roleId/
-- moduleAccess), so v_role_name was always NULL, the INSERT INTO roles
-- violated its NOT NULL constraint, the blanket EXIT HANDLER swallowed the
-- error silently, and every tenant-admin/tenant-manager creation failed with
-- an opaque 500. Also: AuthProcedureRepository declares parameters
-- (p_tenant_id, p_users_json, p_created_by) plus an OUT p_created_count,
-- which never matched this procedure's (p_tenant_id, p_users_json,
-- p_creator_id) with no OUT param either — fixed here too.
--
-- Role creation now happens in Java (UserService.findOrCreateRole) before
-- this procedure is called, so roleId is passed directly instead of being
-- resolved/created from a role name inside the procedure.

DROP PROCEDURE IF EXISTS sp_bulk_create_tenant_users;

DELIMITER $$

CREATE PROCEDURE sp_bulk_create_tenant_users(
    IN p_tenant_id INT,
    IN p_users_json JSON,
    /* Example payload element:
    {
      "firstName": "John", "lastName": "Doe", "email": "j@test.com",
      "passwordHash": "$2a$...", "passwordSalt": "uuid", "userType": "TENANT_MANAGER",
      "roleId": 5,
      "moduleAccess": [{"module": "CRM", "accessLevel": "WRITE"}]
    }
    */
    IN p_created_by INT,
    OUT p_created_count INT
)
BEGIN
    DECLARE v_user_count INT DEFAULT 0;
    DECLARE v_module_count INT DEFAULT 0;
    DECLARE i INT DEFAULT 0;
    DECLARE j INT DEFAULT 0;

    DECLARE v_first VARCHAR(100);
    DECLARE v_last VARCHAR(100);
    DECLARE v_email VARCHAR(255);
    DECLARE v_hash VARCHAR(512);
    DECLARE v_salt VARCHAR(255);
    DECLARE v_user_type VARCHAR(20);
    DECLARE v_role_id INT;
    DECLARE v_new_user_id INT;
    DECLARE v_module_json JSON;
    DECLARE v_module_name VARCHAR(50);
    DECLARE v_access_level VARCHAR(20);

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SET v_user_count = JSON_LENGTH(p_users_json);

    WHILE i < v_user_count DO
        SET v_first     = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].firstName')));
        SET v_last      = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].lastName')));
        SET v_email     = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].email')));
        SET v_hash       = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].passwordHash')));
        SET v_salt       = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].passwordSalt')));
        SET v_user_type  = JSON_UNQUOTE(JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].userType')));
        SET v_role_id    = JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].roleId'));
        SET v_module_json = JSON_EXTRACT(p_users_json, CONCAT('$[', i, '].moduleAccess'));

        INSERT INTO users (
            tenant_id, first_name, last_name, email, password_hash, password_salt,
            role_id, status, is_super_admin, user_type, is_enabled, created_by
        ) VALUES (
            p_tenant_id, v_first, v_last, v_email, v_hash, v_salt,
            v_role_id, 'Active', 0, v_user_type, b'1', p_created_by
        );

        SET v_new_user_id = LAST_INSERT_ID();

        IF v_module_json IS NOT NULL THEN
            SET v_module_count = JSON_LENGTH(v_module_json);
            SET j = 0;
            WHILE j < v_module_count DO
                SET v_module_name  = JSON_UNQUOTE(JSON_EXTRACT(v_module_json, CONCAT('$[', j, '].module')));
                SET v_access_level = JSON_UNQUOTE(JSON_EXTRACT(v_module_json, CONCAT('$[', j, '].accessLevel')));
                INSERT INTO user_module_access (user_id, module, access_level, tenant_id, is_deleted)
                VALUES (v_new_user_id, v_module_name, IFNULL(v_access_level, 'READ'), p_tenant_id, 0);
                SET j = j + 1;
            END WHILE;
        END IF;

        SET i = i + 1;
    END WHILE;

    SET p_created_count = v_user_count;

    COMMIT;
END$$

DELIMITER ;

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

-- Consolidated fix for QA Bugs #7 (stored-procedure contract drift), #9 (NOT NULL columns never
-- populated), and the schema addition needed for #7's employee-termination fix. Covers the
-- CRM/Finance core flow (Opportunity, Invoice, Pay) and the HRM/Platform-Admin procedures that
-- need more than a one-parameter fix (see plans/java-fixes-plan.md for the simpler cases that only
-- need a Java-side change, no SQL). Safe to re-run — every ALTER is idempotent and every
-- CREATE PROCEDURE is preceded by DROP PROCEDURE IF EXISTS.
--
-- Pair this with the matching Java changes in plans/java-fixes-plan.md before deploying — the
-- Java *ProcedureRepository classes must declare the exact same parameter list, in the same order,
-- as each procedure below, or SimpleJdbcCall will bind values to the wrong positions.

-- =============================================================================================
-- 1. sp_create_opportunity — was missing p_tenant_id, ignored 6 fields Java already sends, and
--    never populated fact_opportunities.full_name (NOT NULL, no default) — see Bug #9.
-- =============================================================================================
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_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, 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_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 ;

-- =============================================================================================
-- 2. sp_create_invoice_with_ledger — was missing p_tenant_id, ignored 5 fields Java already
--    sends, and never populated fact_invoices.client_name (NOT NULL, no default) — see Bug #9.
-- =============================================================================================
DROP PROCEDURE IF EXISTS sp_create_invoice_with_ledger;

DELIMITER $$

CREATE PROCEDURE sp_create_invoice_with_ledger(
    IN p_tenant_id INT,
    IN p_opportunity_id BIGINT,
    IN p_wallet_id INT,
    IN p_client_id INT,
    IN p_invoice_no VARCHAR(100),
    IN p_amount DECIMAL(15,2),
    IN p_tax_amount DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_issue_date DATE,
    IN p_due_date DATE,
    IN p_notes TEXT,
    IN p_created_by INT,
    OUT p_invoice_id BIGINT
)
BEGIN
    DECLARE v_client_name VARCHAR(255);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT company_name INTO v_client_name
    FROM dim_companies
    WHERE id = p_client_id AND tenant_id = p_tenant_id;

    INSERT INTO fact_invoices (
        tenant_id, opportunity_id, wallet_id, client_id, invoice_no, client_name, amount,
        tax_amount, currency, status, issue_date, due_date, notes, created_by
    ) VALUES (
        p_tenant_id, p_opportunity_id, p_wallet_id, p_client_id, p_invoice_no, v_client_name,
        p_amount, p_tax_amount, p_currency, 'Draft', p_issue_date, p_due_date, p_notes, p_created_by
    );

    SET p_invoice_id = LAST_INSERT_ID();

    INSERT INTO fact_ledger_entries (
        tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by
    ) VALUES (
        p_tenant_id, p_wallet_id, 'Debit', p_amount + p_tax_amount, 'Invoice', p_invoice_id, p_created_by
    );

    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- 3. sp_pay_invoice — discovered during Finance re-test (2026-07-12): fact_transactions.description
--    is NOT NULL with no default, and this procedure's INSERT never populated it — same schema-
--    drift pattern as Bug #9. Parameter list is unchanged from the original procedure; only the
--    fact_transactions INSERT is fixed to supply a description.
-- =============================================================================================
DROP PROCEDURE IF EXISTS sp_pay_invoice;

DELIMITER $$

CREATE PROCEDURE sp_pay_invoice(
    IN p_tenant_id INT,
    IN p_invoice_id BIGINT,
    IN p_client_wallet_id INT,
    IN p_corp_wallet_id INT,
    IN p_updated_by INT
)
BEGIN
    DECLARE v_total_amount DECIMAL(15,2);
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT (amount + tax_amount) INTO v_total_amount FROM fact_invoices WHERE id = p_invoice_id;

    UPDATE fact_invoices SET status = 'Paid', updated_by = p_updated_by
    WHERE id = p_invoice_id AND tenant_id = p_tenant_id;

    INSERT INTO fact_transactions (
        tenant_id, from_wallet_id, to_wallet_id, amount, description, reference_entity,
        reference_id, transaction_type, status, created_by
    ) VALUES (
        p_tenant_id, p_client_wallet_id, p_corp_wallet_id, v_total_amount,
        CONCAT('Payment for Invoice #', p_invoice_id), 'Invoice', p_invoice_id, 'Income',
        'Completed', p_updated_by
    );

    INSERT INTO fact_ledger_entries (tenant_id, wallet_id, entry_type, amount, reference_entity, reference_id, created_by)
    VALUES (p_tenant_id, p_client_wallet_id, 'Debit', v_total_amount, 'Invoice', p_invoice_id, p_updated_by);

    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- 4. sp_onboard_employee_full — was missing p_tenant_id (had it, but 6 other fields Java sends
--    were dropped: phone, manager, location, currency, password_salt, and department/designation
--    were never resolved from their ID columns to the display-string columns dim_employees also
--    has). Rewritten to accept the full contract OnboardEmployeePayload already sends.
-- =============================================================================================
DROP PROCEDURE IF EXISTS sp_onboard_employee_full;

DELIMITER $$

CREATE PROCEDURE sp_onboard_employee_full(
    IN p_tenant_id INT,
    IN p_first_name VARCHAR(100),
    IN p_last_name VARCHAR(100),
    IN p_email VARCHAR(255),
    IN p_phone VARCHAR(50),
    IN p_department_id INT,
    IN p_designation_id INT,
    IN p_contract_type VARCHAR(50),
    IN p_salary DECIMAL(15,2),
    IN p_currency VARCHAR(10),
    IN p_join_date DATE,
    IN p_manager VARCHAR(100),
    IN p_location VARCHAR(100),
    IN p_contract_start DATE,
    IN p_contract_end DATE,
    IN p_allowances_json JSON,
    IN p_create_user TINYINT,
    IN p_password_hash VARCHAR(512),
    IN p_password_salt VARCHAR(255),
    IN p_role_id INT,
    IN p_created_by INT,
    OUT p_employee_id INT
)
BEGIN
    DECLARE v_user_id INT DEFAULT NULL;
    DECLARE v_department_name VARCHAR(100) DEFAULT NULL;
    DECLARE v_job_title VARCHAR(100) DEFAULT NULL;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    IF p_department_id IS NOT NULL THEN
        SELECT department_name INTO v_department_name
        FROM dim_departments
        WHERE id = p_department_id AND tenant_id = p_tenant_id;
    END IF;

    IF p_designation_id IS NOT NULL THEN
        SELECT title INTO v_job_title
        FROM dim_designations
        WHERE id = p_designation_id AND tenant_id = p_tenant_id;
    END IF;

    IF p_create_user = 1 THEN
        INSERT INTO users (
            tenant_id, first_name, last_name, email, password_hash, password_salt, role_id,
            is_enabled, created_by
        ) VALUES (
            p_tenant_id, p_first_name, p_last_name, p_email, p_password_hash, p_password_salt,
            p_role_id, b'1', p_created_by
        );
        SET v_user_id = LAST_INSERT_ID();
    END IF;

    INSERT INTO dim_employees (
        tenant_id, user_id, department_id, designation_id, first_name, last_name, email, phone,
        department, job_title, contract_type, salary, currency, join_date, manager, location,
        created_by
    ) VALUES (
        p_tenant_id, v_user_id, p_department_id, p_designation_id, p_first_name, p_last_name,
        p_email, p_phone, v_department_name, v_job_title, p_contract_type, p_salary, p_currency,
        p_join_date, p_manager, p_location, p_created_by
    );
    SET p_employee_id = LAST_INSERT_ID();

    INSERT INTO dim_employee_contracts (
        tenant_id, employee_id, start_date, end_date, allowances_json, created_by
    ) VALUES (
        p_tenant_id, p_employee_id, p_contract_start, p_contract_end, p_allowances_json, p_created_by
    );

    INSERT INTO dim_wallets (
        tenant_id, wallet_name, owner_type, employee_id, currency, created_by
    ) VALUES (
        p_tenant_id, CONCAT(p_first_name, ' ', p_last_name, ' Wallet'), 'Employee', p_employee_id,
        p_currency, p_created_by
    );

    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- 5. sp_terminate_employee — dim_employees has no columns to record termination_date/reason;
--    add them (idempotent), then rewrite the procedure to store what TerminateEmployeePayload
--    already sends instead of silently dropping it.
-- =============================================================================================
SET @col_exists = (
    SELECT COUNT(*) FROM information_schema.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'dim_employees' AND COLUMN_NAME = 'termination_date'
);
SET @sql = IF(@col_exists = 0,
    'ALTER TABLE dim_employees ADD COLUMN termination_date DATE NULL',
    'SELECT 1');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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

DROP PROCEDURE IF EXISTS sp_terminate_employee;

DELIMITER $$

CREATE PROCEDURE sp_terminate_employee(
    IN p_tenant_id INT,
    IN p_employee_id INT,
    IN p_termination_date DATE,
    IN p_reason VARCHAR(500),
    IN p_updated_by INT
)
BEGIN
    DECLARE v_user_id INT;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    SELECT user_id INTO v_user_id FROM dim_employees WHERE id = p_employee_id;

    UPDATE dim_employees
    SET status = 'Terminated',
        is_deleted = 1,
        termination_date = p_termination_date,
        termination_reason = p_reason,
        updated_by = p_updated_by
    WHERE id = p_employee_id AND tenant_id = p_tenant_id;

    UPDATE dim_wallets
    SET status = 'Frozen', updated_by = p_updated_by
    WHERE employee_id = p_employee_id AND tenant_id = p_tenant_id;

    IF v_user_id IS NOT NULL THEN
        UPDATE users SET is_enabled = b'0', is_deleted = 1, updated_by = p_updated_by
        WHERE id = v_user_id AND tenant_id = p_tenant_id;

        UPDATE user_api_keys SET is_deleted = 1 WHERE user_id = v_user_id;
    END IF;

    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- 6. sp_suspend_tenant — Java already sends p_reason/p_suspend_until, but the procedure ignored
--    both. Store them in platform_audit_logs.description (existing JSON audit-trail column)
--    rather than adding new tenants columns.
-- =============================================================================================
DROP PROCEDURE IF EXISTS sp_suspend_tenant;

DELIMITER $$

CREATE PROCEDURE sp_suspend_tenant(
    IN p_tenant_id INT,
    IN p_reason VARCHAR(500),
    IN p_suspend_until DATE,
    IN p_performed_by INT
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;

    START TRANSACTION;

    UPDATE tenants SET status = 'Suspended', updated_by = p_performed_by WHERE id = p_tenant_id;
    UPDATE users SET is_enabled = b'0', status = 'Inactive', updated_by = p_performed_by
    WHERE tenant_id = p_tenant_id;

    INSERT INTO platform_audit_logs (tenant_id, action_type, performed_by, description)
    VALUES (
        p_tenant_id, 'SUSPEND_TENANT', p_performed_by,
        JSON_OBJECT('status', 'Suspended', 'reason', p_reason, 'suspendUntil', p_suspend_until)
    );

    COMMIT;
END$$

DELIMITER ;

-- =============================================================================================
-- Not included here (no SQL change needed — Java-only fixes, see plans/java-fixes-plan.md):
--   sp_soft_delete_user, sp_soft_delete_company, sp_link_employee_to_user,
--   sp_update_opportunity_status, sp_assign_role_to_user (Tier 2 — Java just needs to send a
--   parameter it already has, or one already-defined-elsewhere value, the procedures are correct
--   as deployed) and sp_snapshot_tenant_usage (Java needs to match the procedure's simpler
--   single-tenant contract, not the other way around).
-- =============================================================================================

-- -----------------------------------------------------------------------------
-- SECTION: finance_views.sql
-- -----------------------------------------------------------------------------
-- Finance reporting views (deploy manually)
USE `stacie_Aggie_v1.0`;

CREATE OR REPLACE VIEW vw_wallet_ledger_summary AS
SELECT
    w.tenant_id,
    w.id AS wallet_id,
    w.wallet_name,
    w.balance,
    w.currency,
    es.id AS subcategory_id,
    es.subcategory_name,
    COALESCE(SUM(CASE WHEN le.entry_type = 'Debit' THEN le.amount ELSE 0 END), 0) AS total_debits,
    COALESCE(SUM(CASE WHEN le.entry_type = 'Credit' THEN le.amount ELSE 0 END), 0) AS total_credits
FROM dim_wallets w
LEFT JOIN fact_ledger_entries le ON le.wallet_id = w.id AND le.is_deleted = 0
LEFT JOIN dim_expense_subcategories es ON es.id = le.subcategory_id
WHERE w.is_deleted = 0
GROUP BY w.tenant_id, w.id, w.wallet_name, w.balance, w.currency, es.id, es.subcategory_name;

CREATE OR REPLACE VIEW vw_cash_flow AS
SELECT
    t.tenant_id,
    t.id AS transaction_id,
    t.from_wallet_id,
    fw.wallet_name AS from_wallet_name,
    t.to_wallet_id,
    tw.wallet_name AS to_wallet_name,
    t.amount,
    t.currency,
    t.transaction_date,
    t.status,
    t.reference,
    t.type
FROM fact_transactions t
LEFT JOIN dim_wallets fw ON fw.id = t.from_wallet_id
LEFT JOIN dim_wallets tw ON tw.id = t.to_wallet_id
WHERE t.is_deleted = 0;

-- NOTE: originally written against dim_bank_statements/fact_bank_transactions, which never
-- existed under those names — the real tables are fact_statements (joined to
-- dim_financial_entities for the bank/account label) and fact_statement_transactions, whose
-- cash_in_or_out enum is 'Cash In'/'Cash Out', not 'In'/'Out'. Fixed to match the actual schema.
CREATE OR REPLACE VIEW vw_bank_reconciliation AS
SELECT
    s.tenant_id,
    s.id AS statement_id,
    e.bank_or_issuer_name AS bank_name,
    e.account_number,
    s.beginning_balance,
    s.current_balance,
    COALESCE(SUM(CASE WHEN st.cash_in_or_out = 'Cash In' THEN st.amount ELSE 0 END), 0) AS total_cash_in,
    COALESCE(SUM(CASE WHEN st.cash_in_or_out = 'Cash Out' THEN st.amount ELSE 0 END), 0) AS total_cash_out,
    s.beginning_balance
        + COALESCE(SUM(CASE WHEN st.cash_in_or_out = 'Cash In' THEN st.amount WHEN st.cash_in_or_out = 'Cash Out' THEN -st.amount ELSE 0 END), 0) AS reconciled_balance,
    COUNT(st.id) AS transaction_count
FROM fact_statements s
JOIN dim_financial_entities e ON e.id = s.entity_id AND e.is_deleted = 0
LEFT JOIN fact_statement_transactions st ON st.statement_id = s.id AND st.is_deleted = 0
WHERE s.is_deleted = 0
GROUP BY s.tenant_id, s.id, e.bank_or_issuer_name, e.account_number, s.beginning_balance, s.current_balance;

CREATE OR REPLACE VIEW vw_commission_report AS
SELECT
    c.tenant_id,
    c.id AS commission_id,
    c.agent_id,
    c.agent_name,
    c.period,
    c.deal_ref,
    c.gross_amount,
    c.rate,
    c.earned,
    c.bonus,
    c.total,
    c.currency,
    c.status,
    c.paid_date
FROM fact_commissions c
WHERE c.is_deleted = 0;

-- -----------------------------------------------------------------------------
-- 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: 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.
-- 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;