-- Migration: Create patient_service_sessions table for scheduling service visits
-- Run this script in your database console once to add the table.

CREATE TABLE IF NOT EXISTS patient_service_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    session_number INT NOT NULL,
    scheduled_at DATETIME NULL,
    notes TEXT NULL,
    updated_by INT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT uq_patient_service_sessions_submission_session UNIQUE (submission_id, session_number),
    CONSTRAINT fk_patient_service_sessions_submission FOREIGN KEY (submission_id) REFERENCES form_submissions(id) ON DELETE CASCADE,
    CONSTRAINT fk_patient_service_sessions_updated_by FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE INDEX idx_patient_service_sessions_submission ON patient_service_sessions (submission_id);
