-- Tabella lookup per sistemi operativi
-- Usata per validare e descrivere i valori in activation_keys.operating_system

CREATE TABLE IF NOT EXISTS list_operating_systems (
    id INT PRIMARY KEY,
    code VARCHAR(10) NOT NULL UNIQUE,
    description VARCHAR(100) NOT NULL,
    icon VARCHAR(50),
    status INT DEFAULT 1,
    display_order INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Popolamento dati iniziali
INSERT INTO list_operating_systems (id, code, description, icon, status, display_order) VALUES
(0, 'UNK', 'Unknown', 'question-circle', 1, 99),
(10, 'ANDR', 'Android', 'android', 1, 1),
(20, 'IOS', 'iOS', 'apple', 1, 2),
(30, 'WIN', 'Windows', 'windows', 1, 3),
(40, 'MAC', 'macOS', 'apple', 1, 4),
(50, 'LNX', 'Linux', 'linux', 1, 5)
ON DUPLICATE KEY UPDATE
    description = VALUES(description),
    icon = VALUES(icon),
    display_order = VALUES(display_order);

-- Indici per performance
CREATE INDEX idx_status ON list_operating_systems(status);
CREATE INDEX idx_display_order ON list_operating_systems(display_order);
