-- 007-customer-org-fulfillment-default.sql
-- Adds per-customer fulfillment policy + Suncoast Prep auto-default trigger.
--
-- Fulfillment derivation hierarchy (see core/routes/order-routes.js):
--   1. Per-order `orders.fulfillment_type` override (manual)
--   2. Apple device on order → 'hq'
--   3. Repair order → 'hq'
--   4. Customer org `default_fulfillment_type` → use it
--   5. PO-based (hq / dropship / hybrid / null)
--
-- Suncoast Preparatory Academy is always HQ-fulfilled. Triggers below ensure
-- new and renamed orgs whose name starts with "Suncoast Prep" inherit that
-- default automatically (catches "Preparatory Academy", "Prep Academy", and
-- the duplicate-typo variants like "Prepatory Academy"). Triggers do NOT
-- overwrite an explicit non-NULL value, so manual overrides win.

ALTER TABLE customer_organizations
  ADD COLUMN IF NOT EXISTS default_fulfillment_type ENUM('hq','dropship','hybrid') DEFAULT NULL;

DROP TRIGGER IF EXISTS customer_org_suncoast_default_insert;
DROP TRIGGER IF EXISTS customer_org_suncoast_default_update;

CREATE TRIGGER customer_org_suncoast_default_insert
BEFORE INSERT ON customer_organizations
FOR EACH ROW
BEGIN
  IF NEW.name LIKE 'Suncoast Prep%' AND NEW.default_fulfillment_type IS NULL THEN
    SET NEW.default_fulfillment_type = 'hq';
  END IF;
END;

CREATE TRIGGER customer_org_suncoast_default_update
BEFORE UPDATE ON customer_organizations
FOR EACH ROW
BEGIN
  IF NEW.name LIKE 'Suncoast Prep%' AND NEW.default_fulfillment_type IS NULL THEN
    SET NEW.default_fulfillment_type = 'hq';
  END IF;
END;