-- StudyTrack Database Schema
-- Database: edurnxco_studytrack

CREATE DATABASE IF NOT EXISTS `edurnxco_studytrack` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `edurnxco_studytrack`;

CREATE TABLE IF NOT EXISTS `users` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `avatar` VARCHAR(255) DEFAULT NULL,
  `otp_code` VARCHAR(10) DEFAULT NULL,
  `otp_expires` DATETIME DEFAULT NULL,
  `is_verified` TINYINT(1) DEFAULT 0,
  `reset_token` VARCHAR(255) DEFAULT NULL,
  `reset_expires` DATETIME DEFAULT NULL,
  `study_goal_minutes` INT DEFAULT 120,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `admins` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(100) NOT NULL,
  `email` VARCHAR(150) NOT NULL UNIQUE,
  `password` VARCHAR(255) NOT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `subjects` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `name` VARCHAR(100) NOT NULL,
  `color` VARCHAR(10) DEFAULT '#6366f1',
  `target_marks` DECIMAL(5,2) DEFAULT 100,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `study_sessions` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `subject_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `duration_minutes` INT NOT NULL DEFAULT 0,
  `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;

CREATE TABLE IF NOT EXISTS `marks` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `subject_id` INT NOT NULL,
  `exam_name` VARCHAR(150) NOT NULL,
  `marks_obtained` DECIMAL(6,2) NOT NULL,
  `total_marks` DECIMAL(6,2) NOT NULL DEFAULT 100,
  `exam_date` DATE NOT NULL,
  `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;

CREATE TABLE IF NOT EXISTS `tasks` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `subject_id` INT DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT DEFAULT NULL,
  `due_date` DATE DEFAULT NULL,
  `priority` ENUM('low','medium','high') DEFAULT 'medium',
  `status` ENUM('pending','in_progress','completed') DEFAULT 'pending',
  `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;

CREATE TABLE IF NOT EXISTS `al_exam_dates` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `exam_name` VARCHAR(150) NOT NULL,
  `exam_date` DATE NOT NULL,
  `description` TEXT DEFAULT NULL,
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `announcements` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `title` VARCHAR(255) NOT NULL,
  `content` TEXT NOT NULL,
  `priority` ENUM('low','normal','high') DEFAULT 'normal',
  `is_active` TINYINT(1) DEFAULT 1,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `study_streaks` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `date` DATE NOT NULL,
  `minutes_studied` INT DEFAULT 0,
  UNIQUE KEY `user_date` (`user_id`,`date`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

INSERT IGNORE INTO `admins` (`name`, `email`, `password`) VALUES 
('Admin', 'admin@studytrack.lk', '$2y$12$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

INSERT IGNORE INTO `al_exam_dates` (`exam_name`, `exam_date`, `description`) VALUES 
('A/L Examination 2025', '2025-08-15', 'Sri Lanka Advanced Level Examination');

-- ==================== NOTIFICATIONS ====================
CREATE TABLE IF NOT EXISTS `notifications` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT DEFAULT NULL COMMENT 'NULL = broadcast to all users',
  `title` VARCHAR(255) NOT NULL,
  `message` TEXT NOT NULL,
  `type` ENUM('info','warning','success','danger') DEFAULT 'info',
  `is_read` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ================================================================
-- StudyTrack v12 — New Features Database Tables
-- Run this SQL in your database (edurnxco_studytrack)
-- ================================================================

-- 1. TIMETABLE / STUDY SCHEDULE
CREATE TABLE IF NOT EXISTS `timetable` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `subject_id` INT DEFAULT NULL,
  `day_of_week` ENUM('Monday','Tuesday','Wednesday','Thursday','Friday','Saturday','Sunday') NOT NULL,
  `start_time` TIME NOT NULL,
  `end_time` TIME NOT NULL,
  `label` VARCHAR(100) DEFAULT NULL,
  `color` VARCHAR(10) DEFAULT '#38bdf8',
  `is_active` TINYINT(1) DEFAULT 1,
  `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;

-- 2. GOALS
CREATE TABLE IF NOT EXISTS `goals` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `subject_id` INT DEFAULT NULL,
  `title` VARCHAR(200) NOT NULL,
  `type` ENUM('study_hours','mark_target','streak','custom') DEFAULT 'custom',
  `target_value` DECIMAL(8,2) NOT NULL,
  `current_value` DECIMAL(8,2) DEFAULT 0,
  `unit` VARCHAR(30) DEFAULT '',
  `deadline` DATE DEFAULT NULL,
  `status` ENUM('active','completed','failed') DEFAULT 'active',
  `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;

-- 3. STUDY REMINDERS
CREATE TABLE IF NOT EXISTS `reminders` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `title` VARCHAR(200) NOT NULL,
  `message` TEXT DEFAULT NULL,
  `reminder_time` TIME NOT NULL,
  `days` VARCHAR(50) NOT NULL COMMENT 'JSON array e.g. ["Mon","Wed","Fri"]',
  `is_active` TINYINT(1) DEFAULT 1,
  `last_sent` DATE DEFAULT NULL,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 4. NOTES
CREATE TABLE IF NOT EXISTS `notes` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `subject_id` INT DEFAULT NULL,
  `title` VARCHAR(200) NOT NULL,
  `content` LONGTEXT DEFAULT NULL,
  `color` VARCHAR(10) DEFAULT '#38bdf8',
  `is_pinned` TINYINT(1) DEFAULT 0,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE 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;

-- 5. FLASHCARDS
CREATE TABLE IF NOT EXISTS `flashcards` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `subject_id` INT DEFAULT NULL,
  `deck_name` VARCHAR(100) NOT NULL,
  `question` TEXT NOT NULL,
  `answer` TEXT NOT NULL,
  `difficulty` ENUM('easy','medium','hard') DEFAULT 'medium',
  `times_reviewed` INT DEFAULT 0,
  `last_reviewed` DATETIME 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 SET NULL
) ENGINE=InnoDB;

-- 6. ACHIEVEMENTS
CREATE TABLE IF NOT EXISTS `achievement_defs` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `key` VARCHAR(50) NOT NULL UNIQUE,
  `title` VARCHAR(100) NOT NULL,
  `description` VARCHAR(255) NOT NULL,
  `icon` VARCHAR(10) NOT NULL,
  `condition_type` VARCHAR(50) NOT NULL COMMENT 'streak_days, total_hours, total_sessions, marks_avg, etc.',
  `condition_value` INT NOT NULL,
  `xp_reward` INT DEFAULT 50
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS `user_achievements` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `achievement_id` INT NOT NULL,
  `earned_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `user_achiev` (`user_id`,`achievement_id`),
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`achievement_id`) REFERENCES `achievement_defs`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 7. LEADERBOARD (friend connections)
CREATE TABLE IF NOT EXISTS `friend_requests` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `sender_id` INT NOT NULL,
  `receiver_id` INT NOT NULL,
  `status` ENUM('pending','accepted','rejected') DEFAULT 'pending',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY `unique_request` (`sender_id`,`receiver_id`),
  FOREIGN KEY (`sender_id`) REFERENCES `users`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`receiver_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- 8. AI TIPS LOG (cache AI responses)
CREATE TABLE IF NOT EXISTS `ai_tips_log` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `user_id` INT NOT NULL,
  `tip_text` TEXT NOT NULL,
  `context_type` VARCHAR(50) DEFAULT 'general',
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB;

-- Seed achievement definitions
INSERT IGNORE INTO `achievement_defs` (`key`,`title`,`description`,`icon`,`condition_type`,`condition_value`,`xp_reward`) VALUES
('first_session',  'First Step',        'Log your first study session',               '🌱', 'total_sessions', 1,   25),
('sessions_10',    'Getting Started',   'Log 10 study sessions',                      '📚', 'total_sessions', 10,  50),
('sessions_50',    'Dedicated Student', 'Log 50 study sessions',                      '🎯', 'total_sessions', 50,  100),
('sessions_100',   'Study Machine',     'Log 100 study sessions',                     '⚡', 'total_sessions', 100, 200),
('streak_3',       'On a Roll',         'Achieve a 3-day study streak',               '🔥', 'streak_days',    3,   30),
('streak_7',       'Week Warrior',      'Achieve a 7-day study streak',               '💪', 'streak_days',    7,   75),
('streak_30',      'Unstoppable',       'Achieve a 30-day study streak',              '🏆', 'streak_days',    30,  300),
('hours_10',       'Ten Hours',         'Study for 10 total hours',                   '⏱️', 'total_hours',    10,  50),
('hours_100',      'Century Scholar',   'Study for 100 total hours',                  '🎓', 'total_hours',    100, 250),
('marks_75',       'High Achiever',     'Achieve 75%+ average across all exams',      '⭐', 'marks_avg',      75,  100),
('marks_90',       'Excellence',        'Achieve 90%+ average across all exams',      '💎', 'marks_avg',      90,  200),
('first_goal',     'Goal Setter',       'Create your first study goal',               '🎯', 'goals_created',  1,   25),
('first_note',     'Note Taker',        'Write your first study note',                '📝', 'notes_created',  1,   20),
('flashcards_10',  'Flash Learner',     'Create 10 flashcards',                       '🃏', 'flashcards',     10,  50),
('early_bird',     'Early Bird',        'Log a study session before 7am',             '🌅', 'early_session',  1,   40);

-- Add XP column to users table
-- Safely add xp_points column (compatible with older MySQL)
SET @col_exists = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'users'
  AND COLUMN_NAME = 'xp_points'
);
SET @sql = IF(@col_exists = 0,
  'ALTER TABLE `users` ADD COLUMN `xp_points` INT DEFAULT 0',
  'SELECT 1'
);
PREPARE stmt FROM @sql; EXECUTE stmt; DEALLOCATE PREPARE stmt;

-- Safely add display_name column
SET @col2 = (
  SELECT COUNT(*) FROM information_schema.COLUMNS
  WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'users'
  AND COLUMN_NAME = 'display_name'
);
SET @sql2 = IF(@col2 = 0,
  'ALTER TABLE `users` ADD COLUMN `display_name` VARCHAR(100) DEFAULT NULL',
  'SELECT 1'
);
PREPARE stmt2 FROM @sql2; EXECUTE stmt2; DEALLOCATE PREPARE stmt2;
