-- Migration: Rename "loads" to "shipments"
-- Run this SQL script in your MySQL database before deploying code changes
--
-- WARNING: Back up your database before running this migration!

-- 1. Drop foreign key constraints first
ALTER TABLE load_notes DROP FOREIGN KEY fk_load_notes_load;
ALTER TABLE load_documents DROP FOREIGN KEY fk_load_documents_load;
ALTER TABLE load_stops DROP FOREIGN KEY fk_load_stops_load;

-- 2. Rename tables
RENAME TABLE loads TO shipments;
RENAME TABLE load_notes TO shipment_notes;
RENAME TABLE load_documents TO shipment_documents;
RENAME TABLE load_stops TO shipment_stops;

-- 3. Rename foreign key columns
ALTER TABLE shipment_notes CHANGE load_id shipment_id VARCHAR(36) NOT NULL;
ALTER TABLE shipment_documents CHANGE load_id shipment_id VARCHAR(36) NOT NULL;
ALTER TABLE shipment_stops CHANGE load_id shipment_id VARCHAR(36) NOT NULL;

-- 4. Recreate foreign key constraints with new names
ALTER TABLE shipment_notes
  ADD CONSTRAINT fk_shipment_notes_shipment
  FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE CASCADE;

ALTER TABLE shipment_documents
  ADD CONSTRAINT fk_shipment_documents_shipment
  FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE CASCADE;

ALTER TABLE shipment_stops
  ADD CONSTRAINT fk_shipment_stops_shipment
  FOREIGN KEY (shipment_id) REFERENCES shipments(id) ON DELETE CASCADE;

-- 5. Rename indexes on shipments table
ALTER TABLE shipments DROP INDEX idx_loads_status, ADD INDEX idx_shipments_status (status);
ALTER TABLE shipments DROP INDEX idx_loads_broker, ADD INDEX idx_shipments_broker (broker);
ALTER TABLE shipments DROP INDEX idx_loads_client_id, ADD INDEX idx_shipments_client_id (client_id);
ALTER TABLE shipments DROP INDEX idx_loads_freight_partner_id, ADD INDEX idx_shipments_freight_partner_id (freight_partner_id);
ALTER TABLE shipments DROP INDEX idx_loads_created_at, ADD INDEX idx_shipments_created_at (created_at);
ALTER TABLE shipments DROP INDEX idx_loads_reference_id, ADD INDEX idx_shipments_reference_id (reference_id);

-- 6. Rename indexes on child tables
ALTER TABLE shipment_notes DROP INDEX idx_load_notes_load_id, ADD INDEX idx_shipment_notes_shipment_id (shipment_id);
ALTER TABLE shipment_documents DROP INDEX idx_load_documents_load_id, ADD INDEX idx_shipment_documents_shipment_id (shipment_id);
ALTER TABLE shipment_stops DROP INDEX idx_load_stops_load_id, ADD INDEX idx_shipment_stops_shipment_id (shipment_id);

-- Migration complete!
-- Now deploy the updated code that uses "shipments" instead of "loads"
