bmc_hub/migrations/154_links_endpoints_module.sql

-- Migration 154: Links / Endpoints module foundation
-- Removable module schema for operational access layer

CREATE TABLE IF NOT EXISTS link_categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL UNIQUE,
    icon VARCHAR(100),
    sort_order INTEGER NOT NULL DEFAULT 100,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS links (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    type VARCHAR(20) NOT NULL,
    url TEXT,
    host TEXT,
    port INTEGER,
    username TEXT,
    icon VARCHAR(100),
    color VARCHAR(32),
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    case_id INTEGER REFERENCES sag_sager(id) ON DELETE SET NULL,
    hardware_id INTEGER REFERENCES hardware_assets(id) ON DELETE SET NULL,
    vault_item_id TEXT,
    vault_item_ids JSONB NOT NULL DEFAULT '[]'::jsonb,
    is_critical BOOLEAN NOT NULL DEFAULT FALSE,
    is_favorite BOOLEAN NOT NULL DEFAULT FALSE,
    environment VARCHAR(20) NOT NULL DEFAULT 'prod',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    deleted_at TIMESTAMP,
    CONSTRAINT links_type_check CHECK (type IN ('http', 'ssh', 'rdp', 'command')),
    CONSTRAINT links_environment_check CHECK (environment IN ('prod', 'test', 'dev')),
    CONSTRAINT links_port_check CHECK (port IS NULL OR (port >= 1 AND port <= 65535))
);

CREATE TABLE IF NOT EXISTS link_category_map (
    link_id INTEGER NOT NULL REFERENCES links(id) ON DELETE CASCADE,
    category_id INTEGER NOT NULL REFERENCES link_categories(id) ON DELETE CASCADE,
    created_at TIMESTAMP DEFAULT NOW(),
    PRIMARY KEY (link_id, category_id)
);

CREATE TABLE IF NOT EXISTS link_runbooks (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    case_id INTEGER REFERENCES sag_sager(id) ON DELETE SET NULL,
    is_active BOOLEAN NOT NULL DEFAULT TRUE,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    deleted_at TIMESTAMP
);

CREATE TABLE IF NOT EXISTS link_runbook_steps (
    id SERIAL PRIMARY KEY,
    runbook_id INTEGER NOT NULL REFERENCES link_runbooks(id) ON DELETE CASCADE,
    step_order INTEGER NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    link_id INTEGER REFERENCES links(id) ON DELETE SET NULL,
    command_text TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT link_runbook_steps_unique_order UNIQUE (runbook_id, step_order)
);

CREATE TABLE IF NOT EXISTS link_status_checks (
    id SERIAL PRIMARY KEY,
    link_id INTEGER NOT NULL REFERENCES links(id) ON DELETE CASCADE,
    status VARCHAR(20) NOT NULL DEFAULT 'unknown',
    details JSONB NOT NULL DEFAULT '{}'::jsonb,
    checked_at TIMESTAMP DEFAULT NOW(),
    CONSTRAINT link_status_checks_status_check CHECK (status IN ('ok', 'down', 'unknown'))
);

CREATE TABLE IF NOT EXISTS link_access_log (
    id SERIAL PRIMARY KEY,
    link_id INTEGER NOT NULL REFERENCES links(id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    action_type VARCHAR(50) NOT NULL,
    case_id INTEGER REFERENCES sag_sager(id) ON DELETE SET NULL,
    customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
    metadata JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS links_audit_log (
    id SERIAL PRIMARY KEY,
    link_id INTEGER REFERENCES links(id) ON DELETE SET NULL,
    event_type VARCHAR(50) NOT NULL,
    actor_user_id INTEGER REFERENCES users(user_id) ON DELETE SET NULL,
    changes JSONB NOT NULL DEFAULT '{}'::jsonb,
    created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_links_scope_case ON links(case_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_links_scope_customer ON links(customer_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_links_scope_hardware ON links(hardware_id) WHERE deleted_at IS NULL;
CREATE INDEX IF NOT EXISTS idx_links_name ON links(name);
CREATE INDEX IF NOT EXISTS idx_links_host ON links(host);
CREATE INDEX IF NOT EXISTS idx_links_url ON links(url);
CREATE INDEX IF NOT EXISTS idx_links_type ON links(type);
CREATE INDEX IF NOT EXISTS idx_links_updated_at ON links(updated_at DESC);
CREATE INDEX IF NOT EXISTS idx_link_status_checks_link_checked ON link_status_checks(link_id, checked_at DESC);
CREATE INDEX IF NOT EXISTS idx_link_access_log_created ON link_access_log(created_at DESC);

INSERT INTO link_categories (name, icon, sort_order)
VALUES
    ('Network', 'bi-diagram-3', 10),
    ('Monitoring', 'bi-activity', 20),
    ('Servers', 'bi-hdd-network', 30),
    ('Operations', 'bi-tools', 40),
    ('Runbooks', 'bi-journal-check', 50)
ON CONFLICT (name) DO NOTHING;