-- Fase 2: certificados por emisor y secuenciales transaccionales
--
-- Posterior a 20260805120000_multi_ruc_fase1, no la modifica. Escrita a mano (no generada por
-- `prisma migrate dev`) para controlar el backfill igual que en la fase 1: Certificado.activo
-- (booleano) se reemplaza por un estado real (ACTIVO/INACTIVO/REEMPLAZADO) sin perder informacion,
-- y los comprobantes existentes quedan asociados a un punto de emision real en vez de quedar
-- huerfanos cuando se vuelva NOT NULL esa columna.

-- ============================================================================
-- 1) Certificado.activo (boolean) -> Certificado.estado (enum), sin perder informacion
-- ============================================================================
CREATE TYPE "EstadoCertificado" AS ENUM ('ACTIVO', 'INACTIVO', 'REEMPLAZADO');

ALTER TABLE "certificados" ADD COLUMN "estado" "EstadoCertificado";

UPDATE "certificados"
SET "estado" = CASE WHEN "activo" THEN 'ACTIVO'::"EstadoCertificado" ELSE 'INACTIVO'::"EstadoCertificado" END;

ALTER TABLE "certificados" ALTER COLUMN "estado" SET NOT NULL;
ALTER TABLE "certificados" ALTER COLUMN "estado" SET DEFAULT 'ACTIVO';
ALTER TABLE "certificados" DROP COLUMN "activo";

-- Metadatos extraidos del .p12 (nunca la llave privada ni la contrasena). Nulos para los
-- certificados cargados antes de esta fase: no es posible reconstruirlos con SQL puro sin
-- descifrar el archivo, y no se justifica agregar ese paso a una migracion. Quedan disponibles
-- para todo certificado cargado desde ahora.
ALTER TABLE "certificados"
  ADD COLUMN "titular" TEXT,
  ADD COLUMN "entidadEmisora" TEXT,
  ADD COLUMN "numeroSerie" TEXT,
  ADD COLUMN "fechaEmisionCertificado" TIMESTAMP(3);

-- ============================================================================
-- 2) Secuenciales: contador atomico por punto de emision + tipo de comprobante
-- ============================================================================
CREATE TABLE "secuenciales" (
    "id" TEXT NOT NULL,
    "puntoEmisionId" TEXT NOT NULL,
    "tipoComprobante" "TipoComprobante" NOT NULL,
    "ultimoUtilizado" INTEGER NOT NULL DEFAULT 0,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "secuenciales_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "secuenciales_puntoEmisionId_tipoComprobante_key" ON "secuenciales"("puntoEmisionId", "tipoComprobante");

ALTER TABLE "secuenciales" ADD CONSTRAINT "secuenciales_puntoEmisionId_fkey" FOREIGN KEY ("puntoEmisionId") REFERENCES "puntos_emision"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- ============================================================================
-- 3) Reservas de secuencial: una fila por comprobante, nunca libera el numero ante un fallo
-- ============================================================================
CREATE TYPE "EstadoReservaSecuencial" AS ENUM ('RESERVADO', 'UTILIZADO', 'FALLIDO');

CREATE TABLE "reservas_secuencial" (
    "id" TEXT NOT NULL,
    "comprobanteId" TEXT NOT NULL,
    "puntoEmisionId" TEXT NOT NULL,
    "tipoComprobante" "TipoComprobante" NOT NULL,
    "secuencial" TEXT NOT NULL,
    "idempotencyKey" TEXT,
    "estado" "EstadoReservaSecuencial" NOT NULL DEFAULT 'RESERVADO',
    "fechaReserva" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "fechaUtilizacion" TIMESTAMP(3),
    "motivoFallo" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

    CONSTRAINT "reservas_secuencial_pkey" PRIMARY KEY ("id")
);

CREATE UNIQUE INDEX "reservas_secuencial_comprobanteId_key" ON "reservas_secuencial"("comprobanteId");
CREATE UNIQUE INDEX "reservas_secuencial_puntoEmisionId_tipoComprobante_secuencial_key" ON "reservas_secuencial"("puntoEmisionId", "tipoComprobante", "secuencial");
CREATE INDEX "reservas_secuencial_idempotencyKey_idx" ON "reservas_secuencial"("idempotencyKey");

ALTER TABLE "reservas_secuencial" ADD CONSTRAINT "reservas_secuencial_comprobanteId_fkey" FOREIGN KEY ("comprobanteId") REFERENCES "comprobantes"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "reservas_secuencial" ADD CONSTRAINT "reservas_secuencial_puntoEmisionId_fkey" FOREIGN KEY ("puntoEmisionId") REFERENCES "puntos_emision"("id") ON DELETE RESTRICT ON UPDATE CASCADE;

-- ============================================================================
-- 4) Comprobante: relacion con punto de emision, secuencial propio, certificado usado,
--    e idempotencyKey. Se agregan nullable, se backfillean, y puntoEmisionId se vuelve
--    obligatorio recien cuando el backfill garantiza que no queda ninguna fila sin asociar.
-- ============================================================================
ALTER TABLE "comprobantes"
  ADD COLUMN "puntoEmisionId" TEXT,
  ADD COLUMN "secuencial" TEXT,
  ADD COLUMN "certificadoId" TEXT,
  ADD COLUMN "idempotencyKey" TEXT;

-- Backfill del secuencial: la clave de acceso de 49 digitos ya lo trae embebido (posiciones
-- 31-39, 1-indexado), es la fuente de verdad para comprobantes historicos.
UPDATE "comprobantes"
SET "secuencial" = SUBSTRING("claveAcceso" FROM 31 FOR 9)
WHERE "secuencial" IS NULL AND "claveAcceso" ~ '^[0-9]{49}$';

-- Backfill del punto de emision: para cada comprobante sin puntoEmisionId, se usa el punto
-- inicial ("001") del establecimiento inicial ("001") de su propio emisor si lo tiene asociado
-- (emisorId, poblado por la fase 1), o del emisor mas antiguo del sistema como ultimo recurso
-- (comprobantes emitidos antes de existir la relacion con emisor). Si no existe ningun emisor ni
-- punto de emision todavia, no hay nada que backfillear (base nueva).
DO $$
DECLARE
  fila RECORD;
  punto_id TEXT;
BEGIN
  FOR fila IN SELECT "id", "emisorId" FROM "comprobantes" WHERE "puntoEmisionId" IS NULL LOOP
    SELECT pe."id" INTO punto_id
    FROM "puntos_emision" pe
    JOIN "establecimientos" e ON e."id" = pe."establecimientoId"
    WHERE e."emisorId" = COALESCE(fila."emisorId", (SELECT "id" FROM "emisores" ORDER BY "createdAt" ASC LIMIT 1))
    ORDER BY e."codigo" ASC, pe."codigo" ASC
    LIMIT 1;

    IF punto_id IS NOT NULL THEN
      UPDATE "comprobantes"
      SET "puntoEmisionId" = punto_id,
          "emisorId" = COALESCE("emisorId", (SELECT e."emisorId" FROM "establecimientos" e JOIN "puntos_emision" pe ON pe."establecimientoId" = e."id" WHERE pe."id" = punto_id))
      WHERE "id" = fila."id";
    END IF;
  END LOOP;
END $$;

-- Solo se exige NOT NULL si el backfill efectivamente cubrio todas las filas (nunca deja
-- huerfanos): si por algun motivo quedara alguna sin punto de emision, la migracion falla aqui
-- en vez de aplicar una restriccion que la app no podria cumplir en runtime.
ALTER TABLE "comprobantes" ALTER COLUMN "puntoEmisionId" SET NOT NULL;

CREATE UNIQUE INDEX "comprobantes_idempotencyKey_key" ON "comprobantes"("idempotencyKey");
CREATE INDEX "comprobantes_puntoEmisionId_idx" ON "comprobantes"("puntoEmisionId");
CREATE INDEX "comprobantes_certificadoId_idx" ON "comprobantes"("certificadoId");

ALTER TABLE "comprobantes" ADD CONSTRAINT "comprobantes_puntoEmisionId_fkey" FOREIGN KEY ("puntoEmisionId") REFERENCES "puntos_emision"("id") ON DELETE RESTRICT ON UPDATE CASCADE;
ALTER TABLE "comprobantes" ADD CONSTRAINT "comprobantes_certificadoId_fkey" FOREIGN KEY ("certificadoId") REFERENCES "certificados"("id") ON DELETE SET NULL ON UPDATE CASCADE;

-- ============================================================================
-- 5) Inicializar cada contador de secuenciales despues del mayor secuencial historico, solo
--    para los puntos de emision que efectivamente tienen comprobantes previos.
-- ============================================================================
INSERT INTO "secuenciales" ("id", "puntoEmisionId", "tipoComprobante", "ultimoUtilizado", "createdAt", "updatedAt")
SELECT gen_random_uuid()::text, "puntoEmisionId", "tipo", MAX(CAST("secuencial" AS INTEGER)), now(), now()
FROM "comprobantes"
WHERE "secuencial" IS NOT NULL AND "puntoEmisionId" IS NOT NULL
GROUP BY "puntoEmisionId", "tipo"
ON CONFLICT ("puntoEmisionId", "tipoComprobante") DO NOTHING;