backend/src/database/schema.ts - main - npronnikov/ProcessVisualizer

backend/src/database/schema.ts
402 строки14 KB
Madnick
refactor metrics fields and update remaining calculation
18 май 2026, 19:06
18 май 2026, 19:060f0c9d2
import db from './connection';
import bcrypt from 'bcryptjs';

const SALT_ROUNDS = 10;

function hashPassword(password: string): string {
  return bcrypt.hashSync(password, SALT_ROUNDS);
}

/**
 * Initialize database schema
 * Run this on application startup
 */
export function initializeSchema(): void {
  // Create users table
  db.exec(`
    CREATE TABLE IF NOT EXISTS users (
      id TEXT PRIMARY KEY,
      login TEXT UNIQUE NOT NULL,
      password TEXT NOT NULL,
      name TEXT NOT NULL,
      is_admin INTEGER NOT NULL DEFAULT 0 CHECK(is_admin IN (0, 1)),
      created_at TEXT DEFAULT (datetime('now'))
    );
  `);

  // Add is_admin column if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE users ADD COLUMN is_admin INTEGER NOT NULL DEFAULT 0 CHECK(is_admin IN (0, 1));`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Insert default users if they don't exist
  const userCount = db.prepare('SELECT COUNT(*) as count FROM users').get() as { count: number };
  if (userCount.count === 0) {
    const { v4: uuidv4 } = require('uuid');
    db.prepare('INSERT INTO users (id, login, password, name, is_admin) VALUES (?, ?, ?, ?, ?)')
      .run(uuidv4(), 'admin', hashPassword('admin'), 'Administrator', 1);
    db.prepare('INSERT INTO users (id, login, password, name, is_admin) VALUES (?, ?, ?, ?, ?)')
      .run(uuidv4(), 'user', hashPassword('user'), 'User', 0);
    db.prepare('INSERT INTO users (id, login, password, name, is_admin) VALUES (?, ?, ?, ?, ?)')
      .run(uuidv4(), 'demo', hashPassword('demo'), 'Demo User', 0);
    console.log('Default users created: admin/admin (admin), user/user, demo/demo');
  }

  // Insert admin user if doesn't exist (for existing databases)
  const adminExists = db.prepare('SELECT COUNT(*) as count FROM users WHERE login = ?').get('admin') as { count: number };
  if (adminExists.count === 0) {
    const { v4: uuidv4 } = require('uuid');
    db.prepare('INSERT INTO users (id, login, password, name, is_admin) VALUES (?, ?, ?, ?, ?)')
      .run(uuidv4(), 'admin', hashPassword('admin'), 'Administrator', 1);
    console.log('Admin user created: admin/admin');
  }

  // Create process_groups table
  db.exec(`
    CREATE TABLE IF NOT EXISTS process_groups (
      id TEXT PRIMARY KEY,
      name TEXT UNIQUE NOT NULL,
      description TEXT,
      weight_unit TEXT NOT NULL CHECK(weight_unit IN ('percentage')),
      creator_id TEXT REFERENCES users(id),
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now'))
    );
  `);

  // Add creator_id column to process_groups if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE process_groups ADD COLUMN creator_id TEXT REFERENCES users(id);`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Create processes table
  db.exec(`
    CREATE TABLE IF NOT EXISTS processes (
      id TEXT PRIMARY KEY,
      group_id TEXT NOT NULL REFERENCES process_groups(id) ON DELETE CASCADE,
      name TEXT NOT NULL,
      description TEXT,
      responsible TEXT,
      total_value REAL NOT NULL CHECK(total_value > 0),
      target_value REAL,
      bpmn_file_path TEXT,
      status TEXT NOT NULL CHECK(status IN ('OK', 'NEED_BPM_DIAGRAM', 'NEED_HISTORY')) DEFAULT 'NEED_BPM_DIAGRAM',
      bpmn_uploaded_at TEXT,
      creator_id TEXT REFERENCES users(id),
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now'))
    );
  `);

  // Add creator_id column to processes if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE processes ADD COLUMN creator_id TEXT REFERENCES users(id);`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Rename process columns if they exist (for existing databases)
  try {
    db.exec(`ALTER TABLE processes RENAME COLUMN weight TO total_value;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }
  try {
    db.exec(`ALTER TABLE processes RENAME COLUMN target_metric_value TO target_value;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }

  // Add target_value column if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE processes ADD COLUMN target_value REAL;`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Add responsible column if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE processes ADD COLUMN responsible TEXT;`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Migrate process status constraint for existing databases (SQLite can't ALTER CHECK constraints)
  const processesTable = db
    .prepare(`SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'processes'`)
    .get() as { sql?: string } | undefined;

  const needsStatusMigration =
    processesTable?.sql?.includes(`CHECK(status IN ('OK', 'NEEDS_EFFORT'))`) === true;

  if (needsStatusMigration) {
    db.exec(`PRAGMA foreign_keys=OFF;`);
    try {
      db.exec('BEGIN;');
      db.exec(`
        CREATE TABLE processes_new (
          id TEXT PRIMARY KEY,
          group_id TEXT NOT NULL REFERENCES process_groups(id) ON DELETE CASCADE,
          name TEXT NOT NULL,
          description TEXT,
          responsible TEXT,
          total_value REAL NOT NULL CHECK(total_value > 0),
          target_value REAL,
          bpmn_file_path TEXT,
          status TEXT NOT NULL CHECK(status IN ('OK', 'NEED_BPM_DIAGRAM', 'NEED_HISTORY')) DEFAULT 'NEED_BPM_DIAGRAM',
          bpmn_uploaded_at TEXT,
          created_at TEXT DEFAULT (datetime('now')),
          updated_at TEXT DEFAULT (datetime('now'))
        );
      `);

      db.exec(`
        INSERT INTO processes_new (
          id, group_id, name, description, responsible, total_value, target_value, bpmn_file_path, status, bpmn_uploaded_at, created_at, updated_at
        )
        SELECT
          id,
          group_id,
          name,
          description,
          NULL as responsible,
          total_value,
          target_value,
          bpmn_file_path,
          CASE
            WHEN status = 'OK' THEN 'OK'
            WHEN bpmn_file_path IS NULL THEN 'NEED_BPM_DIAGRAM'
            ELSE 'NEED_HISTORY'
          END as status,
          bpmn_uploaded_at,
          created_at,
          updated_at
        FROM processes;
      `);

      db.exec(`DROP TABLE processes;`);
      db.exec(`ALTER TABLE processes_new RENAME TO processes;`);
      db.exec('COMMIT;');
    } catch (error) {
      try {
        db.exec('ROLLBACK;');
      } catch (_) {
        // ignore rollback errors
      }
      throw error;
    } finally {
      db.exec(`PRAGMA foreign_keys=ON;`);
    }
  }

  // Create operations table
  db.exec(`
    CREATE TABLE IF NOT EXISTS operations (
      id TEXT PRIMARY KEY,
      process_id TEXT NOT NULL REFERENCES processes(id) ON DELETE CASCADE,
      bpmn_element_id TEXT NOT NULL,
      name TEXT NOT NULL,
      is_manual INTEGER DEFAULT 1 CHECK(is_manual IN (0, 1)),
      fact_effort REAL,
      target_effort REAL,
      created_at TEXT DEFAULT (datetime('now')),
      updated_at TEXT DEFAULT (datetime('now')),
      UNIQUE(process_id, bpmn_element_id)
    );
  `);

  // Rename operation columns if they exist (for existing databases)
  try {
    db.exec(`ALTER TABLE operations RENAME COLUMN effort_hours_per_day TO fact_effort;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }
  try {
    db.exec(`ALTER TABLE operations RENAME COLUMN effort_per_day TO fact_effort;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }
  try {
    db.exec(`ALTER TABLE operations RENAME COLUMN fact_effort_per_day TO fact_effort;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }
  try {
    db.exec(`ALTER TABLE operations RENAME COLUMN target_effort_hours_per_day TO target_effort;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }
  try {
    db.exec(`ALTER TABLE operations RENAME COLUMN target_effort_per_day TO target_effort;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }
  try {
    db.exec(`ALTER TABLE operations ADD COLUMN fact_effort REAL;`);
  } catch (error) {
    // Column already exists, ignore
  }
  try {
    db.exec(`ALTER TABLE operations ADD COLUMN target_effort REAL;`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Create operation_history table for quarterly snapshots
  db.exec(`
    CREATE TABLE IF NOT EXISTS operation_history (
      id TEXT PRIMARY KEY,
      operation_id TEXT NOT NULL REFERENCES operations(id) ON DELETE CASCADE,
      plan_effort_per_day REAL,
      fact_effort REAL,
      target_effort REAL,
      is_manual INTEGER NOT NULL CHECK(is_manual IN (0, 1)),
      plan_type TEXT NOT NULL DEFAULT 'fact' CHECK(plan_type IN ('plan', 'fact')),
      comment TEXT,
      base_value REAL,
      base_unit TEXT CHECK(base_unit IN ('pieces', 'hours')),
      user_login TEXT,
      recorded_at TEXT DEFAULT (datetime('now'))
    );
  `);

  // Create process_base_history table
  db.exec(`
    CREATE TABLE IF NOT EXISTS process_base_history (
      id TEXT PRIMARY KEY,
      process_id TEXT NOT NULL REFERENCES processes(id) ON DELETE CASCADE,
      base_value REAL NOT NULL CHECK(base_value >= 0),
      base_unit TEXT NOT NULL CHECK(base_unit IN ('pieces', 'hours')),
      user_login TEXT,
      recorded_at TEXT DEFAULT (datetime('now'))
    );
  `);

  // Rename history columns if they exist (for existing databases)
  try {
    db.exec(`ALTER TABLE operation_history RENAME COLUMN effort_hours_per_day TO fact_effort;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }
  try {
    db.exec(`ALTER TABLE operation_history RENAME COLUMN effort_per_day TO fact_effort;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }
  try {
    db.exec(`ALTER TABLE operation_history RENAME COLUMN fact_effort_per_day TO fact_effort;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }
  try {
    db.exec(`ALTER TABLE operation_history RENAME COLUMN target_effort_hours_per_day TO target_effort;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }
  try {
    db.exec(`ALTER TABLE operation_history RENAME COLUMN target_effort_per_day TO target_effort;`);
  } catch (error) {
    // Column already renamed or missing, ignore
  }
  
  // Add plan_effort_per_day column if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE operation_history ADD COLUMN plan_effort_per_day REAL;`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Add fact_effort column if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE operation_history ADD COLUMN fact_effort REAL;`);
  } catch (error) {
    // Column already exists, ignore
  }
  // Add target_effort column if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE operation_history ADD COLUMN target_effort REAL;`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Add plan_type column if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE operation_history ADD COLUMN plan_type TEXT NOT NULL DEFAULT 'fact';`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Add comment column if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE operation_history ADD COLUMN comment TEXT;`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Add base_value column if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE operation_history ADD COLUMN base_value REAL;`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Add base_unit column if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE operation_history ADD COLUMN base_unit TEXT CHECK(base_unit IN ('pieces', 'hours'));`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Add user_login column if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE operation_history ADD COLUMN user_login TEXT;`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Add user_login column to process_base_history if it doesn't exist (for existing databases)
  try {
    db.exec(`ALTER TABLE process_base_history ADD COLUMN user_login TEXT;`);
  } catch (error) {
    // Column already exists, ignore
  }

  // Create indexes for better query performance
  db.exec(`
    CREATE INDEX IF NOT EXISTS idx_processes_group_id ON processes(group_id);
    CREATE INDEX IF NOT EXISTS idx_processes_status ON processes(status);
    CREATE INDEX IF NOT EXISTS idx_operations_process_id ON operations(process_id);
    CREATE INDEX IF NOT EXISTS idx_operations_process_bpmn ON operations(process_id, bpmn_element_id);
    CREATE INDEX IF NOT EXISTS idx_operations_name ON operations(name);
    CREATE INDEX IF NOT EXISTS idx_operations_effort_null ON operations(process_id, fact_effort) WHERE fact_effort IS NULL;
    CREATE INDEX IF NOT EXISTS idx_operation_history_operation_id ON operation_history(operation_id);
    CREATE INDEX IF NOT EXISTS idx_operation_history_recorded_at ON operation_history(recorded_at);
    CREATE INDEX IF NOT EXISTS idx_operation_history_operation_date ON operation_history(operation_id, recorded_at DESC);
    CREATE INDEX IF NOT EXISTS idx_process_base_history_process_id ON process_base_history(process_id);
    CREATE INDEX IF NOT EXISTS idx_process_base_history_recorded_at ON process_base_history(recorded_at);
    CREATE INDEX IF NOT EXISTS idx_process_base_history_process_date ON process_base_history(process_id, recorded_at DESC);
  `);

  console.log('Database schema initialized successfully');
}

/**
 * Drop all tables (for testing purposes)
 */
export function dropSchema(): void {
  db.exec('DROP TABLE IF EXISTS process_base_history');
  db.exec('DROP TABLE IF EXISTS operation_history');
  db.exec('DROP TABLE IF EXISTS operations');
  db.exec('DROP TABLE IF EXISTS processes');
  db.exec('DROP TABLE IF EXISTS process_groups');
  console.log('Database schema dropped');
}

// Run initialization if this file is executed directly
if (require.main === module) {
  initializeSchema();
}