38956-vm/server/db.ts

import Database from 'better-sqlite3';
import path from 'path';
import { fileURLToPath } from 'url';
import bcrypt from 'bcryptjs';

const __filename = fileURLToPath(import.meta.url);
const __dirname = path.dirname(__filename);

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

db.pragma('journal_mode = WAL');

// Initialize database schema
export function initDb() {
  db.exec(`
    CREATE TABLE IF NOT EXISTS branches (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL UNIQUE
    );

    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      email TEXT NOT NULL UNIQUE,
      password TEXT NOT NULL,
      role TEXT NOT NULL CHECK(role IN ('admin', 'manager')),
      branch_id INTEGER,
      FOREIGN KEY(branch_id) REFERENCES branches(id)
    );

    CREATE TABLE IF NOT EXISTS employees (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT NOT NULL,
      branch_id INTEGER NOT NULL,
      status TEXT NOT NULL DEFAULT 'active' CHECK(status IN ('active', 'passive')),
      FOREIGN KEY(branch_id) REFERENCES branches(id)
    );

    CREATE TABLE IF NOT EXISTS shifts (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      employee_id INTEGER NOT NULL,
      branch_id INTEGER NOT NULL,
      start_time INTEGER NOT NULL,
      end_time INTEGER NOT NULL,
      FOREIGN KEY(employee_id) REFERENCES employees(id),
      FOREIGN KEY(branch_id) REFERENCES branches(id)
    );
  `);

  // Seed initial admin user if not exists
  const adminExists = db.prepare('SELECT 1 FROM users WHERE role = ?').get('admin');
  if (!adminExists) {
    const salt = bcrypt.genSaltSync(10);
    const hash = bcrypt.hashSync('admin123', salt);
    db.prepare('INSERT INTO users (email, password, role) VALUES (?, ?, ?)').run('admin@mua.com.tr', hash, 'admin');
  }

  // Seed branches if none exist
  const branchCount = db.prepare('SELECT COUNT(*) as count FROM branches').get() as { count: number };
  if (branchCount.count === 0) {
    const branches = [
      'MUÀ Yenikoy (Merkez)',
      'MUÀ Bebek',
      'MUÀ Akaretler',
      'MUÀ Arnavutkoy',
      'MUÀ Resitpasa',
      'MUÀ Caddebostan',
      'MUÀ Saskinbakkal',
      'MUÀ Gokturk',
      'MUÀ Acarkent Coliseum',
      'MUÀ Fisekhane',
      'MUÀ Bakirkoy',
      'MUÀ Mall of Istanbul',
      'MUÀ Hiltown',
      "City's Nisantasi AVM",
      'MUÀ Watergarden AVM',
      "City's Istanbul AVM",
      'MUÀ Alsancak',
      'MUÀ Istinye Park Izmir',
      'MUÀ Alacati',
      'MUÀ Ergin Concept',
      'MUÀ Yahya Kaptan',
      'MUÀ Bodrum Marina',
      'MUÀ Yalikavak',
      'MUÀ Sakarya'
    ];
    
    const insertBranch = db.prepare('INSERT INTO branches (name) VALUES (?)');
    const insertMany = db.transaction((branchesList: string[]) => {
      for (const branch of branchesList) {
        insertBranch.run(branch);
      }
    });
    
    insertMany(branches);

    // Seed a default manager for testing (Yenikoy Merkez - ID 1)
    const salt = bcrypt.genSaltSync(10);
    const hash = bcrypt.hashSync('mudur123', salt);
    db.prepare('INSERT INTO users (email, password, role, branch_id) VALUES (?, ?, ?, ?)').run('yenikoy@mua.com.tr', hash, 'manager', 1);

    // Seed some employees for Yenikoy
    const emp1 = db.prepare("INSERT INTO employees (name, branch_id) VALUES ('Ahmet Yılmaz', 1)").run().lastInsertRowid;
    const emp2 = db.prepare("INSERT INTO employees (name, branch_id) VALUES ('Ayşe Demir', 1)").run().lastInsertRowid;
    const emp3 = db.prepare("INSERT INTO employees (name, branch_id) VALUES ('Mehmet Kaya', 1)").run().lastInsertRowid;
    const emp4 = db.prepare("INSERT INTO employees (name, branch_id) VALUES ('Fatma Şahin', 1)").run().lastInsertRowid;
    const emp5 = db.prepare("INSERT INTO employees (name, branch_id) VALUES ('Caner Çelik', 1)").run().lastInsertRowid;

    // Generate mock shifts for the current week
    const now = new Date();
    const dayOfWeek = now.getDay();
    const diffToMonday = now.getDate() - dayOfWeek + (dayOfWeek === 0 ? -6 : 1);
    const monday = new Date(now.setDate(diffToMonday));
    monday.setHours(0, 0, 0, 0);

    const insertShift = db.prepare('INSERT INTO shifts (employee_id, branch_id, start_time, end_time) VALUES (?, ?, ?, ?)');
    const getTs = (daysOffset: number, hour: number) => {
      const d = new Date(monday);
      d.setDate(d.getDate() + daysOffset);
      d.setHours(hour, 0, 0, 0);
      return d.getTime();
    };

    // Monday
    insertShift.run(emp1, 1, getTs(0, 8), getTs(0, 16));
    insertShift.run(emp2, 1, getTs(0, 10), getTs(0, 18));
    insertShift.run(emp3, 1, getTs(0, 14), getTs(0, 22));
    
    // Tuesday
    insertShift.run(emp1, 1, getTs(1, 8), getTs(1, 16));
    insertShift.run(emp4, 1, getTs(1, 10), getTs(1, 18));
    insertShift.run(emp5, 1, getTs(1, 14), getTs(1, 22));
    
    // Wednesday
    insertShift.run(emp1, 1, getTs(2, 8), getTs(2, 18)); // 10h
    insertShift.run(emp2, 1, getTs(2, 10), getTs(2, 20)); // 10h
    insertShift.run(emp3, 1, getTs(2, 12), getTs(2, 22)); // 10h
    
    // Thursday
    insertShift.run(emp1, 1, getTs(3, 8), getTs(3, 18)); // 10h
    insertShift.run(emp4, 1, getTs(3, 10), getTs(3, 20)); // 10h
    insertShift.run(emp5, 1, getTs(3, 12), getTs(3, 22)); // 10h
    
    // Friday (emp1 hits 46 hours to trigger the warning AC 3.1)
    insertShift.run(emp1, 1, getTs(4, 8), getTs(4, 18)); // 10h (Total: 8+8+10+10+10 = 46)
    insertShift.run(emp2, 1, getTs(4, 10), getTs(4, 18)); // 8h
    insertShift.run(emp3, 1, getTs(4, 14), getTs(4, 22)); // 8h
  }
}

export default db;