-- migration_remove_region_ad_rd.sql
-- Remove region, area_director, regional_director from loa_form.
-- These values are sourced from locationInfo via v_loa_dashboard JOIN instead.
-- Applied to production 2026-02-24.

-- Step 1: Drop the three redundant columns (already applied)
-- ALTER TABLE loa_form
--     DROP COLUMN region,
--     DROP COLUMN area_director,
--     DROP COLUMN regional_director;

-- Step 2: Recreate the view — region/AD/RD now come from locationInfo
CREATE OR REPLACE VIEW v_loa_dashboard AS
SELECT
    lf.id                    AS loa_id,
    lf.employee_id,
    lf.adp_associate_oid,
    lf.full_name,
    lf.email,
    lf.phone,
    lf.job_title,
    lf.location_name,
    li.region                AS region,
    lf.manager_name,
    li.adName                AS area_director,
    li.rdName                AS regional_director,
    lf.leave_category,
    lf.leave_type,
    lf.leave_type_code,
    lf.details               AS leave_reason,
    lf.leave_classification,
    lf.request_date,
    lf.last_day_worked,
    lf.start_date,
    lf.expected_end_date,
    lf.actual_return_date,
    lf.loa_duration_days,
    lf.leave_status,
    lf.eligible_for_leave    AS fmla_eligible,
    lf.determination_date,
    lf.certification_received,
    lf.fmla_hours_used,
    lf.fmla_hours_remaining,
    lf.pay_status,
    lf.salary_hourly         AS pay_type,
    lf.pto_balance,
    lf.benefits_status,
    lf.cobra_notice_sent_date,
    (SELECT COUNT(*) FROM loa_documentation WHERE loa_form_id = lf.id AND document_status = 'received') AS docs_received_count,
    (SELECT COUNT(*) FROM loa_documentation WHERE loa_form_id = lf.id AND document_status = 'pending')  AS docs_pending_count,
    (SELECT MIN(date_sent)      FROM loa_documentation WHERE loa_form_id = lf.id) AS first_form_sent_date,
    (SELECT MAX(date_received)  FROM loa_documentation WHERE loa_form_id = lf.id) AS last_form_received_date,
    ea.jobTitle              AS adp_job_title,
    ea.hireDate              AS hire_date,
    (TO_DAYS(lf.start_date) - TO_DAYS(ea.hireDate))                   AS tenure_days_at_leave_start,
    ROUND(((TO_DAYS(lf.start_date) - TO_DAYS(ea.hireDate)) / 365.25), 2) AS tenure_years_at_leave_start,
    li.gmName                AS general_manager,
    lf.created_at,
    lf.updated_at,
    lf.form_submitted_at,
    lf.adp_last_sync_at
FROM loa_form lf
LEFT JOIN employee_adp ea
    ON lf.adp_associate_oid COLLATE utf8mb4_general_ci = ea.associateOID COLLATE utf8mb4_general_ci
LEFT JOIN locationInfo li
    ON lf.location_fk = li.locationID
WHERE lf.leave_status <> 'cancelled'
ORDER BY lf.start_date DESC;
