-- ClickDelivery V0.7.1 Zones, Direct Merchant Payments, Merchant Coupons & WhatsApp channel
-- Adds hard geographic scope for sub-administration, direct payment review, Express metadata,
-- merchant-owned coupons, zone banners and optional WhatsApp order mirroring.

-- ----------------------------
-- Zone-scoped sub-administration
-- ----------------------------
CREATE TABLE IF NOT EXISTS subadmin_profiles (
  user_id text PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  display_name text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  permissions jsonb NOT NULL DEFAULT '{
    "reports":true,
    "sales_reports":true,
    "delivery_reports":true,
    "payments_review_view":true,
    "tracking":true,
    "orders_status":true,
    "delivery_status":true,
    "drivers_view":true,
    "drivers_create":true,
    "drivers_approve":true,
    "merchants_view":true,
    "merchants_create":true,
    "merchants_approve":true,
    "merchant_commission_manage":true,
    "trips_cancel":true,
    "trips_reassign":true,
    "coupons_view":true,
    "coupons_manage":true,
    "banners_manage":true,
    "subusers_manage":true,
    "payments_review_decide":false
  }'::jsonb,
  parent_subadmin_user_id text REFERENCES users(id) ON DELETE SET NULL,
  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 subadmin_profiles_org_idx ON subadmin_profiles(organization_id,active);

CREATE TABLE IF NOT EXISTS user_territories (
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  territory_id text NOT NULL REFERENCES territories(id) ON DELETE CASCADE,
  created_by_user_id text REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY(user_id,territory_id)
);
CREATE INDEX IF NOT EXISTS user_territories_org_territory_idx ON user_territories(organization_id,territory_id);

CREATE TABLE IF NOT EXISTS driver_territories (
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  driver_id text NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
  territory_id text NOT NULL REFERENCES territories(id) ON DELETE CASCADE,
  created_at timestamptz NOT NULL DEFAULT now(),
  PRIMARY KEY(driver_id,territory_id)
);
CREATE INDEX IF NOT EXISTS driver_territories_org_territory_idx ON driver_territories(organization_id,territory_id);

ALTER TABLE merchant_branches ADD COLUMN IF NOT EXISTS territory_id text REFERENCES territories(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS merchant_branches_territory_idx ON merchant_branches(territory_id);
ALTER TABLE trips ADD COLUMN IF NOT EXISTS territory_id text REFERENCES territories(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS trips_territory_created_idx ON trips(territory_id,created_at DESC);
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS territory_id text REFERENCES territories(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS merchant_orders_territory_created_idx ON merchant_orders(territory_id,created_at DESC);

-- Best-effort backfill from existing PostGIS points. If polygons overlap, choose the oldest active territory.
UPDATE merchant_branches b
   SET territory_id=(SELECT t.id FROM territories t
                      WHERE t.organization_id=b.organization_id AND t.active=true AND b.location IS NOT NULL
                        AND ST_Covers(t.geometry,b.location::geometry)
                      ORDER BY t.created_at ASC LIMIT 1)
 WHERE b.territory_id IS NULL AND b.location IS NOT NULL;

UPDATE trips tr
   SET territory_id=(SELECT t.id FROM territories t
                      WHERE t.organization_id=tr.organization_id AND t.active=true AND tr.pickup_location IS NOT NULL
                        AND ST_Covers(t.geometry,tr.pickup_location::geometry)
                      ORDER BY t.created_at ASC LIMIT 1)
 WHERE tr.territory_id IS NULL AND tr.pickup_location IS NOT NULL;

UPDATE merchant_orders o
   SET territory_id=COALESCE(
     (SELECT b.territory_id FROM merchant_branches b WHERE b.id=o.branch_id),
     (SELECT tr.territory_id FROM trips tr WHERE tr.id=o.trip_id)
   )
 WHERE o.territory_id IS NULL;

-- ----------------------------
-- Direct payments to merchant
-- ----------------------------
-- Direct-payment accounting: when the merchant receives customer funds directly, ClickDelivery
-- must not create a second sale credit. It records platform commission + driver race as merchant debt.
ALTER TABLE merchant_wallet_entries ADD COLUMN IF NOT EXISTS trip_id text REFERENCES trips(id) ON DELETE SET NULL;
ALTER TABLE merchant_wallet_entries DROP CONSTRAINT IF EXISTS merchant_wallet_entries_entry_type_check;
ALTER TABLE merchant_wallet_entries ADD CONSTRAINT merchant_wallet_entries_entry_type_check
  CHECK(entry_type IN ('sale_credit','platform_commission','delivery_fee_debit','merchant_debt_payment','adjustment','refund','settlement_debit'));
CREATE UNIQUE INDEX IF NOT EXISTS merchant_wallet_order_delivery_uidx
  ON merchant_wallet_entries(order_id,entry_type) WHERE order_id IS NOT NULL AND entry_type='delivery_fee_debit';
CREATE UNIQUE INDEX IF NOT EXISTS merchant_wallet_trip_delivery_uidx
  ON merchant_wallet_entries(trip_id,entry_type) WHERE trip_id IS NOT NULL AND entry_type='delivery_fee_debit';

ALTER TABLE private_storage_objects DROP CONSTRAINT IF EXISTS private_storage_objects_purpose_check;
ALTER TABLE private_storage_objects ADD CONSTRAINT private_storage_objects_purpose_check
  CHECK(purpose IN ('pickup_evidence','delivery_evidence','document','avatar','merchant_payment_qr','payment_proof','coupon_asset','other'));
ALTER TABLE private_upload_sessions DROP CONSTRAINT IF EXISTS private_upload_sessions_purpose_check;
ALTER TABLE private_upload_sessions ADD CONSTRAINT private_upload_sessions_purpose_check
  CHECK(purpose IN ('pickup_evidence','delivery_evidence','document','avatar','merchant_payment_qr','payment_proof','coupon_asset','other'));

CREATE TABLE IF NOT EXISTS merchant_payment_profiles (
  merchant_id text PRIMARY KEY REFERENCES merchants(id) ON DELETE CASCADE,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  direct_payment_enabled boolean NOT NULL DEFAULT false,
  instructions text,
  qr_storage_object_id text REFERENCES private_storage_objects(id) ON DELETE SET NULL,
  account_holder text,
  bank_name text,
  account_number_masked text,
  payment_reference_hint text,
  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_payment_profiles_org_idx ON merchant_payment_profiles(organization_id);

ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS payment_destination text NOT NULL DEFAULT 'platform';
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS payment_review_id text;
ALTER TABLE merchant_orders DROP CONSTRAINT IF EXISTS merchant_orders_payment_destination_check;
ALTER TABLE merchant_orders ADD CONSTRAINT merchant_orders_payment_destination_check CHECK(payment_destination IN ('platform','merchant_direct','cash'));
ALTER TABLE merchant_orders DROP CONSTRAINT IF EXISTS merchant_orders_payment_status_check;
ALTER TABLE merchant_orders ADD CONSTRAINT merchant_orders_payment_status_check CHECK(payment_status IN ('pending','pending_review','authorized','paid','failed','partially_refunded','refunded'));

CREATE TABLE IF NOT EXISTS merchant_payment_reviews (
  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,
  order_id text NOT NULL UNIQUE REFERENCES merchant_orders(id) ON DELETE CASCADE,
  merchant_id text NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
  customer_id text NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  amount numeric(12,2) NOT NULL CHECK(amount >= 0),
  currency char(3) NOT NULL DEFAULT 'BOB',
  proof_storage_object_id text REFERENCES private_storage_objects(id) ON DELETE SET NULL,
  customer_reference text,
  status text NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','approved','rejected','cancelled')),
  review_notes text,
  submitted_at timestamptz NOT NULL DEFAULT now(),
  reviewed_by_user_id text REFERENCES users(id) ON DELETE SET NULL,
  reviewed_at timestamptz,
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS merchant_payment_reviews_org_status_idx ON merchant_payment_reviews(organization_id,status,submitted_at DESC);
CREATE INDEX IF NOT EXISTS merchant_payment_reviews_territory_status_idx ON merchant_payment_reviews(territory_id,status,submitted_at DESC);

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

-- ----------------------------
-- Delivery Express / source channel
-- ----------------------------
ALTER TABLE trips ADD COLUMN IF NOT EXISTS service_mode text NOT NULL DEFAULT 'marketplace';
ALTER TABLE trips ADD COLUMN IF NOT EXISTS source_channel text NOT NULL DEFAULT 'clickdelivery';
ALTER TABLE trips DROP CONSTRAINT IF EXISTS trips_service_mode_check;
ALTER TABLE trips ADD CONSTRAINT trips_service_mode_check CHECK(service_mode IN ('marketplace','delivery_express'));
ALTER TABLE trips DROP CONSTRAINT IF EXISTS trips_source_channel_check;
ALTER TABLE trips ADD CONSTRAINT trips_source_channel_check CHECK(source_channel IN ('clickdelivery','whatsapp','admin','api'));

ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS order_source text NOT NULL DEFAULT 'clickdelivery';
ALTER TABLE merchant_orders DROP CONSTRAINT IF EXISTS merchant_orders_order_source_check;
ALTER TABLE merchant_orders ADD CONSTRAINT merchant_orders_order_source_check CHECK(order_source IN ('clickdelivery','whatsapp','delivery_express','admin','api'));

CREATE TABLE IF NOT EXISTS delivery_express_requests (
  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,
  merchant_id text NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
  branch_id text REFERENCES merchant_branches(id) ON DELETE SET NULL,
  requested_by_user_id text REFERENCES users(id) ON DELETE SET NULL,
  customer_name text NOT NULL,
  customer_phone text,
  pickup_address text NOT NULL,
  dropoff_address text NOT NULL,
  pickup_location geography(Point,4326),
  dropoff_location geography(Point,4326),
  package_description text,
  declared_value numeric(12,2) NOT NULL DEFAULT 0,
  delivery_fee numeric(12,2) NOT NULL DEFAULT 0,
  source_channel text NOT NULL DEFAULT 'clickdelivery' CHECK(source_channel IN ('clickdelivery','whatsapp','admin','api')),
  trip_id text UNIQUE REFERENCES trips(id) ON DELETE SET NULL,
  status text NOT NULL DEFAULT 'requested' CHECK(status IN ('requested','created','cancelled','completed')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS delivery_express_org_created_idx ON delivery_express_requests(organization_id,created_at DESC);
CREATE INDEX IF NOT EXISTS delivery_express_territory_created_idx ON delivery_express_requests(territory_id,created_at DESC);

-- ----------------------------
-- Merchant-owned coupons
-- ----------------------------
CREATE TABLE IF NOT EXISTS merchant_coupons (
  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,
  code text NOT NULL,
  name text NOT NULL,
  description text,
  discount_type text NOT NULL CHECK(discount_type IN ('percent','fixed')),
  discount_value numeric(12,2) NOT NULL CHECK(discount_value > 0),
  max_discount numeric(12,2) CHECK(max_discount IS NULL OR max_discount >= 0),
  minimum_order numeric(12,2) NOT NULL DEFAULT 0 CHECK(minimum_order >= 0),
  product_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
  branch_ids jsonb NOT NULL DEFAULT '[]'::jsonb,
  valid_from timestamptz,
  valid_until timestamptz,
  usage_limit integer CHECK(usage_limit IS NULL OR usage_limit > 0),
  per_customer_limit integer NOT NULL DEFAULT 1 CHECK(per_customer_limit > 0),
  usage_count integer NOT NULL DEFAULT 0 CHECK(usage_count >= 0),
  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(merchant_id,code)
);
CREATE INDEX IF NOT EXISTS merchant_coupons_org_merchant_active_idx ON merchant_coupons(organization_id,merchant_id,active);

CREATE TABLE IF NOT EXISTS merchant_coupon_redemptions (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  coupon_id text NOT NULL REFERENCES merchant_coupons(id) ON DELETE CASCADE,
  merchant_id text NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
  customer_id text NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
  order_id text NOT NULL UNIQUE REFERENCES merchant_orders(id) ON DELETE CASCADE,
  discount_amount numeric(12,2) NOT NULL CHECK(discount_amount >= 0),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS coupon_redemptions_coupon_customer_idx ON merchant_coupon_redemptions(coupon_id,customer_id,created_at DESC);

ALTER TABLE checkout_quotes ADD COLUMN IF NOT EXISTS coupon_id text REFERENCES merchant_coupons(id) ON DELETE SET NULL;
ALTER TABLE checkout_quotes ADD COLUMN IF NOT EXISTS coupon_code text;
ALTER TABLE checkout_quotes ADD COLUMN IF NOT EXISTS discount_total numeric(12,2) NOT NULL DEFAULT 0;
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS coupon_id text REFERENCES merchant_coupons(id) ON DELETE SET NULL;
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS coupon_code text;
ALTER TABLE merchant_orders ADD COLUMN IF NOT EXISTS discount_total numeric(12,2) NOT NULL DEFAULT 0;

-- ----------------------------
-- Banners scoped to polygons
-- ----------------------------
CREATE TABLE IF NOT EXISTS banner_territories (
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  banner_id text NOT NULL REFERENCES banners(id) ON DELETE CASCADE,
  territory_id text NOT NULL REFERENCES territories(id) ON DELETE CASCADE,
  PRIMARY KEY(banner_id,territory_id)
);
CREATE INDEX IF NOT EXISTS banner_territories_territory_idx ON banner_territories(territory_id,banner_id);

-- ----------------------------
-- Optional WhatsApp order channel
-- ----------------------------
CREATE TABLE IF NOT EXISTS merchant_channel_settings (
  merchant_id text PRIMARY KEY REFERENCES merchants(id) ON DELETE CASCADE,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  whatsapp_enabled boolean NOT NULL DEFAULT false,
  whatsapp_mode text NOT NULL DEFAULT 'link' CHECK(whatsapp_mode IN ('link','cloud_api')),
  whatsapp_phone text,
  whatsapp_phone_number_id text,
  whatsapp_waba_id text,
  whatsapp_secret_ref text,
  order_notifications boolean NOT NULL DEFAULT true,
  status_notifications boolean NOT NULL DEFAULT false,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS merchant_channel_settings_org_idx ON merchant_channel_settings(organization_id,whatsapp_enabled);

CREATE TABLE IF NOT EXISTS whatsapp_order_outbox (
  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,
  merchant_id text NOT NULL REFERENCES merchants(id) ON DELETE CASCADE,
  order_id text REFERENCES merchant_orders(id) ON DELETE CASCADE,
  express_request_id text REFERENCES delivery_express_requests(id) ON DELETE CASCADE,
  destination_phone text NOT NULL,
  message_type text NOT NULL DEFAULT 'order_created',
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  status text NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','sending','sent','failed','cancelled')),
  attempts integer NOT NULL DEFAULT 0,
  available_at timestamptz NOT NULL DEFAULT now(),
  last_error text,
  sent_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS whatsapp_outbox_status_idx ON whatsapp_order_outbox(status,available_at,created_at);

-- RLS follows organization isolation; geographic restrictions are additionally enforced in application queries.
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'subadmin_profiles','user_territories','driver_territories','merchant_payment_profiles','merchant_payment_reviews',
    'delivery_express_requests','merchant_coupons','merchant_coupon_redemptions','banner_territories',
    'merchant_channel_settings','whatsapp_order_outbox'
  ] 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 subadmin_profiles,user_territories,driver_territories,merchant_payment_profiles,
      merchant_payment_reviews,delivery_express_requests,merchant_coupons,merchant_coupon_redemptions,banner_territories,
      merchant_channel_settings,whatsapp_order_outbox 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['subadmin_profiles','merchant_payment_profiles','merchant_payment_reviews','delivery_express_requests','merchant_coupons','merchant_channel_settings','whatsapp_order_outbox'] 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 $$;