-- Migration Script for Database Import Issues
-- Use this when your dump file fails due to students table row size error

-- Step 1: Create students table first with optimized structure
CREATE TABLE IF NOT EXISTS `students` (
    `id` int NOT NULL AUTO_INCREMENT,
    `academic_year_id` int NOT NULL,
    `firstName` varchar(100) NOT NULL,
    `middleName` varchar(100) DEFAULT '',
    `lastName` varchar(100) NOT NULL,
    `studentType` enum('new', 'old', 'semi') NOT NULL,
    `class_id` int NOT NULL,
    `division_id` int NOT NULL,
    `admDate` datetime NOT NULL,
    `uidNo` varchar(12) NOT NULL,
    `father_aadhar` varchar(12) DEFAULT NULL,
    `mother_aadhar` varchar(12) DEFAULT NULL,
    `dob` datetime NOT NULL,
    `dobPlace` text DEFAULT NULL,
    `bloodGrp` varchar(10) DEFAULT NULL,
    `photo` text DEFAULT NULL,
    `identificationMark1` text DEFAULT NULL,
    `identificationMark2` text DEFAULT NULL,
    `fatherName` varchar(100) DEFAULT NULL,
    `motherName` varchar(100) DEFAULT NULL,
    `guardianName` varchar(100) DEFAULT '',
    `religion` varchar(50) NOT NULL,
    `category` varchar(50) NOT NULL,
    `nationality` varchar(50) NOT NULL,
    `mobileNo` varchar(10) DEFAULT NULL,
    `alternateMobileNo` varchar(10) DEFAULT NULL,
    `emailId` varchar(100) NOT NULL,
    `isActive` tinyint(1) NOT NULL DEFAULT '1',
    `admNo` int DEFAULT NULL,
    `regdNo` int DEFAULT NULL,
    `bus_route_id` int DEFAULT NULL,
    `password` varchar(100) NOT NULL,
    `carryForwardFee` int DEFAULT '0',
    `carryForwardFee_id` int DEFAULT NULL,
    `gender` varchar(10) NOT NULL,
    `weight` decimal(5, 2) DEFAULT NULL,
    `height` decimal(5, 2) DEFAULT NULL,
    `country` varchar(50) NOT NULL,
    `state` varchar(50) NOT NULL,
    `city` varchar(50) NOT NULL,
    `district` varchar(50) NOT NULL,
    `pincode` varchar(10) NOT NULL,
    `udiseCode` varchar(50) DEFAULT NULL,
    `penCode` varchar(50) DEFAULT NULL,
    `schoolType` varchar(50) NOT NULL,
    `classToBeAdmitted` varchar(50) DEFAULT NULL,
    `lastClassAttended` varchar(50) DEFAULT NULL,
    `lastSchoolAttended` text DEFAULT NULL,
    `fatherPhoto` text DEFAULT NULL,
    `fatherNumber` varchar(10) DEFAULT NULL,
    `fatherWhatsappNumber` varchar(10) DEFAULT NULL,
    `fatherQualification` varchar(100) DEFAULT NULL,
    `fatherOccupation` varchar(100) DEFAULT NULL,
    `motherPhoto` text DEFAULT NULL,
    `motherNumber` varchar(10) DEFAULT NULL,
    `motherWhatsappNumber` varchar(10) DEFAULT NULL,
    `motherQualification` varchar(100) DEFAULT NULL,
    `motherOccupation` varchar(100) DEFAULT NULL,
    `guardianNumber` varchar(10) DEFAULT NULL,
    `relationWithGuardian` varchar(50) DEFAULT NULL,
    `emergencyContactNumber` varchar(10) NOT NULL,
    `permanentAddress` text NOT NULL,
    `presentAddress` text NOT NULL,
    `hostelType` varchar(50) DEFAULT NULL,
    `createdAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updatedAt` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`),
    UNIQUE KEY `uidNo` (`uidNo`),
    KEY `idx_class_division` (`class_id`, `division_id`),
    KEY `idx_admNo` (`admNo`),
    KEY `idx_regdNo` (`regdNo`),
    KEY `idx_academic_year` (`academic_year_id`)
) ENGINE = InnoDB AUTO_INCREMENT = 100 DEFAULT CHARSET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

-- Step 2: Create Users table (if it doesn't exist)
CREATE TABLE IF NOT EXISTS `Users` (
    `userId` INT AUTO_INCREMENT PRIMARY KEY,
    `userName` VARCHAR(255) NOT NULL,
    `userType` VARCHAR(255) NOT NULL,
    `userPassword` VARCHAR(255) NOT NULL,
    `createdAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updatedAt` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `userId` (`userId`),
    CHECK (
        `userType` IN ('staff', 'teacher', 'admin')
    )
) AUTO_INCREMENT = 1000;

-- Step 3: Now you can run your dump file with --force to skip existing tables
-- mysql -u username -p --force database_name < your_dump_file.sql

-- Alternative: Modify existing table step by step (if students table exists but has wrong structure)
-- ALTER TABLE `students`
-- MODIFY COLUMN `firstName` varchar(100) NOT NULL,
-- MODIFY COLUMN `middleName` varchar(100) DEFAULT '',
-- MODIFY COLUMN `lastName` varchar(100) NOT NULL,
-- MODIFY COLUMN `dobPlace` text DEFAULT NULL,
-- MODIFY COLUMN `bloodGrp` varchar(10) DEFAULT NULL,
-- MODIFY COLUMN `photo` text DEFAULT NULL,
-- MODIFY COLUMN `identificationMark1` text DEFAULT NULL,
-- MODIFY COLUMN `identificationMark2` text DEFAULT NULL,
-- MODIFY COLUMN `fatherName` varchar(100) DEFAULT NULL,
-- MODIFY COLUMN `motherName` varchar(100) DEFAULT NULL,
-- MODIFY COLUMN `guardianName` varchar(100) DEFAULT '',
-- MODIFY COLUMN `religion` varchar(50) NOT NULL,
-- MODIFY COLUMN `category` varchar(50) NOT NULL,
-- MODIFY COLUMN `nationality` varchar(50) NOT NULL,
-- MODIFY COLUMN `emailId` varchar(100) NOT NULL,
-- MODIFY COLUMN `password` varchar(100) NOT NULL,
-- MODIFY COLUMN `gender` varchar(10) NOT NULL,
-- MODIFY COLUMN `country` varchar(50) NOT NULL,
-- MODIFY COLUMN `state` varchar(50) NOT NULL,
-- MODIFY COLUMN `city` varchar(50) NOT NULL,
-- MODIFY COLUMN `district` varchar(50) NOT NULL,
-- MODIFY COLUMN `pincode` varchar(10) NOT NULL,
-- MODIFY COLUMN `udiseCode` varchar(50) DEFAULT NULL,
-- MODIFY COLUMN `penCode` varchar(50) DEFAULT NULL,
-- MODIFY COLUMN `schoolType` varchar(50) NOT NULL,
-- MODIFY COLUMN `classToBeAdmitted` varchar(50) DEFAULT NULL,
-- MODIFY COLUMN `lastClassAttended` varchar(50) DEFAULT NULL,
-- MODIFY COLUMN `lastSchoolAttended` text DEFAULT NULL,
-- MODIFY COLUMN `fatherPhoto` text DEFAULT NULL,
-- MODIFY COLUMN `fatherQualification` varchar(100) DEFAULT NULL,
-- MODIFY COLUMN `fatherOccupation` varchar(100) DEFAULT NULL,
-- MODIFY COLUMN `motherPhoto` text DEFAULT NULL,
-- MODIFY COLUMN `motherQualification` varchar(100) DEFAULT NULL,
-- MODIFY COLUMN `motherOccupation` varchar(100) DEFAULT NULL,
-- MODIFY COLUMN `relationWithGuardian` varchar(50) DEFAULT NULL,
-- MODIFY COLUMN `hostelType` varchar(50) DEFAULT NULL;