-- ResellBD Step 12: manual courier and configurable shipping zones. NEW database only.
SET NAMES utf8mb4;
CREATE TABLE IF NOT EXISTS shipping_zones (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 zone_name VARCHAR(120) NOT NULL,
 district_match VARCHAR(120) NULL,
 fee_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
 is_active TINYINT(1) NOT NULL DEFAULT 1,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(id), UNIQUE KEY uq_shipping_zone_name(zone_name), KEY idx_shipping_zone_district(district_match,is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
INSERT IGNORE INTO shipping_zones(zone_name,district_match,fee_paisa,is_active) VALUES
('ঢাকার ভিতরে','ঢাকা',6000,1),('ঢাকার বাইরে',NULL,12000,1);
CREATE TABLE IF NOT EXISTS courier_shipments (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
 order_id BIGINT UNSIGNED NOT NULL,
 courier_name VARCHAR(120) NOT NULL,
 tracking_number VARCHAR(160) NULL,
 courier_booking_ref VARCHAR(160) NULL,
 shipment_status ENUM('not_booked','booked','picked_up','in_transit','out_for_delivery','delivered','delivery_failed','return_in_transit','returned') NOT NULL DEFAULT 'booked',
 cod_expected_paisa BIGINT UNSIGNED NOT NULL DEFAULT 0,
 cod_collected_paisa BIGINT UNSIGNED NULL,
 failure_reason VARCHAR(1000) NULL,
 admin_note VARCHAR(1000) NULL,
 booked_at DATETIME NULL,
 delivered_at DATETIME NULL,
 returned_at DATETIME NULL,
 created_by BIGINT UNSIGNED NULL,
 updated_by BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY(id), KEY idx_courier_order(order_id,created_at), KEY idx_courier_tracking(tracking_number), KEY idx_courier_status(shipment_status,updated_at),
 CONSTRAINT fk_courier_order FOREIGN KEY(order_id) REFERENCES orders(id) ON DELETE RESTRICT ON UPDATE CASCADE,
 CONSTRAINT fk_courier_created_by FOREIGN KEY(created_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE,
 CONSTRAINT fk_courier_updated_by FOREIGN KEY(updated_by) REFERENCES users(id) ON DELETE SET NULL ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;