-- ClickDelivery V0.7.1 Commercial Complete
-- Polygon tariffs, Manager Pay pre-confirm intents, automatic merchant settlements and dispatch optimization metadata.

ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS require_platform_payment_before_order boolean NOT NULL DEFAULT true;
ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS default_settlement_frequency text NOT NULL DEFAULT 'manual';
ALTER TABLE logistics_settings DROP CONSTRAINT IF EXISTS logistics_settings_default_settlement_frequency_check;
ALTER TABLE logistics_settings ADD CONSTRAINT logistics_settings_default_settlement_frequency_check
  CHECK(default_settlement_frequency IN ('manual','daily','weekly'));
ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS default_settlement_min_amount numeric(12,2) NOT NULL DEFAULT 0 CHECK(default_settlement_min_amount >= 0);

CREATE TABLE IF NOT EXISTS territory_delivery_tariffs (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  territory_id text NOT NULL REFERENCES territories(id) ON DELETE CASCADE,
  service_mode text NOT NULL DEFAULT 'marketplace' CHECK(service_mode IN ('marketplace','delivery_express')),
  name text NOT NULL,
  base_fee numeric(12,2) NOT NULL DEFAULT 0 CHECK(base_fee >= 0),
  included_km numeric(10,3) NOT NULL DEFAULT 0 CHECK(included_km >= 0),
  per_km numeric(12,4) NOT NULL DEFAULT 0 CHECK(per_km >= 0),
  min_fee numeric(12,2) NOT NULL DEFAULT 0 CHECK(min_fee >= 0),
  max_fee numeric(12,2) CHECK(max_fee IS NULL OR max_fee >= min_fee),
  surge_multiplier numeric(8,4) NOT NULL DEFAULT 1 CHECK(surge_multiplier >= 0.1 AND surge_multiplier <= 10),
  priority integer NOT NULL DEFAULT 100,
  active boolean NOT NULL DEFAULT true,
  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(),
  UNIQUE(territory_id,service_mode,name)
);
CREATE INDEX IF NOT EXISTS territory_tariffs_scope_idx ON territory_delivery_tariffs(organization_id,territory_id,service_mode,active,priority);

ALTER TABLE checkout_quotes ADD COLUMN IF NOT EXISTS territory_id text REFERENCES territories(id) ON DELETE SET NULL;
ALTER TABLE checkout_quotes ADD COLUMN IF NOT EXISTS tariff_id text REFERENCES territory_delivery_tariffs(id) ON DELETE SET NULL;
ALTER TABLE checkout_quotes ADD COLUMN IF NOT EXISTS payment_required boolean NOT NULL DEFAULT false;

CREATE TABLE IF NOT EXISTS checkout_payment_intents (
  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,
  checkout_quote_id text NOT NULL REFERENCES checkout_quotes(id) ON DELETE CASCADE,
  connector_type text NOT NULL CHECK(connector_type IN ('manager_pay','chefmanager_ai')),
  amount numeric(12,2) NOT NULL CHECK(amount >= 0),
  currency char(3) NOT NULL DEFAULT 'BOB',
  status text NOT NULL DEFAULT 'queued' CHECK(status IN ('queued','creating','pending','authorized','paid','failed','expired','cancelled')),
  external_reference text,
  provider_payment_id text,
  provider_qr_payload text,
  provider_qr_image_url text,
  provider_response jsonb NOT NULL DEFAULT '{}'::jsonb,
  order_id text REFERENCES merchant_orders(id) ON DELETE SET NULL,
  expires_at timestamptz NOT NULL,
  paid_at timestamptz,
  consumed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(checkout_quote_id,connector_type)
);
CREATE INDEX IF NOT EXISTS checkout_payment_intents_customer_idx ON checkout_payment_intents(customer_id,created_at DESC);
CREATE INDEX IF NOT EXISTS checkout_payment_intents_status_idx ON checkout_payment_intents(organization_id,status,created_at DESC);
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS payment_intent_id text REFERENCES checkout_payment_intents(id) ON DELETE SET NULL;
CREATE UNIQUE INDEX IF NOT EXISTS merchant_orders_payment_intent_uidx ON merchant_orders(payment_intent_id) WHERE payment_intent_id IS NOT NULL;

CREATE TABLE IF NOT EXISTS merchant_settlement_rules (
  merchant_id text PRIMARY KEY REFERENCES merchants(id) ON DELETE CASCADE,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  enabled boolean NOT NULL DEFAULT false,
  frequency text NOT NULL DEFAULT 'weekly' CHECK(frequency IN ('daily','weekly')),
  weekday smallint NOT NULL DEFAULT 1 CHECK(weekday BETWEEN 0 AND 6),
  local_hour smallint NOT NULL DEFAULT 9 CHECK(local_hour BETWEEN 0 AND 23),
  min_amount numeric(12,2) NOT NULL DEFAULT 0 CHECK(min_amount >= 0),
  payment_method text,
  destination_reference text,
  last_run_at timestamptz,
  next_run_at timestamptz,
  updated_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_settlement_rules_due_idx ON merchant_settlement_rules(enabled,next_run_at);

CREATE TABLE IF NOT EXISTS dispatch_optimization_plans (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  territory_id text REFERENCES territories(id) ON DELETE SET NULL,
  requested_by_user_id text REFERENCES users(id) ON DELETE SET NULL,
  driver_id text REFERENCES drivers(id) ON DELETE SET NULL,
  order_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
  ordered_order_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
  estimated_distance_m integer,
  score numeric(16,4),
  algorithm text NOT NULL DEFAULT 'nearest_neighbor_v1',
  status text NOT NULL DEFAULT 'planned' CHECK(status IN ('planned','committed','expired','cancelled')),
  expires_at timestamptz NOT NULL DEFAULT now()+interval '10 minutes',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS dispatch_optimization_org_created_idx ON dispatch_optimization_plans(organization_id,created_at DESC);

-- Sub-administrator commission guardrails. Parent admins can tighten these ranges.
ALTER TABLE subadmin_profiles ADD COLUMN IF NOT EXISTS commission_min_percent numeric(7,4) NOT NULL DEFAULT 0 CHECK(commission_min_percent BETWEEN 0 AND 100);
ALTER TABLE subadmin_profiles ADD COLUMN IF NOT EXISTS commission_max_percent numeric(7,4) NOT NULL DEFAULT 100 CHECK(commission_max_percent BETWEEN 0 AND 100);
ALTER TABLE subadmin_profiles DROP CONSTRAINT IF EXISTS subadmin_commission_range_check;
ALTER TABLE subadmin_profiles ADD CONSTRAINT subadmin_commission_range_check CHECK(commission_min_percent <= commission_max_percent);

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['territory_delivery_tariffs','checkout_payment_intents','merchant_settlement_rules','dispatch_optimization_plans'] 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 territory_delivery_tariffs,checkout_payment_intents,merchant_settlement_rules,dispatch_optimization_plans 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['territory_delivery_tariffs','checkout_payment_intents','merchant_settlement_rules','dispatch_optimization_plans'] 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 $$;