from flask import Blueprint, request, jsonify from flask_jwt_extended import jwt_required, get_jwt_identity from db.db import get_db_connection from config.auth import role_required import mysql.connector from datetime import datetime, date, timedelta appoinment_bp = Blueprint('appoinment', __name__) # ───────────────────────────────────────────────────────────── # 🛠️ HELPERS # ───────────────────────────────────────────────────────────── def _td_to_hhmm(val) -> str: """ Convert a MySQL TIME column value (timedelta or string) → "HH:MM" string. Single source of truth for time formatting. Avoids TIME_FORMAT() in SQL to sidestep % escaping issues with Python's DB connector string interpolation. """ if val is None: return None if isinstance(val, str): parts = val.split(':') if len(parts) >= 2: return f"{int(parts[0]):02d}:{parts[1][:2]}" return val if hasattr(val, 'total_seconds'): # timedelta total = int(val.total_seconds()) h, rem = divmod(total, 3600) m, _ = divmod(rem, 60) return f"{h:02d}:{m:02d}" return str(val) def _serialize_row(row: dict) -> dict: """ Convert datetime / date / timedelta / Decimal values to JSON-safe types. TIME columns (timedelta) → "HH:MM" via _td_to_hhmm(). Mutates and returns the row dict in place. """ for key, val in list(row.items()): if isinstance(val, datetime): row[key] = val.strftime('%Y-%m-%d %H:%M:%S') elif isinstance(val, date): row[key] = val.isoformat() elif hasattr(val, 'total_seconds'): # timedelta (raw TIME column) row[key] = _td_to_hhmm(val) elif hasattr(val, '__float__'): # Decimal row[key] = float(val) return row def _fetch_schedules(cursor, doctor_id: int) -> list: """ Return the weekly schedule for a doctor grouped by day. Output schema: [ { "day": "Monday", "sessions": [ { "session_no": 1, "start_time": "09:00", "end_time": "12:00", "max_patients": 20 }, ... ] }, ... ] Times are returned as "HH:MM" strings via _td_to_hhmm(). """ cursor.execute( """ SELECT day, session_no, start_time, end_time, max_patients FROM doctor_schedules WHERE doctor_id = %s ORDER BY FIELD(day,'Monday','Tuesday','Wednesday', 'Thursday','Friday','Saturday','Sunday'), session_no """, (doctor_id,) ) rows = cursor.fetchall() for r in rows: r['start_time'] = _td_to_hhmm(r['start_time']) r['end_time'] = _td_to_hhmm(r['end_time']) grouped = {} for r in rows: d = r['day'] if d not in grouped: grouped[d] = [] grouped[d].append({ 'session_no': r['session_no'], 'start_time': r['start_time'], 'end_time': r['end_time'], 'max_patients': r['max_patients'], }) return [{'day': day, 'sessions': sessions} for day, sessions in grouped.items()] def _get_or_create_session(cursor, conn, doctor_id: int, store_id: int, target_date: date, ds_row: dict) -> int: """ Find an existing channeling_sessions row for (doctor, store, date, session_no) or INSERT one on-the-fly from the doctor_schedules row. Uses INSERT ON DUPLICATE KEY UPDATE for full idempotency. Returns the channeling_sessions.id. """ session_no = ds_row['session_no'] start_time = ds_row['start_time'] # "HH:MM" string end_time = ds_row['end_time'] max_patients = ds_row['max_patients'] try: cursor.execute( """ INSERT INTO channeling_sessions (doctor_id, store_id, session_date, session_no, session_start, session_end, max_patients, status, created_at) VALUES (%s, %s, %s, %s, %s, %s, %s, 'active', NOW()) ON DUPLICATE KEY UPDATE session_no = session_no """, (doctor_id, store_id, target_date, session_no, start_time, end_time, max_patients) ) conn.commit() except mysql.connector.IntegrityError as e: if e.errno == 1062: # Duplicate entry — already exists, safe to continue conn.rollback() else: raise cursor.execute( """ SELECT id FROM channeling_sessions WHERE doctor_id = %s AND store_id = %s AND session_date = %s AND session_no = %s LIMIT 1 """, (doctor_id, store_id, target_date, session_no) ) row = cursor.fetchone() if not row: raise RuntimeError( f"Failed to find or create session for doctor={doctor_id} " f"store={store_id} date={target_date} session_no={session_no}" ) return row['id'] # ───────────────────────────────────────────────────────────── # GET /all_patients?search= # # FIX: Added `nic` to the SELECT so the frontend can auto-fill # the NIC field when a patient is selected from search. # If your `customers` table does not have a `nic` column, # remove it from the SELECT and the frontend will leave the # NIC field blank for manual entry. # ───────────────────────────────────────────────────────────── @appoinment_bp.route('/all_patients', methods=['GET']) @jwt_required() def get_all_patients(): search = request.args.get('search', '').strip() conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) if search: like = f"%{search}%" cursor.execute( """ SELECT id, name, contact, address, dob, gender, nic FROM customers WHERE name LIKE %s OR contact LIKE %s ORDER BY name LIMIT 50 """, (like, like) ) else: cursor.execute( """ SELECT id, name, contact, address, dob, gender, nic FROM customers ORDER BY name LIMIT 200 """ ) patients = cursor.fetchall() for p in patients: _serialize_row(p) return jsonify({"success": True, "patients": patients}), 200 except mysql.connector.Error as e: print(f"[DB ERROR] get_all_patients: {e}") return jsonify({"success": False, "error": "Database error while fetching patients."}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ───────────────────────────────────────────────────────────── # GET /all_doctors?store_id=&status= # # FIX: Added optional `status` query param so the frontend can # filter by status=Active. Previously only store_id was # accepted and status was hard-coded to 'Active' in the WHERE. # Now if ?status= is passed it overrides the default. # ───────────────────────────────────────────────────────────── @appoinment_bp.route('/all_doctors', methods=['GET']) @jwt_required() def get_all_doctors(): store_id = request.args.get('store_id', type=int) status_filter = request.args.get('status', 'Active').strip() # Whitelist allowed status values to prevent SQL injection via enum allowed_statuses = {'Active', 'Inactive'} if status_filter not in allowed_statuses: status_filter = 'Active' conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) query = """ SELECT d.id, d.name, d.reg_no, d.specialization, d.qualification, d.email, d.contact, d.channeling_fee, d.status, d.store_id, d.photo_url FROM doctors d WHERE d.status = %s """ params = [status_filter] if store_id: query += " AND d.store_id = %s" params.append(store_id) query += " ORDER BY d.name ASC" cursor.execute(query, params) doctors = cursor.fetchall() for d in doctors: _serialize_row(d) d['schedule'] = _fetch_schedules(cursor, d['id']) return jsonify({"success": True, "doctors": doctors}), 200 except mysql.connector.Error as e: print(f"[DB ERROR] get_all_doctors: {e}") return jsonify({"success": False, "error": "Database error while fetching doctors."}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ───────────────────────────────────────────────────────────── # GET /sessions?doctor_id=&store_id=&date= # # Returns materialised channeling_sessions rows. # Selects raw session_start / session_end columns (timedelta). # _serialize_row() converts them to "HH:MM" strings. # No TIME_FORMAT() in SQL — % escaping bug eliminated. # # FIX: Added `session_no` to the SELECT so the frontend can # display it correctly in the session dropdown. # ───────────────────────────────────────────────────────────── @appoinment_bp.route('/sessions', methods=['GET']) @jwt_required() def get_sessions(): doctor_id = request.args.get('doctor_id', type=int) store_id = request.args.get('store_id', type=int) session_date = request.args.get('date', '').strip() if not doctor_id: return jsonify({"success": False, "error": "doctor_id is required."}), 400 if not store_id: return jsonify({"success": False, "error": "store_id is required."}), 400 conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) # ── 1. Fetch doctor's weekly schedule ───────────────── cursor.execute( """ SELECT day, session_no, start_time, end_time, max_patients FROM doctor_schedules WHERE doctor_id = %s ORDER BY session_no """, (doctor_id,) ) schedule_rows = cursor.fetchall() for r in schedule_rows: r['start_time'] = _td_to_hhmm(r['start_time']) r['end_time'] = _td_to_hhmm(r['end_time']) if not schedule_rows: return jsonify({ "success": True, "sessions": [], "message": "This doctor has no schedule configured." }), 200 schedule_by_day = {} for r in schedule_rows: schedule_by_day.setdefault(r['day'], []).append(r) # ── 2. Build calendar dates to materialise ──────────── today = date.today() if session_date: try: target = datetime.strptime(session_date, '%Y-%m-%d').date() except ValueError: return jsonify({"success": False, "error": "Invalid date format. Use YYYY-MM-DD."}), 400 if target < today: # Past date requested — return empty (no sessions in the past) return jsonify({"success": True, "sessions": []}), 200 dates_to_check = [target] else: # Default: next 14 days dates_to_check = [today + timedelta(days=i) for i in range(14)] day_names = ['Monday', 'Tuesday', 'Wednesday', 'Thursday', 'Friday', 'Saturday', 'Sunday'] # ── 3. Materialise sessions (get-or-create) ─────────── materialised_ids = [] for cal_date in dates_to_check: day_name = day_names[cal_date.weekday()] if day_name not in schedule_by_day: continue for ds_row in schedule_by_day[day_name]: try: sid = _get_or_create_session( cursor, conn, doctor_id, store_id, cal_date, ds_row ) materialised_ids.append(sid) except Exception as e: print(f"[WARN] _get_or_create_session failed: " f"doctor={doctor_id} date={cal_date} " f"session_no={ds_row.get('session_no')}: {e}") continue if not materialised_ids: return jsonify({ "success": True, "sessions": [], "message": "No sessions available for the selected date(s)." }), 200 # ── 4. Return sessions with live booked_count ───────── placeholders = ','.join(['%s'] * len(materialised_ids)) cursor.execute( f""" SELECT cs.id, cs.doctor_id, cs.store_id, cs.session_date, cs.session_start, cs.session_end, cs.max_patients, cs.session_no, cs.status, COUNT(a.id) AS booked_count FROM channeling_sessions cs LEFT JOIN appointments a ON a.session_id = cs.id AND a.status != 'cancelled' WHERE cs.id IN ({placeholders}) AND cs.status != 'cancelled' GROUP BY cs.id ORDER BY cs.session_date ASC, cs.session_start ASC """, materialised_ids ) sessions = cursor.fetchall() for s in sessions: _serialize_row(s) # timedelta → "HH:MM" return jsonify({"success": True, "sessions": sessions}), 200 except mysql.connector.Error as e: print(f"[DB ERROR] get_sessions: {e}") return jsonify({"success": False, "error": "Database error while fetching sessions."}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ───────────────────────────────────────────────────────────── # GET /all_appointments?store_id=&doctor_id=&date=&search= # # Raw session_start / session_end — _serialize_row() → "HH:MM". # ───────────────────────────────────────────────────────────── @appoinment_bp.route('/all_appointments', methods=['GET']) @jwt_required() def get_all_appointments(): store_id = request.args.get('store_id', type=int) doctor_id = request.args.get('doctor_id', type=int) appt_date = request.args.get('date', '').strip() search = request.args.get('search', '').strip() conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) query = """ SELECT a.id, a.appointment_number, a.patient_name, a.patient_contact, a.patient_nic, a.patient_age, a.appointment_date, a.payment_method, a.payment_status, a.channeling_fee, a.notes, a.status, a.created_at, d.id AS doctor_id, d.name AS doctor_name, d.specialization, cs.id AS session_id, cs.session_date, cs.session_start, cs.session_end, u.name AS booked_by_name FROM appointments a LEFT JOIN doctors d ON a.doctor_id = d.id LEFT JOIN channeling_sessions cs ON a.session_id = cs.id LEFT JOIN users u ON a.booked_by = u.id WHERE 1=1 """ params = [] if store_id: query += " AND a.store_id = %s" params.append(store_id) if doctor_id: query += " AND a.doctor_id = %s" params.append(doctor_id) if appt_date: query += " AND a.appointment_date = %s" params.append(appt_date) if search: like = f"%{search}%" query += """ AND ( a.patient_name LIKE %s OR a.patient_contact LIKE %s OR a.patient_nic LIKE %s ) """ params.extend([like, like, like]) query += " ORDER BY a.appointment_date DESC, a.appointment_number ASC" cursor.execute(query, params) appointments = cursor.fetchall() for appt in appointments: _serialize_row(appt) return jsonify({"success": True, "appointments": appointments}), 200 except mysql.connector.Error as e: print(f"[DB ERROR] get_all_appointments: {e}") return jsonify({"success": False, "error": "Database error while fetching appointments."}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ───────────────────────────────────────────────────────────── # POST /add_appointment # # FIX: Validates that session belongs to the given doctor. # Checks session status before booking. # Uses FOR UPDATE lock to prevent overbooking under concurrency. # channeling_fee taken from doctor record (source of truth). # appointment_number = current booked_count + 1 (within session). # ───────────────────────────────────────────────────────────── @appoinment_bp.route('/add_appointment', methods=['POST']) @jwt_required() def add_appointment(): data = request.get_json() if not data: return jsonify({"success": False, "error": "No data provided."}), 400 patient_name = (data.get('patient_name') or '').strip() patient_contact = (data.get('patient_contact') or '').strip() doctor_id = data.get('doctor_id') session_id = data.get('session_id') appointment_date = data.get('appointment_date') payment_method = data.get('payment_method') store_id = data.get('store_id') patient_id = data.get('patient_id') or None patient_nic = data.get('patient_nic') or None patient_age = data.get('patient_age') or None payment_status = data.get('payment_status') or 'Paid' notes = data.get('notes') or None booked_by = data.get('booked_by') or None # ── Required field validation ────────────────────────────── errors = [] if not patient_name: errors.append("Patient name is required.") if not patient_contact: errors.append("Patient contact is required.") if not doctor_id: errors.append("Doctor is required.") if not session_id: errors.append("Session is required.") if not appointment_date: errors.append("Appointment date is required.") if not payment_method: errors.append("Payment method is required.") if not store_id: errors.append("Store is required.") if errors: return jsonify({"success": False, "error": errors[0]}), 422 # ── Past date guard ──────────────────────────────────────── try: appt_date_obj = datetime.strptime(appointment_date, '%Y-%m-%d').date() except ValueError: return jsonify({"success": False, "error": "Invalid appointment_date format. Use YYYY-MM-DD."}), 422 if appt_date_obj < date.today(): return jsonify({"success": False, "error": "Appointment date cannot be in the past."}), 422 conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) # Lock session row to prevent overbooking under concurrent requests cursor.execute( """ SELECT cs.id, cs.max_patients, cs.session_date, cs.status, d.channeling_fee, COUNT(a.id) AS booked_count FROM channeling_sessions cs LEFT JOIN doctors d ON cs.doctor_id = d.id LEFT JOIN appointments a ON a.session_id = cs.id AND a.status != 'cancelled' WHERE cs.id = %s AND cs.doctor_id = %s GROUP BY cs.id FOR UPDATE """, (int(session_id), int(doctor_id)) ) session = cursor.fetchone() if not session: return jsonify({"success": False, "error": "Session not found for the selected doctor."}), 404 if session['status'] == 'cancelled': return jsonify({"success": False, "error": "This session has been cancelled."}), 409 booked_count = int(session['booked_count'] or 0) max_patients = int(session['max_patients'] or 0) # max_patients = 0 means unlimited (matches DB DEFAULT 20 — but guard for edge cases) if max_patients > 0 and booked_count >= max_patients: return jsonify({"success": False, "error": "This session is fully booked."}), 409 # channeling_fee from doctor record — authoritative source channeling_fee = float(session['channeling_fee'] or 0) appointment_number = booked_count + 1 # ── Validate payment_method enum ────────────────────── valid_payment_methods = {'Cash', 'Card', 'Online'} if payment_method not in valid_payment_methods: return jsonify({"success": False, "error": f"payment_method must be one of: {', '.join(valid_payment_methods)}."}), 422 # ── Validate payment_status enum ────────────────────── valid_payment_statuses = {'Paid', 'Pending'} if payment_status not in valid_payment_statuses: payment_status = 'Paid' cursor.execute( """ INSERT INTO appointments ( patient_id, patient_name, patient_contact, patient_nic, patient_age, doctor_id, session_id, appointment_date, appointment_number, channeling_fee, payment_method, payment_status, notes, store_id, booked_by, status, created_at ) VALUES ( %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, 'booked', NOW() ) """, ( patient_id or None, patient_name, patient_contact, patient_nic, int(patient_age) if patient_age else None, int(doctor_id), int(session_id), appointment_date, appointment_number, channeling_fee, payment_method, payment_status, notes, int(store_id), int(booked_by) if booked_by else None, ) ) conn.commit() new_id = cursor.lastrowid return jsonify({ "success": True, "message": f"Appointment #{appointment_number} booked successfully.", "appointment_id": new_id, "appointment_number": appointment_number, "channeling_fee": channeling_fee, }), 201 except mysql.connector.Error as e: if conn: conn.rollback() print(f"[DB ERROR] add_appointment: {e}") return jsonify({"success": False, "error": "Database error while booking appointment."}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ───────────────────────────────────────────────────────────── # PUT /update_appointment/ # # FIX: Only updates columns that actually exist in appointments table. # Whitelisted fields match exactly the DB schema. # ───────────────────────────────────────────────────────────── @appoinment_bp.route('/update_appointment/', methods=['PUT']) @jwt_required() def update_appointment(appointment_id): data = request.get_json() if not data: return jsonify({"success": False, "error": "No data provided."}), 400 conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute("SELECT id FROM appointments WHERE id = %s", (appointment_id,)) if not cursor.fetchone(): return jsonify({"success": False, "error": "Appointment not found."}), 404 # Whitelist: only real columns in the appointments table allowed_fields = [ 'patient_name', 'patient_contact', 'patient_nic', 'patient_age', 'appointment_date', 'payment_method', 'payment_status', 'notes', 'status', ] set_clauses, params = [], [] for field in allowed_fields: if field in data: set_clauses.append(f"`{field}` = %s") params.append(data[field] if data[field] != '' else None) if not set_clauses: return jsonify({"success": False, "error": "No valid fields to update."}), 400 params.append(appointment_id) cursor.execute( f"UPDATE appointments SET {', '.join(set_clauses)} WHERE id = %s", params ) conn.commit() return jsonify({"success": True, "message": "Appointment updated successfully."}), 200 except mysql.connector.Error as e: if conn: conn.rollback() print(f"[DB ERROR] update_appointment: {e}") return jsonify({"success": False, "error": "Database error while updating appointment."}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ───────────────────────────────────────────────────────────── # DELETE /delete_appointment/ (soft cancel) # ───────────────────────────────────────────────────────────── @appoinment_bp.route('/delete_appointment/', methods=['DELETE']) @jwt_required() def delete_appointment(appointment_id): conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute("SELECT id, status FROM appointments WHERE id = %s", (appointment_id,)) appt = cursor.fetchone() if not appt: return jsonify({"success": False, "error": "Appointment not found."}), 404 if appt['status'] == 'cancelled': return jsonify({"success": False, "error": "Appointment is already cancelled."}), 409 cursor.execute( "UPDATE appointments SET status = 'cancelled' WHERE id = %s", (appointment_id,) ) conn.commit() return jsonify({"success": True, "message": "Appointment cancelled successfully."}), 200 except mysql.connector.Error as e: if conn: conn.rollback() print(f"[DB ERROR] delete_appointment: {e}") return jsonify({"success": False, "error": "Database error while cancelling appointment."}), 500 finally: if cursor: cursor.close() if conn: conn.close() # ───────────────────────────────────────────────────────────── # GET /appointment/ # # Raw session_start / session_end — _serialize_row() → "HH:MM". # ───────────────────────────────────────────────────────────── @appoinment_bp.route('/appointment/', methods=['GET']) @jwt_required() def get_appointment(appointment_id): conn = None cursor = None try: conn = get_db_connection() cursor = conn.cursor(dictionary=True) cursor.execute( """ SELECT a.*, d.name AS doctor_name, d.specialization, d.channeling_fee AS doctor_fee, cs.session_date, cs.session_start, cs.session_end, cs.max_patients, u.name AS booked_by_name FROM appointments a LEFT JOIN doctors d ON a.doctor_id = d.id LEFT JOIN channeling_sessions cs ON a.session_id = cs.id LEFT JOIN users u ON a.booked_by = u.id WHERE a.id = %s """, (appointment_id,) ) appt = cursor.fetchone() if not appt: return jsonify({"success": False, "error": "Appointment not found."}), 404 _serialize_row(appt) return jsonify({"success": True, "appointment": appt}), 200 except mysql.connector.Error as e: print(f"[DB ERROR] get_appointment: {e}") return jsonify({"success": False, "error": "Database error."}), 500 finally: if cursor: cursor.close() if conn: conn.close()