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