-- =============================================================================
-- Post-MEC migration: assign MEC regions from divisions (plans + users)
-- =============================================================================
-- Rule you asked for:
--   If a user's / plan creator's division is e.g. Karachi (district id in
--   users_divisions.division_id), set the matching MEC region id where
--   mec_region_scope has scope_type = 'division' AND scope_id = that id.
--
-- Prerequisites:
--   1. php artisan migrate (plans.mec_region_id, mec_regions, mec_region_scope,
--      user_mec_regions, …)
--   2. Master data: mec_regions + mec_region_scope rows for each district
--      (division) that appears in users_divisions.
--
-- NOTE: If `mec_region_scope` lives on another MySQL database (Laravel `pc`
-- connection), prefix the table: `your_pc_db`.`mec_region_scope`.
-- =============================================================================

START TRANSACTION;

-- ---------------------------------------------------------------------------
-- 0) Preview: which division_id maps to which MEC region (sanity check)
-- ---------------------------------------------------------------------------
-- SELECT mrs.scope_id AS division_id, mr.id AS mec_region_id, mr.name AS region_name
-- FROM mec_region_scope mrs
-- JOIN mec_regions mr ON mr.id = mrs.mec_region_id AND mr.status = 1
-- WHERE mrs.scope_type = 'division'
-- ORDER BY mr.name, mrs.scope_id;

-- ---------------------------------------------------------------------------
-- 1) Users (MEC role): user_mec_regions from users_divisions → region scope
--    (Karachi district id in scope → Karachi MEC region id, etc.)
--    Remove the roles join if you need the same for other roles (not typical).
-- ---------------------------------------------------------------------------
INSERT IGNORE INTO user_mec_regions (user_id, mec_region_id, created_at, updated_at)
SELECT DISTINCT
    u.id,
    mrs.mec_region_id,
    NOW(),
    NOW()
FROM users u
JOIN roles r ON r.id = u.role_id AND LOWER(r.slug) = 'mec'
JOIN users_divisions ud ON ud.user_id = u.id
JOIN mec_region_scope mrs
    ON mrs.scope_type = 'division'
   AND mrs.scope_id = ud.division_id
JOIN mec_regions mr ON mr.id = mrs.mec_region_id AND mr.status = 1;

-- ---------------------------------------------------------------------------
-- 2) Users (MEC role): also map legacy users_locations → region (location scope)
-- ---------------------------------------------------------------------------
INSERT IGNORE INTO user_mec_regions (user_id, mec_region_id, created_at, updated_at)
SELECT DISTINCT
    u.id,
    mrs.mec_region_id,
    NOW(),
    NOW()
FROM users u
JOIN roles r ON r.id = u.role_id AND LOWER(r.slug) = 'mec'
JOIN users_locations ul ON ul.user_id = u.id
JOIN mec_region_scope mrs
    ON mrs.scope_type = 'location'
   AND mrs.scope_id = ul.location_id
JOIN mec_regions mr ON mr.id = mrs.mec_region_id AND mr.status = 1;

-- ---------------------------------------------------------------------------
-- 3) Plans: mec_region_id from PLAN CREATOR's divisions (same mapping)
--
--    Plans do not store division_id on the plan row; we use created_by →
--    users_divisions.division_id → mec_region_scope (division).
--
--    CHOOSE ONE of 3a / 3b / 3c (comment out the others).
-- ---------------------------------------------------------------------------

-- 3a) SAFE: set region only when ALL of the creator's divisions that appear in
--     scope map to exactly ONE distinct MEC region (no guesswork).
UPDATE plans p
JOIN (
    SELECT
        p2.id AS plan_id,
        MIN(mrs.mec_region_id) AS mec_region_id
    FROM plans p2
    JOIN users_divisions ud ON ud.user_id = p2.created_by
    JOIN mec_region_scope mrs
        ON mrs.scope_type = 'division'
       AND mrs.scope_id = ud.division_id
    JOIN mec_regions mr ON mr.id = mrs.mec_region_id AND mr.status = 1
    GROUP BY p2.id
    HAVING COUNT(DISTINCT mrs.mec_region_id) = 1
) x ON x.plan_id = p.id
SET p.mec_region_id = x.mec_region_id
WHERE (p.mec_region_id IS NULL OR p.mec_region_id = 0);

-- 3b) ALTERNATIVE: one region per plan using the creator's "first" division row
--     (lowest users_divisions.id). Use when each user should follow primary division.
-- UPDATE plans p
-- JOIN (
--     SELECT p2.id AS plan_id, mrs.mec_region_id
--     FROM plans p2
--     JOIN users_divisions ud ON ud.user_id = p2.created_by
--     JOIN (
--         SELECT user_id, MIN(id) AS first_ud_id
--         FROM users_divisions
--         GROUP BY user_id
--     ) z ON z.user_id = ud.user_id AND z.first_ud_id = ud.id
--     JOIN mec_region_scope mrs
--         ON mrs.scope_type = 'division'
--        AND mrs.scope_id = ud.division_id
--     JOIN mec_regions mr ON mr.id = mrs.mec_region_id AND mr.status = 1
-- ) x ON x.plan_id = p.id
-- SET p.mec_region_id = x.mec_region_id
-- WHERE (p.mec_region_id IS NULL OR p.mec_region_id = 0);

-- 3c) AGGRESSIVE: MIN(mec_region_id) per plan across all creator divisions
--     (can be wrong if creator spans multiple regions — review afterwards).
-- UPDATE plans p
-- JOIN (
--     SELECT p2.id AS plan_id, MIN(mrs.mec_region_id) AS mec_region_id
--     FROM plans p2
--     JOIN users_divisions ud ON ud.user_id = p2.created_by
--     JOIN mec_region_scope mrs
--         ON mrs.scope_type = 'division'
--        AND mrs.scope_id = ud.division_id
--     JOIN mec_regions mr ON mr.id = mrs.mec_region_id AND mr.status = 1
--     GROUP BY p2.id
-- ) x ON x.plan_id = p.id
-- SET p.mec_region_id = x.mec_region_id
-- WHERE (p.mec_region_id IS NULL OR p.mec_region_id = 0);

-- ---------------------------------------------------------------------------
-- 4) QA: plans still without region (creator has no division row, or no scope
--    row for those divisions, or multiple regions and you used 3a only)
-- ---------------------------------------------------------------------------
-- SELECT p.id, p.status, p.created_by, u.user_name
-- FROM plans p
-- JOIN users u ON u.id = p.created_by
-- WHERE (p.mec_region_id IS NULL OR p.mec_region_id = 0);

-- ---------------------------------------------------------------------------
-- 5) QA: creators whose divisions map to MORE than one MEC region (review plans)
-- ---------------------------------------------------------------------------
-- SELECT p.created_by, u.user_name,
--        COUNT(DISTINCT mrs.mec_region_id) AS region_cnt,
--        GROUP_CONCAT(DISTINCT mr.name ORDER BY mr.name) AS regions
-- FROM plans p
-- JOIN users_divisions ud ON ud.user_id = p.created_by
-- JOIN mec_region_scope mrs ON mrs.scope_type = 'division' AND mrs.scope_id = ud.division_id
-- JOIN mec_regions mr ON mr.id = mrs.mec_region_id AND mr.status = 1
-- GROUP BY p.created_by, u.user_name
-- HAVING region_cnt > 1;

COMMIT;

-- After this, re-save MEC users in the admin UI once if you want users_locations /
-- users_divisions regenerated from region scope (UserService::syncMecUserAssociations).