-- Tipos de certificado (A, B, C) con monto y fondo de impresión.
CREATE TABLE IF NOT EXISTS public.certificado_tipos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    codigo VARCHAR(10) NOT NULL UNIQUE,
    nombre VARCHAR(50) NOT NULL,
    monto NUMERIC(12, 2) NOT NULL CHECK (monto > 0),
    color VARCHAR(30) NOT NULL,
    fondo_archivo VARCHAR(120) NOT NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO public.certificado_tipos (id, codigo, nombre, monto, color, fondo_archivo)
VALUES
    ('a1000001-0000-4000-8000-000000000001', 'A', 'Tipo A', 150.00, '#2E7D32', 'fondo_certificadoA.jpeg'),
    ('a1000001-0000-4000-8000-000000000002', 'B', 'Tipo B', 200.00, '#C4A574', 'fondo_certificadoB.jpeg'),
    ('a1000001-0000-4000-8000-000000000003', 'C', 'Tipo C', 250.00, '#F9A825', 'fondo_certificadoC.jpeg')
ON CONFLICT (codigo) DO NOTHING;

UPDATE public.certificado_tipos SET fondo_archivo = 'fondo_certificadoA.jpeg' WHERE codigo = 'A';
UPDATE public.certificado_tipos SET fondo_archivo = 'fondo_certificadoB.jpeg' WHERE codigo = 'B';
UPDATE public.certificado_tipos SET fondo_archivo = 'fondo_certificadoC.jpeg' WHERE codigo = 'C';

ALTER TABLE public.certificados
    ADD COLUMN IF NOT EXISTS tipo_id UUID REFERENCES public.certificado_tipos(id) ON DELETE RESTRICT;

ALTER TABLE public.certificados
    ADD COLUMN IF NOT EXISTS ferreteria_id UUID REFERENCES public.ferreterias(id) ON DELETE RESTRICT;

CREATE INDEX IF NOT EXISTS idx_certificados_tipo ON public.certificados(tipo_id);
CREATE INDEX IF NOT EXISTS idx_certificados_ferreteria ON public.certificados(ferreteria_id);

-- Asignar tipo y ferretería a certificados existentes
UPDATE public.certificados c
SET tipo_id = t.id
FROM public.certificado_tipos t
WHERE c.tipo_id IS NULL
  AND t.codigo = CASE
    WHEN c.monto_inicial <= 175 THEN 'A'
    WHEN c.monto_inicial <= 225 THEN 'B'
    ELSE 'C'
  END;

UPDATE public.certificados c
SET ferreteria_id = f.id
FROM (
    SELECT id FROM public.ferreterias
    WHERE is_active = TRUE
    ORDER BY nombre ASC
    LIMIT 1
) f
WHERE c.ferreteria_id IS NULL;
