-- OS Images table for managing available operating systems
-- Replaces hardcoded OS_IMAGE_MAP in vps-provider.ts

CREATE TABLE IF NOT EXISTS os_images (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  key TEXT NOT NULL UNIQUE,           -- API key: 'ubuntu_24_04'
  name TEXT NOT NULL,                 -- Display name: 'Ubuntu 24.04 LTS'
  family TEXT NOT NULL,               -- Family: 'ubuntu', 'debian', 'almalinux', 'rocky'
  linode_image_id TEXT,               -- Linode image ID: 'linode/ubuntu24.04'
  vultr_os_id INTEGER,                -- Vultr OS ID: 2284
  active INTEGER DEFAULT 1,           -- 1=available, 0=deprecated
  is_default INTEGER DEFAULT 0,       -- 1=default OS for new servers
  sort_order INTEGER DEFAULT 100,     -- Lower = higher priority in list
  created_at TEXT DEFAULT CURRENT_TIMESTAMP,
  updated_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- Create index for common queries
CREATE INDEX IF NOT EXISTS idx_os_images_active ON os_images(active);
CREATE INDEX IF NOT EXISTS idx_os_images_family ON os_images(family);

-- Insert OS images based on actual Linode/Vultr API data (2026-01)
-- Ubuntu LTS versions
INSERT INTO os_images (key, name, family, linode_image_id, vultr_os_id, active, is_default, sort_order) VALUES
  ('ubuntu_24_04', 'Ubuntu 24.04 LTS', 'ubuntu', 'linode/ubuntu24.04', 2284, 1, 1, 10),
  ('ubuntu_22_04', 'Ubuntu 22.04 LTS', 'ubuntu', 'linode/ubuntu22.04', 1743, 1, 0, 20),
  ('ubuntu_20_04', 'Ubuntu 20.04 LTS', 'ubuntu', 'linode/ubuntu20.04', NULL, 0, 0, 30);

-- Debian versions
INSERT INTO os_images (key, name, family, linode_image_id, vultr_os_id, active, sort_order) VALUES
  ('debian_13', 'Debian 13 (Trixie)', 'debian', 'linode/debian13', 2625, 1, 40),
  ('debian_12', 'Debian 12 (Bookworm)', 'debian', 'linode/debian12', 2136, 1, 50),
  ('debian_11', 'Debian 11 (Bullseye)', 'debian', 'linode/debian11', 477, 1, 60);

-- CentOS alternatives (AlmaLinux - most popular)
INSERT INTO os_images (key, name, family, linode_image_id, vultr_os_id, active, sort_order) VALUES
  ('almalinux_9', 'AlmaLinux 9', 'almalinux', 'linode/almalinux9', 1868, 1, 70),
  ('almalinux_8', 'AlmaLinux 8', 'almalinux', 'linode/almalinux8', 452, 1, 80);

-- Rocky Linux (another CentOS alternative)
INSERT INTO os_images (key, name, family, linode_image_id, vultr_os_id, active, sort_order) VALUES
  ('rocky_9', 'Rocky Linux 9', 'rocky', 'linode/rocky9', 1869, 1, 90),
  ('rocky_8', 'Rocky Linux 8', 'rocky', 'linode/rocky8', 448, 1, 100);

-- Fedora (for cutting-edge users)
INSERT INTO os_images (key, name, family, linode_image_id, vultr_os_id, active, sort_order) VALUES
  ('fedora_42', 'Fedora 42', 'fedora', 'linode/fedora42', 2572, 1, 110);