CREATE TABLE IF NOT EXISTS modern_lessons (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  level TEXT NOT NULL CHECK (level IN ('A1', 'A2', 'B1', 'B2', 'C1', 'C2')),
  day INTEGER NOT NULL,
  title TEXT NOT NULL,
  description TEXT,
  audio_file TEXT,
  is_published INTEGER DEFAULT 1,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(level, day)
);

CREATE TABLE IF NOT EXISTS modern_conversations (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lesson_id INTEGER NOT NULL,
  speaker TEXT NOT NULL CHECK (speaker IN ('A', 'B')),
  text TEXT NOT NULL,
  persian TEXT,
  audio_file TEXT,
  order_index INTEGER DEFAULT 0,
  FOREIGN KEY (lesson_id) REFERENCES modern_lessons(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS modern_speaking_sentences (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  lesson_id INTEGER NOT NULL,
  english TEXT NOT NULL,
  persian TEXT,
  order_index INTEGER DEFAULT 0,
  FOREIGN KEY (lesson_id) REFERENCES modern_lessons(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS modern_user_progress (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  lesson_id INTEGER NOT NULL,
  listening_score INTEGER DEFAULT 0,
  speaking_completed INTEGER DEFAULT 0,
  reading_completed INTEGER DEFAULT 0,
  writing_completed INTEGER DEFAULT 0,
  last_activity DATETIME DEFAULT CURRENT_TIMESTAMP,
  created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
  UNIQUE(user_id, lesson_id)
);

CREATE TABLE IF NOT EXISTS modern_test_results (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id INTEGER NOT NULL,
  lesson_id INTEGER NOT NULL,
  step TEXT NOT NULL CHECK (step IN ('listening', 'speaking', 'reading', 'writing')),
  score INTEGER NOT NULL,
  total_questions INTEGER NOT NULL,
  completed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER IF NOT EXISTS update_modern_lessons_timestamp 
  AFTER UPDATE ON modern_lessons
  BEGIN
    UPDATE modern_lessons SET updated_at = CURRENT_TIMESTAMP WHERE id = NEW.id;
  END;

CREATE TRIGGER IF NOT EXISTS update_modern_progress_activity
  AFTER UPDATE ON modern_user_progress
  BEGIN
    UPDATE modern_user_progress SET last_activity = CURRENT_TIMESTAMP WHERE id = NEW.id;
  END;