CREATE TABLE IF NOT EXISTS appointments (
 id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 patient_id BIGINT UNSIGNED NOT NULL,
 therapist_id BIGINT UNSIGNED NOT NULL,
 service_id BIGINT UNSIGNED NULL,
 appointment_date DATE NOT NULL,
 start_time TIME NOT NULL,
 end_time TIME NOT NULL,
 status VARCHAR(30) NOT NULL DEFAULT 'scheduled',
 notes TEXT NULL,
 created_by BIGINT UNSIGNED NULL,
 session_id BIGINT UNSIGNED NULL,
 created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
 updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 KEY idx_appointments_date (appointment_date,start_time), KEY idx_appointments_therapist (therapist_id,appointment_date), KEY idx_appointments_patient (patient_id,appointment_date), KEY idx_appointments_status (status), KEY idx_appointments_session (session_id),
 CONSTRAINT fk_appointments_patient FOREIGN KEY (patient_id) REFERENCES patients(id) ON DELETE RESTRICT,
 CONSTRAINT fk_appointments_therapist FOREIGN KEY (therapist_id) REFERENCES therapists(id) ON DELETE RESTRICT,
 CONSTRAINT fk_appointments_service FOREIGN KEY (service_id) REFERENCES services(id) ON DELETE SET NULL,
 CONSTRAINT fk_appointments_created_by FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
SET @db := DATABASE();
SET @sql := IF(EXISTS(SELECT 1 FROM information_schema.columns WHERE table_schema=@db AND table_name='therapy_sessions' AND column_name='appointment_id'),'SELECT 1','ALTER TABLE therapy_sessions ADD COLUMN appointment_id BIGINT UNSIGNED NULL AFTER assignment_id'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
SET @sql := IF(EXISTS(SELECT 1 FROM information_schema.statistics WHERE table_schema=@db AND table_name='therapy_sessions' AND index_name='idx_sessions_appointment'),'SELECT 1','ALTER TABLE therapy_sessions ADD KEY idx_sessions_appointment (appointment_id)'); PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;
INSERT INTO permissions (name,display_name) VALUES ('appointments.view','View appointments'),('appointments.create','Create appointments'),('appointments.edit','Edit appointments') ON DUPLICATE KEY UPDATE display_name=VALUES(display_name);
INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p WHERE r.name IN ('SUPER_ADMIN','ADMIN') AND p.name IN ('appointments.view','appointments.create','appointments.edit');
INSERT IGNORE INTO role_permissions (role_id,permission_id) SELECT r.id,p.id FROM roles r JOIN permissions p WHERE r.name='THERAPIST' AND p.name IN ('appointments.view','appointments.create','appointments.edit');
