-- Impact Life Executive Dashboard - API-native database schema -- Generated for All Payments (Endpoint 7) and All Claims (Endpoint 8) -- MySQL 5.7+ / MariaDB 10.4+ CREATE DATABASE IF NOT EXISTS impact_dashboard CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci; USE impact_dashboard; SET NAMES utf8mb4; SET time_zone = '+00:00'; -- ========================================================= -- Payments: mirrors the fields returned by Endpoint 7. -- Only technical keys/metadata are added for deduplication and sync tracking. -- ========================================================= CREATE TABLE IF NOT EXISTS api_payments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `Policynum` VARCHAR(100) NULL, fullname VARCHAR(255) NULL, policy_product VARCHAR(255) NULL, commence_date DATETIME NULL, amountreceived DECIMAL(14,3) NULL, paymentmethod VARCHAR(255) NULL, deduction_date DATE NULL, paiddate DATETIME NULL, agent_code VARCHAR(100) NULL, agent_name VARCHAR(255) NULL, branch VARCHAR(255) NULL, record_hash CHAR(64) NOT NULL, synced_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_api_payments_record_hash (record_hash), KEY idx_api_payments_paiddate_id (paiddate, id), KEY idx_api_payments_product (policy_product), KEY idx_api_payments_branch (branch), KEY idx_api_payments_agent (agent_code), KEY idx_api_payments_policy (`Policynum`), KEY idx_api_payments_method (paymentmethod) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- Claims: mirrors the fields returned by Endpoint 8. -- ========================================================= CREATE TABLE IF NOT EXISTS api_claims ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, claim_id VARCHAR(120) NOT NULL, policy_number VARCHAR(120) NULL, policy_product VARCHAR(255) NULL, classification VARCHAR(255) NULL, amount DECIMAL(14,2) NULL, status VARCHAR(100) NULL, cheque_date DATETIME NULL, source_range_from DATETIME NULL, source_range_to DATETIME NULL, record_hash CHAR(64) NOT NULL, synced_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_api_claims_claim_id (claim_id), KEY idx_api_claims_cheque_date_id (cheque_date, id), KEY idx_api_claims_source_range (source_range_from, source_range_to), KEY idx_api_claims_product (policy_product), KEY idx_api_claims_status (status), KEY idx_api_claims_policy (policy_number), KEY idx_api_claims_classification (classification) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- Sync configuration. The dashboard reads only local tables; -- this configuration controls how the upstream APIs populate them. -- ========================================================= CREATE TABLE IF NOT EXISTS api_sync_settings ( id TINYINT UNSIGNED NOT NULL, base_url VARCHAR(255) NOT NULL DEFAULT 'http://64.23.166.33:8420', payments_endpoint VARCHAR(255) NOT NULL DEFAULT '/api/invoices/get-payments', claims_endpoint VARCHAR(255) NOT NULL DEFAULT '/api/claims/get-claims', bearer_token TEXT NULL, payment_date_from DATETIME NOT NULL, payment_date_to DATETIME NOT NULL, payments_to_now TINYINT(1) NOT NULL DEFAULT 1, claims_date_from DATETIME NOT NULL, claims_date_to DATETIME NOT NULL, claims_to_now TINYINT(1) NOT NULL DEFAULT 1, sync_interval_minutes INT NOT NULL DEFAULT 10, sync_batch_days INT NOT NULL DEFAULT 1, sync_batch_minutes INT NOT NULL DEFAULT 60, min_batch_minutes INT NOT NULL DEFAULT 5, api_timeout_seconds INT NOT NULL DEFAULT 90, api_max_response_mb INT NOT NULL DEFAULT 12, api_retry_count INT NOT NULL DEFAULT 2, auto_lookback_hours INT NOT NULL DEFAULT 24, sync_enabled TINYINT(1) NOT NULL DEFAULT 1, last_sync_at DATETIME NULL, last_success_at DATETIME NULL, last_payments_success_at DATETIME NULL, last_claims_success_at DATETIME NULL, last_status VARCHAR(30) NULL, last_message TEXT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO api_sync_settings ( id, payment_date_from, payment_date_to, claims_date_from, claims_date_to, sync_interval_minutes, sync_batch_days, sync_batch_minutes, min_batch_minutes, api_timeout_seconds, api_max_response_mb, api_retry_count, auto_lookback_hours, sync_enabled ) VALUES ( 1, CONCAT(YEAR(CURDATE()), '-01-01 00:00:00'), CONCAT(CURDATE(), ' 23:59:59'), CONCAT(YEAR(CURDATE()), '-01-01 00:00:00'), CONCAT(CURDATE(), ' 23:59:59'), 10, 1, 60, 5, 90, 12, 2, 24, 1 ) ON DUPLICATE KEY UPDATE id = VALUES(id); -- ========================================================= -- Sync activity / diagnostics. -- ========================================================= CREATE TABLE IF NOT EXISTS api_sync_logs ( sync_log_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, sync_type ENUM('payments','claims','all') NOT NULL, date_from DATETIME NULL, date_to DATETIME NULL, requested_url VARCHAR(500) NULL, http_code INT NULL, fetched_count INT NOT NULL DEFAULT 0, inserted_count INT NOT NULL DEFAULT 0, updated_count INT NOT NULL DEFAULT 0, status ENUM('STARTED','SUCCESS','FAILED','SKIPPED') NOT NULL, message TEXT NULL, started_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, completed_at DATETIME NULL, PRIMARY KEY (sync_log_id), KEY idx_api_sync_logs_started_at (started_at), KEY idx_api_sync_logs_type_status (sync_type, status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ========================================================= -- Detailed upstream HTTP diagnostics. -- Bearer tokens are masked before they are written here. -- This table is also auto-created by api_sync_lib.php so an -- existing installation can start using diagnostics immediately. -- ========================================================= CREATE TABLE IF NOT EXISTS api_sync_http_diagnostics ( diagnostic_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, sync_log_id BIGINT UNSIGNED NULL, sync_type VARCHAR(30) NOT NULL DEFAULT 'manual', requested_url VARCHAR(1000) NULL, request_method VARCHAR(20) NOT NULL DEFAULT 'GET', request_headers LONGTEXT NULL, request_body LONGTEXT NULL, http_code INT NULL, curl_errno INT NULL, curl_error TEXT NULL, response_headers LONGTEXT NULL, response_body MEDIUMTEXT NULL, curl_info LONGTEXT NULL, verbose_output MEDIUMTEXT NULL, result_status VARCHAR(30) NULL, result_message TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (diagnostic_id), KEY idx_sync_http_diag_created (created_at), KEY idx_sync_http_diag_type (sync_type), KEY idx_sync_http_diag_http (http_code), KEY idx_sync_http_diag_log (sync_log_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Existing optional dashboard target table is preserved if already installed. CREATE TABLE IF NOT EXISTS dashboard_targets ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, target_month DATE NOT NULL, target_scope VARCHAR(30) NOT NULL DEFAULT 'COMPANY', target_name VARCHAR(255) NOT NULL DEFAULT 'ALL', premium_target DECIMAL(14,2) NOT NULL DEFAULT 0.00, policy_target INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_dashboard_target (target_month, target_scope, target_name) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;