-- Migration: 015_lesson_type_allows_student_and_notes_private.sql
-- Adds allows_student column to lesson_types and notes_private to appointments

-- 1. lesson_types.allows_student: which lesson types can be assigned a student
ALTER TABLE lesson_types ADD COLUMN IF NOT EXISTS allows_student integer NOT NULL DEFAULT 0;
COMMENT ON COLUMN lesson_types.allows_student IS '1 = lesson type can have a student_id (e.g. driving lessons), 0 = no student (theory, work, private)';

-- 2. appointments.notes_private: private note only visible to the instructor (not chef/office)
ALTER TABLE appointments ADD COLUMN IF NOT EXISTS notes_private text DEFAULT '';

-- Default: only 'student' category allows student_id, all other categories = 0
UPDATE lesson_types SET allows_student = 1 WHERE category = 'student';
UPDATE lesson_types SET allows_student = 0 WHERE category != 'student';

-- For existing appointments, clear student_id if the lesson_type doesn't allow it
-- (data cleanup - not normally needed but defensive)
UPDATE appointments
SET student_id = NULL
WHERE student_id IS NOT NULL
  AND lesson_type_id IN (
    SELECT id FROM lesson_types WHERE allows_student = 0
  );