-- Migration: AddNetworkGroups
  -- Description: Adds SplashNetworkGroup and SplashNetworkGroupMember tables

  BEGIN;

  -- Create SplashNetworkGroups table
  CREATE TABLE "SplashNetworkGroups" (
      "Id" serial PRIMARY KEY,
      "Name" character varying(256) NOT NULL,
      "Description" character varying(512),
      "IsActive" boolean NOT NULL DEFAULT true,
      "TenantId" integer NOT NULL,
      "CreationTime" timestamp with time zone NOT NULL DEFAULT now(),
      "CreatorUserId" bigint,
      "LastModificationTime" timestamp with time zone,
      "LastModifierUserId" bigint,
      "IsDeleted" boolean NOT NULL DEFAULT false,
      "DeleterUserId" bigint,
      "DeletionTime" timestamp with time zone
  );

  -- Create SplashNetworkGroupMembers table
  CREATE TABLE "SplashNetworkGroupMembers" (
      "Id" serial PRIMARY KEY,
      "NetworkGroupId" integer NOT NULL,
      "NetworkId" integer NOT NULL,
      "TenantId" integer NOT NULL,
      "CreationTime" timestamp with time zone NOT NULL DEFAULT now(),
      "CreatorUserId" bigint
  );

  -- Create indexes for SplashNetworkGroups
  CREATE UNIQUE INDEX "IX_SplashNetworkGroups_Name_TenantId"
  ON "SplashNetworkGroups" ("Name", "TenantId");

  CREATE INDEX "IX_SplashNetworkGroups_TenantId"
  ON "SplashNetworkGroups" ("TenantId");

  CREATE INDEX "IX_SplashNetworkGroups_IsActive"
  ON "SplashNetworkGroups" ("IsActive");

  -- Create indexes for SplashNetworkGroupMembers
  CREATE UNIQUE INDEX "IX_SplashNetworkGroupMembers_NetworkGroupId_NetworkId"
  ON "SplashNetworkGroupMembers" ("NetworkGroupId", "NetworkId");

  CREATE INDEX "IX_SplashNetworkGroupMembers_NetworkGroupId"
  ON "SplashNetworkGroupMembers" ("NetworkGroupId");

  CREATE INDEX "IX_SplashNetworkGroupMembers_NetworkId"
  ON "SplashNetworkGroupMembers" ("NetworkId");

  CREATE INDEX "IX_SplashNetworkGroupMembers_TenantId"
  ON "SplashNetworkGroupMembers" ("TenantId");

  -- Add foreign key constraints
  ALTER TABLE "SplashNetworkGroupMembers"
  ADD CONSTRAINT "FK_SplashNetworkGroupMembers_SplashNetworkGroups_NetworkGroupId"
  FOREIGN KEY ("NetworkGroupId") REFERENCES "SplashNetworkGroups" ("Id") ON DELETE CASCADE;

  ALTER TABLE "SplashNetworkGroupMembers"
  ADD CONSTRAINT "FK_SplashNetworkGroupMembers_SplashMerakiNetworks_NetworkId"
  FOREIGN KEY ("NetworkId") REFERENCES "SplashMerakiNetworks" ("Id") ON DELETE CASCADE;

  -- Add SelectedNetworkGroups column to SplashDashboards table
  ALTER TABLE "SplashDashboards"
  ADD COLUMN "SelectedNetworkGroups" integer[] DEFAULT '{}';

  -- Create index for the new column
  CREATE INDEX "IX_SplashDashboards_SelectedNetworkGroups"
  ON "SplashDashboards" USING GIN ("SelectedNetworkGroups");

  -- Insert migration history record (if using EF Core migrations table)
  INSERT INTO "__EFMigrationsHistory" ("MigrationId", "ProductVersion")
  VALUES ('20250106000000_AddNetworkGroups', '8.0.0');

  COMMIT;

  Notas importantes sobre el script:

  1. Tipos de datos PostgreSQL: Uso serial para auto-incremento y timestamp with time zone para fechas
  2. Arrays: PostgreSQL maneja arrays nativamente, por eso SelectedNetworkGroups es integer[]
  3. Índices GIN: Para el array SelectedNetworkGroups uso un índice GIN que es óptimo para arrays
  4. Cascada: Las foreign keys tienen ON DELETE CASCADE para mantener integridad
  5. Valores por defecto: Arrays vacíos se inicializan con '{}'

  Para ejecutar este script:

  # Si usas psql directamente:
  psql -h localhost -U your_user -d your_database -f migration_script.sql

  # O copiarlo y pegarlo en tu cliente de PostgreSQL preferido

  Verificación después de ejecutar:

  -- Verificar que las tablas se crearon correctamente
  SELECT table_name FROM information_schema.tables
  WHERE table_name IN ('SplashNetworkGroups', 'SplashNetworkGroupMembers');

  -- Verificar la nueva columna en SplashDashboards
  SELECT column_name, data_type FROM information_schema.columns
  WHERE table_name = 'SplashDashboards' AND column_name = 'SelectedNetworkGroups';

  -- Verificar índices
  SELECT indexname FROM pg_indexes
  WHERE tablename IN ('SplashNetworkGroups', 'SplashNetworkGroupMembers');