-- Migration: Create patient_services table
-- Run this once in your SQL tool or through your backend

CREATE TABLE IF NOT EXISTS patient_services (
    id INT AUTO_INCREMENT PRIMARY KEY,
    patient_id INT NOT NULL,
    client_id INT NOT NULL,
    service_id INT NOT NULL,
    date_enrolled DATETIME DEFAULT CURRENT_TIMESTAMP,
    status VARCHAR(50) DEFAULT 'active',
    notes TEXT,
    FOREIGN KEY (patient_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE,
    FOREIGN KEY (service_id) REFERENCES client_services(id) ON DELETE CASCADE
);