-- =============================================
-- Advanced Commodity Ledger Indexes
-- Database: naveed_trader_db
-- =============================================

-- purchase_master: composite index for date-range + active filtering
-- We use IF NOT EXISTS logic via a helper or just ADD INDEX if MySQL 8+, but MariaDB/older MySQL might throw errors if index exists.
-- For simplicity in the script, we will just run the statements.

ALTER TABLE purchase_master ADD INDEX idx_pm_date_reversed (voucher_date, is_reversed);

-- purchase_lots: index for item-based lookups (used by advanced ledger)
ALTER TABLE purchase_lots ADD INDEX idx_pl_item_id (item_id);

-- sale_master: composite index for date-range + active filtering
ALTER TABLE sale_master ADD INDEX idx_sm_date_reversed (voucher_date, is_reversed);

-- sale_items: index for item-based lookups
ALTER TABLE sale_items ADD INDEX idx_si_item_id (item_id);

-- journals: composite index for expense ledger filtering
ALTER TABLE journals ADD INDEX idx_j_expense_ledger (source_type, source_id, is_reversed, journal_date);
