-- ====================================================================
-- MIGRATION SCRIPT: beszerzes_dev TO staging_beszer
-- Generated: 2025-09-16
-- 
-- This script migrates the database structure from beszerzes_dev 
-- to match the staging_beszer structure.
-- ====================================================================

-- ====================================================================
-- CRITICAL: BACKUP YOUR DATABASE BEFORE RUNNING THIS SCRIPT!
-- Run: mysqldump -u user -p beszerzes_dev > backup_before_migration.sql
-- ====================================================================

-- Set proper SQL mode for the migration

-- ====================================================================
-- SECTION 1: NEW TABLES (Tables that exist in dev but not in staging)
-- These are new tables that need to be added to staging
-- ====================================================================

-- New table: EntityPermission (Permission management table)
-- This table handles entity-level permissions for users
CREATE TABLE IF NOT EXISTS `EntityPermission` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `userId` int(11) NOT NULL,
  `entityId` int(11) NOT NULL,
  `entityType` varchar(191) NOT NULL,
  `permission` varchar(191) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `EntityPermission_userId_entityId_entityType_permission_key` (`userId`,`entityId`,`entityType`,`permission`),
  CONSTRAINT `EntityPermission_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `Kezelo` (`Azonosito`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- New table: KezeloRole (User-Role mapping table)
-- Links users to their roles
CREATE TABLE IF NOT EXISTS `KezeloRole` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `kezeloId` int(11) NOT NULL,
  `roleId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `KezeloRole_kezeloId_roleId_key` (`kezeloId`,`roleId`),
  KEY `KezeloRole_roleId_fkey` (`roleId`),
  CONSTRAINT `KezeloRole_kezeloId_fkey` FOREIGN KEY (`kezeloId`) REFERENCES `Kezelo` (`Azonosito`) ON UPDATE CASCADE,
  CONSTRAINT `KezeloRole_roleId_fkey` FOREIGN KEY (`roleId`) REFERENCES `Role` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- New table: Permission (Permission definitions)
-- Stores all available permissions in the system
CREATE TABLE IF NOT EXISTS `Permission` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Permission_name_key` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=51 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- New table: Role (Role definitions)
-- Stores all available roles in the system
CREATE TABLE IF NOT EXISTS `Role` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(191) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `Role_name_key` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- New table: RolePermission (Role-Permission mapping)
-- Links roles to their permissions
CREATE TABLE IF NOT EXISTS `RolePermission` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `roleId` int(11) NOT NULL,
  `permissionId` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `RolePermission_roleId_permissionId_key` (`roleId`,`permissionId`),
  KEY `RolePermission_permissionId_fkey` (`permissionId`),
  CONSTRAINT `RolePermission_permissionId_fkey` FOREIGN KEY (`permissionId`) REFERENCES `Permission` (`id`) ON UPDATE CASCADE,
  CONSTRAINT `RolePermission_roleId_fkey` FOREIGN KEY (`roleId`) REFERENCES `Role` (`id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=606 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- New table: _prisma_migrations (Prisma ORM migration tracking)
-- Tracks database migrations performed by Prisma ORM
CREATE TABLE IF NOT EXISTS `_prisma_migrations` (
  `id` varchar(36) NOT NULL,
  `checksum` varchar(64) NOT NULL,
  `finished_at` datetime(3) DEFAULT NULL,
  `migration_name` varchar(255) NOT NULL,
  `logs` text DEFAULT NULL,
  `rolled_back_at` datetime(3) DEFAULT NULL,
  `started_at` datetime(3) NOT NULL DEFAULT current_timestamp(3),
  `applied_steps_count` int(10) unsigned NOT NULL DEFAULT 0,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================================================
-- SECTION 2: STRUCTURAL CHANGES TO EXISTING TABLES
-- These are IMPORTANT changes that modify existing table structures
-- ====================================================================

-- ====================================================================
-- IMPORTANT: Bank table structural change
-- Adding 'id' column as primary key and removing composite primary key
-- ====================================================================
-- Check if id column exists in Bank table and add it only if missing
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'Bank'
  AND COLUMN_NAME = 'id';

SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `Bank` ADD COLUMN `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST',
  'SELECT "Column id already exists in Bank table" as Info');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Note: The Bank table in staging doesn't have a primary key, while dev has an 'id' primary key

-- ====================================================================
-- IMPORTANT: Beszerzes table - Adding missing 'Szamlaszam' column
-- This column exists in staging but is missing in dev
-- ====================================================================
-- Check if Szamlaszam column exists and add it only if missing
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'Beszerzes'
  AND COLUMN_NAME = 'Szamlaszam';

SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `Beszerzes` ADD COLUMN `Szamlaszam` text DEFAULT NULL AFTER `Megjegyzes`',
  'SELECT "Column Szamlaszam already exists in Beszerzes table" as Info');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

-- ====================================================================
-- IMPORTANT: Munkak table - restriction column default value change
-- Dev has DEFAULT 0, staging has no default (NOT NULL)
-- ====================================================================
-- Check if restriction column exists and modify it only if it exists
SET @col_exists = 0;
SELECT COUNT(*) INTO @col_exists
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'Munkak'
  AND COLUMN_NAME = 'restriction';

SET @sql = IF(@col_exists > 0,
  'ALTER TABLE `Munkak` MODIFY COLUMN `restriction` smallint(6) NOT NULL',
  'SELECT "Column restriction does not exist in Munkak table" as Info');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
-- Note: Removing the DEFAULT 0 to match staging

-- ====================================================================
-- SECTION 3: REMOVED TABLES (Tables that exist in staging but not in dev)
-- These tables need to be created as they exist in staging
-- ====================================================================

-- feladat_kiosztas table (Task assignment table)
-- This table exists in staging but not in dev - adding it for completeness
CREATE TABLE IF NOT EXISTS `feladat_kiosztas` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `cim` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_nopad_as_cs DEFAULT NULL,
  `leiras` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_nopad_as_cs DEFAULT NULL,
  `allapot` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT 'folyamatban',
  `dolgozo` varchar(45) CHARACTER SET utf8mb4 COLLATE utf8mb4_uca1400_nopad_as_cs DEFAULT 'Józsi',
  `letrehozas` date DEFAULT NULL,
  `befejezes` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ====================================================================
-- SECTION 4: AUTO_INCREMENT VALUE DIFFERENCES
-- Note: These are informational only - adjust if needed
-- ====================================================================
-- The following tables have different AUTO_INCREMENT values between dev and staging:
-- 
-- Aru_nev: dev=7025, staging=7286
-- UPDATE `Aru_nev` AUTO_INCREMENT = 7286; -- Uncomment if you want to sync AUTO_INCREMENT
--
-- Bank: dev=137, staging has no AUTO_INCREMENT (after adding id column, set it)
-- ALTER TABLE `Bank` AUTO_INCREMENT = 137;
--
-- Beszerzes: dev=52402, staging=57006  
-- UPDATE `Beszerzes` AUTO_INCREMENT = 57006; -- Uncomment if you want to sync AUTO_INCREMENT
--
-- Ceg_nev: dev=1183, staging=1307
-- UPDATE `Ceg_nev` AUTO_INCREMENT = 1307; -- Uncomment if you want to sync AUTO_INCREMENT
--
-- Hazi_penztar: dev=2899, staging=2977
-- UPDATE `Hazi_penztar` AUTO_INCREMENT = 2977; -- Uncomment if you want to sync AUTO_INCREMENT
--
-- Kezelo: dev=55, staging=60
-- UPDATE `Kezelo` AUTO_INCREMENT = 60; -- Uncomment if you want to sync AUTO_INCREMENT
--
-- Kiszallasok: dev=11, staging=12
-- UPDATE `Kiszallasok` AUTO_INCREMENT = 12; -- Uncomment if you want to sync AUTO_INCREMENT
--
-- Munkak: dev=5587, staging=6020
-- UPDATE `Munkak` AUTO_INCREMENT = 6020; -- Uncomment if you want to sync AUTO_INCREMENT
--
-- Rovid_nev: dev=6916, staging=7298
-- UPDATE `Rovid_nev` AUTO_INCREMENT = 7298; -- Uncomment if you want to sync AUTO_INCREMENT
--
-- customers: dev=36, staging=50
-- UPDATE `customers` AUTO_INCREMENT = 50; -- Uncomment if you want to sync AUTO_INCREMENT
--
-- worksheet_validation_links: dev=51, staging=496
-- UPDATE `worksheet_validation_links` AUTO_INCREMENT = 496; -- Uncomment if you want to sync AUTO_INCREMENT
--
-- worksheets: dev=47, staging=496
-- UPDATE `worksheets` AUTO_INCREMENT = 496; -- Uncomment if you want to sync AUTO_INCREMENT

-- ====================================================================
-- SECTION 5: COLLATION DIFFERENCES (Minor - informational)
-- ====================================================================
-- Note: Some minor collation differences exist but shouldn't affect functionality:
-- - feladat_kiosztas uses utf8mb4_uca1400_nopad_as_cs and utf8mb4_bin for some columns

-- ====================================================================
-- SECTION 6: RE-ENABLE CONSTRAINTS
-- ====================================================================

-- ====================================================================
-- POST-MIGRATION STEPS:
-- 1. Verify all tables are created/modified correctly
-- 2. Check foreign key relationships are intact
-- 3. Test application functionality
-- 4. Consider updating AUTO_INCREMENT values if needed
-- ====================================================================

-- Verification queries (run these after migration):
-- SHOW TABLES;
-- DESCRIBE Bank; -- Should have 'id' column
-- DESCRIBE Beszerzes; -- Should have 'Szamlaszam' column
-- DESCRIBE EntityPermission; -- Should exist
-- DESCRIBE Permission; -- Should exist
-- DESCRIBE Role; -- Should exist
-- DESCRIBE RolePermission; -- Should exist
-- DESCRIBE KezeloRole; -- Should exist
-- DESCRIBE feladat_kiosztas; -- Should exist