-- ============================================================================
-- Veeam Service Provider Console (VSPC) Integration Tables
-- Creates tables for synced Veeam backup data and compliance results
-- ============================================================================

-- Veeam Organizations (VSPC tenants/companies)
CREATE TABLE IF NOT EXISTS veeam_organizations (
    instance_uid VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    type VARCHAR(50),
    company_id INTEGER,
    tax_id VARCHAR(100),
    email VARCHAR(255),
    phone VARCHAR(50),
    country VARCHAR(255),
    state VARCHAR(255),
    city VARCHAR(255),
    street VARCHAR(500),
    zip_code VARCHAR(50),
    website VARCHAR(255),
    notes TEXT,
    synced_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Veeam Backup Servers
CREATE TABLE IF NOT EXISTS veeam_backup_servers (
    instance_uid VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    organization_uid VARCHAR(255) REFERENCES veeam_organizations(instance_uid) ON DELETE SET NULL,
    version VARCHAR(100),
    display_version VARCHAR(100),
    status VARCHAR(50),
    backup_server_role_type VARCHAR(50),
    installation_uid VARCHAR(255),
    location_uid VARCHAR(255),
    management_agent_uid VARCHAR(255),
    synced_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Veeam Backup Server Jobs (VM-level backup jobs)
CREATE TABLE IF NOT EXISTS veeam_backup_jobs (
    instance_uid VARCHAR(255) PRIMARY KEY,
    unique_uid VARCHAR(255),
    name VARCHAR(500) NOT NULL,
    description TEXT,
    backup_server_uid VARCHAR(255) REFERENCES veeam_backup_servers(instance_uid) ON DELETE SET NULL,
    organization_uid VARCHAR(255) REFERENCES veeam_organizations(instance_uid) ON DELETE SET NULL,
    status VARCHAR(50),
    type VARCHAR(100),
    last_run TIMESTAMP WITH TIME ZONE,
    last_end_time TIMESTAMP WITH TIME ZONE,
    last_duration INTEGER,
    processing_rate DOUBLE PRECISION,
    avg_duration INTEGER,
    transferred_data BIGINT,
    backup_chain_size BIGINT,
    bottleneck VARCHAR(100),
    is_enabled BOOLEAN DEFAULT true,
    schedule_type VARCHAR(100),
    failure_message TEXT,
    target_type VARCHAR(100),
    destination VARCHAR(500),
    retention_limit INTEGER,
    retention_limit_type VARCHAR(50),
    is_gfs_option_enabled BOOLEAN DEFAULT false,
    synced_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Veeam Backup Agent Jobs (workstation/physical server backup jobs)
CREATE TABLE IF NOT EXISTS veeam_backup_agent_jobs (
    instance_uid VARCHAR(255) PRIMARY KEY,
    original_uid VARCHAR(255),
    backup_agent_uid VARCHAR(255),
    organization_uid VARCHAR(255) REFERENCES veeam_organizations(instance_uid) ON DELETE SET NULL,
    name VARCHAR(500) NOT NULL,
    description TEXT,
    config_uid VARCHAR(255),
    system_type VARCHAR(50),
    backup_policy_uid VARCHAR(255),
    backup_policy_name VARCHAR(500),
    backup_policy_assign_status VARCHAR(50),
    backup_policy_failure_message TEXT,
    status VARCHAR(50),
    operation_mode VARCHAR(50),
    destination VARCHAR(500),
    restore_points INTEGER,
    last_run TIMESTAMP WITH TIME ZONE,
    last_end_time TIMESTAMP WITH TIME ZONE,
    last_duration INTEGER,
    next_run TIMESTAMP WITH TIME ZONE,
    avg_duration INTEGER,
    backup_mode VARCHAR(50),
    target_type VARCHAR(100),
    is_enabled BOOLEAN DEFAULT true,
    schedule_type VARCHAR(100),
    failure_message TEXT,
    backed_up_size BIGINT,
    free_space BIGINT,
    synced_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Veeam Protected Workloads (VMs)
CREATE TABLE IF NOT EXISTS veeam_protected_workloads (
    instance_uid VARCHAR(255) PRIMARY KEY,
    name VARCHAR(500) NOT NULL,
    backup_server_uid VARCHAR(255) REFERENCES veeam_backup_servers(instance_uid) ON DELETE SET NULL,
    organization_uid VARCHAR(255) REFERENCES veeam_organizations(instance_uid) ON DELETE SET NULL,
    job_uid VARCHAR(255),
    ip_addresses TEXT,
    provisioned_source_size BIGINT,
    used_source_size BIGINT,
    total_restore_point_size BIGINT,
    latest_restore_point_size BIGINT,
    restore_points INTEGER,
    latest_restore_point_date TIMESTAMP WITH TIME ZONE,
    malware_state VARCHAR(50),
    immutable BOOLEAN DEFAULT false,
    synced_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Veeam Repositories
CREATE TABLE IF NOT EXISTS veeam_repositories (
    instance_uid VARCHAR(255) PRIMARY KEY,
    name VARCHAR(500) NOT NULL,
    backup_server_uid VARCHAR(255) REFERENCES veeam_backup_servers(instance_uid) ON DELETE SET NULL,
    capacity_bytes BIGINT,
    free_space_bytes BIGINT,
    used_space_bytes BIGINT,
    repository_type VARCHAR(100),
    synced_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- Veeam Compliance Results (computed after each sync)
CREATE TABLE IF NOT EXISTS veeam_compliance_results (
    id SERIAL PRIMARY KEY,
    company_id INTEGER,
    configuration_item_id INTEGER,
    veeam_workload_uid VARCHAR(255),
    mismatch_type VARCHAR(50) NOT NULL,
    backup_type_udf VARCHAR(255),
    device_name VARCHAR(500) NOT NULL,
    contract_name VARCHAR(500),
    veeam_workload_name VARCHAR(500),
    computed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- INDEXES
-- ============================================================================

-- veeam_organizations indexes
CREATE INDEX IF NOT EXISTS idx_veeam_organizations_company_id ON veeam_organizations(company_id);
CREATE INDEX IF NOT EXISTS idx_veeam_organizations_type ON veeam_organizations(type);
CREATE INDEX IF NOT EXISTS idx_veeam_organizations_synced_at ON veeam_organizations(synced_at);

-- veeam_backup_servers indexes
CREATE INDEX IF NOT EXISTS idx_veeam_backup_servers_organization_uid ON veeam_backup_servers(organization_uid);
CREATE INDEX IF NOT EXISTS idx_veeam_backup_servers_status ON veeam_backup_servers(status);
CREATE INDEX IF NOT EXISTS idx_veeam_backup_servers_synced_at ON veeam_backup_servers(synced_at);

-- veeam_backup_jobs indexes
CREATE INDEX IF NOT EXISTS idx_veeam_backup_jobs_organization_uid ON veeam_backup_jobs(organization_uid);
CREATE INDEX IF NOT EXISTS idx_veeam_backup_jobs_backup_server_uid ON veeam_backup_jobs(backup_server_uid);
CREATE INDEX IF NOT EXISTS idx_veeam_backup_jobs_status ON veeam_backup_jobs(status);
CREATE INDEX IF NOT EXISTS idx_veeam_backup_jobs_last_run ON veeam_backup_jobs(last_run);
CREATE INDEX IF NOT EXISTS idx_veeam_backup_jobs_synced_at ON veeam_backup_jobs(synced_at);

-- veeam_backup_agent_jobs indexes
CREATE INDEX IF NOT EXISTS idx_veeam_backup_agent_jobs_organization_uid ON veeam_backup_agent_jobs(organization_uid);
CREATE INDEX IF NOT EXISTS idx_veeam_backup_agent_jobs_status ON veeam_backup_agent_jobs(status);
CREATE INDEX IF NOT EXISTS idx_veeam_backup_agent_jobs_last_run ON veeam_backup_agent_jobs(last_run);
CREATE INDEX IF NOT EXISTS idx_veeam_backup_agent_jobs_synced_at ON veeam_backup_agent_jobs(synced_at);
CREATE INDEX IF NOT EXISTS idx_veeam_backup_agent_jobs_backup_policy_name ON veeam_backup_agent_jobs(backup_policy_name);

-- veeam_protected_workloads indexes
CREATE INDEX IF NOT EXISTS idx_veeam_protected_workloads_organization_uid ON veeam_protected_workloads(organization_uid);
CREATE INDEX IF NOT EXISTS idx_veeam_protected_workloads_backup_server_uid ON veeam_protected_workloads(backup_server_uid);
CREATE INDEX IF NOT EXISTS idx_veeam_protected_workloads_job_uid ON veeam_protected_workloads(job_uid);
CREATE INDEX IF NOT EXISTS idx_veeam_protected_workloads_latest_restore_point ON veeam_protected_workloads(latest_restore_point_date);
CREATE INDEX IF NOT EXISTS idx_veeam_protected_workloads_synced_at ON veeam_protected_workloads(synced_at);

-- veeam_repositories indexes
CREATE INDEX IF NOT EXISTS idx_veeam_repositories_backup_server_uid ON veeam_repositories(backup_server_uid);
CREATE INDEX IF NOT EXISTS idx_veeam_repositories_synced_at ON veeam_repositories(synced_at);

-- veeam_compliance_results indexes
CREATE INDEX IF NOT EXISTS idx_veeam_compliance_results_company_id ON veeam_compliance_results(company_id);
CREATE INDEX IF NOT EXISTS idx_veeam_compliance_results_mismatch_type ON veeam_compliance_results(mismatch_type);
CREATE INDEX IF NOT EXISTS idx_veeam_compliance_results_computed_at ON veeam_compliance_results(computed_at);