-- ITI College ERP System - Complete Database Schema
-- Auto-generated for Private ITI Management

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+05:30";

CREATE DATABASE IF NOT EXISTS `iti_erp` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `iti_erp`;

-- ===================== CORE TABLES =====================

CREATE TABLE IF NOT EXISTS `settings` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `setting_key` VARCHAR(100) UNIQUE NOT NULL,
  `setting_value` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO `settings` (`setting_key`, `setting_value`) VALUES
('college_name', 'Industrial Training Institute'),
('college_short', 'ITI'),
('affiliation', 'NCVT / SCVT / DGT'),
('phone', '+91 9876543210'),
('email', 'info@iticollege.edu.in'),
('address', 'Assam, India'),
('website', 'https://iticollege.edu.in'),
('logo', 'assets/images/logo.png'),
('facebook', '#'),
('twitter', '#'),
('instagram', '#'),
('youtube', '#'),
('whatsapp', '+919876543210'),
('principal_name', 'Principal Name'),
('established_year', '2020')
ON DUPLICATE KEY UPDATE `setting_key`=`setting_key`;

-- Users & Authentication
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `username` VARCHAR(50) UNIQUE NOT NULL,
  `password` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(150) NOT NULL,
  `email` VARCHAR(150),
  `phone` VARCHAR(20),
  `role` ENUM('super_admin','principal','admission_admin','accountant','exam_admin','placement_admin','trainer','student','parent') DEFAULT 'student',
  `avatar` VARCHAR(255),
  `is_active` TINYINT(1) DEFAULT 1,
  `last_login` DATETIME,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO `users` (`username`, `password`, `full_name`, `role`) VALUES
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Super Administrator', 'super_admin')
ON DUPLICATE KEY UPDATE `username`=`username`;

-- ===================== TRADES =====================
CREATE TABLE IF NOT EXISTS `trades` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `trade_name` VARCHAR(150) NOT NULL,
  `trade_code` VARCHAR(20),
  `duration` VARCHAR(50) DEFAULT '2 Years',
  `total_seats` INT DEFAULT 20,
  `eligibility` VARCHAR(255) DEFAULT '10th Pass',
  `syllabus` TEXT,
  `career_scope` TEXT,
  `annual_fee` DECIMAL(10,2) DEFAULT 0,
  `description` TEXT,
  `icon` VARCHAR(100) DEFAULT 'fas fa-tools',
  `image` VARCHAR(255),
  `is_active` TINYINT(1) DEFAULT 1,
  `affiliation_type` ENUM('NCVT','SCVT','Both') DEFAULT 'NCVT',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

INSERT INTO `trades` (`trade_name`, `trade_code`, `duration`, `total_seats`, `eligibility`, `icon`, `career_scope`) VALUES
('Electrician', 'ELE', '2 Years', 24, '10th Pass', 'fas fa-bolt', 'Power plants, electrical maintenance, wiring contractor'),
('Fitter', 'FIT', '2 Years', 20, '10th Pass', 'fas fa-wrench', 'Manufacturing, assembly, maintenance mechanic'),
('Welder', 'WEL', '1 Year', 20, '8th Pass', 'fas fa-fire', 'Fabrication, shipbuilding, construction'),
('COPA', 'COPA', '1 Year', 24, '10th Pass', 'fas fa-laptop', 'Data entry, IT support, office automation'),
('Diesel Mechanic', 'DMM', '2 Years', 20, '10th Pass', 'fas fa-cogs', 'Automobile workshop, transport companies'),
('Wireman', 'WRM', '2 Years', 20, '8th Pass', 'fas fa-plug', 'Electrical wiring, maintenance'),
('Plumber', 'PLM', '1 Year', 20, '8th Pass', 'fas fa-faucet', 'Construction, plumbing contractor'),
('Sewing Technology', 'SEW', '1 Year', 20, '8th Pass', 'fas fa-scissors', 'Garment industry, fashion design')
ON DUPLICATE KEY UPDATE `trade_name`=`trade_name`;

-- ===================== STUDENTS =====================
CREATE TABLE IF NOT EXISTS `students` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT,
  `enrollment_no` VARCHAR(50) UNIQUE,
  `roll_no` VARCHAR(30),
  `student_name` VARCHAR(150) NOT NULL,
  `father_name` VARCHAR(150),
  `mother_name` VARCHAR(150),
  `dob` DATE,
  `gender` ENUM('Male','Female','Other'),
  `category` ENUM('General','OBC','SC','ST','EWS') DEFAULT 'General',
  `religion` VARCHAR(50),
  `aadhaar` VARCHAR(12),
  `mobile` VARCHAR(15),
  `email` VARCHAR(150),
  `address` TEXT,
  `district` VARCHAR(100),
  `state` VARCHAR(100) DEFAULT 'Assam',
  `pincode` VARCHAR(10),
  `qualification` VARCHAR(100),
  `marks_obtained` INT,
  `total_marks` INT,
  `percentage` DECIMAL(5,2),
  `trade_id` INT,
  `session` VARCHAR(20),
  `semester` TINYINT DEFAULT 1,
  `admission_date` DATE,
  `admission_type` ENUM('Regular','Lateral') DEFAULT 'Regular',
  `photo` VARCHAR(255),
  `signature` VARCHAR(255),
  `blood_group` VARCHAR(5),
  `hostel` TINYINT(1) DEFAULT 0,
  `scholarship` TINYINT(1) DEFAULT 0,
  `scholarship_type` VARCHAR(100),
  `status` ENUM('Active','Passed','Dropout','Suspended','Transferred') DEFAULT 'Active',
  `monthly_discount` DECIMAL(10,2) DEFAULT 0,
  `custom_annual_fee` DECIMAL(10,2),
  `remarks` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`trade_id`) REFERENCES `trades`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== DOCUMENTS =====================
CREATE TABLE IF NOT EXISTS `student_documents` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `doc_type` ENUM('photo','signature','marksheet','aadhaar','caste_cert','domicile','tc','other') NOT NULL,
  `file_path` VARCHAR(255),
  `verified` TINYINT(1) DEFAULT 0,
  `uploaded_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===================== ADMISSION =====================
CREATE TABLE IF NOT EXISTS `admissions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `application_no` VARCHAR(50) UNIQUE,
  `student_name` VARCHAR(150) NOT NULL,
  `father_name` VARCHAR(150),
  `mother_name` VARCHAR(150),
  `dob` DATE,
  `gender` ENUM('Male','Female','Other'),
  `category` ENUM('General','OBC','SC','ST','EWS'),
  `aadhaar` VARCHAR(12),
  `mobile` VARCHAR(15),
  `email` VARCHAR(150),
  `address` TEXT,
  `qualification` VARCHAR(100),
  `marks_obtained` INT,
  `total_marks` INT,
  `trade_id` INT,
  `session` VARCHAR(20),
  `photo` VARCHAR(255),
  `status` ENUM('Pending','Approved','Rejected','Enrolled') DEFAULT 'Pending',
  `remarks` TEXT,
  `applied_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`trade_id`) REFERENCES `trades`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== FEES =====================
CREATE TABLE IF NOT EXISTS `fee_structures` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `trade_id` INT,
  `fee_type` ENUM('Admission','Tuition','Exam','Hostel','Practical','Registration','Other') NOT NULL,
  `amount` DECIMAL(10,2) NOT NULL,
  `session` VARCHAR(20),
  `semester` TINYINT,
  `is_active` TINYINT(1) DEFAULT 1,
  FOREIGN KEY (`trade_id`) REFERENCES `trades`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `fee_collections` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `receipt_no` VARCHAR(50) UNIQUE,
  `amount` DECIMAL(10,2) NOT NULL,
  `fee_type` ENUM('Admission','Tuition','Exam','Hostel','Practical','Registration','Fine','Other') NOT NULL,
  `payment_mode` ENUM('Cash','UPI','Bank Transfer','Cheque','Online') DEFAULT 'Cash',
  `payment_date` DATE,
  `semester` TINYINT,
  `session` VARCHAR(20),
  `remarks` TEXT,
  `collected_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`collected_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== ATTENDANCE =====================
CREATE TABLE IF NOT EXISTS `attendance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `trade_id` INT,
  `date` DATE NOT NULL,
  `status` ENUM('Present','Absent','Late','Leave') DEFAULT 'Present',
  `type` ENUM('Theory','Practical') DEFAULT 'Theory',
  `marked_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`trade_id`) REFERENCES `trades`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `staff_attendance` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `status` ENUM('Present','Absent','Late','Leave','Half Day') DEFAULT 'Present',
  `check_in` TIME,
  `check_out` TIME,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===================== EXAMS =====================
CREATE TABLE IF NOT EXISTS `exams` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `exam_name` VARCHAR(200) NOT NULL,
  `exam_type` ENUM('Internal','NCVT','SCVT','Practical','Theory') NOT NULL,
  `trade_id` INT,
  `semester` TINYINT,
  `session` VARCHAR(20),
  `start_date` DATE,
  `end_date` DATE,
  `total_marks` INT DEFAULT 100,
  `pass_marks` INT DEFAULT 33,
  `status` ENUM('Upcoming','Ongoing','Completed','Cancelled') DEFAULT 'Upcoming',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`trade_id`) REFERENCES `trades`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `exam_results` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `exam_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  `theory_marks` DECIMAL(5,2),
  `practical_marks` DECIMAL(5,2),
  `internal_marks` DECIMAL(5,2),
  `total_obtained` DECIMAL(5,2),
  `grade` VARCHAR(5),
  `result` ENUM('Pass','Fail','Absent') DEFAULT 'Pass',
  `rank_in_trade` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`exam_id`) REFERENCES `exams`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===================== CERTIFICATES =====================
CREATE TABLE IF NOT EXISTS `certificates` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `certificate_no` VARCHAR(50) UNIQUE,
  `cert_type` ENUM('Training Completion','Industrial Visit','Participation','Character','NCVT','SCVT','Other') NOT NULL,
  `title` VARCHAR(200),
  `issue_date` DATE,
  `qr_code` VARCHAR(255),
  `file_path` VARCHAR(255),
  `is_verified` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===================== PLACEMENT =====================
CREATE TABLE IF NOT EXISTS `companies` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_name` VARCHAR(200) NOT NULL,
  `logo` VARCHAR(255),
  `industry` VARCHAR(100),
  `contact_person` VARCHAR(150),
  `phone` VARCHAR(20),
  `email` VARCHAR(150),
  `address` TEXT,
  `partnership_type` VARCHAR(100),
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `placements` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `company_id` INT,
  `position` VARCHAR(150),
  `salary` DECIMAL(10,2),
  `joining_date` DATE,
  `offer_letter` VARCHAR(255),
  `status` ENUM('Selected','Joined','Left','Pending') DEFAULT 'Pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`company_id`) REFERENCES `companies`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== SLIDERS =====================
CREATE TABLE IF NOT EXISTS `sliders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(200),
  `subtitle` TEXT,
  `button_text` VARCHAR(100),
  `button_link` VARCHAR(255),
  `image` VARCHAR(255),
  `sort_order` INT DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ===================== NOTICES =====================
CREATE TABLE IF NOT EXISTS `notices` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(300) NOT NULL,
  `content` TEXT,
  `pdf_file` VARCHAR(255),
  `priority` ENUM('Normal','Important','Urgent') DEFAULT 'Normal',
  `is_pinned` TINYINT(1) DEFAULT 0,
  `expiry_date` DATE,
  `target` ENUM('All','Students','Staff','Public') DEFAULT 'All',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_by` INT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== GALLERY =====================
CREATE TABLE IF NOT EXISTS `gallery_categories` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `sort_order` INT DEFAULT 0
) ENGINE=InnoDB;

INSERT INTO `gallery_categories` (`name`) VALUES ('Campus'),('Workshop'),('Sports'),('Annual Function'),('Practical'),('Industrial Visit')
ON DUPLICATE KEY UPDATE `name`=`name`;

CREATE TABLE IF NOT EXISTS `gallery` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `category_id` INT,
  `title` VARCHAR(200),
  `file_path` VARCHAR(255) NOT NULL,
  `file_type` ENUM('image','video') DEFAULT 'image',
  `sort_order` INT DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`category_id`) REFERENCES `gallery_categories`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== FACILITIES =====================
CREATE TABLE IF NOT EXISTS `facilities` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(150) NOT NULL,
  `description` TEXT,
  `icon` VARCHAR(100) DEFAULT 'fas fa-check',
  `image` VARCHAR(255),
  `sort_order` INT DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1
) ENGINE=InnoDB;

INSERT INTO `facilities` (`name`, `icon`, `description`) VALUES
('Smart Classroom', 'fas fa-chalkboard-teacher', 'Modern smart classrooms with digital boards'),
('Workshop Lab', 'fas fa-tools', 'Fully equipped workshop laboratories'),
('Hostel', 'fas fa-building', 'Comfortable hostel accommodation'),
('Library', 'fas fa-book', 'Well-stocked library with reference materials'),
('Computer Lab', 'fas fa-desktop', 'Modern computer lab with latest systems'),
('CCTV Surveillance', 'fas fa-video', '24/7 CCTV monitoring for safety'),
('WiFi Campus', 'fas fa-wifi', 'High-speed WiFi across campus'),
('Placement Cell', 'fas fa-briefcase', 'Dedicated placement cell for career support'),
('First Aid', 'fas fa-first-aid', 'First aid and medical facility available'),
('Sports', 'fas fa-futbol', 'Sports ground and indoor games')
ON DUPLICATE KEY UPDATE `name`=`name`;

-- ===================== STAFF =====================
CREATE TABLE IF NOT EXISTS `staff` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT,
  `staff_name` VARCHAR(150) NOT NULL,
  `designation` VARCHAR(100),
  `department` VARCHAR(100),
  `qualification` VARCHAR(200),
  `experience` VARCHAR(100),
  `specialization` VARCHAR(200),
  `phone` VARCHAR(20),
  `email` VARCHAR(150),
  `salary` DECIMAL(10,2),
  `joining_date` DATE,
  `photo` VARCHAR(255),
  `trade_id` INT,
  `status` ENUM('Active','Inactive','Resigned') DEFAULT 'Active',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  FOREIGN KEY (`trade_id`) REFERENCES `trades`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== HOSTEL =====================
CREATE TABLE IF NOT EXISTS `hostel_rooms` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `room_no` VARCHAR(20) NOT NULL,
  `floor` VARCHAR(20),
  `capacity` INT DEFAULT 4,
  `occupied` INT DEFAULT 0,
  `status` ENUM('Available','Full','Maintenance') DEFAULT 'Available'
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `hostel_allocations` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT NOT NULL,
  `room_id` INT NOT NULL,
  `bed_no` VARCHAR(10),
  `from_date` DATE,
  `to_date` DATE,
  `status` ENUM('Active','Vacated') DEFAULT 'Active',
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`room_id`) REFERENCES `hostel_rooms`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===================== LIBRARY =====================
CREATE TABLE IF NOT EXISTS `library_books` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(300) NOT NULL,
  `author` VARCHAR(200),
  `isbn` VARCHAR(20),
  `category` VARCHAR(100),
  `total_copies` INT DEFAULT 1,
  `available_copies` INT DEFAULT 1,
  `shelf_no` VARCHAR(20),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `book_issues` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `book_id` INT NOT NULL,
  `student_id` INT NOT NULL,
  `issue_date` DATE,
  `due_date` DATE,
  `return_date` DATE,
  `fine` DECIMAL(10,2) DEFAULT 0,
  `status` ENUM('Issued','Returned','Overdue') DEFAULT 'Issued',
  FOREIGN KEY (`book_id`) REFERENCES `library_books`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ===================== INVENTORY =====================
CREATE TABLE IF NOT EXISTS `inventory` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `item_name` VARCHAR(200) NOT NULL,
  `category` ENUM('Tools','Machines','Electrical','Safety','Consumable','Other') NOT NULL,
  `trade_id` INT,
  `quantity` INT DEFAULT 0,
  `unit` VARCHAR(20) DEFAULT 'Pcs',
  `condition_status` ENUM('Good','Fair','Damaged','Disposed') DEFAULT 'Good',
  `purchase_date` DATE,
  `purchase_price` DECIMAL(10,2),
  `supplier` VARCHAR(200),
  `location` VARCHAR(100),
  `last_maintenance` DATE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`trade_id`) REFERENCES `trades`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== COMPLAINTS =====================
CREATE TABLE IF NOT EXISTS `complaints` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT,
  `subject` VARCHAR(200) NOT NULL,
  `category` ENUM('Fees','Trainer','Hostel','Facility','Other') NOT NULL,
  `description` TEXT,
  `status` ENUM('Open','In Progress','Resolved','Closed') DEFAULT 'Open',
  `response` TEXT,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `resolved_at` DATETIME,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== TIMETABLE =====================
CREATE TABLE IF NOT EXISTS `timetable` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `trade_id` INT NOT NULL,
  `day` ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday') NOT NULL,
  `period` TINYINT NOT NULL,
  `subject` VARCHAR(150),
  `type` ENUM('Theory','Practical','Workshop') DEFAULT 'Theory',
  `instructor_id` INT,
  `room` VARCHAR(50),
  `start_time` TIME,
  `end_time` TIME,
  FOREIGN KEY (`trade_id`) REFERENCES `trades`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`instructor_id`) REFERENCES `staff`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== ALUMNI =====================
CREATE TABLE IF NOT EXISTS `alumni` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_id` INT,
  `name` VARCHAR(150) NOT NULL,
  `trade` VARCHAR(100),
  `passing_year` VARCHAR(10),
  `current_company` VARCHAR(200),
  `designation` VARCHAR(100),
  `salary_range` VARCHAR(50),
  `phone` VARCHAR(20),
  `email` VARCHAR(150),
  `photo` VARCHAR(255),
  `success_story` TEXT,
  `is_featured` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`student_id`) REFERENCES `students`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== AUDIT LOG =====================
CREATE TABLE IF NOT EXISTS `audit_log` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT,
  `action` VARCHAR(100),
  `module` VARCHAR(50),
  `details` TEXT,
  `ip_address` VARCHAR(45),
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB;

-- ===================== SUCCESSFUL STUDENTS (for public display) =====================
CREATE TABLE IF NOT EXISTS `successful_students` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `student_name` VARCHAR(150) NOT NULL,
  `trade` VARCHAR(100),
  `company_name` VARCHAR(200),
  `salary` VARCHAR(50),
  `photo` VARCHAR(255),
  `sort_order` INT DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ===================== PARTNERS (Industry) =====================
CREATE TABLE IF NOT EXISTS `partners` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `company_name` VARCHAR(200) NOT NULL,
  `logo` VARCHAR(255),
  `partnership_type` VARCHAR(100),
  `placement_details` TEXT,
  `website` VARCHAR(255),
  `sort_order` INT DEFAULT 0,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;