-- ============================================================
-- SIE Deployer - Database Schema v1
-- Tracks system deployments and step execution status
-- ============================================================

CREATE TABLE IF NOT EXISTS deployments (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name TEXT NOT NULL,
    source_path TEXT NOT NULL,
    target_path TEXT NOT NULL UNIQUE,
    subdomain TEXT,
    status TEXT DEFAULT 'in_progress',
    created_by TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    completed_at DATETIME,
    notes TEXT
);

CREATE TABLE IF NOT EXISTS deployment_steps (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    deployment_id INTEGER NOT NULL,
    step_number INTEGER NOT NULL,
    step_name TEXT NOT NULL,
    status TEXT DEFAULT 'pending',
    executed_by TEXT,
    executed_at DATETIME,
    duration_seconds INTEGER,
    result_message TEXT,
    input_data TEXT,
    UNIQUE(deployment_id, step_number),
    FOREIGN KEY (deployment_id) REFERENCES deployments(id) ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS deployment_log (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    deployment_id INTEGER NOT NULL,
    step_number INTEGER NOT NULL,
    log_level TEXT DEFAULT 'info',
    message TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (deployment_id) REFERENCES deployments(id) ON DELETE CASCADE
);

CREATE INDEX IF NOT EXISTS idx_steps_deployment ON deployment_steps(deployment_id);
CREATE INDEX IF NOT EXISTS idx_steps_status ON deployment_steps(status);
CREATE INDEX IF NOT EXISTS idx_log_deployment ON deployment_log(deployment_id);
CREATE INDEX IF NOT EXISTS idx_log_step ON deployment_log(deployment_id, step_number);
