-- Inventory listing pages (branch stock, assets, damaged, missing, sold items
-- reports) were fetching entire result sets on every request. This migration
-- adds the indexes the new paginated queries (see classes/Inventory.php) rely
-- on to stay fast as items/sold_items grow. All changes are additive.

-- Primary filter used by every items-listing query (shelf_status + branch).
ALTER TABLE items
  ADD INDEX idx_items_shelf_location (shelf_status, current_location_id);

-- Replaces the non-sargable `transaction_id LIKE '%_VERIFIED_%'` full-table
-- scan that ran on every items-listing query with an indexed boolean flag.
ALTER TABLE sold_items
  ADD COLUMN is_verified TINYINT(1)
    AS (transaction_id LIKE '%\_VERIFIED\_%') STORED;

ALTER TABLE sold_items
  ADD INDEX idx_sold_items_item_verified (item_id, is_verified);

-- Speeds up sold-items report pages, which filter by branch + verified and
-- sort by sold_at DESC.
ALTER TABLE sold_items
  ADD INDEX idx_sold_items_branch_verified_date (branch_id, is_verified, sold_at);
