-- CreateEnum
CREATE TYPE "OfficialNormativeSourceType" AS ENUM ('LEY', 'REGLAMENTO', 'LINEAMIENTO', 'PORTAL');

-- CreateEnum
CREATE TYPE "NormativeVerificationStatus" AS ENUM ('SUCCESS', 'WARNING', 'FAILED');

-- CreateTable
CREATE TABLE "OfficialNormativeSource" (
    "id" TEXT NOT NULL,
    "stateCode" TEXT NOT NULL,
    "stateName" TEXT NOT NULL,
    "municipalityCode" TEXT,
    "municipalityName" TEXT,
    "authorityName" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "officialUrl" TEXT NOT NULL,
    "sourceType" "OfficialNormativeSourceType" NOT NULL,
    "versionLabel" TEXT,
    "isPilot" BOOLEAN NOT NULL DEFAULT false,
    "lastKnownHash" TEXT,
    "lastVerifiedAt" TIMESTAMP(3),
    "nextCheckAt" TIMESTAMP(3),
    "lastStatus" "NormativeVerificationStatus",
    "lastMessage" TEXT,
    "lastChangedAt" TIMESTAMP(3),
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

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

-- CreateTable
CREATE TABLE "OfficialNormativeSuggestion" (
    "id" TEXT NOT NULL,
    "stateCode" TEXT NOT NULL,
    "municipalityCode" TEXT,
    "authorityName" TEXT NOT NULL,
    "title" TEXT NOT NULL,
    "officialUrl" TEXT NOT NULL,
    "sourceType" "OfficialNormativeSourceType" NOT NULL,
    "notes" TEXT,
    "createdAt" TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    "updatedAt" TIMESTAMP(3) NOT NULL,

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

-- CreateIndex
CREATE INDEX "OfficialNormativeSource_stateCode_municipalityCode_isPilot_idx" ON "OfficialNormativeSource"("stateCode", "municipalityCode", "isPilot");

-- CreateIndex
CREATE INDEX "OfficialNormativeSource_nextCheckAt_idx" ON "OfficialNormativeSource"("nextCheckAt");

-- CreateIndex
CREATE INDEX "OfficialNormativeSource_lastStatus_lastVerifiedAt_idx" ON "OfficialNormativeSource"("lastStatus", "lastVerifiedAt");

-- CreateIndex
CREATE INDEX "OfficialNormativeSuggestion_stateCode_municipalityCode_createdAt_idx" ON "OfficialNormativeSuggestion"("stateCode", "municipalityCode", "createdAt");

-- Seed pilot sources (Nuevo Leon)
INSERT INTO "OfficialNormativeSource" (
  "id",
  "stateCode",
  "stateName",
  "municipalityCode",
  "municipalityName",
  "authorityName",
  "title",
  "officialUrl",
  "sourceType",
  "versionLabel",
  "isPilot",
  "createdAt",
  "updatedAt"
)
VALUES
  (
    'nl-ley-adquisiciones-estatal',
    'NL',
    'Nuevo Leon',
    NULL,
    NULL,
    'Gobierno del Estado de Nuevo Leon',
    'Ley de Adquisiciones, Arrendamientos y Contratacion de Servicios del Estado de Nuevo Leon',
    'https://www.hcnl.gob.mx/trabajo_legislativo/leyes/',
    'LEY',
    NULL,
    true,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
  ),
  (
    'nl-reglamento-adquisiciones-estatal',
    'NL',
    'Nuevo Leon',
    NULL,
    NULL,
    'Gobierno del Estado de Nuevo Leon',
    'Reglamento de la Ley de Adquisiciones del Estado de Nuevo Leon',
    'https://www.nl.gob.mx/',
    'REGLAMENTO',
    NULL,
    true,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
  ),
  (
    'spgg-reglamento-adquisiciones',
    'NL',
    'Nuevo Leon',
    'SPGG',
    'San Pedro Garza Garcia',
    'Municipio de San Pedro Garza Garcia',
    'Reglamento de Adquisiciones y Contratacion de Servicios de San Pedro Garza Garcia',
    'https://www.sanpedro.gob.mx/',
    'REGLAMENTO',
    NULL,
    true,
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP
  )
ON CONFLICT ("id") DO UPDATE
SET
  "stateCode" = EXCLUDED."stateCode",
  "stateName" = EXCLUDED."stateName",
  "municipalityCode" = EXCLUDED."municipalityCode",
  "municipalityName" = EXCLUDED."municipalityName",
  "authorityName" = EXCLUDED."authorityName",
  "title" = EXCLUDED."title",
  "officialUrl" = EXCLUDED."officialUrl",
  "sourceType" = EXCLUDED."sourceType",
  "versionLabel" = EXCLUDED."versionLabel",
  "isPilot" = EXCLUDED."isPilot",
  "updatedAt" = CURRENT_TIMESTAMP;