-- 1. Create cat_dias table
CREATE TABLE IF NOT EXISTS cat_dias (
    id_cat_dia SERIAL PRIMARY KEY,
    nombre VARCHAR(20) NOT NULL UNIQUE
);

-- 2. Populate cat_dias
INSERT INTO cat_dias (nombre) VALUES 
('Lunes'), ('Martes'), ('Miércoles'), ('Jueves'), ('Viernes'), ('Sábado'), ('Domingo')
ON CONFLICT (nombre) DO NOTHING;

-- 3. Add column to clases
ALTER TABLE clases ADD COLUMN IF NOT EXISTS fk_id_cat_dia INTEGER;

-- 4. Update clases using the mapping (Postgres specific UPDATE with FROM/JOIN logic or simple subquery)
UPDATE clases
SET fk_id_cat_dia = (SELECT id_cat_dia FROM cat_dias WHERE cat_dias.nombre = clases.dia_semana);

-- 5. Add Foreign Key constraint
ALTER TABLE clases 
ADD CONSTRAINT fk_clases_cat_dia 
FOREIGN KEY (fk_id_cat_dia) 
REFERENCES cat_dias(id_cat_dia);

-- 6. Make column not null (optional, verify invalid data first)
-- ALTER TABLE clases ALTER COLUMN fk_id_cat_dia SET NOT NULL;

-- 7. Drop old column (Only after verification)
-- ALTER TABLE clases DROP COLUMN dia_semana;
