-- Migration for teacher interface features (Simple version)
-- Run this after the base schema.sql
-- If you get errors about columns/constraints already existing, that's fine - just continue

USE projet_web_3;

-- Courses table: Types of lessons teachers offer
CREATE TABLE IF NOT EXISTS courses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    teacher_id INT UNSIGNED NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NULL,
    level ENUM('beginner', 'intermediate', 'advanced') NOT NULL DEFAULT 'beginner',
    duration INT NOT NULL COMMENT 'Duration in minutes',
    price DECIMAL(10,2) NOT NULL,
    lesson_type ENUM('individual', 'group') NOT NULL DEFAULT 'individual',
    max_students INT UNSIGNED NULL COMMENT 'For group lessons',
    required_material TEXT NULL,
    is_published BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_teacher (teacher_id),
    INDEX idx_published (is_published),
    CONSTRAINT fk_courses_teacher FOREIGN KEY (teacher_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Update appointments to link to courses (ignore errors if columns already exist)
ALTER TABLE appointments ADD COLUMN course_id INT UNSIGNED NULL AFTER teacher_id;
ALTER TABLE appointments ADD COLUMN max_students INT UNSIGNED NULL AFTER end_time;
CREATE INDEX idx_course ON appointments(course_id);
ALTER TABLE appointments ADD CONSTRAINT fk_appointments_course FOREIGN KEY (course_id) REFERENCES courses (id) ON DELETE SET NULL;

-- Course enrollments: Students registered for appointments
CREATE TABLE IF NOT EXISTS course_enrollments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    appointment_id INT UNSIGNED NOT NULL,
    student_id INT UNSIGNED NOT NULL,
    status ENUM('pending', 'approved', 'rejected', 'attended', 'absent') NOT NULL DEFAULT 'pending',
    attendance_status ENUM('present', 'absent', 'pending') NOT NULL DEFAULT 'pending',
    notes TEXT NULL COMMENT 'Private notes from teacher',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_appointment (appointment_id),
    INDEX idx_student (student_id),
    UNIQUE KEY unique_enrollment (appointment_id, student_id),
    CONSTRAINT fk_enrollments_appointment FOREIGN KEY (appointment_id) REFERENCES appointments (id) ON DELETE CASCADE,
    CONSTRAINT fk_enrollments_student FOREIGN KEY (student_id) REFERENCES users (id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Messages: Teacher-student communication
CREATE TABLE IF NOT EXISTS messages (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    sender_id INT UNSIGNED NOT NULL,
    receiver_id INT UNSIGNED NOT NULL,
    subject VARCHAR(255) NULL,
    content TEXT NOT NULL,
    is_read BOOLEAN NOT NULL DEFAULT FALSE,
    is_announcement BOOLEAN NOT NULL DEFAULT FALSE COMMENT 'Bulk announcement to multiple students',
    appointment_id INT UNSIGNED NULL COMMENT 'Related appointment if any',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_sender (sender_id),
    INDEX idx_receiver (receiver_id),
    INDEX idx_appointment (appointment_id),
    INDEX idx_read (is_read),
    CONSTRAINT fk_messages_sender FOREIGN KEY (sender_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_messages_receiver FOREIGN KEY (receiver_id) REFERENCES users (id) ON DELETE CASCADE,
    CONSTRAINT fk_messages_appointment FOREIGN KEY (appointment_id) REFERENCES appointments (id) ON DELETE SET NULL
) ENGINE=InnoDB;

-- Update reviews to allow teacher responses (ignore errors if columns already exist)
ALTER TABLE reviews ADD COLUMN teacher_response TEXT NULL AFTER comment;
ALTER TABLE reviews ADD COLUMN response_date TIMESTAMP NULL AFTER teacher_response;

-- Sample data (only if tables are empty)
INSERT IGNORE INTO courses (teacher_id, title, description, level, duration, price, lesson_type, max_students, required_material, is_published)
VALUES
    (2, 'Piano Débutant', 'Cours de piano pour débutants. Apprentissage des bases, lecture de partitions.', 'beginner', 60, 45.00, 'individual', NULL, 'Piano ou clavier', TRUE),
    (2, 'Piano Avancé', 'Perfectionnement technique et interprétation.', 'advanced', 60, 50.00, 'individual', NULL, 'Piano', TRUE),
    (3, 'Guitare Jazz', 'Initiation au jazz et improvisation.', 'intermediate', 60, 40.00, 'group', 5, 'Guitare', TRUE);

