-- Migration 144: install_history table (Task #101)
-- Tracks every modpack install/update job through the pg-boss queue.

CREATE TABLE IF NOT EXISTS install_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    server_identifier VARCHAR(36) REFERENCES server_config(server_identifier) ON DELETE SET NULL,
    pterodactyl_server_id VARCHAR(100),
    job_type VARCHAR(50) NOT NULL,         -- fresh_install, update, ghost_update
    target_version_id VARCHAR(100) NOT NULL,
    triggered_by VARCHAR(100) NOT NULL,    -- Discord username
    status VARCHAR(50) NOT NULL DEFAULT 'queued',  -- queued, running, success, failed
    started_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    completed_at TIMESTAMP WITH TIME ZONE,
    duration_seconds INT,
    mods_injected INT DEFAULT 0,
    log_output JSONB DEFAULT '{}',
    error_message TEXT
);

CREATE INDEX IF NOT EXISTS idx_install_history_server
  ON install_history(server_identifier);
CREATE INDEX IF NOT EXISTS idx_install_history_recent_failures
  ON install_history(server_identifier, status, started_at);
CREATE INDEX IF NOT EXISTS idx_install_history_status
  ON install_history(status);
