-- 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 $$;