-- Optimized Students Table Creation Script
-- This script fixes the MySQL row size limit error by using appropriate data types

CREATE TABLE `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;