-- Driver COD settlement, failed/returned visibility and reports update
-- Safe to run manually if not using sequelize sync. Ignore duplicate-column/index errors if already synced.

ALTER TABLE cod_collections
  ADD COLUMN received_from_driver DECIMAL(14,3) NOT NULL DEFAULT 0 AFTER collected_amount,
  ADD COLUMN pending_from_driver DECIMAL(14,3) NOT NULL DEFAULT 0 AFTER received_from_driver,
  ADD COLUMN settlement_status ENUM('PENDING','PARTIAL','SETTLED','CANCELLED') NOT NULL DEFAULT 'PENDING' AFTER collection_status,
  ADD COLUMN received_at DATETIME NULL AFTER collected_at,
  ADD COLUMN settlement_reference_no VARCHAR(100) NULL AFTER received_at;

CREATE INDEX idx_cod_col_drv_stat ON cod_collections (tenant_id, driver_id, settlement_status, is_delete);
CREATE INDEX idx_cod_col_recv ON cod_collections (tenant_id, driver_id, received_at, is_delete);
CREATE INDEX idx_del_attempt_time ON delivery_attempts (tenant_id, driver_id, attempted_at, is_delete);
CREATE INDEX idx_del_attempt_order_status ON delivery_attempts (tenant_id, order_id, attempt_status, is_delete);
CREATE INDEX idx_order_tenant_date ON orders (tenant_id, created_at, is_delete);
CREATE INDEX idx_payment_ref ON payments (tenant_id, reference_type, reference_id, is_delete);
