/**
 * Migration: Add missing chat/messaging tables and teacher-tools tables.
 *
 * These tables are referenced by the messages and teacher controllers but were
 * never created in the database. The init.js schema is missing several tables
 * (CREATE TABLE IF NOT EXISTS is a no-op when init.js never declares them),
 * so this migration backfills them.
 *
 * Tables created:
 *   - syllabuses, schemes_of_work, focus_points, lesson_plans, student_social_docs
 *     (also in migration_teacher_tools.js, included here for idempotence)
 *   - club_chats (referenced by ExtracurricularChat.tsx)
 *   - chat_messages (referenced by messages.controller.js)
 *   - chat_groups, chat_group_members (referenced by /api/messages/conversations)
 *
 * Also adds the messages.group_id column if missing (the controller's group
 * conversations query references it but it was never in the messages schema).
 */

const Database = require('better-sqlite3');
const path = require('path');

const dbPath = process.env.DB_PATH || path.join(__dirname, '../../data/school.db');
const db = new Database(dbPath);
db.pragma('foreign_keys = ON');

console.log('Running migration: Adding chat_groups, club_chats, chat_messages...');

db.exec(`
  -- =============================================
  -- CHAT GROUPS (group conversations)
  -- =============================================
  CREATE TABLE IF NOT EXISTS chat_groups (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uid TEXT UNIQUE,
    name TEXT NOT NULL,
    description TEXT,
    created_by INTEGER REFERENCES users(id),
    is_active INTEGER DEFAULT 1,
    created_at DATETIME DEFAULT (datetime('now', 'localtime')),
    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
    last_synced_at DATETIME,
    sync_status TEXT DEFAULT 'pending',
    is_deleted INTEGER DEFAULT 0
  );

  CREATE TABLE IF NOT EXISTS chat_group_members (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uid TEXT UNIQUE,
    group_id INTEGER NOT NULL REFERENCES chat_groups(id),
    user_id INTEGER NOT NULL REFERENCES users(id),
    role TEXT DEFAULT 'member',
    joined_at DATETIME DEFAULT (datetime('now', 'localtime')),
    last_synced_at DATETIME,
    sync_status TEXT DEFAULT 'pending',
    is_deleted INTEGER DEFAULT 0,
    UNIQUE(group_id, user_id)
  );

  -- =============================================
  -- CLUB CHATS (ExtracurricularChat.tsx)
  -- =============================================
  CREATE TABLE IF NOT EXISTS club_chats (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uid TEXT UNIQUE,
    club_id INTEGER,
    sender_id INTEGER REFERENCES users(id),
    message TEXT,
    image_url TEXT,
    created_at DATETIME DEFAULT (datetime('now', 'localtime')),
    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
    last_synced_at DATETIME,
    sync_status TEXT DEFAULT 'pending',
    is_deleted INTEGER DEFAULT 0
  );

  -- =============================================
  -- CHAT MESSAGES (private messages between users)
  -- =============================================
  CREATE TABLE IF NOT EXISTS chat_messages (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uid TEXT UNIQUE,
    conversation_id TEXT,
    sender_id INTEGER REFERENCES users(id),
    recipient_id INTEGER REFERENCES users(id),
    message TEXT,
    read_at DATETIME,
    created_at DATETIME DEFAULT (datetime('now', 'localtime')),
    updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
    last_synced_at DATETIME,
    sync_status TEXT DEFAULT 'pending',
    is_deleted INTEGER DEFAULT 0
  );
`);

// Add messages.group_id if missing
const cols = db.prepare("PRAGMA table_info(messages)").all();
const hasGroupId = cols.some(c => c.name === 'group_id');
if (!hasGroupId) {
  console.log('Adding messages.group_id column...');
  db.exec('ALTER TABLE messages ADD COLUMN group_id INTEGER REFERENCES chat_groups(id)');
} else {
  console.log('messages.group_id already present');
}

console.log('Migration complete!');
process.exit(0);