-- Migration: 0071_item_settlement
-- Adds ITEM deal type, physical-delivery tables, stock reservation, ZIP telemetry,
-- invoice BYOK tables, and universal settlement-hold tables.

-- ── Enum additions ──────────────────────────────────────────────────────────

ALTER TYPE deal_type ADD VALUE IF NOT EXISTS 'ITEM';

CREATE TYPE shipping_mode AS ENUM (
  'flat',
  'free_over_threshold',
  'pickup_only',
  'free'
);

CREATE TYPE shipment_status AS ENUM (
  'pending',
  'awaiting_ship',
  'shipped',
  'in_transit',
  'out_for_delivery',
  'delivered',
  'pickup_ready',
  'picked_up',
  'confirmed',
  'failed_delivery',
  'lost',
  'cancelled'
);

CREATE TYPE carrier AS ENUM (
  'israel_post',
  'yedioth_couriers',
  'cheetah',
  'hfd',
  'baldar',
  'pickup',
  'other'
);

CREATE TYPE invoice_provider AS ENUM (
  'morning',
  'icount',
  'rivhit',
  'invoice4u',
  'easycount',
  'meshulam',
  'self_handled'
);

CREATE TYPE vendor_payout_release_status AS ENUM (
  'held',
  'enqueued',
  'releasing',
  'released',
  'refunded',
  'cancelled'
);

-- ── ALTER existing tables ───────────────────────────────────────────────────

ALTER TABLE deal_skus ADD COLUMN IF NOT EXISTS reserved_qty integer NOT NULL DEFAULT 0;

-- ── New tables ──────────────────────────────────────────────────────────────

CREATE TABLE IF NOT EXISTS deal_item_config (
  deal_id             uuid PRIMARY KEY REFERENCES deals(id) ON DELETE CASCADE,
  handle_sla_business_days integer NOT NULL,
  shipping_mode       shipping_mode NOT NULL,
  shipping_flat_agorot integer,
  shipping_free_threshold_agorot integer,
  pickup_enabled      boolean NOT NULL DEFAULT false,
  pickup_address_id   uuid REFERENCES vendor_addresses(id),
  pickup_hours_json   jsonb,
  pickup_instructions text,
  return_window_days  integer NOT NULL,
  is_returnable       boolean NOT NULL DEFAULT true,
  insurance_required  boolean NOT NULL DEFAULT false,
  created_at          timestamptz NOT NULL DEFAULT now(),
  updated_at          timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS shipping_addresses (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id         uuid NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  recipient_name  text NOT NULL,
  recipient_phone text NOT NULL,
  city_code       text NOT NULL,
  city_name       text NOT NULL,
  street_code     text,
  street_name     text NOT NULL,
  house_number    text NOT NULL,
  apt             text,
  entrance        text,
  floor           text,
  zip             text NOT NULL,
  zip_validated   boolean NOT NULL DEFAULT false,
  notes           text,
  lat             numeric(10,7),
  lng             numeric(10,7),
  is_default      boolean NOT NULL DEFAULT false,
  created_at      timestamptz NOT NULL DEFAULT now(),
  updated_at      timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS shipping_addresses_user_default_idx ON shipping_addresses(user_id, is_default);
CREATE INDEX IF NOT EXISTS shipping_addresses_city_idx ON shipping_addresses(city_code);

CREATE TABLE IF NOT EXISTS shipments (
  id                      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  vendor_id               uuid NOT NULL REFERENCES vendors(id),
  buyer_id                uuid NOT NULL REFERENCES users(id),
  shipping_address_id     uuid REFERENCES shipping_addresses(id),
  pickup_address_id       uuid REFERENCES vendor_addresses(id),
  status                  shipment_status NOT NULL DEFAULT 'pending',
  carrier                 carrier,
  tracking_number         text,
  tracking_url            text,
  carrier_label_r2_key    text,
  packing_slip_r2_key     text,
  pickup_code             text,
  pickup_code_expires_at  timestamptz,
  handle_by_at            timestamptz NOT NULL,
  shipped_at              timestamptz,
  delivered_at            timestamptz,
  confirmed_at            timestamptz,
  auto_confirm_at         timestamptz,
  settlement_at           timestamptz,
  stripe_transfer_id      text,
  shipping_charged_agorot integer NOT NULL DEFAULT 0,
  insurance_purchased     boolean NOT NULL DEFAULT false,
  insurance_cost_agorot   integer NOT NULL DEFAULT 0,
  created_at              timestamptz NOT NULL DEFAULT now(),
  updated_at              timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS shipments_status_handle_idx ON shipments(status, handle_by_at);
CREATE INDEX IF NOT EXISTS shipments_vendor_status_idx ON shipments(vendor_id, status);
CREATE INDEX IF NOT EXISTS shipments_buyer_status_idx ON shipments(buyer_id, status);
CREATE INDEX IF NOT EXISTS shipments_auto_confirm_idx ON shipments(auto_confirm_at);
CREATE INDEX IF NOT EXISTS shipments_tracking_idx ON shipments(carrier, tracking_number);

CREATE TABLE IF NOT EXISTS shipment_purchases (
  shipment_id  uuid NOT NULL REFERENCES shipments(id) ON DELETE CASCADE,
  purchase_id  uuid NOT NULL REFERENCES purchases(id) ON DELETE RESTRICT,
  PRIMARY KEY (shipment_id, purchase_id)
);

CREATE UNIQUE INDEX IF NOT EXISTS shipment_purchases_purchase_unique ON shipment_purchases(purchase_id);

CREATE TABLE IF NOT EXISTS shipment_events (
  id               uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  shipment_id      uuid NOT NULL REFERENCES shipments(id) ON DELETE CASCADE,
  from_status      shipment_status,
  to_status        shipment_status NOT NULL,
  source           text NOT NULL,
  actor_id         uuid,
  carrier_raw_json jsonb,
  note             text,
  created_at       timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS shipment_events_shipment_idx ON shipment_events(shipment_id, created_at);

CREATE TABLE IF NOT EXISTS stock_reservations (
  id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  sku_id             uuid NOT NULL REFERENCES deal_skus(id),
  user_id            uuid NOT NULL REFERENCES users(id),
  cart_token_hash    text,
  qty                integer NOT NULL,
  payment_intent_id  text,
  expires_at         timestamptz NOT NULL,
  consumed_at        timestamptz,
  released_at        timestamptz,
  created_at         timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS stock_reservations_sku_active_idx
  ON stock_reservations(sku_id)
  WHERE consumed_at IS NULL AND released_at IS NULL;

CREATE INDEX IF NOT EXISTS stock_reservations_expires_idx
  ON stock_reservations(expires_at)
  WHERE consumed_at IS NULL AND released_at IS NULL;

CREATE TABLE IF NOT EXISTS address_zip_cache_misses (
  id           uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  city_code    text NOT NULL,
  street_code  text,
  house_number text,
  resolution   text NOT NULL,
  resolved_zip text,
  user_id      uuid REFERENCES users(id),
  created_at   timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS zip_misses_resolution_idx ON address_zip_cache_misses(resolution, created_at);
CREATE INDEX IF NOT EXISTS zip_misses_city_idx ON address_zip_cache_misses(city_code, created_at);

CREATE TABLE IF NOT EXISTS vendor_invoice_settings (
  vendor_id               uuid PRIMARY KEY REFERENCES vendors(id) ON DELETE CASCADE,
  provider                invoice_provider NOT NULL DEFAULT 'self_handled',
  credentials_ciphertext  text,
  credentials_iv          text,
  credentials_tag         text,
  settings_json           jsonb NOT NULL DEFAULT '{}',
  last_test_at            timestamptz,
  last_test_ok            boolean,
  last_test_error         text,
  created_at              timestamptz NOT NULL DEFAULT now(),
  updated_at              timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS invoices (
  id                       uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  purchase_id              uuid NOT NULL REFERENCES purchases(id),
  vendor_id                uuid NOT NULL REFERENCES vendors(id),
  buyer_id                 uuid NOT NULL REFERENCES users(id),
  provider                 invoice_provider NOT NULL,
  provider_document_id     text,
  provider_document_number text,
  provider_document_url    text,
  status                   text NOT NULL,
  failure_reason           text,
  retry_count              integer NOT NULL DEFAULT 0,
  gross_agorot             integer NOT NULL,
  vat_agorot               integer NOT NULL,
  net_agorot               integer NOT NULL,
  created_at               timestamptz NOT NULL DEFAULT now(),
  updated_at               timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS invoices_purchase_provider_uniq ON invoices(purchase_id, provider);
CREATE INDEX IF NOT EXISTS invoices_status_retry_idx ON invoices(status, retry_count) WHERE status = 'failed';

CREATE TABLE IF NOT EXISTS vendor_payout_releases (
  id                      uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  purchase_id             uuid NOT NULL REFERENCES purchases(id),
  vendor_id               uuid NOT NULL REFERENCES vendors(id),
  vendor_acct_id          text NOT NULL,
  payment_intent_id       text NOT NULL,
  charge_id               text NOT NULL,
  transfer_id             text NOT NULL,
  net_amount_agorot       integer NOT NULL,
  application_fee_agorot  integer NOT NULL,
  status                  vendor_payout_release_status NOT NULL DEFAULT 'held',
  trigger_reason          text NOT NULL,
  held_at                 timestamptz NOT NULL DEFAULT now(),
  release_at              timestamptz NOT NULL,
  enqueued_at             timestamptz,
  claimed_at              timestamptz,
  released_at             timestamptz,
  cancelled_at            timestamptz,
  payout_id               text,
  payout_batch_key        text,
  refund_id               text,
  claim_attempts          integer NOT NULL DEFAULT 0,
  last_error              text,
  metadata                jsonb NOT NULL DEFAULT '{}'
);

CREATE UNIQUE INDEX IF NOT EXISTS vendor_payout_releases_purchase_uniq ON vendor_payout_releases(purchase_id);
CREATE INDEX IF NOT EXISTS vendor_payout_releases_due_idx ON vendor_payout_releases(status, release_at);
CREATE INDEX IF NOT EXISTS vendor_payout_releases_vendor_idx ON vendor_payout_releases(vendor_id, status);
CREATE INDEX IF NOT EXISTS vendor_payout_releases_batch_idx ON vendor_payout_releases(payout_batch_key);

CREATE TABLE IF NOT EXISTS payout_audit_log (
  id                     uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  kind                   text NOT NULL,
  vendor_id              uuid,
  stripe_balance_agorot  integer,
  held_liability_agorot  integer,
  drift_agorot           integer,
  payout_id              text,
  refund_id              text,
  note                   text,
  created_at             timestamptz NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS payout_audit_log_kind_idx ON payout_audit_log(kind, created_at);
CREATE INDEX IF NOT EXISTS payout_audit_log_vendor_idx ON payout_audit_log(vendor_id, created_at);
