DELIMITER $$

DROP PROCEDURE IF EXISTS alter_table_engine_if_exists $$
CREATE PROCEDURE alter_table_engine_if_exists(
    IN p_table_name VARCHAR(64),
    IN p_engine VARCHAR(32)
)
BEGIN
    IF EXISTS (
        SELECT 1
        FROM information_schema.tables
        WHERE table_schema = DATABASE()
          AND table_name = p_table_name
    ) THEN
        SET @sql = CONCAT('ALTER TABLE `', p_table_name, '` ENGINE=', p_engine);
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END $$

CALL alter_table_engine_if_exists('invoice', 'InnoDB') $$
CALL alter_table_engine_if_exists('invoice_detail', 'InnoDB') $$
CALL alter_table_engine_if_exists('bayar_invoice_detail', 'InnoDB') $$
CALL alter_table_engine_if_exists('konsumen', 'InnoDB') $$
CALL alter_table_engine_if_exists('tb_users', 'InnoDB') $$
CALL alter_table_engine_if_exists('jurnal', 'InnoDB') $$
CALL alter_table_engine_if_exists('jurnal_detail', 'InnoDB') $$
CALL alter_table_engine_if_exists('jurnal_transaksi', 'InnoDB') $$

DROP PROCEDURE IF EXISTS add_index_if_missing $$
CREATE PROCEDURE add_index_if_missing(
    IN p_table_name VARCHAR(64),
    IN p_index_name VARCHAR(64),
    IN p_columns VARCHAR(255)
)
BEGIN
    IF NOT EXISTS (
        SELECT 1
        FROM information_schema.statistics
        WHERE table_schema = DATABASE()
          AND table_name = p_table_name
          AND index_name = p_index_name
    ) THEN
        SET @sql = CONCAT('CREATE INDEX ', p_index_name, ' ON `', p_table_name, '` (', p_columns, ')');
        PREPARE stmt FROM @sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
END $$

DELIMITER ;

CALL add_index_if_missing('invoice', 'idx_invoice_tgl_status', '`tgl_trx`, `status`');
CALL add_index_if_missing('invoice', 'idx_invoice_konsumen', '`id_konsumen`');
CALL add_index_if_missing('invoice', 'idx_invoice_user_tgl', '`id_user`, `tgl_trx`');
CALL add_index_if_missing('invoice', 'idx_invoice_marketing_tgl', '`id_marketing`, `tgl_trx`');
CALL add_index_if_missing('invoice', 'idx_invoice_transaksi', '`id_transaksi`');
CALL add_index_if_missing('invoice', 'idx_invoice_status', '`status`');
CALL add_index_if_missing('invoice', 'idx_invoice_status_board', '`hide_status_board`, `status`, `tgl_trx`');

CALL add_index_if_missing('invoice_detail', 'idx_invoice_detail_invoice', '`id_invoice`');
CALL add_index_if_missing('invoice_detail', 'idx_invoice_detail_bahan', '`id_bahan`');
CALL add_index_if_missing('invoice_detail', 'idx_invoice_detail_produk', '`id_produk`');
CALL add_index_if_missing('invoice_detail', 'idx_invoice_detail_status', '`status`');

CALL add_index_if_missing('bayar_invoice_detail', 'idx_bayar_invoice', '`id_invoice`');
CALL add_index_if_missing('bayar_invoice_detail', 'idx_bayar_user_tgl', '`id_user`, `tgl_bayar`');
CALL add_index_if_missing('bayar_invoice_detail', 'idx_bayar_hapus_setor', '`hapus`, `tgl_setor`');

CALL add_index_if_missing('jurnal', 'idx_jurnal_no_trans', '`no_trans`');
CALL add_index_if_missing('jurnal', 'idx_jurnal_tgl_user', '`tgl`, `id_user`');
CALL add_index_if_missing('jurnal_detail', 'idx_jurnal_detail_jurnal_akun', '`id_jurnal`, `id_akun`');

CALL add_index_if_missing('stok_keluar', 'idx_stok_keluar_invoice', '`id_invoice`');
CALL add_index_if_missing('stok_keluar', 'idx_stok_keluar_bahan', '`id_bahan`');
CALL add_index_if_missing('stok_keluar', 'idx_stok_keluar_tanggal', '`create_date`');

CALL add_index_if_missing('tb_users', 'idx_users_email_aktif', '`email`, `aktif`');
CALL add_index_if_missing('tb_users', 'idx_users_level_aktif', '`level`, `aktif`');
CALL add_index_if_missing('tb_users', 'idx_users_idlevel_aktif', '`id_level`, `aktif`');
CALL add_index_if_missing('tb_users', 'idx_users_sesi_login', '`sesi_login`');

CALL add_index_if_missing('menuadmin', 'idx_menu_parent_active_order', '`idparent`, `aktif`, `urutan`');
CALL add_index_if_missing('menuadmin', 'idx_menu_link_active', '`link`, `aktif`');

CALL add_index_if_missing('konsumen', 'idx_konsumen_hp_hapus', '`no_hp`, `hapus`');
CALL add_index_if_missing('konsumen', 'idx_konsumen_nama_hapus', '`nama`, `hapus`');

CALL add_index_if_missing('bahan', 'idx_bahan_jenis_pub', '`id_jenis`, `pub`');
CALL add_index_if_missing('bahan', 'idx_bahan_produk_pub', '`id_produk`, `pub`');
CALL add_index_if_missing('bahan', 'idx_bahan_stok_kunci', '`status_stok`, `kunci`');

DROP PROCEDURE IF EXISTS add_index_if_missing;
DROP PROCEDURE IF EXISTS alter_table_engine_if_exists;
