95 lines
3.7 KiB
JavaScript
95 lines
3.7 KiB
JavaScript
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 CURRENT_TIMESTAMP,
|
|
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
|
|
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);
|