-- GuruConnect Initial Schema
-- Machine persistence, session audit logging, and support codes

-- Enable UUID generation
CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Machines table - persistent agent records that survive server restarts
CREATE TABLE connect_machines (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    agent_id VARCHAR(255) UNIQUE NOT NULL,
    hostname VARCHAR(255) NOT NULL,
    os_version VARCHAR(255),
    is_elevated BOOLEAN DEFAULT FALSE,
    is_persistent BOOLEAN DEFAULT TRUE,
    first_seen TIMESTAMPTZ DEFAULT NOW(),
    last_seen TIMESTAMPTZ DEFAULT NOW(),
    last_session_id UUID,
    status VARCHAR(20) DEFAULT 'offline',
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_connect_machines_agent_id ON connect_machines(agent_id);
CREATE INDEX idx_connect_machines_status ON connect_machines(status);

-- Sessions table - connection history
CREATE TABLE connect_sessions (
    id UUID PRIMARY KEY,
    machine_id UUID REFERENCES connect_machines(id) ON DELETE CASCADE,
    started_at TIMESTAMPTZ DEFAULT NOW(),
    ended_at TIMESTAMPTZ,
    duration_secs INTEGER,
    is_support_session BOOLEAN DEFAULT FALSE,
    support_code VARCHAR(10),
    status VARCHAR(20) DEFAULT 'active'
);

CREATE INDEX idx_connect_sessions_machine ON connect_sessions(machine_id);
CREATE INDEX idx_connect_sessions_started ON connect_sessions(started_at DESC);
CREATE INDEX idx_connect_sessions_support_code ON connect_sessions(support_code);

-- Session events - comprehensive audit log
CREATE TABLE connect_session_events (
    id BIGSERIAL PRIMARY KEY,
    session_id UUID REFERENCES connect_sessions(id) ON DELETE CASCADE,
    event_type VARCHAR(50) NOT NULL,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    viewer_id VARCHAR(255),
    viewer_name VARCHAR(255),
    details JSONB,
    ip_address INET
);

CREATE INDEX idx_connect_events_session ON connect_session_events(session_id);
CREATE INDEX idx_connect_events_time ON connect_session_events(timestamp DESC);
CREATE INDEX idx_connect_events_type ON connect_session_events(event_type);

-- Support codes - persistent across restarts
CREATE TABLE connect_support_codes (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    code VARCHAR(10) UNIQUE NOT NULL,
    session_id UUID,
    created_by VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    expires_at TIMESTAMPTZ,
    status VARCHAR(20) DEFAULT 'pending',
    client_name VARCHAR(255),
    client_machine VARCHAR(255),
    connected_at TIMESTAMPTZ
);

CREATE INDEX idx_support_codes_code ON connect_support_codes(code);
CREATE INDEX idx_support_codes_status ON connect_support_codes(status);
CREATE INDEX idx_support_codes_session ON connect_support_codes(session_id);

-- Trigger to auto-update updated_at on machines
CREATE OR REPLACE FUNCTION update_connect_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_connect_machines_updated_at
    BEFORE UPDATE ON connect_machines
    FOR EACH ROW
    EXECUTE FUNCTION update_connect_updated_at();