-- Anomaly Events Table for Manufacturing Anomaly Detection
-- Stores all detected anomalies with full context for analysis

CREATE TABLE IF NOT EXISTS anomaly_events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    timestamp BIGINT NOT NULL COMMENT 'Unix timestamp in milliseconds',
    work_order_id VARCHAR(50) COMMENT 'Associated work order if applicable',

    -- Anomaly Classification
    anomaly_type VARCHAR(50) NOT NULL COMMENT 'Type: slow-cycle, production-stopped, high-scrap, etc.',
    severity ENUM('info', 'warning', 'critical') NOT NULL DEFAULT 'warning',

    -- Event Details
    title VARCHAR(200) NOT NULL COMMENT 'Short human-readable title',
    description TEXT COMMENT 'Detailed description of the anomaly',

    -- Context Data (JSON stored as TEXT)
    data_json TEXT COMMENT 'Anomaly-specific data: actual_value, expected_value, delta, etc.',
    kpi_snapshot_json TEXT COMMENT 'KPI values at time of event: OEE, Availability, Performance, Quality',

    -- Status Tracking
    status ENUM('active', 'acknowledged', 'resolved') DEFAULT 'active',
    acknowledged_at BIGINT COMMENT 'When user acknowledged',
    resolved_at BIGINT COMMENT 'When anomaly was resolved',
    auto_resolved BOOLEAN DEFAULT FALSE COMMENT 'True if system auto-resolved',

    -- Additional Metadata
    cycle_count INT COMMENT 'Cycle count at time of event',
    occurrence_count INT DEFAULT 1 COMMENT 'How many times this occurred (for deduplication)',
    last_occurrence BIGINT COMMENT 'Last time this anomaly re-occurred',
    notes TEXT COMMENT 'User notes',

    -- Indexes for fast queries
    INDEX idx_timestamp (timestamp),
    INDEX idx_work_order (work_order_id),
    INDEX idx_status (status),
    INDEX idx_type (anomaly_type),
    INDEX idx_severity (severity),
    INDEX idx_type_status (anomaly_type, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Stores detected anomalies and events';