-- AI Insights: Suggestions log and chatbot conversations

CREATE TABLE IF NOT EXISTS ai_suggestions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    insight_type ENUM('maintenance','route','driver_behavior','fuel_prediction','utilization','general') NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    severity ENUM('info','warning','critical') DEFAULT 'info',
    related_entity_type VARCHAR(50) DEFAULT NULL,
    related_entity_id BIGINT UNSIGNED DEFAULT NULL,
    data JSON DEFAULT NULL,
    is_dismissed TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_ais_type (insight_type),
    INDEX idx_ais_severity (severity),
    INDEX idx_ais_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS ai_chat_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT NOT NULL,
    role ENUM('user','assistant') NOT NULL,
    message TEXT NOT NULL,
    context JSON DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_acm_user (user_id),
    INDEX idx_acm_created (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT IGNORE INTO permissions (code, description) VALUES
('ai_insights.view', 'View AI insights and predictions'),
('ai_insights.chat', 'Use AI assistant chatbot'),
('ai_insights.dismiss', 'Dismiss AI suggestions');

INSERT IGNORE INTO role_permissions (role_id, permission_id)
SELECT 1, id FROM permissions WHERE code LIKE 'ai_insights.%';