-- BDS report performance indexes
-- Run manually on the application database (e.g. nhance_live).
-- Safe to re-run: each statement checks information_schema before creating.

-- policy_transaction: default 90-day filter + active flag
SET @idx := (
    SELECT COUNT(1) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'policy_transaction'
      AND index_name = 'idx_pt_active_created'
);
SET @sql := IF(@idx = 0,
    'CREATE INDEX idx_pt_active_created ON policy_transaction (is_active, created_at)',
    'SELECT ''idx_pt_active_created already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- pt_co_share_details: join from policy_transaction
SET @idx := (
    SELECT COUNT(1) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'pt_co_share_details'
      AND index_name = 'idx_pcsd_pt_active'
);
SET @sql := IF(@idx = 0,
    'CREATE INDEX idx_pcsd_pt_active ON pt_co_share_details (pt_id, is_active)',
    'SELECT ''idx_pcsd_pt_active already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- co_share_stmt_details: billed/reward aggregate joins
SET @idx := (
    SELECT COUNT(1) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'co_share_stmt_details'
      AND index_name = 'idx_cssd_coshare_stmt_active'
);
SET @sql := IF(@idx = 0,
    'CREATE INDEX idx_cssd_coshare_stmt_active ON co_share_stmt_details (co_share_id, statement_id, is_active)',
    'SELECT ''idx_cssd_coshare_stmt_active already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- insurer_statements: month + invoice filters used by aggregates
SET @idx := (
    SELECT COUNT(1) FROM information_schema.statistics
    WHERE table_schema = DATABASE()
      AND table_name = 'insurer_statements'
      AND index_name = 'idx_insq_active_month_invoice'
);
SET @sql := IF(@idx = 0,
    'CREATE INDEX idx_insq_active_month_invoice ON insurer_statements (is_active, month, invoice_status)',
    'SELECT ''idx_insq_active_month_invoice already exists'' AS info'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;