-- ═══════════════════════════════════════════════════════════════
-- Zufiti – Migration 002: Multi-track Fulfillment
-- Adds: listing_type to properties, fulfillment_type to ad_slots,
--       physical slot metadata, shipment/install tracking to bookings
-- ═══════════════════════════════════════════════════════════════
SET NAMES utf8mb4;

-- ─── Properties: listing type + ship-to-owner address ───────────
ALTER TABLE properties
  ADD COLUMN listing_type ENUM('digital','physical') NOT NULL DEFAULT 'digital' AFTER type,
  ADD COLUMN shipping_address TEXT NULL AFTER listing_type;

-- ─── Ad Slots: fulfillment type + physical metadata ─────────────
ALTER TABLE ad_slots
  ADD COLUMN fulfillment_type ENUM('digital','self_install','ship_to_owner') NOT NULL DEFAULT 'digital' AFTER description,
  ADD COLUMN placement_method ENUM('screen','led_panel','digital_billboard','projection','fence','building_wall','stake','banner_stand','window','vehicle','other') NULL AFTER fulfillment_type,
  ADD COLUMN physical_material_notes TEXT NULL AFTER placement_method,
  ADD COLUMN installation_fee DECIMAL(12,2) NOT NULL DEFAULT 0.00 AFTER physical_material_notes;

-- ─── Ad Slot Images ─────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS ad_slot_images (
    id            CHAR(36)     NOT NULL DEFAULT (UUID()),
    tenant_id     CHAR(36)     NOT NULL,
    ad_slot_id    CHAR(36)     NOT NULL,
    filename      VARCHAR(255) NOT NULL,
    original_name VARCHAR(255) NOT NULL,
    mime_type     VARCHAR(80)  NOT NULL,
    size_bytes    INT          NOT NULL,
    width_px      SMALLINT     NULL,
    height_px     SMALLINT     NULL,
    is_primary    TINYINT(1)  NOT NULL DEFAULT 0,
    sort_order    TINYINT      NOT NULL DEFAULT 0,
    created_at    DATETIME     NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id),
    INDEX idx_slotimg_slot (ad_slot_id),
    CONSTRAINT fk_slotimg_slot   FOREIGN KEY (ad_slot_id) REFERENCES ad_slots(id) ON DELETE CASCADE,
    CONSTRAINT fk_slotimg_tenant FOREIGN KEY (tenant_id)  REFERENCES tenants(id)  ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ─── Bookings: extended status + shipment/install columns ───────
ALTER TABLE bookings
  MODIFY COLUMN status ENUM(
    'pending','approved','active','completed','cancelled','disputed',
    'awaiting_shipment','shipped','received','installed','removed'
  ) NOT NULL DEFAULT 'pending';

ALTER TABLE bookings
  ADD COLUMN fulfillment_type ENUM('digital','self_install','ship_to_owner') NOT NULL DEFAULT 'digital' AFTER status,
  ADD COLUMN tracking_number  VARCHAR(100) NULL AFTER fulfillment_type,
  ADD COLUMN tracking_carrier VARCHAR(80)  NULL AFTER tracking_number,
  ADD COLUMN shipped_at       DATETIME     NULL AFTER tracking_carrier,
  ADD COLUMN received_at      DATETIME     NULL AFTER shipped_at,
  ADD COLUMN installed_at     DATETIME     NULL AFTER received_at,
  ADD COLUMN installation_photo VARCHAR(500) NULL AFTER installed_at,
  ADD COLUMN installation_notes TEXT        NULL AFTER installation_photo,
  ADD COLUMN removed_at       DATETIME     NULL AFTER installation_notes;
