const Database = require('better-sqlite3');
const dbPath = process.env.DB_PATH || 'data/school.db';
const db = new Database(dbPath);
db.pragma('foreign_keys=off');
db.exec(`
BEGIN TRANSACTION;
CREATE TABLE users_new (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    uid TEXT UNIQUE,
    email TEXT UNIQUE NOT NULL,
    password TEXT NOT NULL,
    role TEXT NOT NULL CHECK(role IN ('systems_admin', 'school_admin', 'teacher', 'student', 'parent', 'accountant', 'librarian', 'nurse', 'clubs_head')),
    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,
    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
);
INSERT INTO users_new (id, uid, email, password, role, first_name, last_name, phone, address, date_of_birth, gender, id_number, profile_image, emergency_contact, emergency_phone, is_active, last_login, created_at, updated_at, last_synced_at, sync_status, is_deleted) 
SELECT id, uid, email, password, CASE WHEN role = 'admin' THEN 'school_admin' WHEN role = 'principal' THEN 'school_admin' ELSE role END, first_name, last_name, phone, address, date_of_birth, gender, id_number, profile_image, emergency_contact, emergency_phone, is_active, last_login, created_at, updated_at, last_synced_at, sync_status, is_deleted FROM users;

DROP TABLE users;
ALTER TABLE users_new RENAME TO users;
CREATE INDEX IF NOT EXISTS idx_users_role ON users(role);

INSERT OR IGNORE INTO users (email, password, role, first_name, last_name, phone)
VALUES ('sysadmin@school.com', '$2a$10$XU0k2s/A/8JmI00.41/o..p25aI1WnB1I.hS12hYq1N1D8KzR2x4W', 'systems_admin', 'System', 'Administrator', '+1234567890');

COMMIT;
PRAGMA foreign_keys=on;
`);
console.log('Migration successful');