-- ============================================================================
-- Migration : Infrastructure du réseau ferroviaire (Ferrovia Panel)
-- ============================================================================

-- 1. Table principale pour l'infrastructure
CREATE TABLE IF NOT EXISTS "public"."infrastructure_reseau" (
  "id"           uuid DEFAULT gen_random_uuid() NOT NULL PRIMARY KEY,
  "version"      varchar(20) DEFAULT '1.0' NOT NULL,
  "nom_reseau"   varchar(255) DEFAULT 'Réseau Ferroviaire Principal',
  "region"       varchar(100),
  "rails"        jsonb DEFAULT '[]'::jsonb NOT NULL,
  "signaux"      jsonb DEFAULT '[]'::jsonb NOT NULL,
  "pancartes"    jsonb DEFAULT '[]'::jsonb NOT NULL,
  "config"       jsonb DEFAULT '{}'::jsonb NOT NULL,
  "created_at"   timestamptz NOT NULL DEFAULT now(),
  "updated_at"   timestamptz NOT NULL DEFAULT now()
);

-- Index pour recherche rapide
CREATE INDEX IF NOT EXISTS "idx_infra_reseau_region" ON "public"."infrastructure_reseau" ("region");

-- 2. Ajout des colonnes latitude & longitude dans la table gares si nécessaire
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_schema = 'public' AND table_name = 'gares' AND column_name = 'latitude'
  ) THEN
    ALTER TABLE "public"."gares" ADD COLUMN "latitude" double precision;
  END IF;

  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns 
    WHERE table_schema = 'public' AND table_name = 'gares' AND column_name = 'longitude'
  ) THEN
    ALTER TABLE "public"."gares" ADD COLUMN "longitude" double precision;
  END IF;
END $$;

-- 3. RLS (Row-Level Security)
ALTER TABLE "public"."infrastructure_reseau" ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Lecture ouverte infrastructure_reseau"
  ON "public"."infrastructure_reseau"
  FOR SELECT
  TO authenticated, anon
  USING (true);

CREATE POLICY "Écriture ouverte infrastructure_reseau"
  ON "public"."infrastructure_reseau"
  FOR ALL
  TO authenticated, anon
  USING (true)
  WITH CHECK (true);