database.js

require('dotenv').config();

const sqlite3 = require('sqlite3').verbose();
const path = require('path');

const dbPath = path.join(__dirname, 'appython.db');
const db = new sqlite3.Database(dbPath);

// Initialize database tables
db.serialize(() => {
  // Assignments table
  db.run(`CREATE TABLE IF NOT EXISTS assignments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    title TEXT NOT NULL,
    prompt TEXT NOT NULL,
    rubric TEXT NOT NULL,
    max_submissions INTEGER DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);

  // Students table
  db.run(`CREATE TABLE IF NOT EXISTS students (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
  )`);

  // Submissions table
  db.run(`CREATE TABLE IF NOT EXISTS submissions (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    assignment_id INTEGER NOT NULL,
    code TEXT NOT NULL,
    feedback TEXT,
    grade TEXT,
    submitted_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (assignment_id) REFERENCES assignments(id)
  )`);

  // Chat history for tutoring
  db.run(`CREATE TABLE IF NOT EXISTS chat_history (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    assignment_id INTEGER NOT NULL,
    role TEXT NOT NULL,
    message TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (assignment_id) REFERENCES assignments(id)
  )`);

  // Settings table for API key and config
  db.run(`CREATE TABLE IF NOT EXISTS settings (
    key TEXT PRIMARY KEY,
    value TEXT NOT NULL
  )`);

  // Drafts table for auto-saving student code
  db.run(`CREATE TABLE IF NOT EXISTS drafts (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    assignment_id INTEGER NOT NULL,
    code TEXT NOT NULL,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (assignment_id) REFERENCES assignments(id),
    UNIQUE(student_id, assignment_id)
  )`);

  // Submission adjustments table - allows per-student submission limit overrides
  db.run(`CREATE TABLE IF NOT EXISTS submission_adjustments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    assignment_id INTEGER NOT NULL,
    adjustment INTEGER NOT NULL DEFAULT 0,
    reason TEXT,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (assignment_id) REFERENCES assignments(id),
    UNIQUE(student_id, assignment_id)
  )`);

  // Tab violations table - tracks when students navigate away from quiz
  db.run(`CREATE TABLE IF NOT EXISTS tab_violations (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    assignment_id INTEGER NOT NULL,
    left_at DATETIME NOT NULL,
    returned_at DATETIME NOT NULL,
    duration_seconds INTEGER NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (assignment_id) REFERENCES assignments(id)
  )`);

  // Project files table - stores individual files for multi-file projects
  db.run(`CREATE TABLE IF NOT EXISTS project_files (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    assignment_id INTEGER NOT NULL,
    filename TEXT NOT NULL,
    content TEXT DEFAULT '',
    is_main_class INTEGER DEFAULT 0,
    tab_order INTEGER DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (assignment_id) REFERENCES assignments(id),
    UNIQUE(student_id, assignment_id, filename)
  )`);

  // Canvas student mapping table - maps our students to Canvas user IDs per course
  db.run(`CREATE TABLE IF NOT EXISTS canvas_student_mapping (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    student_id INTEGER NOT NULL,
    canvas_course_id TEXT NOT NULL,
    canvas_user_id INTEGER NOT NULL,
    canvas_name TEXT NOT NULL,
    FOREIGN KEY (student_id) REFERENCES students(id),
    UNIQUE(student_id, canvas_course_id)
  )`);

  // Add max_submissions column to existing assignments table if it doesn't exist
  db.run(`ALTER TABLE assignments ADD COLUMN max_submissions INTEGER DEFAULT NULL`, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding max_submissions column:', err);
    }
  });

  // Add is_visible column to existing assignments table if it doesn't exist (1 = visible, 0 = hidden)
  db.run(`ALTER TABLE assignments ADD COLUMN is_visible INTEGER DEFAULT 1`, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding is_visible column:', err);
    }
  });

  // Add tab_monitoring_enabled column to existing assignments table if it doesn't exist (1 = enabled, 0 = disabled)
  db.run(`ALTER TABLE assignments ADD COLUMN tab_monitoring_enabled INTEGER DEFAULT 0`, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding tab_monitoring_enabled column:', err);
    }
  });

  // Add authentication columns to students table
  db.run(`ALTER TABLE students ADD COLUMN username TEXT`, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding username column:', err);
    } else if (!err) {
      // Create unique index after adding column
      db.run(`CREATE UNIQUE INDEX IF NOT EXISTS idx_students_username ON students(username)`, (err) => {
        if (err) {
          console.error('Error creating username index:', err);
        }
      });
    }
  });

  db.run(`ALTER TABLE students ADD COLUMN password_hash TEXT`, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding password_hash column:', err);
    }
  });

  db.run(`ALTER TABLE students ADD COLUMN email TEXT`, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding email column:', err);
    }
  });

  db.run(`ALTER TABLE students ADD COLUMN is_active INTEGER DEFAULT 1`, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding is_active column:', err);
    }
  });

  db.run(`ALTER TABLE students ADD COLUMN failed_login_attempts INTEGER DEFAULT 0`, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding failed_login_attempts column:', err);
    }
  });

  db.run(`ALTER TABLE students ADD COLUMN last_login DATETIME`, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding last_login column:', err);
    }
  });

  db.run(`ALTER TABLE students ADD COLUMN must_change_password INTEGER DEFAULT 1`, (err) => {
    if (err && !err.message.includes('duplicate column name')) {
      console.error('Error adding must_change_password column:', err);
    }
  });

  // Initialize default teacher password if not already set
  db.get(`SELECT value FROM settings WHERE key = 'teacher_password'`, async (err, row) => {
    if (err) {
      console.error('Error checking teacher password:', err);
      return;
    }
    if (!row) {
      // SECURITY: Hash default password with bcrypt
      const bcrypt = require('bcrypt');
      const defaultPassword = process.env.DEFAULT_TEACHER_PASSWORD || 'SMCSChief';

      try {
        const passwordHash = await bcrypt.hash(defaultPassword, 10);
        db.run(`INSERT INTO settings (key, value) VALUES ('teacher_password', ?)`, [passwordHash], (err) => {
          if (err) {
            console.error('Error setting default teacher password:', err);
          } else {
            console.log('Default teacher password initialized and hashed');
            console.log('IMPORTANT: Default password is:', defaultPassword);
            console.log('Please change this password immediately after first login!');
          }
        });
      } catch (hashError) {
        console.error('Error hashing default password:', hashError);
      }
    }
  });

  // Initialize AI help enabled setting (default to enabled)
  db.get(`SELECT value FROM settings WHERE key = 'ai_help_enabled'`, (err, row) => {
    if (err) {
      console.error('Error checking AI help setting:', err);
      return;
    }
    if (!row) {
      db.run(`INSERT INTO settings (key, value) VALUES ('ai_help_enabled', ?)`, ['1'], (err) => {
        if (err) {
          console.error('Error setting default AI help setting:', err);
        } else {
          console.log('AI help setting initialized (enabled by default)');
        }
      });
    }
  });

  // Initialize AI model selection (default to claude-sonnet-4-5)
  db.get(`SELECT value FROM settings WHERE key = 'ai_model'`, (err, row) => {
    if (err) {
      console.error('Error checking AI model setting:', err);
      return;
    }
    if (!row) {
      db.run(`INSERT INTO settings (key, value) VALUES ('ai_model', ?)`, ['claude-sonnet-4-5'], (err) => {
        if (err) {
          console.error('Error setting default AI model:', err);
        } else {
          console.log('AI model setting initialized (claude-sonnet-4-5)');
        }
      });
    }
  });

  // Initialize AI models list (available models for dropdown)
  db.get(`SELECT value FROM settings WHERE key = 'ai_models_list'`, (err, row) => {
    if (err) {
      console.error('Error checking AI models list:', err);
      return;
    }
    if (!row) {
      const defaultModels = JSON.stringify([
        { id: 'claude-opus-4-20250514', name: 'Claude Opus 4' },
        { id: 'claude-sonnet-4-5', name: 'Claude Sonnet 4.5' },
        { id: 'claude-haiku-4-20250514', name: 'Claude Haiku 4' }
      ]);
      db.run(`INSERT INTO settings (key, value) VALUES ('ai_models_list', ?)`, [defaultModels], (err) => {
        if (err) {
          console.error('Error setting default AI models list:', err);
        } else {
          console.log('AI models list initialized with default models');
        }
      });
    }
  });
});

module.exports = db;