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