-- ================================================================
-- StudyTrack — New Features Migration
-- Run this once on the existing `edurnxco_studytrack` database.
-- Adds: Voice Notes, Subject File Storage, PDF Annotation,
--       Calendar Sync (secret feed token on users table)
-- Safe to re-run — uses IF NOT EXISTS / information_schema checks.
-- ================================================================

-- ── 1. Calendar sync token on users (safe ALTER, won't error if it already exists) ──
SET @col_cal := (SELECT COUNT(*) FROM information_schema.COLUMNS WHERE TABLE_SCHEMA=DATABASE() AND TABLE_NAME='users' AND COLUMN_NAME='calendar_token');
SET @sql_cal := IF(@col_cal=0,'ALTER TABLE `users` ADD COLUMN `calendar_token` VARCHAR(64) DEFAULT NULL UNIQUE AFTER `study_goal_minutes`','SELECT 1');
PREPARE stmt_cal FROM @sql_cal; EXECUTE stmt_cal; DEALLOCATE PREPARE stmt_cal;

-- ── 2. Voice Notes ──────────────────────────────────────────────
CREATE TABLE IF NOT EXISTS `voice_notes` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`     INT NOT NULL,
  `subject_id`  INT DEFAULT NULL,
  `title`       VARCHAR(150) NOT NULL DEFAULT 'Voice Note',
  `file_path`   VARCHAR(255) NOT NULL,
  `duration_seconds` INT DEFAULT 0,
  `file_size`   INT DEFAULT 0,
  `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 SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ── 3. Subject File Storage (documents / images per subject) ────
CREATE TABLE IF NOT EXISTS `subject_files` (
  `id`          INT AUTO_INCREMENT PRIMARY KEY,
  `user_id`     INT NOT NULL,
  `subject_id`  INT NOT NULL,
  `file_name`   VARCHAR(255) NOT NULL COMMENT 'Original filename shown to user',
  `stored_name` VARCHAR(255) NOT NULL COMMENT 'Randomised name on disk',
  `file_path`   VARCHAR(255) NOT NULL COMMENT 'Relative path from site root',
  `file_type`   VARCHAR(20) NOT NULL COMMENT 'pdf, image, doc, ppt, other',
  `mime_type`   VARCHAR(100) DEFAULT NULL,
  `file_size`   INT DEFAULT 0 COMMENT 'bytes',
  `description` VARCHAR(255) 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;

-- ── 4. PDF Annotations (per page, per file) ──────────────────────
CREATE TABLE IF NOT EXISTS `pdf_annotations` (
  `id`              INT AUTO_INCREMENT PRIMARY KEY,
  `file_id`         INT NOT NULL,
  `user_id`         INT NOT NULL,
  `page_number`     INT NOT NULL DEFAULT 1,
  `annotation_data` LONGTEXT NOT NULL COMMENT 'JSON — strokes/highlights/text for this page',
  `updated_at`      TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `uq_file_page` (`file_id`,`page_number`),
  FOREIGN KEY (`file_id`) REFERENCES `subject_files`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
