-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `miti_admission_db`
--

-- --------------------------------------------------------

--
-- Table structure for table `academic_records`
--

CREATE TABLE `academic_records` (
  `record_id` int(11) NOT NULL,
  `applicant_id` int(11) NOT NULL,
  `exam_type` enum('WAEC','NECO','Others') NOT NULL,
  `exam_year` int(4) NOT NULL,
  `subject` varchar(100) NOT NULL,
  `grade` varchar(10) NOT NULL,
  `attachment_path` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `academic_records`
--

INSERT INTO `academic_records` (`record_id`, `applicant_id`, `exam_type`, `exam_year`, `subject`, `grade`, `attachment_path`, `created_at`) VALUES
(12, 14, 'WAEC', 2024, 'Mathematics', 'A1', '/school_admission/public/uploads/academic_records/acad_68be974716b03.png', '2025-09-08 08:43:51');

-- --------------------------------------------------------

--
-- Table structure for table `admission_letters`
--

CREATE TABLE `admission_letters` (
  `letter_id` int(11) NOT NULL,
  `applicant_id` int(11) NOT NULL,
  `letter_content` longtext NOT NULL,
  `generated_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `generated_by_user_id` int(11) NOT NULL,
  `printed_at` timestamp NULL DEFAULT NULL,
  `printed_by_user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `admission_letters`
--

INSERT INTO `admission_letters` (`letter_id`, `applicant_id`, `letter_content`, `generated_at`, `generated_by_user_id`, `printed_at`, `printed_by_user_id`) VALUES
(4, 14, '<!DOCTYPE html>\n<html lang=\"en\">\n<head>\n    <meta charset=\"UTF-8\">\n    <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\">\n    <title>Provisional Admission Letter</title>\n    <style>\n        body {\n            font-family: \'Times New Roman\', serif;\n            margin: 0;\n            padding: 0;\n            background-color: #f9f9f9;\n            color: #333;\n            line-height: 1.6;\n        }\n        .print-area { /* This new div will wrap content to be printed */\n            width: 210mm; /* A4 width */\n            min-height: 297mm; /* A4 height */\n            margin: 20mm auto;\n            padding: 25mm;\n            background: white;\n            border: 1px solid #ccc;\n            box-shadow: 0 0 10px rgba(0,0,0,0.1);\n            box-sizing: border-box;\n        }\n        .header {\n            text-align: center;\n            margin-bottom: 30px;\n        }\n        .header img {\n            max-width: 100px; /* Adjust logo size */\n            margin-bottom: 10px;\n        }\n        .header h1 {\n            font-size: 28px;\n            color: #0056b3;\n            margin: 0;\n            text-transform: uppercase;\n        }\n        .header h2 {\n            font-size: 18px;\n            color: #555;\n            margin: 5px 0;\n            font-weight: normal;\n        }\n        .header p {\n            font-size: 14px;\n            color: #777;\n            margin: 2px 0;\n        }\n        .title {\n            text-align: center;\n            font-size: 24px;\n            font-weight: bold;\n            margin: 40px 0;\n            text-decoration: underline;\n            color: #0056b3;\n        }\n        .content {\n            font-size: 16px;\n            margin-bottom: 20px;\n        }\n        .content p {\n            margin-bottom: 15px;\n            text-align: justify;\n        }\n        .details-table {\n            width: 100%;\n            border-collapse: collapse;\n            margin: 20px 0;\n        }\n        .details-table td {\n            padding: 8px 0;\n            vertical-align: top;\n        }\n        .details-table td:first-child {\n            font-weight: bold;\n            width: 30%;\n        }\n        .requirements {\n            margin-top: 30px;\n            margin-bottom: 30px;\n        }\n        .requirements ul {\n            list-style: disc;\n            margin-left: 20px;\n            padding: 0;\n        }\n        .requirements ul li {\n            margin-bottom: 8px;\n            text-align: justify;\n        }\n        .signature {\n            margin-top: 50px;\n            text-align: left;\n        }\n        .signature img {\n            max-width: 150px; /* Adjust signature image size */\n            display: block;\n            margin-bottom: 5px;\n        }\n        .signature p {\n            margin: 0;\n            font-weight: bold;\n            border-top: 1px solid #000;\n            padding-top: 5px;\n            display: inline-block;\n        }\n        .footer {\n            text-align: center;\n            margin-top: 40px;\n            font-size: 12px;\n            color: #777;\n        }\n\n        /* Print Button Styles (visible on screen, hidden on print) */\n        .print-button-container {\n            text-align: center;\n            margin: 20px auto;\n            max-width: 210mm; /* Match letter width for centering */\n        }\n        .print-button {\n            background-color: #007bff;\n            color: white;\n            padding: 10px 20px;\n            border: none;\n            border-radius: 5px;\n            cursor: pointer;\n            font-size: 16px;\n            font-family: Arial, sans-serif;\n            transition: background-color 0.3s ease;\n        }\n        .print-button:hover {\n            background-color: #0056b3;\n        }\n\n        /* Print Specific Styles */\n        @media print {\n            body {\n                margin: 0;\n                -webkit-print-color-adjust: exact; /* Ensure background colors are printed */\n                print-color-adjust: exact;\n            }\n            .print-button-container {\n                display: none; /* Hide the print button when printing */\n            }\n            .print-area {\n                margin: 0;\n                border: none;\n                box-shadow: none;\n                width: 210mm;\n                height: 297mm;\n                padding: 15mm; /* Adjust padding for better print margins if needed */\n            }\n        }\n    </style>\n</head>\n<body>\n    <div class=\"print-button-container\">\n        <button class=\"print-button\" onclick=\"window.print()\"><i class=\"fas fa-print\"></i> Print Admission Letter</button>\n        <button class=\"print-button\" style=\"background-color: #6c757d;\" onclick=\"window.close()\"><i class=\"fas fa-times-circle\"></i> Close Window</button>\n    </div>\n    <div class=\"print-area\">\n        <div class=\"header\">\n            <!-- You can add your MITI logo here if you have one in public/assets -->\n            <!-- <img src=\"/school_admission/public/assets/miti_logo.png\" alt=\"MITI Logo\"> -->\n            <h1>MINNA INSTITUTE OF TECHNOLOGY AND INNOVATION</h1>\n            <h2>SKILLS FOR SELF RELIANCE</h2>\n            <p>OPP. OLD SECRETARIAT, KETEREN, GWARI RD. MINNA, NIGER STATE.</p>\n        </div>\n\n        <div class=\"title\">\n            OFFER OF PROVISIONAL ADMISSION 2025/2026        </div>\n\n        <div class=\"content\">\n            <p>Date: 01-January-1970</p>\n            <p>I write to inform you that you have been offered provisional admission, as indicated below, subject to meeting all registration requirements.</p>\n\n            <table class=\"details-table\">\n                <tr>\n                    <td>Name:</td>\n                    <td><strong>SHUAIB ABDOOL </strong></td>\n                </tr>\n                <tr>\n                    <td>Application No:</td>\n                    <td><strong>0</strong></td>\n                </tr>\n                <tr>\n                    <td>Level:</td>\n                    <td><strong>NID 1</strong></td>\n                </tr>\n                <tr>\n                    <td>Course:</td>\n                    <td><strong>BUSINESS MANAGEMENT &amp; INFORMATION TECHNOLOGY</strong></td>\n                </tr>\n            </table>\n\n            <p>This is subject to meeting the following requirements: You MUST ensure that you complete the registration process within the stipulated period. For the purpose of screening and documentation, you are required to bring along the under listed items.</p>\n\n            <div class=\"requirements\">\n                <ul>\n                    <li>Proceed to the Department for verification of original credentials.</li>\n                    <li>Photocopies of Credentials (Primary School Certificate, Secondary School Certificate and L.G.A)</li>\n                    <li>Complete your registration within two(2) weeks from the date of this offer (i.e., by **22-September-2025**).</li>\n                </ul>\n            </div>\n\n            <p>Ensure that the particulars and the information you are providing are correct in every respect. If at any time, it is discovered that you do not satisfy the minimum entry requirements prescribed for the course of the study at the time of this offer, or that any of the qualifications you claimed to have is false or incorrect, you will be required to withdraw from the school.</p>\n\n            <p>The name by which you are hereby admitted and by which you will be registered is the one that will appear on any certificate that the school may issue to you on successful completion of your programme.</p>\n\n            <p>Candidates are hereby informed that fees must be paid at the beginning of the session.</p>\n\n            <p>All fees paid are non-refundable.</p>\n\n            <p>Please accept our congratulations.</p>\n        </div>\n\n        <div class=\"signature\">\n            <!-- If you have a scanned signature image for the Registrar, you can place it here -->\n            <!-- <img src=\"/school_admission/public/assets/registrar_signature.png\" alt=\"Registrar Signature\"> -->\n            <p>Saidu Kabiru</p>\n            <p style=\"border:none;\">Registrar</p>\n        </div>\n\n        <div class=\"footer\">\n            <p>Minna Institute of Technology and Innovation &copy; 2025</p>\n        </div>\n    </div>\n</body>\n</html>', '2025-09-08 08:45:56', 1, '2025-09-08 09:46:53', 2);

-- --------------------------------------------------------

--
-- Table structure for table `applicants`
--

CREATE TABLE `applicants` (
  `applicant_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `first_name` varchar(100) NOT NULL,
  `last_name` varchar(100) NOT NULL,
  `other_names` varchar(100) DEFAULT NULL,
  `date_of_birth` date NOT NULL,
  `marital_status` enum('Single','Married','Divorced','Widowed') NOT NULL DEFAULT 'Single',
  `gender` enum('Male','Female','Other') NOT NULL,
  `tribe` varchar(100) DEFAULT NULL,
  `address` text NOT NULL,
  `phone_number` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `application_no` varchar(100) DEFAULT NULL,
  `course_id` int(11) NOT NULL,
  `level` varchar(50) NOT NULL DEFAULT 'NID 1',
  `application_date` date NOT NULL,
  `status` enum('pending_review','approved','rejected','admission_letter_generated') NOT NULL DEFAULT 'pending_review',
  `branch_id` int(11) NOT NULL,
  `approved_by_user_id` int(11) DEFAULT NULL,
  `approval_date` timestamp NULL DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `parent_guardian_name` varchar(255) DEFAULT NULL,
  `parent_guardian_contact` varchar(50) DEFAULT NULL,
  `parent_guardian_relationship` varchar(100) DEFAULT NULL,
  `parent_guardian_address` text DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `applicants`
--

INSERT INTO `applicants` (`applicant_id`, `user_id`, `first_name`, `last_name`, `other_names`, `date_of_birth`, `marital_status`, `gender`, `tribe`, `address`, `phone_number`, `email`, `application_no`, `course_id`, `level`, `application_date`, `status`, `branch_id`, `approved_by_user_id`, `approval_date`, `created_at`, `updated_at`, `parent_guardian_name`, `parent_guardian_contact`, `parent_guardian_relationship`, `parent_guardian_address`) VALUES
(14, NULL, 'SHUAIB', 'ABDOOL', '', '2025-09-26', 'Married', 'Male', 'yoruba', 'bOSSO NGIGERIA NIGER STATE', '08122598372', 'shuaibabdul192@gmail.com', '0', 3, 'NID 1', '2025-09-08', 'admission_letter_generated', 1, 1, '2025-09-08 09:45:56', '2025-09-08 08:43:51', '2025-09-08 08:45:56', 'SHUAIB ABDOOL', '08122598372', 'ggg', 'bOSSO NGIGERIA NIGER STATE');

-- --------------------------------------------------------

--
-- Table structure for table `branches`
--

CREATE TABLE `branches` (
  `branch_id` int(11) NOT NULL,
  `branch_name` varchar(255) NOT NULL,
  `location` text DEFAULT NULL,
  `contact_person` varchar(255) DEFAULT NULL,
  `contact_email` varchar(255) DEFAULT NULL,
  `contact_phone` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `branches`
--

INSERT INTO `branches` (`branch_id`, `branch_name`, `location`, `contact_person`, `contact_email`, `contact_phone`, `created_at`, `updated_at`) VALUES
(1, 'Main Campus', 'Opp. Old Secretariat, Keteren, Gwari Rd, Minna, Niger State', 'Registrar', 'registrar@miti.edu.ng', '08012345678', '2025-09-06 16:31:20', '2025-09-06 16:31:20');

-- --------------------------------------------------------

--
-- Table structure for table `courses`
--

CREATE TABLE `courses` (
  `course_id` int(11) NOT NULL,
  `course_name` varchar(255) NOT NULL,
  `course_code` varchar(50) DEFAULT NULL,
  `description` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `courses`
--

INSERT INTO `courses` (`course_id`, `course_name`, `course_code`, `description`, `created_at`, `updated_at`) VALUES
(1, 'AUTOMOTIVE MECHATRONICS', 'AM001', 'Study of automotive systems with electronic controls.', '2025-09-06 16:31:20', '2025-09-06 16:31:20'),
(2, 'BUILDING CONSTRUCTION TECHNOLOGY', 'BCT001', 'Comprehensive course in building and construction techniques.', '2025-09-06 16:31:20', '2025-09-06 16:31:20'),
(3, 'BUSINESS MANAGEMENT & INFORMATION TECHNOLOGY', 'BMIT001', 'Covers business principles and IT applications.', '2025-09-06 16:31:20', '2025-09-06 16:31:20'),
(4, 'ELECTRICAL ELECTRONICS TECHNOLOGY', 'EET001', 'Focuses on electrical and electronic systems.', '2025-09-06 16:31:20', '2025-09-06 16:31:20');

-- --------------------------------------------------------

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `payment_id` int(11) NOT NULL,
  `applicant_id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_date` date NOT NULL,
  `payment_method` varchar(50) DEFAULT NULL,
  `recorded_by_user_id` int(11) NOT NULL,
  `transaction_id` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `uploaded_documents`
--

CREATE TABLE `uploaded_documents` (
  `document_id` int(11) NOT NULL,
  `applicant_id` int(11) NOT NULL,
  `document_type` varchar(100) NOT NULL,
  `file_path` varchar(255) NOT NULL,
  `uploaded_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `uploaded_documents`
--

INSERT INTO `uploaded_documents` (`document_id`, `applicant_id`, `document_type`, `file_path`, `uploaded_at`) VALUES
(21, 14, 'Primary School Certificate', '/school_admission/public/uploads/general_documents/primary_school_cert_68be974716ec6.png', '2025-09-08 08:43:51'),
(22, 14, 'Secondary School Certificate', '/school_admission/public/uploads/general_documents/secondary_school_cert_68be97471714a.png', '2025-09-08 08:43:51'),
(23, 14, 'L.G.A Letter', '/school_admission/public/uploads/general_documents/lga_letter_68be974717304.png', '2025-09-08 08:43:51'),
(24, 14, 'Passport Photo', '/school_admission/public/uploads/general_documents/passport_photo_68be9747177c8.png', '2025-09-08 08:43:51');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `academic_records`
--
ALTER TABLE `academic_records`
  ADD PRIMARY KEY (`record_id`),
  ADD KEY `applicant_id` (`applicant_id`);

--
-- Indexes for table `admission_letters`
--
ALTER TABLE `admission_letters`
  ADD PRIMARY KEY (`letter_id`),
  ADD KEY `applicant_id` (`applicant_id`),
  ADD KEY `generated_by_user_id` (`generated_by_user_id`),
  ADD KEY `printed_by_user_id` (`printed_by_user_id`);

--
-- Indexes for table `applicants`
--
ALTER TABLE `applicants`
  ADD PRIMARY KEY (`applicant_id`),
  ADD UNIQUE KEY `email` (`email`),
  ADD UNIQUE KEY `application_no` (`application_no`),
  ADD KEY `user_id` (`user_id`),
  ADD KEY `course_id` (`course_id`),
  ADD KEY `branch_id` (`branch_id`),
  ADD KEY `approved_by_user_id` (`approved_by_user_id`);

--
-- Indexes for table `branches`
--
ALTER TABLE `branches`
  ADD PRIMARY KEY (`branch_id`),
  ADD UNIQUE KEY `branch_name` (`branch_name`);

--
-- Indexes for table `courses`
--
ALTER TABLE `courses`
  ADD PRIMARY KEY (`course_id`),
  ADD UNIQUE KEY `course_name` (`course_name`),
  ADD UNIQUE KEY `course_code` (`course_code`);

--
-- Indexes for table `payments`
--
ALTER TABLE `payments`
  ADD PRIMARY KEY (`payment_id`),
  ADD KEY `applicant_id` (`applicant_id`),
  ADD KEY `recorded_by_user_id` (`recorded_by_user_id`);

--
-- Indexes for table `uploaded_documents`
--
ALTER TABLE `uploaded_documents`
  ADD PRIMARY KEY (`document_id`),
  ADD KEY `applicant_id` (`applicant_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `academic_records`
--
ALTER TABLE `academic_records`
  MODIFY `record_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- AUTO_INCREMENT for table `admission_letters`
--
ALTER TABLE `admission_letters`
  MODIFY `letter_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `applicants`
--
ALTER TABLE `applicants`
  MODIFY `applicant_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=15;

--
-- AUTO_INCREMENT for table `branches`
--
ALTER TABLE `branches`
  MODIFY `branch_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;

--
-- AUTO_INCREMENT for table `courses`
--
ALTER TABLE `courses`
  MODIFY `course_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `payments`
--
ALTER TABLE `payments`
  MODIFY `payment_id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `uploaded_documents`
--
ALTER TABLE `uploaded_documents`
  MODIFY `document_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=25;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `academic_records`
--
ALTER TABLE `academic_records`
  ADD CONSTRAINT `academic_records_ibfk_1` FOREIGN KEY (`applicant_id`) REFERENCES `applicants` (`applicant_id`) ON DELETE CASCADE ON UPDATE CASCADE;

--
-- Constraints for table `admission_letters`
--
ALTER TABLE `admission_letters`
  ADD CONSTRAINT `admission_letters_ibfk_1` FOREIGN KEY (`applicant_id`) REFERENCES `applicants` (`applicant_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `admission_letters_ibfk_2` FOREIGN KEY (`generated_by_user_id`) REFERENCES `users` (`user_id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `admission_letters_ibfk_3` FOREIGN KEY (`printed_by_user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `applicants`
--
ALTER TABLE `applicants`
  ADD CONSTRAINT `applicants_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE,
  ADD CONSTRAINT `applicants_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `courses` (`course_id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `applicants_ibfk_3` FOREIGN KEY (`branch_id`) REFERENCES `branches` (`branch_id`) ON UPDATE CASCADE,
  ADD CONSTRAINT `applicants_ibfk_4` FOREIGN KEY (`approved_by_user_id`) REFERENCES `users` (`user_id`) ON DELETE SET NULL ON UPDATE CASCADE;

--
-- Constraints for table `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`applicant_id`) REFERENCES `applicants` (`applicant_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `payments_ibfk_2` FOREIGN KEY (`recorded_by_user_id`) REFERENCES `users` (`user_id`) ON UPDATE CASCADE;

--
-- Constraints for table `uploaded_documents`
--
ALTER TABLE `uploaded_documents`
  ADD CONSTRAINT `uploaded_documents_ibfk_1` FOREIGN KEY (`applicant_id`) REFERENCES `applicants` (`applicant_id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

