-- ═══════════════════════════════════════════════════════════════
-- StudyTrack: Class Hours Feature - Database Migration
-- Run this SQL on your database to add the class_hours table
-- ═══════════════════════════════════════════════════════════════

CREATE TABLE IF NOT EXISTS `class_hours` (
  `id`                INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`           INT NOT NULL,
  `subject_id`        INT NOT NULL,
  `class_date`        DATE NOT NULL,
  `start_time`        TIME NOT NULL,
  `end_time`          TIME NOT NULL,
  `duration_minutes`  INT NOT NULL DEFAULT 0,
  `class_type`        ENUM('regular','revision','practical','tutorial','online') DEFAULT 'regular',
  `notes`             TEXT DEFAULT NULL,
  `attended`          TINYINT(1) DEFAULT 1 COMMENT '1=attended, 0=missed',
  `created_at`        TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`)    REFERENCES `users`(`id`)    ON DELETE CASCADE,
  FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`) ON DELETE CASCADE,
  INDEX idx_user_date (`user_id`, `class_date`),
  INDEX idx_user_subject (`user_id`, `subject_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
