-- Fix existing users.id / foreign key type mismatch in prepplus
-- Run this in MySQL after stopping the backend server.

USE prepplus;
SET FOREIGN_KEY_CHECKS = 0;

-- 1) Drop all foreign keys referencing users(id)
SELECT CONCAT('ALTER TABLE `', TABLE_NAME, '` DROP FOREIGN KEY `', CONSTRAINT_NAME, '`;') AS stmt
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = DATABASE()
  AND REFERENCED_TABLE_NAME = 'users'
  AND REFERENCED_COLUMN_NAME = 'id';

-- Copy the output from the above SELECT and execute it.

-- 2) Alter users.id to BIGINT UNSIGNED AUTO_INCREMENT
ALTER TABLE users MODIFY COLUMN id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT;

-- 3) Alter all child user_id columns to BIGINT UNSIGNED
SELECT DISTINCT CONCAT(
  'ALTER TABLE `', TABLE_NAME, '` MODIFY COLUMN `', COLUMN_NAME, '` BIGINT UNSIGNED',
  IF(IS_NULLABLE = 'YES', ' NULL', ' NOT NULL'),
  ';'
) AS stmt
FROM information_schema.COLUMNS c
JOIN information_schema.KEY_COLUMN_USAGE k
  ON c.TABLE_SCHEMA = k.TABLE_SCHEMA
  AND c.TABLE_NAME = k.TABLE_NAME
  AND c.COLUMN_NAME = k.COLUMN_NAME
WHERE k.REFERENCED_TABLE_SCHEMA = DATABASE()
  AND k.REFERENCED_TABLE_NAME = 'users'
  AND k.REFERENCED_COLUMN_NAME = 'id';

-- Copy the output from the above SELECT and execute it.

-- 4) Recreate user FKs with matching types
SELECT CONCAT(
  'ALTER TABLE `', TABLE_NAME, '` ADD CONSTRAINT `', CONSTRAINT_NAME,
  '` FOREIGN KEY (`', COLUMN_NAME, '`) REFERENCES `users`(`id`) ON DELETE CASCADE;'
) AS stmt
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = DATABASE()
  AND REFERENCED_TABLE_NAME = 'users'
  AND REFERENCED_COLUMN_NAME = 'id';

-- Copy the output from the above SELECT and execute it.

SET FOREIGN_KEY_CHECKS = 1;

-- IMPORTANT: after applying this fix, set synchronize=false in src/db.ts
-- and restart the backend to avoid repeated TypeORM auto-schema changes.