-- تذكيرات المتابعة ومفضلة المرضى
CREATE TABLE IF NOT EXISTS doctor_follow_up_reminders (
    id INT AUTO_INCREMENT PRIMARY KEY,
    doctor_account_id INT NOT NULL,
    patient_id INT NOT NULL,
    reminder_date DATE NOT NULL,
    note VARCHAR(500) NULL,
    is_done TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (doctor_account_id) REFERENCES doctor_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE,
    INDEX idx_doctor_date (doctor_account_id, reminder_date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS doctor_favorite_patients (
    doctor_account_id INT NOT NULL,
    patient_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (doctor_account_id, patient_id),
    FOREIGN KEY (doctor_account_id) REFERENCES doctor_accounts(id) ON DELETE CASCADE,
    FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
