MLA Portal

Develop the citizen appointment booking module that allows users to select an available date and time slot from the MLA’s calendar, enter the purpose of the visit, and confirm their appointment. The system ensures real-time slot availability, prevents duplicate bookings, and updates appointment status throughout the lifecycle (booked, cancelled, or completed). Notifications are sent to both citizens and admins for every booking action.

***Table: table_appointment
Field Type Description
id BIGINT (PK) Appointment ID
availability_id BIGINT (FK → mla_availability.id) Linked availability
constituency_id BIGINT (FK → constituencies.id) Constituency
user_id BIGINT (FK → users.id) Citizen
appointment_date DATE Appointment date
slot_time VARCHAR(50) Selected time slot
location VARCHAR(200) MLA meeting location
purpose VARCHAR(255) Reason for appointment
status ENUM('BOOKED','CANCELLED','COMPLETED') Appointment status
created_at TIMESTAMP Booking time

***Validations

*availability_id must exist in mla_availability and status must be AVAILABLE

*appointment_date must match the selected available_date

*slot_time must fall within the defined start_time and end_time

*user_id must exist and have role = PUBLIC

*One user can book only one appointment per date per constituency

*Duplicate slot booking is not allowed

*purpose cannot be empty

*status must be BOOKED, CANCELLED, or COMPLETED

*On cancellation, slot count must be updated in mla_availability

*Only Admin can mark appointment as COMPLETED

*Appointments cannot be created for past dates