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: Updating Roles and Departmental links...');

try {
  db.transaction(() => {
    // 1. Add department_id to users
    try {
      db.exec('ALTER TABLE users ADD COLUMN department_id INTEGER REFERENCES departments(id)');
      console.log('Added department_id column to users table');
    } catch (e) {
      console.log('department_id column might already exist');
    }

    // 2. We can't easily change the CHECK constraint in SQLite without recreating the table.
    // However, we can rename the table, create the new one, and copy data.
    
    const tableInfo = db.prepare("PRAGMA table_info(users)").all();
    const hasNewRoles = db.prepare("SELECT sql FROM sqlite_master WHERE name='users'").get().sql.includes('principal');

    if (!hasNewRoles) {
      console.log('Updating users table to include new roles...');
      
      db.exec('ALTER TABLE users RENAME TO users_old');
      
      db.exec(`
        CREATE TABLE users (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          uid TEXT UNIQUE,
          email TEXT UNIQUE NOT NULL,
          password TEXT NOT NULL,
          role TEXT NOT NULL CHECK(role IN ('admin', 'teacher', 'student', 'parent', 'accountant', 'librarian', 'nurse', 'principal', 'hr', 'bursar')),
          first_name TEXT NOT NULL,
          last_name TEXT NOT NULL,
          phone TEXT,
          address TEXT,
          date_of_birth DATE,
          gender TEXT CHECK(gender IN ('male', 'female', 'other')),
          id_number TEXT,
          profile_image TEXT,
          emergency_contact TEXT,
          emergency_phone TEXT,
          department_id INTEGER REFERENCES departments(id),
          is_active INTEGER DEFAULT 1,
          last_login DATETIME,
          created_at DATETIME DEFAULT (datetime('now', 'localtime')),
          updated_at DATETIME DEFAULT (datetime('now', 'localtime')),
          last_synced_at DATETIME,
          sync_status TEXT DEFAULT 'pending' CHECK(sync_status IN ('synced', 'pending', 'conflict')),
          is_deleted INTEGER DEFAULT 0
        )
      `);

      db.exec(`
        INSERT INTO users (
          id, uid, email, password, role, first_name, last_name, phone, address, 
          date_of_birth, gender, id_number, profile_image, emergency_contact, 
          emergency_phone, department_id, is_active, last_login, created_at, 
          updated_at, last_synced_at, sync_status, is_deleted
        )
        SELECT 
          id, uid, email, password, role, first_name, last_name, phone, address, 
          date_of_birth, gender, id_number, profile_image, emergency_contact, 
          emergency_phone, department_id, is_active, last_login, created_at, 
          updated_at, last_synced_at, sync_status, is_deleted
        FROM users_old
      `);

      db.exec('DROP TABLE users_old');
      
      // Re-create indexes
      db.exec('CREATE INDEX idx_users_sync ON users(sync_status, is_deleted)');
      db.exec('CREATE INDEX idx_users_uid ON users(uid)');
      db.exec('CREATE INDEX idx_users_email ON users(email)');
      db.exec('CREATE INDEX idx_users_role ON users(role)');
      db.exec('CREATE INDEX idx_users_dept ON users(department_id)');
      
      console.log('Users table recreated with expanded roles and department_id index.');
    } else {
      console.log('Users table already has updated role constraints.');
    }
  })();
  
  console.log('Migration complete!');
} catch (error) {
  console.error('Migration failed:', error);
}

process.exit(0);
