# Migration Strategy and Recommendations

## Overview

This document provides a comprehensive migration strategy for transforming the current chocolate tempering machine control system from its existing monolithic Avalonia/.NET architecture to a modern, maintainable, and scalable solution.

## Current System Analysis

### Architectural Debt Assessment

```
Technical Debt Analysis:
┌─────────────────────────────────────────────────────────────┐
│                   DEBT CATEGORIES                          │
│                                                             │
│ ┌─────────────────┐ ┌─────────────────┐ ┌─────────────────┐ │
│ │   CRITICAL      │ │      HIGH       │ │     MEDIUM      │ │
│ │    ISSUES       │ │     ISSUES      │ │     ISSUES      │ │
│ │                 │ │                 │ │                 │ │
│ │ • Thread safety │ │ • Code          │ │ • CSV storage   │ │
│ │ • Memory leaks  │ │   duplication   │ │ • Hard-coded    │ │
│ │ • Error         │ │ • Monolithic    │ │   values        │ │
│ │   handling      │ │   design        │ │ • Limited       │ │
│ │ • Resource      │ │ • Testing       │ │   logging       │ │
│ │   management    │ │   absence       │ │ • Performance   │ │
│ │                 │ │ • Poor          │ │   bottlenecks   │ │
│ │ Impact: System  │ │   separation    │ │                 │ │
│ │ instability     │ │   of concerns   │ │ Impact:         │ │
│ │                 │ │                 │ │ Maintenance     │ │
│ │ Risk: HIGH      │ │ Risk: HIGH      │ │ complexity      │ │
│ └─────────────────┘ └─────────────────┘ └─────────────────┘ │
└─────────────────────────────────────────────────────────────┘
```

### Code Quality Metrics

| Metric | Current State | Target State | Priority |
|--------|---------------|--------------|----------|
| Cyclomatic Complexity | >50 (MainWindow) | <10 per method | Critical |
| Code Coverage | 0% | >80% | High |
| Thread Safety | None | Complete | Critical |
| Error Handling | Inconsistent | Comprehensive | Critical |
| Documentation | Minimal | Complete | Medium |
| Performance | Poor (polling) | Event-driven | High |
| Maintainability | Very Low | High | High |

## Recommended Target Architecture

### Modern Architecture Overview

```
Target System Architecture:
┌─────────────────────────────────────────────────────────────┐
│                     PRESENTATION TIER                      │
│  ┌─────────────────────────────────────────────────────────┐│
│  │           WEB-BASED USER INTERFACE                     ││
│  │                                                         ││
│  │ ┌─────────────┐ ┌─────────────┐ ┌─────────────────────┐││
│  │ │   React     │ │   Vue.js    │ │      Blazor         │││
│  │ │   Frontend  │ │   Frontend  │ │      WebAssembly    │││
│  │ │             │ │             │ │                     │││
│  │ │ • Touch UI  │ │ • Touch UI  │ │ • Touch UI          │││
│  │ │ • Real-time │ │ • Real-time │ │ • Real-time         │││
│  │ │ • Responsive│ │ • Responsive│ │ • Responsive        │││
│  │ └─────────────┘ └─────────────┘ └─────────────────────┘││
│  └─────────────────────────────────────────────────────────┘│
│                            │ WebSocket/SignalR                │
├────────────────────────────┼────────────────────────────────┤
│                     APPLICATION TIER                       │
│  ┌─────────────────────────────────────────────────────────┐│
│  │                   MICROSERVICES                        ││
│  │                                                         ││
│  │ ┌─────────────┐ ┌─────────────┐ ┌─────────────────────┐││
│  │ │   Recipe    │ │ Temperature │ │     Hardware        │││
│  │ │  Service    │ │  Service    │ │     Service         │││
│  │ │             │ │             │ │                     │││
│  │ │ • Recipe    │ │ • PID       │ │ • Modbus            │││
│  │ │   execution │ │   control   │ │   communication     │││
│  │ │ • Phase     │ │ • Monitoring│ │ • I/O control       │││
│  │ │   management│ │ • Safety    │ │ • Motor management  │││
│  │ └─────────────┘ └─────────────┘ └─────────────────────┘││
│  │                                                         ││
│  │ ┌─────────────┐ ┌─────────────┐ ┌─────────────────────┐││
│  │ │   Safety    │ │  Gateway    │ │      Logging        │││
│  │ │  Service    │ │  Service    │ │      Service        │││
│  │ │             │ │             │ │                     │││
│  │ │ • Error     │ │ • API       │ │ • System logs       │││
│  │ │   monitoring│ │   routing   │ │ • Audit trails      │││
│  │ │ • Interlocks│ │ • Auth      │ │ • Performance       │││
│  │ │ • Recovery  │ │ • Rate      │ │ • Analytics         │││
│  │ │             │ │   limiting  │ │                     │││
│  │ └─────────────┘ └─────────────┘ └─────────────────────┘││
│  └─────────────────────────────────────────────────────────┘│
│                            │ Message Bus                     │
├────────────────────────────┼────────────────────────────────┤
│                       DATA TIER                            │
│  ┌─────────────────────────────────────────────────────────┐│
│  │ ┌─────────────┐ ┌─────────────┐ ┌─────────────────────┐││
│  │ │ PostgreSQL  │ │    Redis    │ │      InfluxDB       │││
│  │ │  Database   │ │    Cache    │ │    Time Series      │││
│  │ │             │ │             │ │                     │││
│  │ │ • Config    │ │ • Session   │ │ • Temperature       │││
│  │ │ • Recipes   │ │ • State     │ │ • Performance       │││
│  │ │ • Users     │ │ • Temp data │ │ • Historical data   │││
│  │ │ • Audit     │ │             │ │                     │││
│  │ └─────────────┘ └─────────────┘ └─────────────────────┘││
│  └─────────────────────────────────────────────────────────┘│
└─────────────────────────────────────────────────────────────┘
```

### Technology Stack Recommendations

#### Option 1: .NET Microservices

```
.NET Stack Architecture:
┌─────────────────────────────────────────────────────────────┐
│                    TECHNOLOGY STACK                        │
│                                                             │
│ Frontend:                                                   │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Blazor Server/WebAssembly                             │ │
│ │ • SignalR for real-time updates                         │ │
│ │ │ Progressive Web App (PWA) capabilities               │ │
│ │ • Responsive design for touch interface                 │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Backend Services:                                           │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • ASP.NET Core 8.0 Web APIs                            │ │
│ │ • MediatR for CQRS pattern                             │ │
│ │ • FluentValidation for input validation                │ │
│ │ • Serilog for structured logging                       │ │
│ │ • AutoMapper for object mapping                        │ │
│ │ • Quartz.NET for scheduled tasks                       │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Communication:                                              │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • RabbitMQ/Azure Service Bus for messaging             │ │
│ │ • gRPC for inter-service communication                 │ │
│ │ • HTTP/REST for external APIs                          │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Data Layer:                                                 │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Entity Framework Core                                 │ │
│ │ • PostgreSQL for relational data                       │ │
│ │ • Redis for caching and session state                  │ │
│ │ • InfluxDB for time-series data                        │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Deployment:                                                 │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Docker containers                                     │ │
│ │ • Docker Compose for local development                 │ │
│ │ • Kubernetes for production (optional)                 │ │
│ └─────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
```

#### Option 2: Node.js/Python Stack

```
Alternative Stack Architecture:
┌─────────────────────────────────────────────────────────────┐
│                    TECHNOLOGY STACK                        │
│                                                             │
│ Frontend:                                                   │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • React/Vue.js with TypeScript                          │ │
│ │ • Socket.IO for real-time communication                │ │
│ │ • PWA capabilities for offline operation               │ │
│ │ • Material-UI/Ant Design for components                │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Backend Services:                                           │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ Control Services (Node.js):                            │ │
│ │ • Express.js/Fastify for APIs                          │ │
│ │ • TypeScript for type safety                           │ │
│ │ • Bull/Agenda for job queues                           │ │
│ │                                                         │ │
│ │ Hardware Interface (Python):                           │ │
│ │ • FastAPI for high-performance APIs                    │ │
│ │ • pyModbus for Modbus communication                    │ │
│ │ • AsyncIO for concurrent operations                    │ │
│ │ • Pydantic for data validation                         │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Communication:                                              │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Redis for pub/sub messaging                          │ │
│ │ • RabbitMQ for reliable message queuing                │ │
│ │ • GraphQL for flexible API queries                     │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Data Layer:                                                 │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • PostgreSQL with Prisma ORM (Node.js)                 │ │
│ │ • SQLAlchemy ORM (Python)                              │ │
│ │ • Redis for caching                                    │ │
│ │ • InfluxDB for time-series data                        │ │
│ └─────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
```

## Migration Strategy

### Phase-Based Migration Approach

```
Migration Timeline (12-18 months):
┌─────────────────────────────────────────────────────────────┐
│                   MIGRATION PHASES                         │
│                                                             │
│ Phase 1: Foundation (Months 1-3)                          │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Set up development environment                        │ │
│ │ • Create data models and database schema               │ │
│ │ • Implement basic CRUD operations                      │ │
│ │ • Develop hardware communication service               │ │
│ │ • Create basic API gateway                             │ │
│ │ • Set up CI/CD pipeline                                │ │
│ │                                                         │ │
│ │ Deliverables:                                           │ │
│ │ ✓ Database schema                                       │ │
│ │ ✓ Hardware service (Modbus communication)              │ │
│ │ ✓ Basic API endpoints                                   │ │
│ │ ✓ Development environment                               │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Phase 2: Core Services (Months 4-6)                       │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Implement recipe management service                   │ │
│ │ • Develop temperature control service                   │ │
│ │ • Create safety monitoring service                      │ │
│ │ • Implement error handling system                       │ │
│ │ • Add real-time data streaming                          │ │
│ │ • Create basic web interface                            │ │
│ │                                                         │ │
│ │ Deliverables:                                           │ │
│ │ ✓ Recipe execution engine                               │ │
│ │ ✓ Temperature control system                            │ │
│ │ ✓ Safety monitoring                                     │ │
│ │ ✓ Basic web UI                                          │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Phase 3: User Interface (Months 7-9)                      │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Develop complete web-based UI                         │ │
│ │ • Implement touch-friendly interface                    │ │
│ │ • Add real-time monitoring dashboards                   │ │
│ │ • Create user management system                         │ │
│ │ • Implement role-based access control                   │ │
│ │ • Add mobile responsiveness                             │ │
│ │                                                         │ │
│ │ Deliverables:                                           │ │
│ │ ✓ Complete web interface                                │ │
│ │ ✓ User authentication/authorization                     │ │
│ │ ✓ Mobile-responsive design                              │ │
│ │ ✓ Real-time dashboards                                  │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Phase 4: Integration & Testing (Months 10-12)             │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Comprehensive system integration                      │ │
│ │ • Performance optimization                              │ │
│ │ • Security hardening                                    │ │
│ │ • Load testing and reliability testing                  │ │
│ │ • Documentation and training materials                  │ │
│ │ • Parallel running with existing system                 │ │
│ │                                                         │ │
│ │ Deliverables:                                           │ │
│ │ ✓ Production-ready system                               │ │
│ │ ✓ Performance benchmarks met                            │ │
│ │ ✓ Security audit passed                                 │ │
│ │ ✓ Documentation complete                                │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Phase 5: Deployment & Transition (Months 13-15)          │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Production deployment                                 │ │
│ │ • Data migration from CSV files                        │ │
│ │ • User training and change management                   │ │
│ │ • Gradual system transition                             │ │
│ │ • Monitoring and support setup                         │ │
│ │ • Old system decommissioning                           │ │
│ │                                                         │ │
│ │ Deliverables:                                           │ │
│ │ ✓ Live production system                                │ │
│ │ ✓ Data successfully migrated                            │ │
│ │ ✓ Users trained                                         │ │
│ │ ✓ Old system retired                                    │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Phase 6: Optimization & Enhancement (Months 16-18)        │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Performance tuning based on production data          │ │
│ │ • Feature enhancements based on user feedback          │ │
│ │ • Advanced analytics and reporting                      │ │
│ │ • Predictive maintenance features                       │ │
│ │ • System scalability improvements                       │ │
│ │                                                         │ │
│ │ Deliverables:                                           │ │
│ │ ✓ Optimized performance                                 │ │
│ │ ✓ Enhanced features                                     │ │
│ │ ✓ Analytics platform                                    │ │
│ │ ✓ Scalability plan                                      │ │
│ └─────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
```

### Risk Mitigation Strategy

```
Risk Management Approach:
┌─────────────────────────────────────────────────────────────┐
│                    RISK ASSESSMENT                         │
│                                                             │
│ High-Risk Areas:                                            │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ 1. Hardware Communication                               │ │
│ │    Risk: Modbus communication failure                  │ │
│ │    Mitigation:                                          │ │
│ │    • Parallel implementation during development        │ │
│ │    • Comprehensive testing with hardware simulator     │ │
│ │    • Gradual migration with fallback options          │ │
│ │                                                         │ │
│ │ 2. Real-time Performance                                │ │
│ │    Risk: System not meeting real-time requirements     │ │
│ │    Mitigation:                                          │ │
│ │    • Performance benchmarking early in development     │ │
│ │    • Load testing with production-like scenarios       │ │
│ │    • Optimize critical paths first                     │ │
│ │                                                         │ │
│ │ 3. Data Migration                                       │ │
│ │    Risk: Loss of existing configuration/recipe data    │ │
│ │    Mitigation:                                          │ │
│ │    • Automated migration tools with validation         │ │
│ │    • Multiple backup strategies                        │ │
│ │    • Parallel data validation processes                │ │
│ │                                                         │ │
│ │ 4. User Adoption                                        │ │
│ │    Risk: Resistance to new interface                   │ │
│ │    Mitigation:                                          │ │
│ │    • Early user involvement in design process          │ │
│ │    • Comprehensive training programs                   │ │
│ │    • Gradual interface transition options              │ │
│ └─────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
```

## Data Migration Strategy

### CSV to Database Migration

```
Data Migration Process:
┌─────────────────────────────────────────────────────────────┐
│                  MIGRATION WORKFLOW                        │
│                                                             │
│ ┌─────────────────┐    ┌─────────────────┐                 │
│ │ CSV Analysis    │───▶│ Schema Mapping  │                 │
│ │                 │    │                 │                 │
│ │ • Parse existing│    │ • Recipe.csv    │                 │
│ │   CSV files     │    │   ─▶ Recipes    │                 │
│ │ • Validate data │    │ • Machine.csv   │                 │
│ │   integrity     │    │   ─▶ Machine    │                 │
│ │ • Identify      │    │   Configuration │                 │
│ │   inconsistencies│    │ • Mapping.csv   │                │
│ │ • Clean data    │    │   ─▶ Hardware   │                 │
│ │                 │    │   Mappings      │                 │
│ └─────────────────┘    └─────────────────┘                 │
│                                │                            │
│                                ▼                            │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │               TRANSFORMATION RULES                      │ │
│ │                                                         │ │
│ │ Recipe Migration:                                       │ │
│ │ • ID preservation for referential integrity            │ │
│ │ • Boolean conversion (0/1 → true/false)                │ │
│ │ • Temperature validation and unit conversion           │ │
│ │ • Default value assignment for missing fields          │ │
│ │                                                         │ │
│ │ Machine Configuration:                                  │ │
│ │ • Single record validation (ID=1 requirement)          │ │
│ │ • Temperature limit validation                          │ │
│ │ • Timer value validation and range checking            │ │
│ │                                                         │ │
│ │ Hardware Mapping:                                       │ │
│ │ • Bit number parsing (pipe-separated → array)          │ │
│ │ • Address validation and format checking               │ │
│ │ • Modbus function code assignment                      │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                │                            │
│                                ▼                            │
│ ┌─────────────────┐    ┌─────────────────┐                 │
│ │ Database        │◄───│ Validation &    │                 │
│ │ Population      │    │ Verification    │                 │
│ │                 │    │                 │                 │
│ │ • Insert        │    │ • Cross-         │                 │
│ │   validated     │    │   reference     │                 │
│ │   data          │    │   validation    │                 │
│ │ • Create        │    │ • Constraint    │                 │
│ │   indexes       │    │   checking      │                 │
│ │ • Set up        │    │ • Data          │                 │
│ │   relationships │    │   completeness  │                 │
│ └─────────────────┘    └─────────────────┘                 │
└─────────────────────────────────────────────────────────────┘
```

### Database Schema Design

```sql
-- Modern Database Schema Design
-- Replaces CSV-based storage with proper relational model

-- Recipes table (replaces Recipe.csv)
CREATE TABLE recipes (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    created_date TIMESTAMP DEFAULT NOW(),
    modified_date TIMESTAMP DEFAULT NOW(),
    is_active BOOLEAN DEFAULT true,
    
    -- Temperature goals
    heating_goal DECIMAL(4,1) CHECK (heating_goal BETWEEN 40 AND 60),
    cooling_goal DECIMAL(4,1) CHECK (cooling_goal BETWEEN 20 AND 40),
    pouring_goal DECIMAL(4,1) CHECK (pouring_goal BETWEEN 20 AND 60),
    
    -- Motor settings
    mixer_enabled BOOLEAN DEFAULT false,
    fountain_enabled BOOLEAN DEFAULT false,
    mold_heater_enabled BOOLEAN DEFAULT false,
    vibration_enabled BOOLEAN DEFAULT false,
    vib_heater_enabled BOOLEAN DEFAULT false,
    
    -- Pedal configuration
    pedal_mode VARCHAR(10) CHECK (pedal_mode IN ('manual', 'auto')),
    pedal_on_time INTEGER CHECK (pedal_on_time > 0),
    pedal_off_time INTEGER CHECK (pedal_off_time > 0),
    
    -- Constraints
    CONSTRAINT valid_temperature_range 
        CHECK (cooling_goal < heating_goal),
    CONSTRAINT valid_pouring_range 
        CHECK (pouring_goal >= cooling_goal AND pouring_goal <= heating_goal)
);

-- Machine configuration (replaces Machine.csv)
CREATE TABLE machine_config (
    id INTEGER PRIMARY KEY DEFAULT 1,
    
    -- Temperature limits
    tank_max_heat DECIMAL(4,1) NOT NULL DEFAULT 50,
    pump_max_heat DECIMAL(4,1) NOT NULL DEFAULT 50,
    pump_min_heat DECIMAL(4,1) NOT NULL DEFAULT -10,
    abs_max_heat DECIMAL(4,1) NOT NULL DEFAULT 65,
    abs_min_heat DECIMAL(4,1) NOT NULL DEFAULT -14,
    pre_heating_temp DECIMAL(4,1) NOT NULL DEFAULT 5,
    
    -- Timing parameters
    pump_delay INTEGER NOT NULL DEFAULT 60,
    mixer_delay INTEGER NOT NULL DEFAULT 60,
    heating_delay INTEGER NOT NULL DEFAULT 60,
    cooling_delay INTEGER NOT NULL DEFAULT 60,
    pouring_delay INTEGER NOT NULL DEFAULT 60,
    
    -- Temperature setpoint offsets
    set_temp1 DECIMAL(4,1) DEFAULT 0,
    set_temp2 DECIMAL(4,1) DEFAULT -10,
    set_temp3 DECIMAL(4,1) DEFAULT 5,
    set_temp4 DECIMAL(4,1) DEFAULT 0,
    
    modified_date TIMESTAMP DEFAULT NOW(),
    
    -- Ensure only one configuration record
    CONSTRAINT single_config CHECK (id = 1)
);

-- Hardware mapping (replaces Mapping.csv)
CREATE TABLE hardware_mappings (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    modbus_address INTEGER NOT NULL,
    is_read_operation BOOLEAN NOT NULL DEFAULT false,
    bit_numbers INTEGER[] DEFAULT '{}',
    function_code INTEGER,
    data_type VARCHAR(20) DEFAULT 'uint16',
    description TEXT,
    is_active BOOLEAN DEFAULT true
);

-- PID Configuration (replaces Configuration.csv)
CREATE TABLE pid_configs (
    id SERIAL PRIMARY KEY,
    zone_name VARCHAR(50) NOT NULL,
    zone_number INTEGER UNIQUE NOT NULL,
    
    -- Temperature limits
    max_temp DECIMAL(5,1) NOT NULL,
    min_temp DECIMAL(5,1) NOT NULL,
    
    -- PID parameters
    kp DECIMAL(8,4) NOT NULL DEFAULT 1.0,
    ki DECIMAL(8,4) NOT NULL DEFAULT 0.1,
    kd DECIMAL(8,4) NOT NULL DEFAULT 0.01,
    kl DECIMAL(8,4) NOT NULL DEFAULT 10.0,
    
    -- Control configuration
    fc_threshold DECIMAL(5,1) DEFAULT 50,
    heat_control_range DECIMAL(4,1) DEFAULT 2.0,
    
    -- Current limits
    i_neutral DECIMAL(4,1) DEFAULT 5.0,
    i_motor1 DECIMAL(4,1) DEFAULT 10.0,
    i_motor2 DECIMAL(4,1) DEFAULT 10.0,
    
    -- Output mappings (store as JSON)
    heating_outputs JSONB DEFAULT '[]',
    first_cooling_outputs JSONB DEFAULT '[]',
    second_cooling_outputs JSONB DEFAULT '[]',
    
    is_active BOOLEAN DEFAULT true
);

-- System settings (replaces Screen.csv)
CREATE TABLE system_settings (
    id INTEGER PRIMARY KEY DEFAULT 1,
    
    -- Communication settings
    serial_port VARCHAR(20) DEFAULT 'COM1',
    baud_rate INTEGER DEFAULT 9600,
    parity INTEGER DEFAULT 0, -- 0=None, 1=Odd, 2=Even
    stop_bits INTEGER DEFAULT 1,
    sending_interval INTEGER DEFAULT 100, -- milliseconds
    
    -- Error thresholds
    warning_limit DECIMAL(3,1) DEFAULT 1.5,
    error_limit DECIMAL(3,1) DEFAULT 3.0,
    
    -- Display settings
    brightness INTEGER DEFAULT 100,
    dim_timeout INTEGER DEFAULT 30, -- seconds
    screen_off_timeout INTEGER DEFAULT 60, -- seconds
    
    modified_date TIMESTAMP DEFAULT NOW(),
    
    CONSTRAINT single_settings CHECK (id = 1)
);

-- User management (replaces Users.csv)
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) NOT NULL DEFAULT 'operator',
    full_name VARCHAR(100),
    email VARCHAR(100),
    is_active BOOLEAN DEFAULT true,
    last_login TIMESTAMP,
    created_date TIMESTAMP DEFAULT NOW(),
    modified_date TIMESTAMP DEFAULT NOW(),
    
    CONSTRAINT valid_role 
        CHECK (role IN ('operator', 'technician', 'admin'))
);

-- Error settings (replaces ErrorSettings.csv)
CREATE TABLE error_settings (
    id INTEGER PRIMARY KEY DEFAULT 1,
    
    -- Grid monitoring
    grid_frequency DECIMAL(4,1) DEFAULT 50.0,
    phase_count INTEGER DEFAULT 3,
    voltage_high_limit DECIMAL(5,1) DEFAULT 242.0,
    voltage_low_limit DECIMAL(5,1) DEFAULT 198.0,
    current_high_limit DECIMAL(5,1) DEFAULT 20.0,
    
    -- Temperature monitoring
    temperature_high_limit DECIMAL(4,1) DEFAULT 70.0,
    temperature_low_limit DECIMAL(4,1) DEFAULT -20.0,
    
    modified_date TIMESTAMP DEFAULT NOW(),
    
    CONSTRAINT single_error_settings CHECK (id = 1)
);

-- System logs (new - not in original CSV system)
CREATE TABLE system_logs (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMP DEFAULT NOW(),
    level VARCHAR(10) NOT NULL, -- DEBUG, INFO, WARN, ERROR, FATAL
    category VARCHAR(50), -- HARDWARE, RECIPE, SAFETY, USER, etc.
    message TEXT NOT NULL,
    details JSONB,
    user_id INTEGER REFERENCES users(id),
    recipe_id INTEGER REFERENCES recipes(id)
);

-- Recipe execution history (new - enhanced tracking)
CREATE TABLE recipe_executions (
    id BIGSERIAL PRIMARY KEY,
    recipe_id INTEGER NOT NULL REFERENCES recipes(id),
    user_id INTEGER REFERENCES users(id),
    started_at TIMESTAMP DEFAULT NOW(),
    completed_at TIMESTAMP,
    status VARCHAR(20) DEFAULT 'running', -- running, completed, aborted, error
    
    -- Phase tracking
    heating_duration INTEGER, -- seconds
    cooling_duration INTEGER,
    pouring_duration INTEGER,
    
    -- Temperature data (summary)
    max_heating_temp DECIMAL(4,1),
    min_cooling_temp DECIMAL(4,1),
    avg_pouring_temp DECIMAL(4,1),
    
    -- Quality metrics
    temperature_variance DECIMAL(4,2),
    cycle_efficiency DECIMAL(5,2),
    
    notes TEXT,
    error_count INTEGER DEFAULT 0
);

-- Temperature data (new - time series for analytics)
CREATE TABLE temperature_readings (
    timestamp TIMESTAMP DEFAULT NOW(),
    zone INTEGER NOT NULL, -- 1=Tank Bottom, 2=Tank Wall, 3=Pump, 4=Fountain
    temperature DECIMAL(5,2) NOT NULL,
    setpoint DECIMAL(5,2),
    recipe_execution_id BIGINT REFERENCES recipe_executions(id),
    
    PRIMARY KEY (timestamp, zone)
);

-- Indexes for performance
CREATE INDEX idx_recipes_active ON recipes(is_active);
CREATE INDEX idx_recipe_executions_recipe ON recipe_executions(recipe_id);
CREATE INDEX idx_recipe_executions_user ON recipe_executions(user_id);
CREATE INDEX idx_recipe_executions_status ON recipe_executions(status);
CREATE INDEX idx_temperature_readings_time ON temperature_readings(timestamp);
CREATE INDEX idx_temperature_readings_zone ON temperature_readings(zone);
CREATE INDEX idx_system_logs_timestamp ON system_logs(timestamp);
CREATE INDEX idx_system_logs_level ON system_logs(level);
CREATE INDEX idx_system_logs_category ON system_logs(category);
```

## Implementation Recommendations

### Development Best Practices

```
Development Standards:
┌─────────────────────────────────────────────────────────────┐
│                    CODE QUALITY                            │
│                                                             │
│ 1. Architecture Patterns:                                  │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Domain-Driven Design (DDD)                           │ │
│ │ • Command Query Responsibility Segregation (CQRS)      │ │
│ │ • Event Sourcing for audit trails                      │ │
│ │ • Repository pattern for data access                   │ │
│ │ • Unit of Work pattern for transactions                │ │
│ │ • Factory pattern for hardware communication           │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ 2. Testing Strategy:                                        │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Unit Tests: >90% coverage for business logic         │ │
│ │ • Integration Tests: Database and external services    │ │
│ │ • Hardware Simulation: Mock Modbus devices             │ │
│ │ • End-to-End Tests: Complete user workflows            │ │
│ │ • Performance Tests: Load and stress testing           │ │
│ │ • Security Tests: Penetration and vulnerability        │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ 3. Error Handling:                                          │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Result pattern for operation outcomes                 │ │
│ │ • Global exception handling middleware                  │ │
│ │ • Structured logging with correlation IDs              │ │
│ │ • Circuit breaker pattern for external dependencies    │ │
│ │ • Retry policies with exponential backoff              │ │
│ │ • Health checks for all services                       │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ 4. Security Measures:                                       │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • JWT tokens for authentication                         │ │
│ │ • Role-based authorization (RBAC)                      │ │
│ │ • Input validation and sanitization                    │ │
│ │ • SQL injection prevention                             │ │
│ │ • HTTPS/TLS encryption for all communications          │ │
│ │ • Audit logging for all user actions                   │ │
│ └─────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
```

### Performance Optimization

```
Performance Requirements:
┌─────────────────────────────────────────────────────────────┐
│                  PERFORMANCE TARGETS                       │
│                                                             │
│ Response Time Requirements:                                 │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Temperature readings: < 100ms                        │ │
│ │ • Motor control commands: < 50ms                       │ │
│ │ • Recipe phase transitions: < 200ms                    │ │
│ │ • Error detection response: < 100ms                    │ │
│ │ • UI interactions: < 50ms                              │ │
│ │ • Database queries: < 100ms (95th percentile)          │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Throughput Requirements:                                    │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Modbus messages: 10-20 messages/second              │ │
│ │ • Temperature samples: 1 sample/second per zone       │ │
│ │ • UI updates: 30 fps for smooth animations            │ │
│ │ • Log entries: 1000 entries/minute sustainable        │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Resource Constraints:                                       │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • RAM usage: < 512MB total system                      │ │
│ │ • CPU usage: < 50% average, < 80% peak                │ │
│ │ • Storage: < 1GB for application and data             │ │
│ │ • Network: < 1Mbps bandwidth usage                    │ │
│ └─────────────────────────────────────────────────────────┘ │
│                                                             │
│ Optimization Strategies:                                    │
│ ┌─────────────────────────────────────────────────────────┐ │
│ │ • Connection pooling for database access               │ │
│ │ • Redis caching for frequently accessed data           │ │
│ │ • Message queuing for asynchronous operations          │ │
│ │ • Efficient serialization (JSON vs MessagePack)       │ │
│ │ • Database indexing and query optimization             │ │
│ │ • Memory pooling for object reuse                      │ │
│ └─────────────────────────────────────────────────────────┘ │
└─────────────────────────────────────────────────────────────┘
```

This migration strategy provides a comprehensive roadmap for transforming the chocolate tempering machine control system from its current problematic state to a modern, maintainable, and scalable solution. The phased approach minimizes risk while ensuring business continuity throughout the transition.
