USE db_abos_v0.1;

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