-- ═══════════════════════════════════════════════════════════════════
-- StudyTrack: New Features Migration
-- Run this entire file on your MySQL database (edurnxco_studytrack)
-- ═══════════════════════════════════════════════════════════════════

-- 1. PAST PAPERS TRACKER
CREATE TABLE IF NOT EXISTS `past_papers` (
  `id`           INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`      INT NOT NULL,
  `subject_id`   INT NOT NULL,
  `year`         YEAR NOT NULL,
  `paper_no`     TINYINT DEFAULT 1 COMMENT '1=Paper 1, 2=Paper 2, 3=Paper 3',
  `done`         TINYINT(1) DEFAULT 0,
  `score`        DECIMAL(6,2) DEFAULT NULL,
  `total_marks`  DECIMAL(6,2) DEFAULT 100,
  `done_date`    DATE DEFAULT NULL,
  `notes`        TEXT DEFAULT NULL,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_user_paper` (`user_id`,`subject_id`,`year`,`paper_no`),
  FOREIGN KEY (`user_id`)    REFERENCES `users`(`id`)    ON DELETE CASCADE,
  FOREIGN KEY (`subject_id`) REFERENCES `subjects`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 2. USER'S OWN RESOURCE LINKS (per subject)
CREATE TABLE IF NOT EXISTS `subject_resources` (
  `id`           INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`      INT NOT NULL,
  `subject_id`   INT NOT NULL,
  `title`        VARCHAR(255) NOT NULL,
  `url`          TEXT NOT NULL,
  `type`         ENUM('youtube','telegram','pdf','website','notes','book','link') DEFAULT 'link',
  `notes`        TEXT DEFAULT NULL,
  `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
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 3. ADMIN-PUBLISHED RESOURCES (visible to all students)
CREATE TABLE IF NOT EXISTS `admin_resources` (
  `id`           INT AUTO_INCREMENT PRIMARY KEY,
  `title`        VARCHAR(255) NOT NULL,
  `url`          TEXT NOT NULL,
  `type`         ENUM('youtube','telegram','pdf','website','notes','book','link') DEFAULT 'link',
  `subject_tag`  VARCHAR(100) DEFAULT '',
  `notes`        TEXT DEFAULT NULL,
  `is_active`    TINYINT(1) DEFAULT 1 COMMENT '1=visible to students, 0=hidden',
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 4. MUSIC LIBRARY (per user)
CREATE TABLE IF NOT EXISTS `music_library` (
  `id`           INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`      INT NOT NULL,
  `title`        VARCHAR(255) NOT NULL,
  `filename`     VARCHAR(255) NOT NULL,
  `filesize`     INT DEFAULT 0,
  `created_at`   TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
