<?php
$createTablesMySql = array(

    "
        CREATE TABLE IF NOT EXISTS progress_categories (
          id INT AUTO_INCREMENT PRIMARY KEY,
          code VARCHAR(50) UNIQUE,
          title VARCHAR(100),
          description TEXT
        ) ENGINE=InnoDB;
    ",

    "
        CREATE TABLE IF NOT EXISTS student_progress (
          id INT AUTO_INCREMENT PRIMARY KEY,
          student_id INT NOT NULL,
          teacher_id INT NOT NULL,
          category_id INT NOT NULL,
          progress_level TINYINT,
          summary TEXT,
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        
          FOREIGN KEY (student_id) REFERENCES users(id),
          FOREIGN KEY (teacher_id) REFERENCES users(id),
          FOREIGN KEY (category_id) REFERENCES progress_categories(id)
        ) ENGINE=InnoDB;
    ",

    "
        CREATE TABLE IF NOT EXISTS progress_snapshots (
          id INT AUTO_INCREMENT PRIMARY KEY,
          progress_id INT NOT NULL,
          stage ENUM('before','after'),
          created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        
          FOREIGN KEY (progress_id) REFERENCES student_progress(id)
        ) ENGINE=InnoDB;
    ",

    "CREATE TABLE IF NOT EXISTS progress_entries (
      id INT AUTO_INCREMENT PRIMARY KEY,
      snapshot_id INT,
      progress_id INT,
      type VARCHAR(10), -- text | image | video
      position INT,
      created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
      FOREIGN KEY (snapshot_id) REFERENCES progress_snapshots(id),
      FOREIGN KEY (progress_id) REFERENCES student_progress(id)
    ) ENGINE=InnoDB;",

    "CREATE TABLE IF NOT EXISTS progress_texts (
      entry_id INT PRIMARY KEY,
      content TEXT,
      FOREIGN KEY (entry_id) REFERENCES progress_entries(id) ON DELETE CASCADE
    ) ENGINE=InnoDB;",

    "CREATE TABLE IF NOT EXISTS progress_media (
      entry_id INT PRIMARY KEY,
      file_path VARCHAR(255),
      mime_type VARCHAR(50),
      size_kb INT,
      duration_sec INT,
      FOREIGN KEY (entry_id) REFERENCES progress_entries(id) ON DELETE CASCADE
    ) ENGINE=InnoDB;"

);