-- Migration helper for leave_requests and leave_balances UUID columns
-- ------------------------------------------------------------------
-- PostgreSQL section
-- ------------------
-- Adds the new UUID columns if they do not exist and attempts to backfill
-- them using the payroll users directory and latest org membership.
DO $$
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'leave_requests' AND column_name = 'user_id'
    ) THEN
        ALTER TABLE leave_requests ADD COLUMN user_id uuid NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'leave_requests' AND column_name = 'org_id'
    ) THEN
        ALTER TABLE leave_requests ADD COLUMN org_id uuid NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'leave_balances' AND column_name = 'user_id'
    ) THEN
        ALTER TABLE leave_balances ADD COLUMN user_id uuid NULL;
    END IF;

    IF NOT EXISTS (
        SELECT 1 FROM information_schema.columns
        WHERE table_name = 'leave_balances' AND column_name = 'org_id'
    ) THEN
        ALTER TABLE leave_balances ADD COLUMN org_id uuid NULL;
    END IF;
END$$;

-- Backfill leave_requests.user_id/org_id using the payroll users table.
WITH matched_requests AS (
    SELECT lr.id,
           u.user_id,
           (
               SELECT uom.org_id
               FROM user_org_memberships uom
               WHERE uom.user_id = u.user_id AND uom.isDeleted = false
               ORDER BY uom.joined_at DESC
               LIMIT 1
           ) AS org_id
    FROM leave_requests lr
    JOIN users u ON LOWER(u.full_name) = LOWER(lr."employeeName")
)
UPDATE leave_requests lr
SET user_id = matched_requests.user_id,
    org_id = COALESCE(lr.org_id, matched_requests.org_id)
FROM matched_requests
WHERE lr.id = matched_requests.id
  AND lr.user_id IS NULL;

-- Backfill leave_balances.user_id/org_id by matching employees to users.
WITH matched_balances AS (
    SELECT lb.id,
           u.user_id,
           (
               SELECT uom.org_id
               FROM user_org_memberships uom
               WHERE uom.user_id = u.user_id AND uom.isDeleted = false
               ORDER BY uom.joined_at DESC
               LIMIT 1
           ) AS org_id
    FROM leave_balances lb
    JOIN employees e ON e.id = lb.employee_id
    JOIN users u ON LOWER(u.full_name) = LOWER(e.name)
)
UPDATE leave_balances lb
SET user_id = matched_balances.user_id,
    org_id = COALESCE(lb.org_id, matched_balances.org_id)
FROM matched_balances
WHERE lb.id = matched_balances.id
  AND lb.user_id IS NULL;

-- MySQL section
-- -------------
-- Apply the schema changes and perform a best-effort backfill. Adjust
-- the JOIN conditions if your production data needs different matching logic.
ALTER TABLE `leave_requests`
    ADD COLUMN IF NOT EXISTS `user_id` CHAR(36) NULL,
    ADD COLUMN IF NOT EXISTS `org_id` CHAR(36) NULL;

ALTER TABLE `leave_balances`
    MODIFY COLUMN `employee_id` INT NULL,
    ADD COLUMN IF NOT EXISTS `user_id` CHAR(36) NULL,
    ADD COLUMN IF NOT EXISTS `org_id` CHAR(36) NULL;

UPDATE `leave_requests` lr
JOIN `users` u ON LOWER(u.full_name) = LOWER(lr.`employeeName`)
LEFT JOIN (
    SELECT base.user_id,
           SUBSTRING_INDEX(GROUP_CONCAT(base.org_id ORDER BY base.joined_at DESC), ',', 1) AS org_id
    FROM user_org_memberships base
    WHERE base.isDeleted = 0
    GROUP BY base.user_id
) latest ON latest.user_id = u.user_id
SET lr.user_id = u.user_id,
    lr.org_id = COALESCE(lr.org_id, latest.org_id)
WHERE lr.user_id IS NULL;

UPDATE `leave_balances` lb
JOIN `employees` e ON e.id = lb.employee_id
JOIN `users` u ON LOWER(u.full_name) = LOWER(e.name)
LEFT JOIN (
    SELECT base.user_id,
           SUBSTRING_INDEX(GROUP_CONCAT(base.org_id ORDER BY base.joined_at DESC), ',', 1) AS org_id
    FROM user_org_memberships base
    WHERE base.isDeleted = 0
    GROUP BY base.user_id
) latest ON latest.user_id = u.user_id
SET lb.user_id = u.user_id,
    lb.org_id = COALESCE(lb.org_id, latest.org_id)
WHERE lb.user_id IS NULL;

-- Rows that could not be matched should be reviewed manually. Consider
-- exporting the unmatched rows and mapping them to payroll users by email.
