-- ---------------------------------------------------------------------------
-- Migration: bookings can now be fulfilled in several installments (e.g. 200
-- trays booked, taken as 50 + 50 + 100 across separate visits) instead of
-- only all at once. See schema.sql's comment on egg_bookings for the design.
-- ---------------------------------------------------------------------------
-- schema.sql already carries this for FRESH installs. Run this ONCE against
-- an EXISTING database (paste into phpMyAdmin's SQL tab, or
-- `mysql -u <user> -p <db> < sql/migration-2026-07-booking-partial-fulfillment.sql`).
-- Every ALTER is guarded so this is safely re-runnable from any partial state.
-- ---------------------------------------------------------------------------

SET @check = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'egg_bookings' AND COLUMN_NAME = 'qty_fulfilled');
SET @ddl = IF(@check = 0,
  'ALTER TABLE egg_bookings ADD COLUMN qty_fulfilled INT UNSIGNED NOT NULL DEFAULT 0 AFTER qty',
  'DO 0');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

SET @check = (SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'egg_bookings' AND COLUMN_NAME = 'amount_paid_applied');
SET @ddl = IF(@check = 0,
  'ALTER TABLE egg_bookings ADD COLUMN amount_paid_applied DECIMAL(14,2) NOT NULL DEFAULT 0 AFTER amount_paid',
  'DO 0');
PREPARE s FROM @ddl; EXECUTE s; DEALLOCATE PREPARE s;

-- backfill: any booking already 'fulfilled' under the old all-at-once model
-- is fully accounted for — qty_fulfilled = its full qty, and the deposit
-- applied = whatever amount_paid already was (matches how fulfill() used to
-- compute the one-shot sale's own amount_paid before this migration existed)
UPDATE egg_bookings SET qty_fulfilled = qty, amount_paid_applied = amount_paid
  WHERE status = 'fulfilled' AND qty_fulfilled = 0;

CREATE TABLE IF NOT EXISTS egg_booking_fulfillments (
  id            INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  booking_id    INT UNSIGNED  NOT NULL,
  sale_id       INT UNSIGNED  NULL,
  qty           INT UNSIGNED  NOT NULL DEFAULT 0,
  fulfilled_on  DATE          NOT NULL,
  created_at    TIMESTAMP     NOT NULL DEFAULT CURRENT_TIMESTAMP,
  KEY idx_bookingfulfillments_booking (booking_id),
  CONSTRAINT fk_bookingfulfillments_booking FOREIGN KEY (booking_id) REFERENCES egg_bookings(id) ON DELETE CASCADE,
  CONSTRAINT fk_bookingfulfillments_sale FOREIGN KEY (sale_id) REFERENCES sales(id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- backfill: one fulfillment-history row for every booking already fulfilled
-- under the old model, so its history isn't silently blank going forward
INSERT INTO egg_booking_fulfillments (booking_id, sale_id, qty, fulfilled_on)
  SELECT id, sale_id, qty, booked_on FROM egg_bookings
  WHERE status = 'fulfilled'
    AND id NOT IN (SELECT booking_id FROM egg_booking_fulfillments);