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

START TRANSACTION;



--
-- Database: `ab`
--

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

--
-- Table structure for table `academic_session`
--

CREATE TABLE `academic_session` (
  `id` int(11) NOT NULL,
  `academic_year` varchar(10) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `branches` (
  `id` int(11) NOT NULL,
  `branches_fields` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

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

CREATE TABLE `courses` (
  `id` int(11) NOT NULL,
  `courses_fields` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `fees_plans`
--

CREATE TABLE `fees_plans` (
  `id` int(11) NOT NULL,
  `plan_name` varchar(50) NOT NULL,
  `installment_frequency` enum('Semester','Annual','Monthly','One-time') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `fees_plans_child`
--

CREATE TABLE `fees_plans_child` (
  `id` int(11) NOT NULL,
  `fees_plan_id` int(11) NOT NULL,
  `fee_head` varchar(100) NOT NULL,
  `amount` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `institute`
--

CREATE TABLE `institute` (
  `id` int(11) NOT NULL,
  `logo` varchar(300) NOT NULL,
  `institute_name` varchar(300) NOT NULL,
  `phone_no` varchar(15) NOT NULL,
  `address` text NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `record_payment`
--

CREATE TABLE `record_payment` (
  `id` int(11) NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `academic_term` varchar(20) NOT NULL,
  `payment_node` enum('UPI Digital','Cash','Bank Transfer','Cheque') NOT NULL,
  `transaction_id` varchar(200) NOT NULL,
  `student_name_rollno` varchar(200) NOT NULL,
  `transaction_date` date NOT NULL,
  `bank_account_number` varchar(50) DEFAULT NULL,
  `branch_id` int(11) NOT NULL,
  `fee_plan_id` int(11) NOT NULL,
  `guardian_name` varchar(100) DEFAULT NULL,
  `guardian_phone_no` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `semester`
--

CREATE TABLE `semester` (
  `id` int(11) NOT NULL,
  `semester_name` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

--
-- Table structure for table `student_dictionary`
--

CREATE TABLE `student_dictionary` (
  `id` int(11) NOT NULL,
  `student_name` varchar(100) NOT NULL,
  `roll_no` varchar(30) NOT NULL,
  `guardian_name` varchar(100) NOT NULL,
  `guardian_phone_no` varchar(15) NOT NULL,
  `semester_id` int(11) NOT NULL,
  `academic_id` int(11) NOT NULL,
  `branch_id` int(11) NOT NULL,
  `plan_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Indexes for dumped tables
--

--
-- Indexes for table `academic_session`
--
ALTER TABLE `academic_session`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `academic_year` (`academic_year`);

--
-- Indexes for table `branches`
--
ALTER TABLE `branches`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `branches_fileds` (`branches_fields`);

--
-- Indexes for table `courses`
--
ALTER TABLE `courses`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `courses_fields` (`courses_fields`);

--
-- Indexes for table `fees_plans`
--
ALTER TABLE `fees_plans`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `plan_name` (`plan_name`);

--
-- Indexes for table `fees_plans_child`
--
ALTER TABLE `fees_plans_child`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_plan_head` (`fees_plan_id`,`fee_head`);

--
-- Indexes for table `institute`
--
ALTER TABLE `institute`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `record_payment`
--
ALTER TABLE `record_payment`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `transaction_id` (`transaction_id`),
  ADD KEY `fk_branch` (`branch_id`),
  ADD KEY `fk_fee_plan_id` (`fee_plan_id`);

--
-- Indexes for table `semester`
--
ALTER TABLE `semester`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `semester_name` (`semester_name`);

--
-- Indexes for table `student_dictionary`
--
ALTER TABLE `student_dictionary`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `roll_no` (`roll_no`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `academic_session`
--
ALTER TABLE `academic_session`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `branches`
--
ALTER TABLE `branches`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `courses`
--
ALTER TABLE `courses`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fees_plans`
--
ALTER TABLE `fees_plans`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `fees_plans_child`
--
ALTER TABLE `fees_plans_child`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `institute`
--
ALTER TABLE `institute`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `record_payment`
--
ALTER TABLE `record_payment`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `semester`
--
ALTER TABLE `semester`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `student_dictionary`
--
ALTER TABLE `student_dictionary`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `fees_plans_child`
--
ALTER TABLE `fees_plans_child`
  ADD CONSTRAINT `fk_fees_plan` FOREIGN KEY (`fees_plan_id`) REFERENCES `fees_plans` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;



use studentEnrollment_db;
show tables;
