-- ClickDelivery Platform PostgreSQL/PostGIS aggregate schema
-- Generated from ordered migrations. Use scripts/migrate-postgres.js in production.


-- ===== 001_v04_core.sql =====
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS postgis;

CREATE TABLE IF NOT EXISTS settings (
  key text PRIMARY KEY,
  value jsonb NOT NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS organizations (
  id text PRIMARY KEY,
  mode text NOT NULL CHECK (mode IN ('white_label','franchise')),
  brand_name text NOT NULL,
  legal_name text NOT NULL,
  nit text,
  department text NOT NULL,
  city text NOT NULL,
  city_class text NOT NULL CHECK (city_class IN ('pequena','intermedia','grande')),
  status text NOT NULL CHECK (status IN ('pending','trial','active','suspended','rejected','terminated')) DEFAULT 'pending',
  trip_fee numeric(12,2) NOT NULL DEFAULT 0,
  franchise_percent numeric(7,4) NOT NULL DEFAULT 0,
  primary_color text NOT NULL DEFAULT '#ff411f',
  secondary_color text NOT NULL DEFAULT '#0f172a',
  logo_url text,
  contract_status text NOT NULL DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS organizations_mode_status_idx ON organizations(mode,status);
CREATE INDEX IF NOT EXISTS organizations_city_idx ON organizations(department,city);

CREATE TABLE IF NOT EXISTS users (
  id text PRIMARY KEY,
  organization_id text REFERENCES organizations(id) ON DELETE CASCADE,
  email text UNIQUE NOT NULL,
  name text NOT NULL,
  role text NOT NULL,
  password_salt text NOT NULL,
  password_hash text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS users_org_idx ON users(organization_id);

CREATE TABLE IF NOT EXISTS sessions (
  token_hash text PRIMARY KEY,
  user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  expires_at timestamptz NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS sessions_user_idx ON sessions(user_id);
CREATE INDEX IF NOT EXISTS sessions_exp_idx ON sessions(expires_at);

CREATE TABLE IF NOT EXISTS contracts (
  id text PRIMARY KEY,
  version text UNIQUE NOT NULL,
  title text NOT NULL,
  body text NOT NULL,
  body_hash text NOT NULL,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS contract_acceptances (
  id text PRIMARY KEY,
  contract_id text NOT NULL REFERENCES contracts(id),
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  signer_name text NOT NULL,
  signer_document text NOT NULL,
  accepted_at timestamptz NOT NULL DEFAULT now(),
  ip inet,
  user_agent text,
  body_hash text NOT NULL
);

CREATE TABLE IF NOT EXISTS documents (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  filename text NOT NULL,
  doc_type text NOT NULL,
  storage_key text,
  status text NOT NULL CHECK(status IN ('pending','approved','rejected')) DEFAULT 'pending',
  review_notes text,
  created_at timestamptz NOT NULL DEFAULT now(),
  reviewed_at timestamptz
);
CREATE INDEX IF NOT EXISTS documents_org_status_idx ON documents(organization_id,status);

CREATE TABLE IF NOT EXISTS merchants (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name text NOT NULL,
  status text NOT NULL CHECK(status IN ('pending','active','suspended','rejected')) DEFAULT 'pending',
  orders integer NOT NULL DEFAULT 0,
  balance numeric(12,2) NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS merchants_org_status_idx ON merchants(organization_id,status);

CREATE TABLE IF NOT EXISTS merchant_branches (
  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,
  name text NOT NULL,
  address text,
  location geography(Point,4326),
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS merchant_branches_org_idx ON merchant_branches(organization_id);
CREATE INDEX IF NOT EXISTS merchant_branches_location_gix ON merchant_branches USING GIST(location);

CREATE TABLE IF NOT EXISTS drivers (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name text NOT NULL,
  email text,
  phone text,
  vehicle text NOT NULL DEFAULT 'Moto',
  approval text NOT NULL CHECK(approval IN ('pending','approved','rejected')) DEFAULT 'pending',
  availability text NOT NULL CHECK(availability IN ('online','offline','busy')) DEFAULT 'offline',
  rating numeric(4,2) NOT NULL DEFAULT 0,
  trips integer NOT NULL DEFAULT 0,
  current_location geography(Point,4326),
  last_location_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS drivers_org_availability_idx ON drivers(organization_id,availability);
CREATE INDEX IF NOT EXISTS drivers_location_gix ON drivers USING GIST(current_location);

CREATE TABLE IF NOT EXISTS driver_locations (
  id bigserial PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  driver_id text NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
  location geography(Point,4326) NOT NULL,
  accuracy_m numeric(10,2),
  speed_kmh numeric(10,2),
  heading numeric(7,2),
  captured_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS driver_locations_driver_time_idx ON driver_locations(driver_id,captured_at DESC);
CREATE INDEX IF NOT EXISTS driver_locations_location_gix ON driver_locations USING GIST(location);

CREATE TABLE IF NOT EXISTS territories (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name text NOT NULL,
  kind text NOT NULL DEFAULT 'service_area',
  geometry geometry(MultiPolygon,4326) NOT NULL,
  exclusive boolean NOT NULL DEFAULT false,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS territories_org_idx ON territories(organization_id);
CREATE INDEX IF NOT EXISTS territories_geometry_gix ON territories USING GIST(geometry);

CREATE TABLE IF NOT EXISTS trips (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  external_reference text,
  merchant_id text REFERENCES merchants(id),
  branch_id text REFERENCES merchant_branches(id),
  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),
  driver_id text REFERENCES drivers(id),
  amount numeric(12,2) NOT NULL DEFAULT 0,
  delivery_fee numeric(12,2) NOT NULL DEFAULT 0,
  status text NOT NULL CHECK(status IN ('requested','searching','assigned','en_route_store','at_store','picked_up','en_route_customer','at_customer','completed','cancelled','failed')) DEFAULT 'requested',
  platform_fee numeric(12,2) NOT NULL DEFAULT 0,
  pickup_pin text,
  delivery_pin text,
  created_at timestamptz NOT NULL DEFAULT now(),
  completed_at timestamptz
);
CREATE UNIQUE INDEX IF NOT EXISTS trips_org_ext_ref_uq ON trips(organization_id,external_reference) WHERE external_reference IS NOT NULL;
CREATE INDEX IF NOT EXISTS trips_org_created_idx ON trips(organization_id,created_at DESC);
CREATE INDEX IF NOT EXISTS trips_pickup_gix ON trips USING GIST(pickup_location);
CREATE INDEX IF NOT EXISTS trips_dropoff_gix ON trips USING GIST(dropoff_location);

CREATE TABLE IF NOT EXISTS trip_events (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text NOT NULL REFERENCES trips(id) ON DELETE CASCADE,
  event_type text NOT NULL,
  actor_user_id text,
  detail_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS trip_events_trip_created_idx ON trip_events(trip_id,created_at);

CREATE TABLE IF NOT EXISTS charges (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text REFERENCES trips(id),
  charge_type text NOT NULL,
  amount numeric(12,2) NOT NULL,
  currency char(3) NOT NULL DEFAULT 'BOB',
  status text NOT NULL CHECK(status IN ('pending','invoiced','paid','void')) DEFAULT 'pending',
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS charges_trip_type_uq ON charges(trip_id,charge_type) WHERE trip_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS charges_org_created_idx ON charges(organization_id,created_at DESC);

CREATE TABLE IF NOT EXISTS ledger_entries (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text REFERENCES trips(id),
  entry_type text NOT NULL,
  amount numeric(12,2) NOT NULL,
  currency char(3) NOT NULL DEFAULT 'BOB',
  reference text,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS ledger_org_created_idx ON ledger_entries(organization_id,created_at DESC);

CREATE TABLE IF NOT EXISTS banners (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  title text NOT NULL,
  placement text NOT NULL DEFAULT 'Inicio',
  image_url text,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS app_builds (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  app text NOT NULL,
  platform text NOT NULL,
  package_id text,
  version text,
  status text NOT NULL DEFAULT 'planned',
  artifact_url text,
  config_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS app_builds_org_idx ON app_builds(organization_id);

CREATE TABLE IF NOT EXISTS webhook_endpoints (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  url text NOT NULL,
  secret_ciphertext text NOT NULL,
  events text[] NOT NULL DEFAULT '{}',
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS audit_logs (
  id text PRIMARY KEY,
  actor_user_id text,
  organization_id text REFERENCES organizations(id) ON DELETE SET NULL,
  action text NOT NULL,
  entity_type text,
  entity_id text,
  detail_json jsonb NOT NULL DEFAULT '{}'::jsonb,
  ip inet,
  correlation_id uuid NOT NULL DEFAULT gen_random_uuid(),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS audit_org_created_idx ON audit_logs(organization_id,created_at DESC);

CREATE TABLE IF NOT EXISTS idempotency_keys (
  scope text NOT NULL,
  route_key text NOT NULL,
  idem_key text NOT NULL,
  request_hash text NOT NULL,
  response_json jsonb,
  status_code integer,
  created_at timestamptz NOT NULL DEFAULT now(),
  expires_at timestamptz NOT NULL DEFAULT now() + interval '24 hours',
  PRIMARY KEY(scope,route_key,idem_key)
);
CREATE INDEX IF NOT EXISTS idempotency_exp_idx ON idempotency_keys(expires_at);

INSERT INTO settings(key,value) VALUES
  ('platformName','"ClickDelivery Platform"'::jsonb),
  ('currency','"BOB"'::jsonb),
  ('smallTripFee','0.99'::jsonb),
  ('largeTripFee','1.39'::jsonb),
  ('franchisePercent','8'::jsonb)
ON CONFLICT(key) DO NOTHING;


-- ===== 002_v04_rls.sql =====
CREATE OR REPLACE FUNCTION app_tenant_allowed(row_org text)
RETURNS boolean
LANGUAGE sql
STABLE
AS $$
  SELECT
    COALESCE(current_setting('app.is_superadmin', true), 'false') = 'true'
    OR row_org = NULLIF(current_setting('app.organization_id', true), '');
$$;

DO $$
DECLARE
  t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'contract_acceptances','documents','merchants','merchant_branches','drivers','driver_locations',
    'territories','trips','trip_events','charges','ledger_entries','banners','audit_logs','app_builds','webhook_endpoints'
  ]
  LOOP
    IF to_regclass(format('public.%I', t)) IS NOT NULL THEN
      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 IF;
  END LOOP;
END $$;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname = 'clickdelivery_app') THEN
    GRANT USAGE ON SCHEMA public TO clickdelivery_app;
    GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO clickdelivery_app;
    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO clickdelivery_app;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO clickdelivery_app;
    ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT USAGE, SELECT ON SEQUENCES TO clickdelivery_app;
  END IF;
END $$;


-- ===== 003_v04_legacy_foundation_upgrade.sql =====
-- Compatibilidad con la primera base de migración V0.4 generada antes de convertir server.js.
-- Es idempotente y también es segura en instalaciones nuevas.

ALTER TABLE idempotency_keys ADD COLUMN IF NOT EXISTS request_hash text;
ALTER TABLE idempotency_keys ADD COLUMN IF NOT EXISTS expires_at timestamptz NOT NULL DEFAULT now() + interval '24 hours';
ALTER TABLE idempotency_keys ALTER COLUMN response_json DROP NOT NULL;
ALTER TABLE idempotency_keys ALTER COLUMN status_code DROP NOT NULL;
UPDATE idempotency_keys SET request_hash='legacy' WHERE request_hash IS NULL;
ALTER TABLE idempotency_keys ALTER COLUMN request_hash SET NOT NULL;
CREATE INDEX IF NOT EXISTS idempotency_exp_idx ON idempotency_keys(expires_at);

DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema='public' AND table_name='app_builds' AND column_name='app_type'
  ) AND NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_schema='public' AND table_name='app_builds' AND column_name='app'
  ) THEN
    ALTER TABLE app_builds RENAME COLUMN app_type TO app;
  END IF;
END $$;
ALTER TABLE app_builds ADD COLUMN IF NOT EXISTS package_id text;
ALTER TABLE app_builds ADD COLUMN IF NOT EXISTS artifact_url text;
ALTER TABLE app_builds ADD COLUMN IF NOT EXISTS config_snapshot jsonb NOT NULL DEFAULT '{}'::jsonb;

CREATE TABLE IF NOT EXISTS driver_locations (
  id bigserial PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  driver_id text NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
  location geography(Point,4326) NOT NULL,
  accuracy_m numeric(10,2),
  speed_kmh numeric(10,2),
  heading numeric(7,2),
  captured_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS driver_locations_driver_time_idx ON driver_locations(driver_id,captured_at DESC);
CREATE INDEX IF NOT EXISTS driver_locations_location_gix ON driver_locations USING GIST(location);

CREATE TABLE IF NOT EXISTS banners (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  title text NOT NULL,
  placement text NOT NULL DEFAULT 'Inicio',
  image_url text,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now()
);

CREATE UNIQUE INDEX IF NOT EXISTS charges_trip_type_uq ON charges(trip_id,charge_type) WHERE trip_id IS NOT NULL;
CREATE INDEX IF NOT EXISTS charges_org_created_idx ON charges(organization_id,created_at DESC);
CREATE INDEX IF NOT EXISTS app_builds_org_idx ON app_builds(organization_id);

-- Si driver_locations/banners fueron creadas después de 002, aplicar RLS aquí también.
DO $$
DECLARE
  t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['driver_locations','banners']
  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 driver_locations, banners TO clickdelivery_app;
    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO clickdelivery_app;
  END IF;
END $$;


-- ===== 004_v04_logistics_core.sql =====
-- ClickDelivery V0.4 Logistics Core
-- Geocercas, auto-asignacion, tracking, evidencia y perfiles para APK.

CREATE TABLE IF NOT EXISTS logistics_settings (
  organization_id text PRIMARY KEY REFERENCES organizations(id) ON DELETE CASCADE,
  assignment_radius_m integer NOT NULL DEFAULT 8000 CHECK (assignment_radius_m BETWEEN 500 AND 50000),
  assignment_max_candidates integer NOT NULL DEFAULT 10 CHECK (assignment_max_candidates BETWEEN 1 AND 50),
  stale_location_seconds integer NOT NULL DEFAULT 120 CHECK (stale_location_seconds BETWEEN 15 AND 3600),
  pickup_geofence_radius_m integer NOT NULL DEFAULT 120 CHECK (pickup_geofence_radius_m BETWEEN 20 AND 2000),
  dropoff_geofence_radius_m integer NOT NULL DEFAULT 150 CHECK (dropoff_geofence_radius_m BETWEEN 20 AND 2000),
  require_pickup_geofence boolean NOT NULL DEFAULT true,
  require_dropoff_geofence boolean NOT NULL DEFAULT true,
  auto_advance_geofence boolean NOT NULL DEFAULT true,
  require_pickup_pin boolean NOT NULL DEFAULT true,
  require_delivery_pin boolean NOT NULL DEFAULT true,
  require_pickup_evidence boolean NOT NULL DEFAULT false,
  require_delivery_evidence boolean NOT NULL DEFAULT true,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS customers (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  name text NOT NULL,
  phone text,
  email text,
  status text NOT NULL DEFAULT 'active' CHECK(status IN ('active','blocked')),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS customers_org_phone_idx ON customers(organization_id, phone);
CREATE INDEX IF NOT EXISTS customers_org_email_idx ON customers(organization_id, lower(email));

CREATE TABLE IF NOT EXISTS app_identities (
  user_id text PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  actor_type text NOT NULL CHECK(actor_type IN ('customer','driver','merchant','dispatcher','admin')),
  actor_id text,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(organization_id, actor_type, actor_id)
);
CREATE INDEX IF NOT EXISTS app_identities_org_type_idx ON app_identities(organization_id, actor_type);

ALTER TABLE trips ADD COLUMN IF NOT EXISTS customer_id text REFERENCES customers(id) ON DELETE SET NULL;
ALTER TABLE trips ADD COLUMN IF NOT EXISTS assigned_at timestamptz;
ALTER TABLE trips ADD COLUMN IF NOT EXISTS accepted_at timestamptz;
ALTER TABLE trips ADD COLUMN IF NOT EXISTS pickup_geofence_entered_at timestamptz;
ALTER TABLE trips ADD COLUMN IF NOT EXISTS dropoff_geofence_entered_at timestamptz;
ALTER TABLE trips ADD COLUMN IF NOT EXISTS pickup_verified_at timestamptz;
ALTER TABLE trips ADD COLUMN IF NOT EXISTS delivery_verified_at timestamptz;
ALTER TABLE trips ADD COLUMN IF NOT EXISTS assignment_score numeric(8,3);
CREATE INDEX IF NOT EXISTS trips_customer_created_idx ON trips(customer_id, created_at DESC);
CREATE INDEX IF NOT EXISTS trips_driver_status_idx ON trips(driver_id, status, created_at DESC);

CREATE TABLE IF NOT EXISTS trip_assignment_candidates (
  id bigserial PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text NOT NULL REFERENCES trips(id) ON DELETE CASCADE,
  driver_id text NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
  rank integer NOT NULL,
  distance_m integer NOT NULL,
  score numeric(8,3) NOT NULL,
  score_detail jsonb NOT NULL DEFAULT '{}'::jsonb,
  status text NOT NULL DEFAULT 'ranked' CHECK(status IN ('ranked','assigned','skipped','rejected','expired')),
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(trip_id,driver_id)
);
CREATE INDEX IF NOT EXISTS trip_assignment_candidates_trip_rank_idx ON trip_assignment_candidates(trip_id,rank);

CREATE TABLE IF NOT EXISTS trip_tracking_points (
  id bigserial PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text NOT NULL REFERENCES trips(id) ON DELETE CASCADE,
  driver_id text NOT NULL REFERENCES drivers(id) ON DELETE CASCADE,
  location geography(Point,4326) NOT NULL,
  accuracy_m numeric(10,2),
  speed_kmh numeric(10,2),
  heading numeric(7,2),
  captured_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS trip_tracking_points_trip_time_idx ON trip_tracking_points(trip_id,captured_at DESC);
CREATE INDEX IF NOT EXISTS trip_tracking_points_location_gix ON trip_tracking_points USING GIST(location);

CREATE TABLE IF NOT EXISTS trip_geofence_events (
  id bigserial PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text NOT NULL REFERENCES trips(id) ON DELETE CASCADE,
  driver_id text REFERENCES drivers(id) ON DELETE SET NULL,
  stage text NOT NULL CHECK(stage IN ('pickup','dropoff')),
  event_type text NOT NULL DEFAULT 'entered' CHECK(event_type IN ('entered','manual_override')),
  distance_m numeric(12,2),
  radius_m integer,
  location geography(Point,4326),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS trip_geofence_events_trip_time_idx ON trip_geofence_events(trip_id,created_at DESC);

CREATE TABLE IF NOT EXISTS trip_delivery_proofs (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text NOT NULL REFERENCES trips(id) ON DELETE CASCADE,
  stage text NOT NULL CHECK(stage IN ('pickup','delivery')),
  proof_type text NOT NULL CHECK(proof_type IN ('photo','signature','document','note','other')),
  storage_key text,
  content_sha256 text,
  mime_type text,
  size_bytes bigint,
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_by_user_id text REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS trip_delivery_proofs_trip_stage_idx ON trip_delivery_proofs(trip_id,stage,created_at DESC);

-- Realtime durable: cada evento operativo persiste en trip_events; esta tabla solo registra
-- clientes conectables si en el futuro se habilitan tickets anonimizados/efimeros.
CREATE TABLE IF NOT EXISTS realtime_stream_audit (
  id bigserial PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id text REFERENCES users(id) ON DELETE SET NULL,
  trip_id text REFERENCES trips(id) ON DELETE CASCADE,
  action text NOT NULL CHECK(action IN ('connected','disconnected')),
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS realtime_stream_audit_trip_time_idx ON realtime_stream_audit(trip_id,created_at DESC);

-- RLS para las tablas nuevas.
DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'logistics_settings','customers','app_identities','trip_assignment_candidates','trip_tracking_points',
    'trip_geofence_events','trip_delivery_proofs','realtime_stream_audit'
  ]
  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 logistics_settings, customers, app_identities,
      trip_assignment_candidates, trip_tracking_points, trip_geofence_events,
      trip_delivery_proofs, realtime_stream_audit TO clickdelivery_app;
    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO clickdelivery_app;
  END IF;
END $$;


-- ===== 005_v05_mobile_operations.sql =====
-- ClickDelivery V0.5 Mobile & Operations
-- Storage privado, dispositivos/notificaciones, realtime ampliado, bootstrap APK e integraciones desacopladas.

CREATE TABLE IF NOT EXISTS private_storage_objects (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text REFERENCES trips(id) ON DELETE CASCADE,
  purpose text NOT NULL CHECK(purpose IN ('pickup_evidence','delivery_evidence','document','avatar','other')),
  storage_key text NOT NULL UNIQUE,
  relative_path text NOT NULL,
  mime_type text NOT NULL,
  size_bytes bigint NOT NULL CHECK(size_bytes >= 0),
  content_sha256 text NOT NULL CHECK(content_sha256 ~ '^[a-f0-9]{64}$'),
  original_filename text,
  status text NOT NULL DEFAULT 'ready' CHECK(status IN ('ready','quarantined','deleted')),
  created_by_user_id text REFERENCES users(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  deleted_at timestamptz
);
CREATE INDEX IF NOT EXISTS private_storage_objects_org_created_idx ON private_storage_objects(organization_id,created_at DESC);
CREATE INDEX IF NOT EXISTS private_storage_objects_trip_created_idx ON private_storage_objects(trip_id,created_at DESC);

CREATE TABLE IF NOT EXISTS private_upload_sessions (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  trip_id text REFERENCES trips(id) ON DELETE CASCADE,
  purpose text NOT NULL CHECK(purpose IN ('pickup_evidence','delivery_evidence','document','avatar','other')),
  expected_mime_type text NOT NULL,
  expected_size_bytes bigint CHECK(expected_size_bytes IS NULL OR expected_size_bytes >= 0),
  max_size_bytes bigint NOT NULL DEFAULT 25000000 CHECK(max_size_bytes BETWEEN 1 AND 50000000),
  original_filename text,
  upload_token_hash text NOT NULL,
  status text NOT NULL DEFAULT 'pending' CHECK(status IN ('pending','uploading','completed','failed','expired')),
  object_id text REFERENCES private_storage_objects(id) ON DELETE SET NULL,
  expires_at timestamptz NOT 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 private_upload_sessions_org_created_idx ON private_upload_sessions(organization_id,created_at DESC);
CREATE INDEX IF NOT EXISTS private_upload_sessions_exp_idx ON private_upload_sessions(expires_at,status);

CREATE TABLE IF NOT EXISTS mobile_devices (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  app_role text NOT NULL CHECK(app_role IN ('administrator','dispatcher','merchant','customer','driver')),
  platform text NOT NULL CHECK(platform IN ('android','ios','web')),
  provider text NOT NULL DEFAULT 'gateway' CHECK(provider IN ('gateway','fcm','apns','web')),
  push_token text NOT NULL,
  device_name text,
  app_version text,
  locale text,
  active boolean NOT NULL DEFAULT true,
  last_seen_at timestamptz NOT NULL DEFAULT now(),
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(user_id,push_token)
);
CREATE INDEX IF NOT EXISTS mobile_devices_org_user_idx ON mobile_devices(organization_id,user_id,active);

CREATE TABLE IF NOT EXISTS notifications (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  trip_id text REFERENCES trips(id) ON DELETE CASCADE,
  category text NOT NULL DEFAULT 'operational',
  title text NOT NULL,
  body text NOT NULL,
  data jsonb NOT NULL DEFAULT '{}'::jsonb,
  read_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS notifications_user_created_idx ON notifications(user_id,created_at DESC);
CREATE INDEX IF NOT EXISTS notifications_user_unread_idx ON notifications(user_id,created_at DESC) WHERE read_at IS NULL;

CREATE TABLE IF NOT EXISTS push_outbox (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  notification_id text NOT NULL REFERENCES notifications(id) ON DELETE CASCADE,
  device_id text NOT NULL REFERENCES mobile_devices(id) ON DELETE CASCADE,
  status text NOT NULL DEFAULT 'queued' CHECK(status IN ('queued','sending','sent','retry','failed','skipped')),
  attempts integer NOT NULL DEFAULT 0,
  available_at timestamptz NOT NULL DEFAULT now(),
  last_error text,
  provider_response jsonb,
  sent_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(notification_id,device_id)
);
CREATE INDEX IF NOT EXISTS push_outbox_status_available_idx ON push_outbox(status,available_at,created_at);

CREATE TABLE IF NOT EXISTS mobile_feature_flags (
  organization_id text PRIMARY KEY REFERENCES organizations(id) ON DELETE CASCADE,
  realtime_sse boolean NOT NULL DEFAULT true,
  private_evidence_storage boolean NOT NULL DEFAULT true,
  push_notifications boolean NOT NULL DEFAULT true,
  live_dispatch_map boolean NOT NULL DEFAULT true,
  payments_connector boolean NOT NULL DEFAULT false,
  updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS integration_connectors (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  connector_type text NOT NULL CHECK(connector_type IN ('manager_pay','chefmanager_ai','custom')),
  name text NOT NULL,
  base_url text,
  secret_ref text,
  enabled boolean NOT NULL DEFAULT false,
  status text NOT NULL DEFAULT 'not_configured' CHECK(status IN ('not_configured','ready','degraded','disabled')),
  config jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(organization_id,connector_type)
);

CREATE TABLE IF NOT EXISTS integration_outbox (
  id text PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  connector_type text NOT NULL CHECK(connector_type IN ('manager_pay','chefmanager_ai','custom')),
  event_type text NOT NULL,
  aggregate_type text NOT NULL,
  aggregate_id text NOT NULL,
  payload jsonb NOT NULL,
  status text NOT NULL DEFAULT 'queued' CHECK(status IN ('queued','sending','sent','retry','failed','skipped')),
  attempts integer NOT NULL DEFAULT 0,
  available_at timestamptz NOT NULL DEFAULT now(),
  last_error text,
  response_json jsonb,
  sent_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(connector_type,event_type,aggregate_type,aggregate_id)
);
CREATE INDEX IF NOT EXISTS integration_outbox_status_available_idx ON integration_outbox(status,available_at,created_at);

-- V0.5 enlaza evidencia verificada con un objeto privado real cuando corresponda.
ALTER TABLE trip_delivery_proofs ADD COLUMN IF NOT EXISTS storage_object_id text REFERENCES private_storage_objects(id) ON DELETE SET NULL;
CREATE INDEX IF NOT EXISTS trip_delivery_proofs_storage_object_idx ON trip_delivery_proofs(storage_object_id);

-- La auditoría realtime ahora también puede representar streams de organización/usuario.
ALTER TABLE realtime_stream_audit ADD COLUMN IF NOT EXISTS stream_scope text NOT NULL DEFAULT 'trip';
ALTER TABLE realtime_stream_audit ADD COLUMN IF NOT EXISTS stream_key text;

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'private_storage_objects','private_upload_sessions','mobile_devices','notifications','push_outbox',
    'mobile_feature_flags','integration_connectors','integration_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 private_storage_objects,private_upload_sessions,mobile_devices,
      notifications,push_outbox,mobile_feature_flags,integration_connectors,integration_outbox TO clickdelivery_app;
    GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO clickdelivery_app;
  END IF;
END $$;


-- ===== 006_v05_mobile_hardening.sql =====
-- ClickDelivery V0.5 hardening
-- Un token push activo solo puede pertenecer a un dispositivo/usuario a la vez por proveedor.
CREATE UNIQUE INDEX IF NOT EXISTS mobile_devices_active_push_token_uidx
  ON mobile_devices(provider,push_token)
  WHERE active=true;


-- ===== 007_v06_apk_integration.sql =====
-- ClickDelivery V0.6 APK Integration

ALTER TABLE mobile_feature_flags
  ADD COLUMN IF NOT EXISTS realtime_websocket boolean NOT NULL DEFAULT true;

UPDATE mobile_feature_flags SET realtime_websocket=true WHERE realtime_websocket IS DISTINCT FROM true;

-- Corrige los package IDs provisionales V0.5 usando los AndroidManifest reales auditados.
UPDATE app_builds SET package_id='com.clickdelivery.administrador'
 WHERE package_id IN ('bo.clickdelivery.admin','com.clickdelivery.administrador.2');
UPDATE app_builds SET package_id='com.clickdelivery.comercio'
 WHERE package_id IN ('bo.clickdelivery.comercio','com.clickdelivery.comercio.7');
UPDATE app_builds SET package_id='com.clickdelivery.usuario'
 WHERE package_id IN ('bo.clickdelivery.delivery','com.clickdelivery.usuario.3');
UPDATE app_builds SET package_id='com.clickdelivery.repartidor'
 WHERE package_id IN ('bo.clickdelivery.repartidor','com.clickdelivery.repartidor.1');


-- ===== 008_v061_mobile_production.sql =====
-- ClickDelivery V0.6.1 Backend Mobile Production
-- Refresh tokens, sincronizacion offline, GPS batch, catalogo/pedidos, marketplace y ganancias/liquidaciones.

CREATE TABLE IF NOT EXISTS mobile_refresh_tokens (
  id text PRIMARY KEY,
  organization_id text REFERENCES organizations(id) ON DELETE CASCADE,
  user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  device_id text REFERENCES mobile_devices(id) ON DELETE SET NULL,
  token_hash text NOT NULL UNIQUE,
  expires_at timestamptz NOT NULL,
  last_used_at timestamptz,
  revoked_at timestamptz,
  rotated_from_id text REFERENCES mobile_refresh_tokens(id) ON DELETE SET NULL,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS mobile_refresh_tokens_user_idx ON mobile_refresh_tokens(user_id,expires_at DESC);
CREATE INDEX IF NOT EXISTS mobile_refresh_tokens_exp_idx ON mobile_refresh_tokens(expires_at) WHERE revoked_at IS NULL;

CREATE TABLE IF NOT EXISTS mobile_sync_operations (
  id bigserial PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  user_id text NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  device_id text REFERENCES mobile_devices(id) ON DELETE SET NULL,
  client_operation_id text NOT NULL,
  operation_type text NOT NULL,
  payload jsonb NOT NULL DEFAULT '{}'::jsonb,
  status text NOT NULL DEFAULT 'received' CHECK(status IN ('received','applied','rejected','failed')),
  result jsonb,
  error_code text,
  captured_at timestamptz,
  processed_at timestamptz,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE(user_id,client_operation_id)
);
CREATE INDEX IF NOT EXISTS mobile_sync_operations_user_created_idx ON mobile_sync_operations(user_id,created_at DESC);

ALTER TABLE driver_locations ADD COLUMN IF NOT EXISTS client_point_id text;
CREATE UNIQUE INDEX IF NOT EXISTS driver_locations_client_point_uidx
  ON driver_locations(driver_id,client_point_id) WHERE client_point_id IS NOT NULL;

ALTER TABLE trip_tracking_points ADD COLUMN IF NOT EXISTS client_point_id text;
CREATE UNIQUE INDEX IF NOT EXISTS trip_tracking_points_client_point_uidx
  ON trip_tracking_points(driver_id,client_point_id) WHERE client_point_id IS NOT NULL;

ALTER TABLE trips ADD COLUMN IF NOT EXISTS updated_at timestamptz NOT NULL DEFAULT now();

ALTER TABLE logistics_settings ADD COLUMN IF NOT EXISTS default_delivery_fee numeric(12,2) NOT NULL DEFAULT 0 CHECK(default_delivery_fee >= 0);

CREATE TABLE IF NOT EXISTS merchant_products (
  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,
  name text NOT NULL,
  description text,
  category text,
  price numeric(12,2) NOT NULL CHECK(price >= 0),
  currency char(3) NOT NULL DEFAULT 'BOB',
  image_url text,
  stock_status text NOT NULL DEFAULT 'available' CHECK(stock_status IN ('available','out_of_stock','hidden')),
  active boolean NOT NULL DEFAULT true,
  sort_order integer NOT NULL DEFAULT 0,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS merchant_products_org_merchant_idx ON merchant_products(organization_id,merchant_id,active,sort_order);

CREATE TABLE IF NOT EXISTS customer_addresses (
  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,
  label text,
  address text NOT NULL,
  location geography(Point,4326),
  reference text,
  is_default boolean NOT NULL DEFAULT false,
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS customer_addresses_customer_idx ON customer_addresses(customer_id,active);
CREATE INDEX IF NOT EXISTS customer_addresses_location_gix ON customer_addresses USING GIST(location);

CREATE TABLE IF NOT EXISTS merchant_orders (
  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,
  branch_id text REFERENCES merchant_branches(id) ON DELETE SET NULL,
  customer_id text NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  trip_id text UNIQUE REFERENCES trips(id) ON DELETE SET NULL,
  external_reference text,
  status text NOT NULL DEFAULT 'pending_confirmation'
    CHECK(status IN ('pending_confirmation','confirmed','preparing','ready','completed','cancelled')),
  payment_status text NOT NULL DEFAULT 'pending'
    CHECK(payment_status IN ('pending','authorized','paid','failed','partially_refunded','refunded')),
  payment_method text,
  subtotal numeric(12,2) NOT NULL DEFAULT 0 CHECK(subtotal >= 0),
  delivery_fee numeric(12,2) NOT NULL DEFAULT 0 CHECK(delivery_fee >= 0),
  total numeric(12,2) NOT NULL DEFAULT 0 CHECK(total >= 0),
  currency char(3) NOT NULL DEFAULT 'BOB',
  delivery_address text NOT NULL,
  delivery_location geography(Point,4326),
  delivery_reference text,
  notes text,
  cancel_reason text,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE UNIQUE INDEX IF NOT EXISTS merchant_orders_org_ext_ref_uidx
  ON merchant_orders(organization_id,external_reference) WHERE external_reference IS NOT NULL;
CREATE INDEX IF NOT EXISTS merchant_orders_customer_created_idx ON merchant_orders(customer_id,created_at DESC);
CREATE INDEX IF NOT EXISTS merchant_orders_merchant_status_idx ON merchant_orders(merchant_id,status,created_at DESC);

CREATE TABLE IF NOT EXISTS merchant_order_items (
  id bigserial PRIMARY KEY,
  organization_id text NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  order_id text NOT NULL REFERENCES merchant_orders(id) ON DELETE CASCADE,
  product_id text REFERENCES merchant_products(id) ON DELETE SET NULL,
  product_name text NOT NULL,
  quantity integer NOT NULL CHECK(quantity BETWEEN 1 AND 999),
  unit_price numeric(12,2) NOT NULL CHECK(unit_price >= 0),
  line_total numeric(12,2) NOT NULL CHECK(line_total >= 0),
  metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
  created_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS merchant_order_items_order_idx ON merchant_order_items(order_id,id);

CREATE TABLE IF NOT EXISTS driver_wallet_entries (
  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 CASCADE,
  trip_id text REFERENCES trips(id) ON DELETE SET NULL,
  entry_type text NOT NULL CHECK(entry_type IN ('delivery_earning','bonus','adjustment','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 driver_wallet_trip_earning_uidx
  ON driver_wallet_entries(trip_id,entry_type) WHERE trip_id IS NOT NULL AND entry_type='delivery_earning';
CREATE INDEX IF NOT EXISTS driver_wallet_driver_created_idx ON driver_wallet_entries(driver_id,created_at DESC);

CREATE TABLE IF NOT EXISTS driver_settlements (
  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 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 driver_settlements_driver_created_idx ON driver_settlements(driver_id,created_at DESC);

ALTER TABLE driver_wallet_entries
  DROP CONSTRAINT IF EXISTS driver_wallet_entries_settlement_fk;
ALTER TABLE driver_wallet_entries
  ADD CONSTRAINT driver_wallet_entries_settlement_fk
  FOREIGN KEY(settlement_id) REFERENCES driver_settlements(id) ON DELETE SET NULL;

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY[
    'mobile_sync_operations','merchant_products','customer_addresses','merchant_orders',
    'merchant_order_items','driver_wallet_entries','driver_settlements'
  ]
  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 $$;

-- Refresh tokens are accessed before tenant context is known, same as sessions/users.
ALTER TABLE mobile_refresh_tokens DISABLE ROW LEVEL SECURITY;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='clickdelivery_app') THEN
    GRANT SELECT,INSERT,UPDATE,DELETE ON mobile_refresh_tokens,mobile_sync_operations,merchant_products,
      customer_addresses,merchant_orders,merchant_order_items,driver_wallet_entries,driver_settlements TO clickdelivery_app;
    GRANT USAGE,SELECT ON ALL SEQUENCES IN SCHEMA public TO clickdelivery_app;
  END IF;
END $$;


CREATE OR REPLACE FUNCTION clickdelivery_touch_updated_at()
RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
  NEW.updated_at=now();
  RETURN NEW;
END $$;

DO $$
DECLARE t text;
BEGIN
  FOREACH t IN ARRAY ARRAY['trips','merchant_products','customer_addresses','merchant_orders','driver_settlements']
  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 $$;


-- ===== 009_v07_referencia funcional externa_operations.sql =====
-- 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 $$;


-- ===== 010_v071_zones_direct_payments.sql =====
-- 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 $$;



-- ============================================================
-- MIGRATION 011 INLINE: V0.7.1 Commercial Complete
-- ============================================================
-- 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 $$;


-- ===== 012_v072_lm_networks_governance.sql =====
-- ClickDelivery V0.7.2 — LM Networks governance correction
-- Manager Pay and ChefManager AI are future integrations, not runtime dependencies.

-- Normalize the previously reserved internal connector identifier without preserving
-- an incorrect product label in this release.
ALTER TABLE integration_connectors DROP CONSTRAINT IF EXISTS integration_connectors_connector_type_check;
ALTER TABLE integration_outbox DROP CONSTRAINT IF EXISTS integration_outbox_connector_type_check;
ALTER TABLE checkout_payment_intents DROP CONSTRAINT IF EXISTS checkout_payment_intents_connector_type_check;

UPDATE integration_connectors SET connector_type='chefmanager_ai', name='ChefManager AI'
 WHERE connector_type=('chefmanager' || '_' || 'pay') OR lower(name) LIKE 'chefmanager%';
UPDATE integration_outbox SET connector_type='chefmanager_ai'
 WHERE connector_type=('chefmanager' || '_' || 'pay');
UPDATE checkout_payment_intents SET connector_type='chefmanager_ai'
 WHERE connector_type=('chefmanager' || '_' || 'pay');

ALTER TABLE integration_connectors ADD CONSTRAINT integration_connectors_connector_type_check CHECK(connector_type IN ('manager_pay','chefmanager_ai','custom'));
ALTER TABLE integration_outbox ADD CONSTRAINT integration_outbox_connector_type_check CHECK(connector_type IN ('manager_pay','chefmanager_ai','custom'));
ALTER TABLE checkout_payment_intents ADD CONSTRAINT checkout_payment_intents_connector_type_check CHECK(connector_type IN ('manager_pay','chefmanager_ai'));

UPDATE integration_connectors
   SET enabled=false,status='disabled',
       config=COALESCE(config,'{}'::jsonb) || '{"stage":"future","owner":"LM Networks"}'::jsonb
 WHERE connector_type IN ('manager_pay','chefmanager_ai');

UPDATE integration_outbox
   SET status='skipped',last_error='FUTURE_INTEGRATION_NOT_RELEASED',updated_at=now()
 WHERE connector_type IN ('manager_pay','chefmanager_ai') AND status IN ('queued','retry','sending');

ALTER TABLE logistics_settings ALTER COLUMN require_platform_payment_before_order SET DEFAULT false;
UPDATE logistics_settings SET require_platform_payment_before_order=false;

COMMENT ON COLUMN organizations.mode IS 'Modelo comercial ClickDelivery: franchise o white_label. La Sub-Administración territorial aplica dentro de ambos modelos y nunca cruza organization_id.';



-- ============================================================
-- MIGRATION 013 INLINE: V0.8 Loader Studio + Landing Demo
-- ============================================================
-- ClickDelivery V0.8 — Loader Studio, branding runtime and expanded demo profiles
-- Developed by LM Networks.

CREATE TABLE IF NOT EXISTS loading_animation_library (
  id text PRIMARY KEY,
  name text NOT NULL,
  asset_url text NOT NULL,
  asset_kind text NOT NULL CHECK(asset_kind IN ('gif','webp','png','jpg','svg')),
  source text NOT NULL DEFAULT 'lm_networks' CHECK(source IN ('lm_networks','organization')),
  organization_id text REFERENCES organizations(id) ON DELETE CASCADE,
  default_motion_preset text NOT NULL DEFAULT 'pulse',
  active boolean NOT NULL DEFAULT true,
  created_at timestamptz NOT NULL DEFAULT now(),
  updated_at timestamptz NOT NULL DEFAULT now(),
  CHECK ((source='lm_networks' AND organization_id IS NULL) OR (source='organization' AND organization_id IS NOT NULL))
);
CREATE INDEX IF NOT EXISTS loading_animation_library_org_idx ON loading_animation_library(organization_id,active);

CREATE TABLE IF NOT EXISTS organization_loading_profiles (
  organization_id text PRIMARY KEY REFERENCES organizations(id) ON DELETE CASCADE,
  library_animation_id text REFERENCES loading_animation_library(id) ON DELETE SET NULL,
  custom_asset_url text,
  motion_preset text NOT NULL DEFAULT 'pulse' CHECK(motion_preset IN ('none','pulse','bounce','float','spin','zoom','shake','orbit')),
  duration_ms integer NOT NULL DEFAULT 1200 CHECK(duration_ms BETWEEN 250 AND 10000),
  scale numeric(5,2) NOT NULL DEFAULT 1 CHECK(scale BETWEEN 0.25 AND 3),
  background_color text NOT NULL DEFAULT '#ffffff',
  icon_color text,
  loop boolean NOT NULL DEFAULT true,
  updated_by text REFERENCES users(id) ON DELETE SET NULL,
  updated_at timestamptz NOT NULL DEFAULT now()
);

INSERT INTO loading_animation_library(id,name,asset_url,asset_kind,source,default_motion_preset,active)
VALUES
 ('loader_clickdelivery_rabbit','ClickDelivery Conejo','/assets/loaders/original/clickdelivery-rabbit.gif','gif','lm_networks','none',true),
 ('loader_clickdelivery_rabbit_alt','ClickDelivery Conejo Alternativo','/assets/loaders/original/clickdelivery-rabbit-alt.gif','gif','lm_networks','none',true),
 ('loader_pin','Ubicación','/assets/loaders/library/pin.svg','svg','lm_networks','bounce',true),
 ('loader_package','Paquete','/assets/loaders/library/package.svg','svg','lm_networks','float',true),
 ('loader_route','Ruta','/assets/loaders/library/route.svg','svg','lm_networks','pulse',true),
 ('loader_bolt','Express','/assets/loaders/library/bolt.svg','svg','lm_networks','zoom',true),
 ('loader_ring','Anillo','/assets/loaders/library/ring.svg','svg','lm_networks','spin',true)
ON CONFLICT(id) DO UPDATE SET name=EXCLUDED.name,asset_url=EXCLUDED.asset_url,asset_kind=EXCLUDED.asset_kind,active=true,updated_at=now();

-- Existing franchise organizations receive the official ClickDelivery loader.
INSERT INTO organization_loading_profiles(organization_id,library_animation_id,motion_preset,duration_ms,scale,background_color,loop,updated_at)
SELECT id,'loader_clickdelivery_rabbit','none',1200,1,'#ffffff',true,now()
FROM organizations WHERE mode='franchise'
ON CONFLICT(organization_id) DO NOTHING;

DO $$
BEGIN
  IF EXISTS (SELECT 1 FROM pg_roles WHERE rolname='clickdelivery_app') THEN
    GRANT SELECT,INSERT,UPDATE,DELETE ON loading_animation_library,organization_loading_profiles TO clickdelivery_app;
  END IF;
END $$;

ALTER TABLE loading_animation_library ENABLE ROW LEVEL SECURITY;
ALTER TABLE organization_loading_profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE loading_animation_library FORCE ROW LEVEL SECURITY;
ALTER TABLE organization_loading_profiles FORCE ROW LEVEL SECURITY;

DROP POLICY IF EXISTS loading_animation_library_tenant_policy ON loading_animation_library;
CREATE POLICY loading_animation_library_tenant_policy ON loading_animation_library
  USING (
    source='lm_networks'
    OR current_setting('app.is_superadmin',true)='true'
    OR organization_id=current_setting('app.organization_id',true)
  )
  WITH CHECK (
    current_setting('app.is_superadmin',true)='true'
    OR organization_id=current_setting('app.organization_id',true)
  );

DROP POLICY IF EXISTS organization_loading_profiles_tenant_policy ON organization_loading_profiles;
CREATE POLICY organization_loading_profiles_tenant_policy ON organization_loading_profiles
  USING (
    current_setting('app.is_superadmin',true)='true'
    OR organization_id=current_setting('app.organization_id',true)
  )
  WITH CHECK (
    current_setting('app.is_superadmin',true)='true'
    OR organization_id=current_setting('app.organization_id',true)
  );

COMMENT ON TABLE loading_animation_library IS 'Biblioteca LM Networks y activos de carga propios de cada Marca Blanca.';
COMMENT ON TABLE organization_loading_profiles IS 'Configuración del loader por tenant. Franquicia ClickDelivery usa por defecto el conejo oficial; Marca Blanca puede personalizar.';