-- ============================================================================
-- SIMPLIFIED MIGRATION FOR BEEKEEPER STUDIO
-- Database: machine_data
-- Version: 2.0 - Beekeeper Compatible
-- ============================================================================

-- ----------------------------------------------------------------------------
-- Table 1: KPI Snapshots
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS kpi_snapshots (
  id INT AUTO_INCREMENT PRIMARY KEY,
  timestamp BIGINT NOT NULL COMMENT 'Unix timestamp in milliseconds',
  work_order_id VARCHAR(255),
  oee_percent DECIMAL(5,2) DEFAULT 0,
  availability_percent DECIMAL(5,2) DEFAULT 0,
  performance_percent DECIMAL(5,2) DEFAULT 0,
  quality_percent DECIMAL(5,2) DEFAULT 0,
  cycle_count INT DEFAULT 0,
  good_parts INT DEFAULT 0,
  scrap_count INT DEFAULT 0,
  operating_time DECIMAL(10,2) DEFAULT 0 COMMENT 'Accumulated seconds in state 1',
  downtime DECIMAL(10,2) DEFAULT 0 COMMENT 'Accumulated seconds in state 0 while tracking',
  machine_state INT DEFAULT 0 COMMENT 'Current machine state: 0 or 1',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_timestamp (timestamp),
  INDEX idx_work_order (work_order_id),
  INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- Table 2: Alert History
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS alert_history (
  id INT AUTO_INCREMENT PRIMARY KEY,
  timestamp BIGINT NOT NULL COMMENT 'Unix timestamp in milliseconds',
  alert_type VARCHAR(100) NOT NULL,
  description TEXT,
  severity VARCHAR(20) NOT NULL COMMENT 'info, warning, critical',
  source VARCHAR(50) NOT NULL COMMENT 'manual or automatic',
  work_order_id VARCHAR(255),
  acknowledged BOOLEAN DEFAULT 0,
  acknowledged_at BIGINT,
  acknowledged_by VARCHAR(100),
  auto_resolved BOOLEAN DEFAULT 0,
  resolved_at BIGINT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_timestamp (timestamp),
  INDEX idx_severity (severity),
  INDEX idx_acknowledged (acknowledged),
  INDEX idx_work_order (work_order_id),
  INDEX idx_source (source)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- Table 3: Shift Definitions
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS shift_definitions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  shift_name VARCHAR(50) NOT NULL,
  start_hour INT NOT NULL COMMENT '0-23',
  start_minute INT NOT NULL DEFAULT 0,
  end_hour INT NOT NULL COMMENT '0-23',
  end_minute INT NOT NULL DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE KEY unique_shift_name (shift_name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO shift_definitions (id, shift_name, start_hour, start_minute, end_hour, end_minute) VALUES
(1, 'Day Shift', 6, 0, 15, 0),
(2, 'Evening Shift', 15, 0, 23, 0),
(3, 'Night Shift', 23, 0, 6, 0);

-- ----------------------------------------------------------------------------
-- Table 4: Session State (For crash recovery)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS session_state (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_key VARCHAR(50) NOT NULL COMMENT 'Always "current_session"',
  work_order_id VARCHAR(255),
  cycle_count INT DEFAULT 0,
  production_start_time BIGINT COMMENT 'Unix timestamp',
  operating_time DECIMAL(10,2) DEFAULT 0,
  downtime DECIMAL(10,2) DEFAULT 0,
  last_update_time BIGINT,
  tracking_enabled BOOLEAN DEFAULT 0,
  machine_state INT DEFAULT 0,
  scrap_prompt_issued_for VARCHAR(255),
  current_session_id VARCHAR(100),
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY unique_session (session_key)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO session_state (session_key, work_order_id, cycle_count, tracking_enabled)
VALUES ('current_session', NULL, 0, 0);

-- ----------------------------------------------------------------------------
-- Table 5: Stop Events (Intelligent downtime categorization)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS stop_events (
  id INT AUTO_INCREMENT PRIMARY KEY,
  work_order_id VARCHAR(255),
  session_id VARCHAR(100),
  stop_time BIGINT NOT NULL COMMENT 'Unix timestamp',
  resume_time BIGINT COMMENT 'Unix timestamp when resumed',
  duration DECIMAL(10,2) COMMENT 'Duration in seconds',
  reason_category VARCHAR(20) NOT NULL COMMENT 'planned or unplanned',
  reason_detail VARCHAR(100) NOT NULL,
  affects_availability BOOLEAN NOT NULL,
  operator_notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_work_order (work_order_id),
  INDEX idx_session (session_id),
  INDEX idx_stop_time (stop_time),
  INDEX idx_category (reason_category)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- Table 6: Production Sessions (Session management)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS production_sessions (
  id INT AUTO_INCREMENT PRIMARY KEY,
  session_id VARCHAR(100) UNIQUE NOT NULL,
  work_order_id VARCHAR(255),
  start_time BIGINT NOT NULL COMMENT 'Unix timestamp',
  end_time BIGINT COMMENT 'Unix timestamp',
  duration DECIMAL(10,2) COMMENT 'Duration in seconds',
  cycles_completed INT DEFAULT 0,
  reason_for_start VARCHAR(50),
  reason_for_end VARCHAR(50),
  operating_time DECIMAL(10,2) DEFAULT 0,
  downtime DECIMAL(10,2) DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_work_order (work_order_id),
  INDEX idx_session (session_id),
  INDEX idx_start_time (start_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- Table 7: Cycle Anomalies (Hardware irregularity tracking)
-- ----------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS cycle_anomalies (
  id INT AUTO_INCREMENT PRIMARY KEY,
  work_order_id VARCHAR(255),
  session_id VARCHAR(100),
  cycle_number INT NOT NULL,
  expected_time DECIMAL(10,2),
  actual_time DECIMAL(10,2),
  deviation_percent DECIMAL(5,2),
  anomaly_type VARCHAR(50),
  timestamp BIGINT NOT NULL COMMENT 'Unix timestamp',
  notes TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  INDEX idx_work_order (work_order_id),
  INDEX idx_session (session_id),
  INDEX idx_timestamp (timestamp),
  INDEX idx_anomaly_type (anomaly_type)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ----------------------------------------------------------------------------
-- Table 8: Update work_orders table
-- NOTE: Run these ONE AT A TIME and ignore errors if columns already exist
-- ----------------------------------------------------------------------------

-- Add new columns (run these individually, ignore "duplicate column" errors)
-- ALTER TABLE work_orders ADD COLUMN scrap_count INT DEFAULT 0;
-- ALTER TABLE work_orders ADD COLUMN total_sessions INT DEFAULT 0;
-- ALTER TABLE work_orders ADD COLUMN total_operating_time DECIMAL(10,2) DEFAULT 0;
-- ALTER TABLE work_orders ADD COLUMN total_downtime DECIMAL(10,2) DEFAULT 0;
-- ALTER TABLE work_orders ADD COLUMN avg_session_duration DECIMAL(10,2) DEFAULT 0;

-- ============================================================================
-- END OF MIGRATION
-- ============================================================================