-- ClickDelivery V0.7 Android & Operations
-- Checkout preflight, route distance, multi-order dispatch batches, merchant balances/settlements and thermal print jobs.

ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS require_checkout_preflight boolean NOT NULL DEFAULT true;
ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS require_real_route_distance boolean NOT NULL DEFAULT false;
ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS delivery_fee_per_km numeric(12,4) NOT NULL DEFAULT 0 CHECK(delivery_fee_per_km >= 0);
ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS delivery_fee_min numeric(12,2) NOT NULL DEFAULT 0 CHECK(delivery_fee_min >= 0);
ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS delivery_fee_max numeric(12,2) CHECK(delivery_fee_max IS NULL OR delivery_fee_max >= 0);
ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS checkout_quote_ttl_seconds integer NOT NULL DEFAULT 300 CHECK(checkout_quote_ttl_seconds BETWEEN 60 AND 1800);
ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS max_batch_orders integer NOT NULL DEFAULT 3 CHECK(max_batch_orders BETWEEN 1 AND 10);

ALTER TABLE merchants ADD COLUMN IF NOT EXISTS commission_percent numeric(7,4) NOT NULL DEFAULT 0 CHECK(commission_percent BETWEEN 0 AND 100);

ALTER TABLE merchant_branches ADD COLUMN IF NOT EXISTS accepting_orders boolean NOT NULL DEFAULT true;
ALTER TABLE merchant_branches ADD COLUMN IF NOT EXISTS minimum_order numeric(12,2) NOT NULL DEFAULT 0 CHECK(minimum_order >= 0);
ALTER TABLE merchant_branches ADD COLUMN IF NOT EXISTS delivery_radius_m integer CHECK(delivery_radius_m IS NULL OR delivery_radius_m BETWEEN 100 AND 100000);
ALTER TABLE merchant_branches ADD COLUMN IF NOT EXISTS estimated_prep_minutes integer NOT NULL DEFAULT 25 CHECK(estimated_prep_minutes BETWEEN 1 AND 240);
ALTER TABLE merchant_branches ADD COLUMN IF NOT EXISTS opening_hours jsonb NOT NULL DEFAULT '{}'::jsonb;

ALTER TABLE drivers ADD COLUMN IF NOT EXISTS max_concurrent_trips integer NOT NULL DEFAULT 3 CHECK(max_concurrent_trips BETWEEN 1 AND 10);

ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS checkout_quote_id text;
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS route_distance_m integer;
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS route_duration_s integer;
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS route_distance_source text;
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS merchant_commission numeric(12,2) NOT NULL DEFAULT 0;
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS merchant_net numeric(12,2) NOT NULL DEFAULT 0;
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS pricing_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb;
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS dispatch_batch_id text;
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS printed_at timestamptz;

CREATE TABLE IF NOT EXISTS checkout_quotes (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  customer_id text NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  merchant_id text NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
  branch_id text NOT NULL REFERENCES merchant_branches(id) ON DELETE CASCADE,
  cart_hash text NOT NULL,
  subtotal numeric(12,2) NOT NULL CHECK(subtotal >= 0),
  delivery_fee numeric(12,2) NOT NULL CHECK(delivery_fee >= 0),
  total numeric(12,2) NOT NULL CHECK(total >= 0),
  currency char(3) NOT NULL DEFAULT 'BOB',
  route_distance_m integer,
  route_duration_s integer,
  route_distance_source text,
  delivery_address text NOT NULL,
  delivery_location geography(Point,4326),
  validation jsonb NOT NULL DEFAULT '{}'::jsonb,
  expires_at timestamptz NOT NULL,
  consumed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS checkout_quotes_customer_exp_idx ON checkout_quotes(customer_id,expires_at DESC);
CREATE INDEX IF NOT EXISTS checkout_quotes_org_created_idx ON checkout_quotes(organization_id,created_at DESC);

CREATE TABLE IF NOT EXISTS dispatch_batches (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  driver_id text NOT NULL REFERENCES drivers(id) ON DELETE RESTRICT,
  status text NOT NULL DEFAULT 'assigned' CHECK(status IN ('assigned','accepted','in_progress','completed','cancelled')),
  order_count integer NOT NULL DEFAULT 0 CHECK(order_count BETWEEN 1 AND 10),
  estimated_distance_m integer,
  created_by_user_id text REFERENCES users(id) ON DELETE SET NULL,
  accepted_at timestamptz,
  completed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS dispatch_batches_driver_status_idx ON dispatch_batches(driver_id,status,created_at DESC);

CREATE TABLE IF NOT EXISTS dispatch_batch_items (
  batch_id text NOT NULL REFERENCES dispatch_batches(id) ON DELETE CASCADE,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  order_id text NOT NULL REFERENCES merchant_orders(id) ON DELETE CASCADE,
  trip_id text NOT NULL REFERENCES trips(id) ON DELETE CASCADE,
  sequence integer NOT NULL CHECK(sequence BETWEEN 1 AND 10),
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY(batch_id,order_id),
  UNIQUE(batch_id,sequence),
  UNIQUE(order_id)
);
CREATE INDEX IF NOT EXISTS dispatch_batch_items_trip_idx ON dispatch_batch_items(trip_id);

DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM pg_constraint WHERE conname='merchant_orders_dispatch_batch_fk'
  ) THEN
    ALTER TABLE merchant_orders ADD CONSTRAINT merchant_orders_dispatch_batch_fk
      FOREIGN KEY(dispatch_batch_id) REFERENCES dispatch_batches(id) ON DELETE SET NULL;
  END IF;
END $$;

CREATE TABLE IF NOT EXISTS merchant_wallet_entries (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  merchant_id text NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
  order_id text REFERENCES merchant_orders(id) ON DELETE SET NULL,
  entry_type text NOT NULL CHECK(entry_type IN ('sale_credit','platform_commission','adjustment','refund','settlement_debit')),
  amount numeric(12,2) NOT NULL,
  currency char(3) NOT NULL DEFAULT 'BOB',
  settlement_id text,
  description text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS merchant_wallet_order_sale_uidx
  ON merchant_wallet_entries(order_id,entry_type) WHERE order_id IS NOT NULL AND entry_type IN ('sale_credit','platform_commission');
CREATE INDEX IF NOT EXISTS merchant_wallet_merchant_created_idx ON merchant_wallet_entries(merchant_id,created_at DESC);

CREATE TABLE IF NOT EXISTS merchant_settlements (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  merchant_id text NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
  amount numeric(12,2) NOT NULL CHECK(amount >= 0),
  currency char(3) NOT NULL DEFAULT 'BOB',
  status text NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','processing','paid','failed','void')),
  method text,
  reference text,
  period_from timestamptz,
  period_to timestamptz,
  paid_at timestamptz,
  created_by_user_id text REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS merchant_settlements_merchant_created_idx ON merchant_settlements(merchant_id,created_at DESC);

ALTER TABLE merchant_wallet_entries DROP CONSTRAINT IF EXISTS merchant_wallet_entries_settlement_fk;
ALTER TABLE merchant_wallet_entries ADD CONSTRAINT merchant_wallet_entries_settlement_fk
  FOREIGN KEY(settlement_id) REFERENCES merchant_settlements(id) ON DELETE SET NULL;

CREATE TABLE IF NOT EXISTS thermal_print_jobs (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  merchant_id text NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
  order_id text NOT NULL REFERENCES merchant_orders(id) ON DELETE CASCADE,
  printer_width_mm integer NOT NULL DEFAULT 80 CHECK(printer_width_mm IN (58,80)),
  format text NOT NULL DEFAULT 'escpos' CHECK(format IN ('escpos','text')),
  payload_base64 text NOT NULL,
  status text NOT NULL DEFAULT 'queued' CHECK(status IN ('queued','sent','printed','failed','cancelled')),
  attempts integer NOT NULL DEFAULT 0,
  last_error text,
  printed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS thermal_print_jobs_merchant_status_idx ON thermal_print_jobs(merchant_id,status,created_at DESC);

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'checkout_quotes','dispatch_batches','dispatch_batch_items','merchant_wallet_entries','merchant_settlements','thermal_print_jobs'
  ] LOOP
    EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY',t);
    EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY',t);
    EXECUTE format('DROP POLICY IF EXISTS tenant_isolation ON %I',t);
    EXECUTE format(
      'CREATE POLICY tenant_isolation ON %I FOR ALL USING (app_tenant_allowed(organization_id)) WITH CHECK (app_tenant_allowed(organization_id))',t
    );
  END LOOP;
END $$;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='clickdelivery_app') THEN
    GRANT SELECT,INSERT,UPDATE,DELETE ON checkout_quotes,dispatch_batches,dispatch_batch_items,
      merchant_wallet_entries,merchant_settlements,thermal_print_jobs TO clickdelivery_app;
    GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA public TO clickdelivery_app;
  END IF;
END $$;

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['dispatch_batches','merchant_settlements','thermal_print_jobs'] LOOP
    EXECUTE format('DROP TRIGGER IF EXISTS touch_updated_at ON %I',t);
    EXECUTE format('CREATE TRIGGER touch_updated_at BEFORE UPDATE ON %I FOR EACH ROW EXECUTE FUNCTION clickdelivery_touch_updated_at()',t);
  END LOOP;
END $$;