-- ============================================================
-- Fund Ingestion — CostusWorx © 2026
-- Run AFTER funds_schema.sql. Idempotent via IF NOT EXISTS guards.
-- ============================================================

-- ── Run history ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS fund_ingest_runs (
    id              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    started_at      DATETIME      NOT NULL,
    finished_at     DATETIME      DEFAULT NULL,
    status          ENUM('running','success','partial','failed') NOT NULL DEFAULT 'running',
    source          VARCHAR(64)   NOT NULL,                 -- e.g. 'fundsdata.co.za', 'csv:asisa-2026-05'
    source_version  VARCHAR(64)   DEFAULT NULL,             -- snapshot identifier from the source
    app_version     VARCHAR(32)   NOT NULL,                 -- from config/version.php
    flush_mode      ENUM('none','soft','hard') NOT NULL DEFAULT 'none',
    dry_run         TINYINT(1)    NOT NULL DEFAULT 0,
    processed       INT UNSIGNED  NOT NULL DEFAULT 0,
    inserted        INT UNSIGNED  NOT NULL DEFAULT 0,
    updated         INT UNSIGNED  NOT NULL DEFAULT 0,
    skipped         INT UNSIGNED  NOT NULL DEFAULT 0,
    failed          INT UNSIGNED  NOT NULL DEFAULT 0,
    log_file        VARCHAR(255)  DEFAULT NULL,
    error_message   TEXT          DEFAULT NULL,
    INDEX idx_started (started_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── Per-fund tracking on the existing funds table ────────────
-- Wrapped in stored-procedure-style guards so re-running is safe.
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'funds' AND COLUMN_NAME = 'last_synced_at');
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE funds ADD COLUMN last_synced_at DATETIME DEFAULT NULL AFTER is_active',
    '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 = 'funds' AND COLUMN_NAME = 'source');
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE funds ADD COLUMN source VARCHAR(64) DEFAULT NULL AFTER last_synced_at',
    '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 = 'funds' AND COLUMN_NAME = 'data_hash');
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE funds ADD COLUMN data_hash CHAR(40) DEFAULT NULL AFTER source',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- is_verified flags rows from authoritative sources (JSE, paid feeds) vs seed data.
-- Default 0 — UI shows an "Unverified data — illustrative" banner for is_verified=0 rows.
SET @col_exists := (SELECT COUNT(*) FROM information_schema.COLUMNS
                    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'funds' AND COLUMN_NAME = 'is_verified');
SET @sql := IF(@col_exists = 0,
    'ALTER TABLE funds ADD COLUMN is_verified TINYINT(1) NOT NULL DEFAULT 0 AFTER is_active',
    'SELECT 1');
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;