-- Migration: Dual Approval for Class Rescheduling

-- 1. Create Catalog Table
CREATE TABLE IF NOT EXISTS "cat_recalendarizacion_estatus" (
  "id" SERIAL PRIMARY KEY,
  "nombre" VARCHAR(50) NOT NULL, -- PENDIENTE, APROBADO, RECHAZADO
  "color" VARCHAR(20) DEFAULT 'gray', -- blue, green, red
  "descripcion" VARCHAR(255),
  "created_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
  "updated_at" TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 2. Insert Default Values
INSERT INTO "cat_recalendarizacion_estatus" ("id", "nombre", "color", "descripcion") VALUES
(1, 'PENDIENTE', 'orange', 'Esperando aprobación'),
(2, 'APROBADO', 'green', 'Solicitud aprobada y confirmada'),
(3, 'RECHAZADO', 'red', 'Solicitud rechazada')
ON CONFLICT ("id") DO NOTHING;

-- 3. Alter Main Table
-- We add new columns first, migrate data if needed, then drop old enum if we want to be clean (or keep it as legacy for a bit).
-- Since this is 'dev' environment mostly, we can just replace. 

-- Add new FK columns
ALTER TABLE "clase_recalendarizaciones" 
ADD COLUMN "fk_id_estatus_general" INTEGER REFERENCES "cat_recalendarizacion_estatus"("id") DEFAULT 1;

ALTER TABLE "clase_recalendarizaciones" 
ADD COLUMN "fk_id_estatus_profesor" INTEGER REFERENCES "cat_recalendarizacion_estatus"("id") DEFAULT 1;

ALTER TABLE "clase_recalendarizaciones" 
ADD COLUMN "fk_id_estatus_tutor" INTEGER REFERENCES "cat_recalendarizacion_estatus"("id") DEFAULT 1;

ALTER TABLE "clase_recalendarizaciones" 
ADD COLUMN "fecha_voto_profesor" TIMESTAMP WITH TIME ZONE;

ALTER TABLE "clase_recalendarizaciones" 
ADD COLUMN "fecha_voto_tutor" TIMESTAMP WITH TIME ZONE;

-- (Optional) Data Migration: If we had data, we would map ENUM 'PENDIENTE' -> 1, etc.
-- For now, defaults assume PENDIENTE.

-- Drop old 'estado' column if desired, or keep and ignore. 
-- Let's rename it to avoid confusion in Sequelize models? 
-- ALTER TABLE "clase_recalendarizacion" RENAME COLUMN "estado" TO "estado_legacy";
-- For safety, we will just start using the new columns in the code.
