-- Add columns to usuarios
ALTER TABLE "public"."usuarios" ADD COLUMN IF NOT EXISTS "telefono_fijo" varchar(20);
ALTER TABLE "public"."usuarios" ADD COLUMN IF NOT EXISTS "pais" varchar(100) DEFAULT 'México';

-- Create rel_alumno_tutor
CREATE TABLE IF NOT EXISTS "public"."rel_alumno_tutor" (
    "id" SERIAL PRIMARY KEY,
    "fk_id_alumno" INT NOT NULL REFERENCES "public"."usuarios"("id_usuario") ON DELETE CASCADE,
    "fk_id_tutor" INT NOT NULL REFERENCES "public"."usuarios"("id_usuario") ON DELETE CASCADE,
    "parentesco" VARCHAR(50),
    "activo" BOOLEAN DEFAULT TRUE,
    CONSTRAINT "uk_alumno_tutor" UNIQUE ("fk_id_alumno", "fk_id_tutor")
);

-- Create rel_profesor_especialidad
CREATE TABLE IF NOT EXISTS "public"."rel_profesor_especialidad" (
    "id" SERIAL PRIMARY KEY,
    "fk_id_profesor" INT NOT NULL REFERENCES "public"."usuarios"("id_usuario") ON DELETE CASCADE,
    "especialidad" VARCHAR(100) NOT NULL
);

-- Update cat_archivo_tipos
INSERT INTO "public"."cat_archivo_tipos" ("codigo_tipo", "descripcion")
SELECT 'REGLAMENTO_FIRMADO', 'Reglamento Firmado'
WHERE NOT EXISTS (SELECT 1 FROM "public"."cat_archivo_tipos" WHERE "codigo_tipo" = 'REGLAMENTO_FIRMADO');

-- Standardize cat_cp
DO $$
BEGIN
  -- Rename fk_id_cat_entidad -> fk_id_cat_estado if it exists
  IF EXISTS(SELECT * FROM information_schema.columns WHERE table_name = 'cat_cp' AND column_name = 'fk_id_cat_entidad') THEN
      ALTER TABLE "public"."cat_cp" RENAME COLUMN "fk_id_cat_entidad" TO "fk_id_cat_estado";
  END IF;
END $$;

-- Change cp to varchar if needed
ALTER TABLE "public"."cat_cp" ALTER COLUMN "cp" TYPE varchar(10);

-- Ensure UNIQUE constraints for FK targets (Good practice even if we skip FK for messy data)
DO $$
BEGIN
    -- cat_estados
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'uk_cat_estado_id') THEN
        ALTER TABLE "public"."cat_estados" ADD CONSTRAINT "uk_cat_estado_id" UNIQUE ("id_cat_estado");
    END IF;

    -- cat_municipios (Composite Unique)
    IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'uk_cat_municipio_composite') THEN
        ALTER TABLE "public"."cat_municipios" ADD CONSTRAINT "uk_cat_municipio_composite" UNIQUE ("fk_id_cat_estado", "id_cat_municipio");
    END IF;
END $$;

-- Add FK Constraints to cat_cp
DO $$
BEGIN
  -- Simple FK to states
  IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_cp_estado') THEN
      ALTER TABLE "public"."cat_cp" ADD CONSTRAINT "fk_cp_estado" FOREIGN KEY ("fk_id_cat_estado") REFERENCES "public"."cat_estados" ("id_cat_estado");
  END IF;

  -- SKIP Composite FK to municipalities due to data inconsistency (orphaned records in cat_cp)
  -- We will handle joins gracefully in backend.
  -- IF NOT EXISTS (SELECT 1 FROM pg_constraint WHERE conname = 'fk_cp_municipio_composite') THEN
  --    ALTER TABLE "public"."cat_cp" ADD CONSTRAINT "fk_cp_municipio_composite" FOREIGN KEY ("fk_id_cat_estado", "fk_id_cat_municipio") REFERENCES "public"."cat_municipios" ("fk_id_cat_estado", "id_cat_municipio");
  -- END IF;
END $$;
