-- ============================================================
--  CareBill — indexes for a book of a thousand hospitals.
--  Existing database:  mysql carebill < sql/indexes.sql
--  Every one of these was added after measuring, not in anticipation.
--  Safe to re-run: each is skipped if it is already there.
-- ============================================================
DROP PROCEDURE IF EXISTS cb_add_index;
DELIMITER //
CREATE PROCEDURE cb_add_index(IN tbl VARCHAR(64), IN idx VARCHAR(64), IN cols VARCHAR(255))
BEGIN
  IF NOT EXISTS (SELECT 1 FROM information_schema.STATISTICS
                 WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tbl AND INDEX_NAME = idx) THEN
    SET @s = CONCAT('ALTER TABLE `', tbl, '` ADD INDEX `', idx, '` (', cols, ')');
    PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st;
  END IF;
END //
DELIMITER ;

CALL cb_add_index('invoices',            'ix_inv_chase',         'status, balance, due_date');
CALL cb_add_index('invoices',            'ix_inv_client_status', 'client_id, status');
CALL cb_add_index('invoices',            'ix_inv_brand_date',    'brand_id, invoice_date');
CALL cb_add_index('payments',            'ix_pay_client_date',   'client_id, pay_date');
CALL cb_add_index('payment_allocations', 'ix_pa_invoice_kind',   'invoice_id, kind');
CALL cb_add_index('invoice_lines',       'ix_il_service_period', 'service_id, period_from');
CALL cb_add_index('services',            'ix_svc_due',           'status, next_invoice_date');
CALL cb_add_index('services',            'ix_svc_expiry',        'status, period_to');
CALL cb_add_index('outbox',              'ix_out_invoice_key',   'invoice_id, dedupe_key');
CALL cb_add_index('tasks',               'ix_task_invoice_key',  'invoice_id, dedupe_key');
CALL cb_add_index('suppressions',        'ix_supp_email_live',   'email, released_at');

DROP PROCEDURE cb_add_index;