-- ============================================================================================
-- Rework `user` for CMS-only use.
-- Same result as the migration app/Database/Migrations/2026-09-13-000001_ReworkUserForCms.php;
-- use this when applying through phpMyAdmin instead of `php spark migrate`.
--
-- BEFORE RUNNING: export a backup (phpMyAdmin > Export) of user, user_activity, user_grade,
-- warning_letter, asset, requirement, test_case, general, role and action. Dropped columns and
-- tables cannot be recovered any other way.
--
-- Run STEP 1 first. Only continue to STEP 2 if every STEP 1 query returns no rows.
-- Both steps are safe to re-run.
-- ============================================================================================


-- ============================================================================================
-- STEP 1 -- pre-flight. Each query must return NO rows; fix any it returns before STEP 2,
-- or the NOT NULL / UNIQUE / FOREIGN KEY changes will stop halfway.
-- ============================================================================================

-- Accounts missing a name, email or password.
SELECT user_id, user_name, user_email FROM `user`
WHERE user_email IS NULL OR user_email = ''
   OR user_name IS NULL OR user_name = ''
   OR user_password IS NULL OR user_password = '';

-- Email addresses used by more than one account (sign-in looks accounts up by email).
SELECT user_email, COUNT(*) AS accounts FROM `user`
GROUP BY user_email HAVING COUNT(*) > 1;

-- Accounts pointing at a role that does not exist.
SELECT u.user_id, u.user_name, u.role_role_id FROM `user` u
LEFT JOIN `role` r ON r.role_id = u.role_role_id
WHERE r.role_id IS NULL AND u.role_role_id <> 'RL003';


-- ============================================================================================
-- STEP 2 -- the change.
-- ============================================================================================

-- HR leftovers. asset and warning_letter hold foreign keys to user, so they go first.
DROP TABLE IF EXISTS `warning_letter`;
DROP TABLE IF EXISTS `asset`;
DROP TABLE IF EXISTS `user_grade`;
DROP TABLE IF EXISTS `requirement`;
DROP TABLE IF EXISTS `test_case`;
DROP TABLE IF EXISTS `general`;

-- Keep sign-in history; drop leave accrual and request events from the HR app.
DELETE FROM `user_activity` WHERE user_activity_action NOT IN ('Login', 'Logout');

-- user_status was removed by an earlier migration; cleared here too in case this database
-- never received it.
ALTER TABLE `user`
    DROP COLUMN IF EXISTS `user_status`,
    DROP COLUMN IF EXISTS `user_nik`,
    DROP COLUMN IF EXISTS `user_personal_email`,
    DROP COLUMN IF EXISTS `user_birthday`,
    DROP COLUMN IF EXISTS `user_phone`,
    DROP COLUMN IF EXISTS `user_address`,
    DROP COLUMN IF EXISTS `user_emergency_contact`,
    DROP COLUMN IF EXISTS `user_blood_type`,
    DROP COLUMN IF EXISTS `user_type`,
    DROP COLUMN IF EXISTS `user_leave`,
    DROP COLUMN IF EXISTS `user_medical`,
    DROP COLUMN IF EXISTS `user_grade_id`,
    DROP COLUMN IF EXISTS `user_join_date`,
    DROP COLUMN IF EXISTS `user_probation_end`,
    DROP COLUMN IF EXISTS `user_contract_start`,
    DROP COLUMN IF EXISTS `user_contract_end`,
    DROP COLUMN IF EXISTS `user_permanent_date`,
    DROP COLUMN IF EXISTS `user_resign_date`,
    DROP COLUMN IF EXISTS `user_resign_detail`,
    DROP COLUMN IF EXISTS `user_work_experience`,
    DROP COLUMN IF EXISTS `user_gender`,
    DROP COLUMN IF EXISTS `user_lead`,
    DROP COLUMN IF EXISTS `user_refferal`;
DROP TABLE IF EXISTS `user_status`;

-- The fallback role: it exists but holds no action rows, so it grants nothing.
INSERT INTO `role` (role_id, role_name)
SELECT 'RL003', 'Pending Approval' FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `role` WHERE role_id = 'RL003');

-- user_password widens to fit password_hash(); existing md5 hashes still fit and are replaced
-- automatically the next time each account signs in. role_role_id defaulted to 'RL005',
-- a role that never existed.
ALTER TABLE `user`
    MODIFY `user_name`     VARCHAR(100) NOT NULL,
    MODIFY `user_email`    VARCHAR(100) NOT NULL,
    MODIFY `user_password` VARCHAR(255) NOT NULL,
    MODIFY `user_ava`      VARCHAR(255) NULL DEFAULT NULL,
    MODIFY `role_role_id`  VARCHAR(45)  NOT NULL DEFAULT 'RL003';

UPDATE `user` SET user_ava = NULL WHERE user_ava = '';

ALTER TABLE `user` ADD UNIQUE KEY IF NOT EXISTS `user_email` (`user_email`);

ALTER TABLE `user`
    ADD CONSTRAINT `fk_user_role` FOREIGN KEY IF NOT EXISTS (`role_role_id`)
    REFERENCES `role` (`role_id`) ON UPDATE CASCADE ON DELETE RESTRICT;

-- Record the migration, so a later `php spark migrate` does not consider it pending.
INSERT INTO `migrations` (version, class, `group`, namespace, time, batch)
SELECT '2026-09-13-000001', 'App\\Database\\Migrations\\ReworkUserForCms', 'default', 'App',
       UNIX_TIMESTAMP(), (SELECT COALESCE(MAX(m.batch), 0) + 1 FROM `migrations` m)
FROM DUAL
WHERE NOT EXISTS (SELECT 1 FROM `migrations` WHERE version = '2026-09-13-000001');

-- Check: 6 columns (user_id, user_name, user_email, user_password, user_ava, role_role_id).
SHOW COLUMNS FROM `user`;