-- ============================================================
-- FIX: Historizar posicion y area en contratos
-- Fecha: 2026-06-09
-- Autor: Analisis automatico del sistema
-- ============================================================
-- PROBLEMA IDENTIFICADO:
-- La tabla contracts no guardaba la posicion ni el area del
-- empleado en el momento del contrato. Todos los reportes de
-- contratos obtenian estos datos desde la tabla employees,
-- por lo que al actualizar la posicion/area del empleado,
-- todos los contratos historicos mostraban la informacion
-- actual en lugar de la del momento del contrato.
--
-- SOLUCION APLICADA:
-- 1. Se agregan las columnas id_position e id_area a la
--    tabla contracts.
-- 2. Se rellenan las columnas nuevas con la posicion/area
--    actual del empleado (fuente de verdad temporal).
-- 3. Se modifican newcontract() y updatecontract() para
--    guardar la posicion/area recibida en el contrato.
-- 4. Se modifican getcontracts() y reportemployeecontract()
--    para mostrar la posicion/area del contrato, no la
--    actual del empleado.
--
-- NOTA: Este cambio es reversible. Si algo falla, ejecutar
--       rollback_contracts_position_area.sql
-- ============================================================

-- ============================================================
-- 1. AGREGAR COLUMNAS A contracts
-- ============================================================
ALTER TABLE contracts
    ADD COLUMN IF NOT EXISTS id_position integer,
    ADD COLUMN IF NOT EXISTS id_area integer;

-- ============================================================
-- 2. RELLENAR COLUMNAS NUEVAS CON DATOS ACTUALES DEL EMPLEADO
-- ============================================================
UPDATE contracts c
SET
    id_position = emp.id_position,
    id_area = emp.id_area
FROM employees emp
WHERE c.rfc_employee = emp.rfc_employee;

-- ============================================================
-- 3. AGREGAR FOREIGN KEYS (opcional, recomendado)
-- ============================================================
-- Nota: Si ya existen contratos con id_position/id_area NULL
-- o con valores que no existan en positions/areas, estas FKs
-- fallaran. Descomentar solo si se valida la consistencia.
--
-- ALTER TABLE contracts
--     ADD CONSTRAINT fk_contracts_position
--     FOREIGN KEY (id_position) REFERENCES positions(id_position);
--
-- ALTER TABLE contracts
--     ADD CONSTRAINT fk_contracts_area
--     FOREIGN KEY (id_area) REFERENCES areas(id_area);

-- ============================================================
-- 4. FIX: getcontracts()
-- ============================================================
-- Ahora position_employee y area_employee vienen de contracts.
-- ============================================================
CREATE OR REPLACE FUNCTION public.getcontracts()
    RETURNS TABLE(
        id_contract integer,
        name_employee character varying,
        position_employee character varying,
        area_employee character varying,
        contratc_start date,
        contratc_end date,
        uniforms jsonb,
        daily_pay numeric,
        status_contract character varying,
        position_entry character varying
    )
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000
AS $BODY$
BEGIN
    RETURN QUERY
    SELECT
        co.id_contract,
        emp.name_employee,
        p.name_position,
        a.name_area,
        co.contratc_start,
        co.contratc_end,
        COALESCE(
            (
                SELECT jsonb_object_agg(u.name_uniform, du.quantity)
                FROM detail_unifor_employee du
                INNER JOIN uniforms u ON du.id_uniform = u.id_uniform
                WHERE du.rfc_employee = emp.rfc_employee
            ),
            '{}'::jsonb
        ) AS uniforms,
        co.daily_pay,
        stat.name_stat_contract,
        co.entry_position
    FROM contracts co
    INNER JOIN employees emp ON co.rfc_employee = emp.rfc_employee
    INNER JOIN positions p ON co.id_position = p.id_position
    INNER JOIN areas a ON co.id_area = a.id_area
    INNER JOIN status_contract stat ON co.id_contract_status = stat.id_stat_contract;
END;
$BODY$;

-- ============================================================
-- 5. FIX: newcontract()
-- ============================================================
-- Guarda id_position e id_area en contracts.
-- ============================================================
CREATE OR REPLACE FUNCTION public.newcontract(
    rfc_emp character varying,
    id_position_emp integer,
    id_area_emp integer,
    start_contract date,
    end_contract date,
    new_daily_pay numeric,
    boss_rfc character varying,
    boss_name character varying,
    position_entry character varying,
    uniforms jsonb)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    new_contract int;
    uniform jsonb;
BEGIN
    IF EXISTS (SELECT 1 FROM employees WHERE rfc_employee = rfc_emp) THEN
        DELETE FROM detail_unifor_employee
        WHERE rfc_employee = rfc_emp;

        UPDATE employees
        SET
            id_position = id_position_emp,
            id_area = id_area_emp
        WHERE rfc_employee = rfc_emp;

        INSERT INTO contracts(
            id_company, id_properties, rfc_employee,
            contratc_start, contratc_end, id_contract_status,
            daily_pay, rfc_boss, name_boss, entry_position,
            id_position, id_area
        )
        VALUES (
            1, 1, rfc_emp,
            start_contract, end_contract, 1,
            new_daily_pay, boss_rfc, boss_name, position_entry,
            id_position_emp, id_area_emp
        )
        RETURNING id_contract INTO new_contract;

        FOR uniform IN
            SELECT jsonb_array_elements(uniforms)
        LOOP
            INSERT INTO detail_unifor_employee(rfc_employee, id_uniform, id_contract, quantity)
            VALUES (rfc_emp, (uniform->>'id_uniform')::INT, new_contract, (uniform->>'quantity')::INT);
        END LOOP;

        RETURN 1;
    ELSE
        RETURN -1; -- empleado inexistente
    END IF;
END;
$BODY$;

-- ============================================================
-- 6. FIX: updatecontract()
-- ============================================================
-- Actualiza id_position e id_area en contracts.
-- ============================================================
CREATE OR REPLACE FUNCTION public.updatecontract(
    contract_id integer,
    rfc_emp character varying,
    id_position_emp integer,
    id_area_emp integer,
    start_contract date,
    end_contract date,
    new_daily_pay numeric,
    boss_rfc character varying,
    boss_name character varying,
    reason character varying,
    uniforms jsonb)
    RETURNS integer
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
    new_contract int;
    uniform jsonb;
    final_status int;
BEGIN
    DELETE FROM detail_unifor_employee
    WHERE id_contract = contract_id;

    UPDATE employees
    SET
        id_position = id_position_emp,
        id_area = id_area_emp
    WHERE rfc_employee = rfc_emp;

    IF end_contract < CURRENT_DATE THEN
        final_status := 2; -- Inactivo
    ELSE
        final_status := 1;
    END IF;

    UPDATE contracts SET
        rfc_employee = rfc_emp,
        contratc_start = start_contract,
        contratc_end = end_contract,
        id_contract_status = final_status,
        daily_pay = new_daily_pay,
        rfc_boss = boss_rfc,
        name_boss = boss_name,
        reasonfordismissal = reason,
        id_position = id_position_emp,
        id_area = id_area_emp
    WHERE id_contract = contract_id;

    FOR uniform IN
        SELECT jsonb_array_elements(uniforms)
    LOOP
        INSERT INTO detail_unifor_employee(rfc_employee, id_uniform, id_contract, quantity)
        VALUES (rfc_emp, (uniform->>'id_uniform')::INT, contract_id, (uniform->>'quantity')::INT);
    END LOOP;

    RETURN 1;
END;
$BODY$;

-- ============================================================
-- 7. FIX: reportemployeecontract()
-- ============================================================
-- Ahora name_position y name_area vienen de contracts.
-- ============================================================
CREATE OR REPLACE FUNCTION public.reportemployeecontract()
    RETURNS TABLE(
        name_employee character varying,
        rfc_employee character varying,
        nss_employee bigint,
        name_position character varying,
        spanish_name character varying,
        name_area character varying,
        addres_employee character varying,
        phone_employee character varying,
        email_employee character varying,
        birthday date,
        curp character varying,
        name_study character varying,
        id_contract integer,
        contratc_start date,
        contratc_end date,
        name_status character varying,
        daily_pay numeric,
        rfc_boss character varying,
        name_boss character varying,
        reasonfordismissal character varying,
        name_contact character varying,
        tel_contact character varying,
        name_relationship character varying
    )
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
    ROWS 1000
AS $BODY$
BEGIN
    RETURN QUERY
    SELECT
        emp.name_employee,
        emp.rfc_employee,
        emp.nss_employee,
        p.name_position,
        p.spanish_name,
        a.name_area,
        emp.addres_employee,
        emp.phone_employee,
        emp.email_employee,
        emp.birthday,
        emp.curp,
        st.name_study,
        c.id_contract,
        c.contratc_start,
        c.contratc_end,
        cs.name_stat_contract,
        c.daily_pay,
        c.rfc_boss,
        c.name_boss,
        c.reasonfordismissal,
        ec.name_contact,
        ec.tel_contact,
        r.name_relationship
    FROM employees emp
    INNER JOIN contracts c ON emp.rfc_employee = c.rfc_employee
    INNER JOIN emergencycontact ec ON emp.rfc_employee = ec.rfc_employee
    INNER JOIN areas a ON c.id_area = a.id_area
    INNER JOIN positions p ON c.id_position = p.id_position
    INNER JOIN degreeofstudy st ON emp.id_study = st.id_study
    INNER JOIN status_contract cs ON c.id_contract_status = cs.id_stat_contract
    INNER JOIN relationship_employee r ON ec.id_relationship = r.id_relationship;
END;
$BODY$;

-- Confirmacion
SELECT 'Fix de posicion/area en contratos aplicado correctamente' AS resultado;