Doctor Management

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'doctor', 'secretary') NOT NULL,
    initials VARCHAR(5),
    branch_id INT,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE SET NULL
);

CREATE TABLE doctors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT UNIQUE NOT NULL,
    specialty VARCHAR(100),
    license_number VARCHAR(50),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE doctor_branches (
    doctor_id INT NOT NULL,
    branch_id INT NOT NULL,
    PRIMARY KEY (doctor_id, branch_id),
    FOREIGN KEY (doctor_id) REFERENCES doctors(id) ON DELETE CASCADE,
    FOREIGN KEY (branch_id) REFERENCES branches(id) ON DELETE CASCADE
);

POST /api/doctors
Content-Type: application/json

{
  "name": "Dr. María González",
  "username": "dr.gonzalez",
  "password": "securePassword123",
  "email": "[email protected]",
  "specialty": "Ortodoncia",
  "license_number": "MAT-67890",
  "branch_id": 1
}

const initials = name
  .split(" ")
  .map(n => n[0])
  .join("")
  .toUpperCase()
  .substring(0, 3);

const hashedPassword = await bcrypt.hash(password, 10);

PUT /api/doctors
Content-Type: application/json

{
  "doctor_id": 1,
  "user_id": 2,
  "name": "Dr. Carlos Soto",
  "email": "[email protected]",
  "username": "dr.soto",
  "specialty": "Cirugía Oral",
  "license_number": "MAT-12345",
  "branch_id": 2,
  "status": "active"
}

DELETE /api/doctors?userId=2

const filteredDoctors = doctors.filter(d => {
  const q = searchQuery.toLowerCase();
  return (
    d.name?.toLowerCase().includes(q) ||
    d.specialty?.toLowerCase().includes(q) ||
    d.license_number?.toLowerCase().includes(q)
  );
});

CALL sp_list_doctors_view();

CALL sp_create_doctor_full(name, username, email, password, initials, specialty, license_number, branch_id);

CALL sp_update_doctor_full(doctor_id, user_id, name, email, username, initials, specialty, license_number, branch_id, status);

CALL sp_delete_doctor_full(user_id);

INSERT INTO doctor_branches (doctor_id, branch_id) VALUES 
  (1, 1),  -- Doctor 1 at Branch 1
  (1, 2);  -- Doctor 1 at Branch 2