-- Phase M-Slice3 — Unified Marketplace / Escrow Foundation.
--
-- Creates the 6 foundation tables for the unified marketplace:
--   marketplace_vendors      — vendor identity & governance state
--   marketplace_listings     — items for sale
--   marketplace_orders       — completed purchases
--   escrow_cases             — escrow state for each order
--   escrow_milestones        — audit log of escrow transitions
--   inspection_reports       — rigger/inspector reports
--
-- Spec reference: `docs/SkyLara_Social_Booking_Verification_Marketplace_Ecosystem_Master_Spec.md`
-- §F "Escrow and inspection workflow".
--
-- Idempotent: uses INFORMATION_SCHEMA to check before creating,
-- matching the pattern used by earlier SkyLara migrations
-- (0019_dropzone_runway_heading.sql, 0020_meteo_policy_overrides.sql).
--
-- This slice is FOUNDATION ONLY:
--   • schema + read-only routes + pure state machine helper
--   • NO payment capture, NO actual money movement, NO payout integration
--   • write-path (orders, escrow transitions, inspections) lands in
--     a dedicated payment/escrow slice gated by Stripe Connect
--     provisioning
--
-- Tables are ordered so each CREATE TABLE's foreign keys reference
-- already-created tables.

DELIMITER $$

-- 1. marketplace_vendors — vendor identity, governance state, payout linkage
DROP PROCEDURE IF EXISTS create_marketplace_vendors $$
CREATE PROCEDURE create_marketplace_vendors()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'marketplace_vendors'
  ) THEN
    CREATE TABLE `marketplace_vendors` (
      `id`                 INT NOT NULL AUTO_INCREMENT,
      `uuid`               VARCHAR(36) NOT NULL,
      `userId`             INT NULL,
      `organizationId`     INT NULL,
      `displayName`        VARCHAR(255) NOT NULL,
      `bio`                TEXT NULL,
      `country`            VARCHAR(2) NULL,
      `commissionRatePct`  DECIMAL(5, 2) NULL,
      `state`              VARCHAR(32) NOT NULL DEFAULT 'PENDING',
      `isVerified`         TINYINT(1) NOT NULL DEFAULT 0,
      `payoutAccountId`    VARCHAR(255) NULL,
      `createdAt`          DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `updatedAt`          DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      PRIMARY KEY (`id`),
      UNIQUE KEY `marketplace_vendors_uuid_key` (`uuid`),
      KEY `marketplace_vendors_userId_idx` (`userId`),
      KEY `marketplace_vendors_state_idx` (`state`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  END IF;
END $$

CALL create_marketplace_vendors() $$
DROP PROCEDURE create_marketplace_vendors $$

-- 2. marketplace_listings — items for sale, with media/attributes and status
DROP PROCEDURE IF EXISTS create_marketplace_listings $$
CREATE PROCEDURE create_marketplace_listings()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'marketplace_listings'
  ) THEN
    CREATE TABLE `marketplace_listings` (
      `id`                  INT NOT NULL AUTO_INCREMENT,
      `uuid`                VARCHAR(36) NOT NULL,
      `vendorId`            INT NOT NULL,
      `title`               VARCHAR(255) NOT NULL,
      `description`         TEXT NULL,
      `category`            VARCHAR(64) NOT NULL,
      `subcategory`         VARCHAR(64) NULL,
      `condition`           VARCHAR(32) NULL,
      `priceCents`          INT NOT NULL,
      `currency`            VARCHAR(3) NOT NULL DEFAULT 'USD',
      `mediaJson`           JSON NULL,
      `attributesJson`      JSON NULL,
      `requiresInspection`  TINYINT(1) NOT NULL DEFAULT 0,
      `status`              VARCHAR(32) NOT NULL DEFAULT 'DRAFT',
      `publishedAt`         DATETIME(3) NULL,
      `soldAt`              DATETIME(3) NULL,
      `createdAt`           DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `updatedAt`           DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      PRIMARY KEY (`id`),
      UNIQUE KEY `marketplace_listings_uuid_key` (`uuid`),
      KEY `marketplace_listings_vendorId_idx` (`vendorId`),
      KEY `marketplace_listings_category_idx` (`category`),
      KEY `marketplace_listings_status_idx` (`status`),
      CONSTRAINT `marketplace_listings_vendorId_fkey`
        FOREIGN KEY (`vendorId`) REFERENCES `marketplace_vendors`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  END IF;
END $$

CALL create_marketplace_listings() $$
DROP PROCEDURE create_marketplace_listings $$

-- 3. marketplace_orders — completed purchases, linked to listing + buyer
DROP PROCEDURE IF EXISTS create_marketplace_orders $$
CREATE PROCEDURE create_marketplace_orders()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'marketplace_orders'
  ) THEN
    CREATE TABLE `marketplace_orders` (
      `id`                   INT NOT NULL AUTO_INCREMENT,
      `uuid`                 VARCHAR(36) NOT NULL,
      `listingId`            INT NOT NULL,
      `vendorId`             INT NOT NULL,
      `buyerUserId`          INT NOT NULL,
      `totalCents`           INT NOT NULL,
      `platformFeeCents`     INT NOT NULL DEFAULT 0,
      `vendorPayoutCents`    INT NOT NULL DEFAULT 0,
      `currency`             VARCHAR(3) NOT NULL DEFAULT 'USD',
      `state`                VARCHAR(32) NOT NULL DEFAULT 'PENDING',
      `shippingAddressJson`  JSON NULL,
      `buyerNote`            TEXT NULL,
      `createdAt`            DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `updatedAt`            DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      PRIMARY KEY (`id`),
      UNIQUE KEY `marketplace_orders_uuid_key` (`uuid`),
      KEY `marketplace_orders_buyerUserId_idx` (`buyerUserId`),
      KEY `marketplace_orders_vendorId_idx` (`vendorId`),
      KEY `marketplace_orders_listingId_idx` (`listingId`),
      KEY `marketplace_orders_state_idx` (`state`),
      CONSTRAINT `marketplace_orders_listingId_fkey`
        FOREIGN KEY (`listingId`) REFERENCES `marketplace_listings`(`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE,
      CONSTRAINT `marketplace_orders_vendorId_fkey`
        FOREIGN KEY (`vendorId`) REFERENCES `marketplace_vendors`(`id`)
        ON DELETE RESTRICT ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  END IF;
END $$

CALL create_marketplace_orders() $$
DROP PROCEDURE create_marketplace_orders $$

-- 4. escrow_cases — one per order, tracks current escrow state
DROP PROCEDURE IF EXISTS create_escrow_cases $$
CREATE PROCEDURE create_escrow_cases()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'escrow_cases'
  ) THEN
    CREATE TABLE `escrow_cases` (
      `id`               INT NOT NULL AUTO_INCREMENT,
      `uuid`             VARCHAR(36) NOT NULL,
      `orderId`          INT NOT NULL,
      `state`            VARCHAR(32) NOT NULL DEFAULT 'PENDING',
      `heldAmountCents`  INT NOT NULL DEFAULT 0,
      `currency`         VARCHAR(3) NOT NULL DEFAULT 'USD',
      `holdReason`       VARCHAR(255) NULL,
      `createdAt`        DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      `updatedAt`        DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
      PRIMARY KEY (`id`),
      UNIQUE KEY `escrow_cases_uuid_key` (`uuid`),
      UNIQUE KEY `escrow_cases_orderId_key` (`orderId`),
      KEY `escrow_cases_state_idx` (`state`),
      CONSTRAINT `escrow_cases_orderId_fkey`
        FOREIGN KEY (`orderId`) REFERENCES `marketplace_orders`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  END IF;
END $$

CALL create_escrow_cases() $$
DROP PROCEDURE create_escrow_cases $$

-- 5. escrow_milestones — audit log of every escrow state transition
DROP PROCEDURE IF EXISTS create_escrow_milestones $$
CREATE PROCEDURE create_escrow_milestones()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'escrow_milestones'
  ) THEN
    CREATE TABLE `escrow_milestones` (
      `id`                 INT NOT NULL AUTO_INCREMENT,
      `escrowCaseId`       INT NOT NULL,
      `event`              VARCHAR(64) NOT NULL,
      `fromState`          VARCHAR(32) NOT NULL,
      `toState`            VARCHAR(32) NOT NULL,
      `triggeredByUserId`  INT NULL,
      `note`               TEXT NULL,
      `transitionedAt`     DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      PRIMARY KEY (`id`),
      KEY `escrow_milestones_escrowCaseId_idx` (`escrowCaseId`),
      CONSTRAINT `escrow_milestones_escrowCaseId_fkey`
        FOREIGN KEY (`escrowCaseId`) REFERENCES `escrow_cases`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  END IF;
END $$

CALL create_escrow_milestones() $$
DROP PROCEDURE create_escrow_milestones $$

-- 6. inspection_reports — rigger/inspector outcome for orders that require inspection
DROP PROCEDURE IF EXISTS create_inspection_reports $$
CREATE PROCEDURE create_inspection_reports()
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM INFORMATION_SCHEMA.TABLES
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'inspection_reports'
  ) THEN
    CREATE TABLE `inspection_reports` (
      `id`               INT NOT NULL AUTO_INCREMENT,
      `uuid`             VARCHAR(36) NOT NULL,
      `orderId`          INT NOT NULL,
      `inspectorUserId`  INT NOT NULL,
      `inspectorRole`    VARCHAR(64) NULL,
      `outcome`          VARCHAR(32) NOT NULL,
      `notes`            TEXT NULL,
      `photosJson`       JSON NULL,
      `inspectedAt`      DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
      PRIMARY KEY (`id`),
      UNIQUE KEY `inspection_reports_uuid_key` (`uuid`),
      KEY `inspection_reports_orderId_idx` (`orderId`),
      KEY `inspection_reports_inspectorUserId_idx` (`inspectorUserId`),
      CONSTRAINT `inspection_reports_orderId_fkey`
        FOREIGN KEY (`orderId`) REFERENCES `marketplace_orders`(`id`)
        ON DELETE CASCADE ON UPDATE CASCADE
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
  END IF;
END $$

CALL create_inspection_reports() $$
DROP PROCEDURE create_inspection_reports $$

DELIMITER ;