38815-vm/db/migrate_003_full_schema.php

<?php
require_once __DIR__ . '/config.php';
$db = db();

try {
    // Disable foreign key checks for dropping tables
    $db->exec("SET FOREIGN_KEY_CHECKS=0");

    // Drop existing tables if we want a clean slate
    $tables = ['time_study_events', 'inventory_transactions', 'inventory', 'operations', 'components', 'jobs', 'users', 'process_types', 'subtasks', 'reorder_alerts', 'machined_parts'];
    foreach ($tables as $table) {
        $db->exec("DROP TABLE IF EXISTS `$table`");
    }

    // 1. Users (Admins and Workers)
    $db->exec("CREATE TABLE users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        uuid VARCHAR(36) UNIQUE,
        name VARCHAR(255) NOT NULL,
        role ENUM('worker', 'admin') NOT NULL,
        pin_hash VARCHAR(255) DEFAULT NULL,
        assigned_processes JSON DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    // 2. Process Types (System defaults)
    $db->exec("CREATE TABLE process_types (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(100) NOT NULL UNIQUE,
        description TEXT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    // 3. Jobs (Projects/Orders)
    $db->exec("CREATE TABLE jobs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        customer VARCHAR(255) DEFAULT NULL,
        quantity INT DEFAULT 1,
        due_date DATE DEFAULT NULL,
        serial_number VARCHAR(100) UNIQUE,
        priority ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
        status ENUM('planned', 'in_progress', 'completed', 'archived', 'template') DEFAULT 'planned',
        description TEXT DEFAULT NULL,
        is_template BOOLEAN DEFAULT FALSE,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        completed_at DATETIME DEFAULT NULL
    )");

    // 4. Components (BOM Tree)
    $db->exec("CREATE TABLE components (
        id INT AUTO_INCREMENT PRIMARY KEY,
        job_id INT NOT NULL,
        parent_id INT DEFAULT NULL,
        name VARCHAR(255) NOT NULL,
        type VARCHAR(100) DEFAULT 'part',
        status ENUM('pending', 'in_progress', 'completed') DEFAULT 'pending',
        quantity INT DEFAULT 1,
        thickness VARCHAR(50) DEFAULT NULL,
        material VARCHAR(100) DEFAULT NULL,
        notes TEXT DEFAULT NULL,
        thumbnail_data LONGTEXT DEFAULT NULL,
        blueprint_url VARCHAR(255) DEFAULT NULL,
        order_index INT DEFAULT 0,
        priority_weight INT DEFAULT 50,
        assigned_to INT DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        completed_at DATETIME DEFAULT NULL,
        FOREIGN KEY (job_id) REFERENCES jobs(id) ON DELETE CASCADE,
        FOREIGN KEY (parent_id) REFERENCES components(id) ON DELETE CASCADE,
        FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL
    )");

    // 5. Operations (Tasks within a Component)
    $db->exec("CREATE TABLE operations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        component_id INT NOT NULL,
        name VARCHAR(255) NOT NULL,
        process_type VARCHAR(100) NOT NULL,
        status ENUM('pending', 'in_progress', 'stalled', 'completed') DEFAULT 'pending',
        order_index INT DEFAULT 0,
        estimated_minutes INT DEFAULT NULL,
        instructions TEXT DEFAULT NULL,
        assigned_worker_id INT DEFAULT NULL,
        quantity_made INT DEFAULT 0,
        fulfilled_from_inventory BOOLEAN DEFAULT FALSE,
        priority_tier ENUM('low', 'normal', 'high', 'urgent') DEFAULT 'normal',
        blocked_reason TEXT DEFAULT NULL,
        start_time DATETIME DEFAULT NULL,
        completed_at DATETIME DEFAULT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (component_id) REFERENCES components(id) ON DELETE CASCADE,
        FOREIGN KEY (assigned_worker_id) REFERENCES users(id) ON DELETE SET NULL
    )");

    // 6. Subtasks (Checklists for Operations/Components)
    $db->exec("CREATE TABLE subtasks (
        id INT AUTO_INCREMENT PRIMARY KEY,
        component_id INT NOT NULL,
        operation_id INT DEFAULT NULL,
        name VARCHAR(255) NOT NULL,
        status ENUM('pending', 'completed') DEFAULT 'pending',
        FOREIGN KEY (component_id) REFERENCES components(id) ON DELETE CASCADE,
        FOREIGN KEY (operation_id) REFERENCES operations(id) ON DELETE CASCADE
    )");

    // 7. Inventory (Materials & Consumables)
    $db->exec("CREATE TABLE inventory (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(255) NOT NULL,
        category ENUM('material', 'consumable', 'hardware', 'machined_part') NOT NULL,
        stock_level DECIMAL(10,2) DEFAULT 0,
        reorder_level DECIMAL(10,2) DEFAULT 0,
        unit VARCHAR(50) DEFAULT 'pcs',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
    )");

    // 8. Reorder Alerts
    $db->exec("CREATE TABLE reorder_alerts (
        id INT AUTO_INCREMENT PRIMARY KEY,
        inventory_id INT NOT NULL,
        status ENUM('active', 'ordered', 'dismissed', 'fulfilled') DEFAULT 'active',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (inventory_id) REFERENCES inventory(id) ON DELETE CASCADE
    )");

    // 9. Time Study Events (Analytics)
    $db->exec("CREATE TABLE time_study_events (
        id INT AUTO_INCREMENT PRIMARY KEY,
        operation_id INT NOT NULL,
        user_id INT NOT NULL,
        event_type ENUM('start', 'stalled', 'completed', 'resume') NOT NULL,
        reason TEXT DEFAULT NULL,
        timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        FOREIGN KEY (operation_id) REFERENCES operations(id) ON DELETE CASCADE,
        FOREIGN KEY (user_id) REFERENCES users(id)
    )");

    $db->exec("SET FOREIGN_KEY_CHECKS=1");

    // SEED ONLY SYSTEM DATA (No user-added records)
    
    // Default Admin User (Pin: 1234 hash)
    $defaultPinHash = password_hash('1234', PASSWORD_DEFAULT);
    $db->prepare("INSERT INTO users (name, role, pin_hash) VALUES (?, ?, ?)")
       ->execute(['System Admin', 'admin', $defaultPinHash]);

    // Default Process Types
    $processes = [
        'Cutting', 'Welding', 'Bending', 'Assembly', 'Painting', 'Quality Control', 'Packaging', 'Machining'
    ];
    $stmt = $db->prepare("INSERT INTO process_types (name) VALUES (?)");
    foreach ($processes as $p) {
        $stmt->execute([$p]);
    }

    echo "Full schema migrated successfully without user data. Admin PIN is 1234.\n";

} catch (Exception $e) {
    $db->rollBack();
    echo "Error: " . $e->getMessage() . "\n";
}