server/db.ts

import { drizzle } from "drizzle-orm/postgres-js";
import postgres from "postgres";
import * as schema from "../shared/schema";

const connectionString = process.env.DATABASE_URL!;

// Create the connection
const client = postgres(connectionString);
export const db = drizzle(client, { schema });

// Initialize database and create tables
export async function initializeDatabase() {
  try {
    // Create tables if they don't exist
    await client`
      CREATE TABLE IF NOT EXISTS users (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL,
        email TEXT NOT NULL UNIQUE,
        password TEXT NOT NULL,
        role TEXT NOT NULL DEFAULT 'viewer',
        department TEXT,
        position TEXT,
        phone TEXT,
        avatar TEXT,
        is_active BOOLEAN NOT NULL DEFAULT true,
        created_at TIMESTAMP NOT NULL DEFAULT NOW()
      )
    `;

    await client`
      CREATE TABLE IF NOT EXISTS equipment (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        type TEXT NOT NULL,
        description TEXT,
        status TEXT NOT NULL DEFAULT 'active',
        last_maintenance TEXT NOT NULL,
        next_maintenance TEXT NOT NULL,
        responsible TEXT NOT NULL,
        maintenance_periods TEXT[] NOT NULL DEFAULT '{}',
        department TEXT NOT NULL
      )
    `;

    await client`
      CREATE TABLE IF NOT EXISTS maintenance_records (
        id SERIAL PRIMARY KEY,
        equipment_name TEXT NOT NULL,
        type TEXT NOT NULL,
        date TEXT NOT NULL,
        duration TEXT NOT NULL,
        responsible TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'scheduled',
        priority TEXT NOT NULL DEFAULT 'medium'
      )
    `;

    await client`
      CREATE TABLE IF NOT EXISTS remarks (
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT NOT NULL,
        equipment_name TEXT NOT NULL,
        equipment_id TEXT NOT NULL,
        type TEXT NOT NULL,
        priority TEXT NOT NULL DEFAULT 'medium',
        status TEXT NOT NULL DEFAULT 'open',
        reported_by TEXT NOT NULL,
        assigned_to TEXT NOT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
        resolved_at TIMESTAMP,
        notes TEXT[] NOT NULL DEFAULT '{}'
      )
    `;

    await client`
      CREATE TABLE IF NOT EXISTS roles (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL UNIQUE,
        description TEXT NOT NULL,
        permissions TEXT[] NOT NULL
      )
    `;

    await client`
      CREATE TABLE IF NOT EXISTS campaigns (
        id SERIAL PRIMARY KEY,
        name TEXT NOT NULL,
        progress INTEGER NOT NULL DEFAULT 0,
        start_date TIMESTAMP NOT NULL,
        end_date TIMESTAMP,
        status TEXT NOT NULL DEFAULT 'active'
      )
    `;

    await client`
      CREATE TABLE IF NOT EXISTS tasks (
        id SERIAL PRIMARY KEY,
        title TEXT NOT NULL,
        description TEXT,
        user_id INTEGER NOT NULL,
        campaign_id INTEGER,
        status TEXT NOT NULL DEFAULT 'pending',
        due_date TIMESTAMP,
        completed_at TIMESTAMP
      )
    `;

    await client`
      CREATE TABLE IF NOT EXISTS metrics (
        id SERIAL PRIMARY KEY,
        user_id INTEGER NOT NULL,
        tasks_completed INTEGER NOT NULL DEFAULT 0,
        tasks_total INTEGER NOT NULL DEFAULT 0,
        on_time_rate INTEGER NOT NULL DEFAULT 0,
        productivity_score INTEGER NOT NULL DEFAULT 0
      )
    `;

    await client`
      CREATE TABLE IF NOT EXISTS activities (
        id SERIAL PRIMARY KEY,
        user_id INTEGER NOT NULL,
        action TEXT NOT NULL,
        timestamp TIMESTAMP NOT NULL DEFAULT NOW(),
        resource_type TEXT,
        resource_id INTEGER
      )
    `;

    await client`
      CREATE TABLE IF NOT EXISTS inspection_checklists (
        id SERIAL PRIMARY KEY,
        equipment_type TEXT NOT NULL,
        check_items TEXT[] NOT NULL,
        created_by TEXT NOT NULL,
        created_at TIMESTAMP NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP NOT NULL DEFAULT NOW()
      )
    `;

    await client`
      CREATE TABLE IF NOT EXISTS daily_inspections (
        id SERIAL PRIMARY KEY,
        equipment_id TEXT NOT NULL,
        equipment_name TEXT NOT NULL,
        inspection_date TIMESTAMP NOT NULL,
        check_results TEXT[] NOT NULL,
        comments TEXT[] NOT NULL DEFAULT '{}',
        inspected_by TEXT NOT NULL,
        status TEXT NOT NULL DEFAULT 'completed',
        created_at TIMESTAMP NOT NULL DEFAULT NOW(),
        updated_at TIMESTAMP NOT NULL DEFAULT NOW()
      )
    `;

    console.log("Database initialized successfully");
    
    // Seed initial data
    await seedInitialData();
  } catch (error) {
    console.error("Database initialization error:", error);
    throw error;
  }
}

// Seed initial data for the system
async function seedInitialData() {
  try {
    // Check if data already exists
    const existingUsers = await client`SELECT COUNT(*) FROM users`;
    const userCount = parseInt(existingUsers[0].count);
    
    if (userCount > 0) {
      console.log("Database already contains data, skipping seed");
      return;
    }

    // Create default users
    const bcrypt = await import('bcryptjs');
    
    const users = [
      {
        name: 'Купцов Денис',
        email: 'admin@starline.com',
        password: await bcrypt.hash('admin123', 10),
        role: 'admin',
        department: 'Техническая служба',
        position: 'Старший инженер'
      },
      {
        name: 'Иванов Сергей',
        email: 'ivanov@starline.com',
        password: await bcrypt.hash('user123', 10),
        role: 'engineer',
        department: 'Механический цех',
        position: 'Инженер-механик'
      },
      {
        name: 'Петрова Анна',
        email: 'petrova@starline.com',
        password: await bcrypt.hash('user123', 10),
        role: 'technician',
        department: 'Энергетический цех',
        position: 'Техник-электрик'
      },
      {
        name: 'Сидоров Михаил',
        email: 'sidorov@starline.com',
        password: await bcrypt.hash('user123', 10),
        role: 'operator',
        department: 'Сварочный участок',
        position: 'Оператор сварочного оборудования'
      }
    ];

    for (const user of users) {
      await client`
        INSERT INTO users (name, email, password, role, department, position)
        VALUES (${user.name}, ${user.email}, ${user.password}, ${user.role}, ${user.department}, ${user.position})
      `;
    }

    // Insert real equipment data
    const equipmentData = [
      {
        id: 'EQ001',
        name: 'Станок токарно-винторезный 16К20',
        type: 'Металлообрабатывающий станок',
        description: 'Токарно-винторезный станок для обработки деталей диаметром до 400мм',
        status: 'active',
        lastMaintenance: '2024-11-15',
        nextMaintenance: '2025-02-15',
        responsible: 'Купцов Денис',
        maintenancePeriods: ['1M-TO', '3M-TO', '6M-TO', '1G-TO'],
        department: 'Механический цех'
      },
      {
        id: 'EQ002',
        name: 'Фрезерный станок 6Р13',
        type: 'Металлообрабатывающий станок',
        description: 'Универсальный фрезерный станок для обработки заготовок',
        status: 'active',
        lastMaintenance: '2024-10-20',
        nextMaintenance: '2025-01-20',
        responsible: 'Купцов Денис',
        maintenancePeriods: ['1M-TO', '3M-TO', '6M-TO', '1G-TO'],
        department: 'Механический цех'
      },
      {
        id: 'EQ003',
        name: 'Компрессор винтовой АС-20',
        type: 'Компрессорное оборудование',
        description: 'Винтовой компрессор производительностью 20 м³/мин',
        status: 'active',
        lastMaintenance: '2024-12-01',
        nextMaintenance: '2025-03-01',
        responsible: 'Купцов Денис',
        maintenancePeriods: ['1M-TO', '3M-TO', '6M-TO', '1G-TO'],
        department: 'Энергетический цех'
      },
      {
        id: 'EQ004',
        name: 'Кран мостовой 5т',
        type: 'Подъемно-транспортное оборудование',
        description: 'Мостовой кран грузоподъемностью 5 тонн',
        status: 'active',
        lastMaintenance: '2024-11-10',
        nextMaintenance: '2025-02-10',
        responsible: 'Купцов Денис',
        maintenancePeriods: ['1M-TO', '3M-TO', '6M-TO', '1G-TO'],
        department: 'Механический цех'
      },
      {
        id: 'EQ005',
        name: 'Сварочный аппарат САК-40',
        type: 'Сварочное оборудование',
        description: 'Сварочный автомат для дуговой сварки',
        status: 'maintenance',
        lastMaintenance: '2024-11-25',
        nextMaintenance: '2025-02-25',
        responsible: 'Купцов Денис',
        maintenancePeriods: ['1M-TO', '3M-TO', '6M-TO', '1G-TO'],
        department: 'Сварочный участок'
      }
    ];

    for (const eq of equipmentData) {
      await client`
        INSERT INTO equipment (id, name, type, description, status, last_maintenance, next_maintenance, responsible, maintenance_periods, department)
        VALUES (${eq.id}, ${eq.name}, ${eq.type}, ${eq.description}, ${eq.status}, ${eq.lastMaintenance}, ${eq.nextMaintenance}, ${eq.responsible}, ${eq.maintenancePeriods}, ${eq.department})
      `;
    }

    // Insert maintenance records
    const maintenanceData = [
      {
        equipmentName: 'Станок токарно-винторезный 16К20',
        type: '1M-TO',
        date: '2025-01-15',
        duration: '2 часа',
        responsible: 'Купцов Денис',
        status: 'scheduled',
        priority: 'medium'
      },
      {
        equipmentName: 'Фрезерный станок 6Р13',
        type: '3M-TO',
        date: '2025-01-20',
        duration: '4 часа',
        responsible: 'Купцов Денис',
        status: 'scheduled',
        priority: 'medium'
      },
      {
        equipmentName: 'Компрессор винтовой АС-20',
        type: '1M-TO',
        date: '2025-01-01',
        duration: '1 час',
        responsible: 'Купцов Денис',
        status: 'completed',
        priority: 'high'
      },
      {
        equipmentName: 'Сварочный аппарат САК-40',
        type: '6M-TO',
        date: '2024-12-25',
        duration: '6 часов',
        responsible: 'Купцов Денис',
        status: 'in_progress',
        priority: 'high'
      }
    ];

    for (const maint of maintenanceData) {
      await client`
        INSERT INTO maintenance_records (equipment_name, type, date, duration, responsible, status, priority)
        VALUES (${maint.equipmentName}, ${maint.type}, ${maint.date}, ${maint.duration}, ${maint.responsible}, ${maint.status}, ${maint.priority})
      `;
    }

    // Insert sample remarks
    const remarksData = [
      {
        id: 'REM001',
        title: 'Повышенная вибрация',
        description: 'Обнаружена повышенная вибрация в главном шпинделе токарного станка',
        equipmentName: 'Станок токарно-винторезный 16К20',
        equipmentId: 'EQ001',
        type: 'inspection',
        priority: 'medium',
        status: 'open',
        reportedBy: 'Купцов Денис',
        assignedTo: 'Купцов Денис',
        notes: ['Требуется проверка подшипников', 'Планируется замена в следующем ТО']
      },
      {
        id: 'REM002',
        title: 'Утечка масла',
        description: 'Обнаружена небольшая утечка гидравлического масла',
        equipmentName: 'Фрезерный станок 6Р13',
        equipmentId: 'EQ002',
        type: 'maintenance',
        priority: 'low',
        status: 'in_progress',
        reportedBy: 'Купцов Денис',
        assignedTo: 'Купцов Денис',
        notes: ['Заказаны новые уплотнения']
      }
    ];

    for (const remark of remarksData) {
      await client`
        INSERT INTO remarks (id, title, description, equipment_name, equipment_id, type, priority, status, reported_by, assigned_to, notes)
        VALUES (${remark.id}, ${remark.title}, ${remark.description}, ${remark.equipmentName}, ${remark.equipmentId}, ${remark.type}, ${remark.priority}, ${remark.status}, ${remark.reportedBy}, ${remark.assignedTo}, ${remark.notes})
      `;
    }

    console.log("Initial data seeded successfully");
  } catch (error) {
    console.error("Error seeding initial data:", error);
  }
}