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"; }