CREATE EXTENSION IF NOT EXISTS pgcrypto;

CREATE TABLE IF NOT EXISTS xbp_projects (
    project_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_name text NOT NULL,
    project_path text NOT NULL,
    version text,
    build_dir text,
    port integer,
    app_type text,
    branch text,
    target text,
    config_kind text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (project_path)
);
CREATE INDEX IF NOT EXISTS idx_xbp_projects_project_name ON xbp_projects (project_name);
CREATE INDEX IF NOT EXISTS idx_xbp_projects_updated_at ON xbp_projects (updated_at DESC);

CREATE TABLE IF NOT EXISTS xbp_project_services (
    project_service_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL REFERENCES xbp_projects(project_id) ON DELETE CASCADE,
    service_name text NOT NULL,
    target text,
    branch text,
    port integer,
    root_directory text,
    url text,
    healthcheck_path text,
    restart_policy text,
    start_wrapper text,
    systemd_service_name text,
    commands jsonb NOT NULL DEFAULT '{}'::jsonb,
    environment jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (project_id, service_name)
);
CREATE INDEX IF NOT EXISTS idx_xbp_project_services_project_id ON xbp_project_services (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_project_services_port ON xbp_project_services (port);

CREATE TABLE IF NOT EXISTS xbp_logs (
    log_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    log_level text NOT NULL,
    command text NOT NULL,
    message text NOT NULL,
    details text,
    duration_ms bigint,
    occurred_at timestamptz NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_logs_command ON xbp_logs (command);
CREATE INDEX IF NOT EXISTS idx_xbp_logs_occurred_at ON xbp_logs (occurred_at DESC);

CREATE TABLE IF NOT EXISTS xbp_project_logs (
    project_log_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL REFERENCES xbp_projects(project_id) ON DELETE CASCADE,
    global_log_id uuid REFERENCES xbp_logs(log_id) ON DELETE SET NULL,
    log_level text NOT NULL,
    command text NOT NULL,
    message text NOT NULL,
    details text,
    duration_ms bigint,
    occurred_at timestamptz NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_project_logs_project_id ON xbp_project_logs (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_project_logs_occurred_at ON xbp_project_logs (occurred_at DESC);

CREATE TABLE IF NOT EXISTS xbp_nginx_configs (
    nginx_config_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid REFERENCES xbp_projects(project_id) ON DELETE SET NULL,
    domain text NOT NULL,
    config_path text NOT NULL,
    content text,
    upstream_ports jsonb NOT NULL DEFAULT '[]'::jsonb,
    listen_ports jsonb NOT NULL DEFAULT '[]'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (domain, config_path)
);
CREATE INDEX IF NOT EXISTS idx_xbp_nginx_configs_project_id ON xbp_nginx_configs (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_nginx_configs_domain ON xbp_nginx_configs (domain);

CREATE TABLE IF NOT EXISTS xbp_nginx_logs (
    nginx_log_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid REFERENCES xbp_projects(project_id) ON DELETE SET NULL,
    domain text,
    action text NOT NULL,
    success boolean NOT NULL DEFAULT true,
    message text,
    details text,
    occurred_at timestamptz NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_nginx_logs_project_id ON xbp_nginx_logs (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_nginx_logs_domain ON xbp_nginx_logs (domain);
CREATE INDEX IF NOT EXISTS idx_xbp_nginx_logs_occurred_at ON xbp_nginx_logs (occurred_at DESC);

CREATE TABLE IF NOT EXISTS xbp_nginx_edit_audit_logs (
    nginx_edit_audit_log_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid REFERENCES xbp_projects(project_id) ON DELETE SET NULL,
    domain text,
    config_path text,
    actor text,
    action text NOT NULL,
    old_content text,
    new_content text,
    occurred_at timestamptz NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_nginx_audit_project_id ON xbp_nginx_edit_audit_logs (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_nginx_audit_domain ON xbp_nginx_edit_audit_logs (domain);
CREATE INDEX IF NOT EXISTS idx_xbp_nginx_audit_occurred_at ON xbp_nginx_edit_audit_logs (occurred_at DESC);

CREATE TABLE IF NOT EXISTS xbp_docker_containers (
    docker_container_row_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid REFERENCES xbp_projects(project_id) ON DELETE SET NULL,
    container_id text NOT NULL,
    container_name text,
    status text,
    ports text,
    inspected_at timestamptz NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (container_id)
);
CREATE INDEX IF NOT EXISTS idx_xbp_docker_containers_project_id ON xbp_docker_containers (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_docker_containers_container_id ON xbp_docker_containers (container_id);

CREATE TABLE IF NOT EXISTS xbp_docker_logs (
    docker_log_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid REFERENCES xbp_projects(project_id) ON DELETE SET NULL,
    container_id text,
    command text,
    stream text,
    message text,
    occurred_at timestamptz NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_docker_logs_project_id ON xbp_docker_logs (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_docker_logs_container_id ON xbp_docker_logs (container_id);
CREATE INDEX IF NOT EXISTS idx_xbp_docker_logs_occurred_at ON xbp_docker_logs (occurred_at DESC);

CREATE TABLE IF NOT EXISTS xbp_schedules (
    schedule_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid REFERENCES xbp_projects(project_id) ON DELETE SET NULL,
    schedule_type text NOT NULL,
    target_kind text NOT NULL,
    target_ref text NOT NULL DEFAULT '',
    expression text NOT NULL,
    timezone text,
    enabled boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    occurred_at timestamptz NOT NULL,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (schedule_type, target_kind, target_ref, expression)
);
CREATE INDEX IF NOT EXISTS idx_xbp_schedules_project_id ON xbp_schedules (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_schedules_occurred_at ON xbp_schedules (occurred_at DESC);

CREATE TABLE IF NOT EXISTS xbp_organizations (
    organization_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_name text NOT NULL,
    organization_slug text NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (organization_slug)
);
CREATE INDEX IF NOT EXISTS idx_xbp_organizations_slug ON xbp_organizations (organization_slug);

ALTER TABLE xbp_projects
    ADD COLUMN IF NOT EXISTS organization_id uuid REFERENCES xbp_organizations(organization_id) ON DELETE SET NULL,
    ADD COLUMN IF NOT EXISTS project_slug text,
    ADD COLUMN IF NOT EXISTS repository_provider text,
    ADD COLUMN IF NOT EXISTS repository_owner text,
    ADD COLUMN IF NOT EXISTS repository_name text,
    ADD COLUMN IF NOT EXISTS repository_id text,
    ADD COLUMN IF NOT EXISTS default_branch text DEFAULT 'main',
    ADD COLUMN IF NOT EXISTS runtime text,
    ADD COLUMN IF NOT EXISTS root_directory text,
    ADD COLUMN IF NOT EXISTS build_command text,
    ADD COLUMN IF NOT EXISTS install_command text,
    ADD COLUMN IF NOT EXISTS start_command text,
    ADD COLUMN IF NOT EXISTS output_directory text;
CREATE INDEX IF NOT EXISTS idx_xbp_projects_organization_id ON xbp_projects (organization_id);
CREATE UNIQUE INDEX IF NOT EXISTS idx_xbp_projects_org_slug
    ON xbp_projects (organization_id, project_slug)
    WHERE organization_id IS NOT NULL AND project_slug IS NOT NULL;
CREATE INDEX IF NOT EXISTS idx_xbp_projects_repository
    ON xbp_projects (repository_provider, repository_owner, repository_name);

CREATE TABLE IF NOT EXISTS xbp_environments (
    environment_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL REFERENCES xbp_projects(project_id) ON DELETE CASCADE,
    environment_name text NOT NULL,
    environment_slug text NOT NULL,
    branch_pattern text,
    is_production boolean NOT NULL DEFAULT false,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (project_id, environment_slug)
);
CREATE INDEX IF NOT EXISTS idx_xbp_environments_project_id ON xbp_environments (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_environments_slug ON xbp_environments (environment_slug);

CREATE TABLE IF NOT EXISTS xbp_env_vars (
    environment_variable_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL REFERENCES xbp_projects(project_id) ON DELETE CASCADE,
    environment_id uuid NOT NULL REFERENCES xbp_environments(environment_id) ON DELETE CASCADE,
    key_name text NOT NULL,
    value_encrypted text,
    value_hash text,
    is_sensitive boolean NOT NULL DEFAULT false,
    description text,
    created_by text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (project_id, environment_id, key_name)
);
CREATE INDEX IF NOT EXISTS idx_xbp_env_vars_project_id ON xbp_env_vars (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_env_vars_environment_id ON xbp_env_vars (environment_id);

CREATE TABLE IF NOT EXISTS xbp_github_installations (
    github_installation_row_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES xbp_organizations(organization_id) ON DELETE CASCADE,
    github_installation_id bigint NOT NULL,
    github_account_login text NOT NULL,
    github_account_id bigint NOT NULL,
    permissions jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (github_installation_id)
);
CREATE INDEX IF NOT EXISTS idx_xbp_github_installations_org_id ON xbp_github_installations (organization_id);

CREATE TABLE IF NOT EXISTS xbp_deployments (
    deployment_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL REFERENCES xbp_projects(project_id) ON DELETE CASCADE,
    environment_id uuid NOT NULL REFERENCES xbp_environments(environment_id) ON DELETE CASCADE,
    status text NOT NULL CHECK (
        status IN (
            'queued',
            'claimed',
            'cloning',
            'installing',
            'building',
            'pushing_artifact',
            'deploying',
            'warming',
            'degraded',
            'healthy',
            'failed',
            'cancelled',
            'rolled_back',
            'superseded'
        )
    ),
    health_status text NOT NULL DEFAULT 'unknown' CHECK (
        health_status IN ('unknown', 'passing', 'failing', 'degraded')
    ),
    status_reason text,
    failure_reason text,
    failure_stage text,
    source_type text NOT NULL CHECK (source_type IN ('github', 'cli', 'image', 'artifact')),
    git_provider text,
    git_owner text,
    git_repo text,
    git_branch text,
    git_commit_sha text,
    git_commit_message text,
    git_commit_author text,
    git_commit_url text,
    runtime text CHECK (runtime IN ('container', 'native_node', 'native_rust', 'static')),
    image_ref text,
    artifact_id uuid,
    build_id uuid,
    daemon_id uuid,
    node_id text,
    deployment_url text,
    preview_url text,
    queued_at timestamptz NOT NULL DEFAULT now(),
    claimed_at timestamptz,
    started_at timestamptz,
    built_at timestamptz,
    deployed_at timestamptz,
    healthy_at timestamptz,
    failed_at timestamptz,
    finished_at timestamptz,
    build_duration_ms bigint,
    deploy_duration_ms bigint,
    total_duration_ms bigint,
    created_by text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_deployments_project_id ON xbp_deployments (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_deployments_environment_id ON xbp_deployments (environment_id);
CREATE INDEX IF NOT EXISTS idx_xbp_deployments_status ON xbp_deployments (status);
CREATE INDEX IF NOT EXISTS idx_xbp_deployments_health_status ON xbp_deployments (health_status);
CREATE INDEX IF NOT EXISTS idx_xbp_deployments_commit_sha ON xbp_deployments (git_commit_sha);
CREATE INDEX IF NOT EXISTS idx_xbp_deployments_pull_request_number ON xbp_deployments ((metadata->>'pull_request_number')) WHERE metadata ? 'pull_request_number';
CREATE INDEX IF NOT EXISTS idx_xbp_deployments_created_at ON xbp_deployments (created_at DESC);

CREATE TABLE IF NOT EXISTS xbp_builds (
    build_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    deployment_id uuid NOT NULL REFERENCES xbp_deployments(deployment_id) ON DELETE CASCADE,
    project_id uuid NOT NULL REFERENCES xbp_projects(project_id) ON DELETE CASCADE,
    status text NOT NULL CHECK (status IN ('queued', 'running', 'success', 'failed', 'cancelled')),
    builder_type text NOT NULL CHECK (builder_type IN ('node', 'rust', 'docker', 'static', 'unknown')),
    package_manager text,
    install_command text,
    build_command text,
    dockerfile_path text,
    context_path text,
    cache_hit boolean NOT NULL DEFAULT false,
    cache_key text,
    started_at timestamptz,
    finished_at timestamptz,
    duration_ms bigint,
    exit_code integer,
    failure_reason text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_builds_deployment_id ON xbp_builds (deployment_id);
CREATE INDEX IF NOT EXISTS idx_xbp_builds_project_id ON xbp_builds (project_id);

CREATE TABLE IF NOT EXISTS xbp_artifacts (
    artifact_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL REFERENCES xbp_projects(project_id) ON DELETE CASCADE,
    deployment_id uuid REFERENCES xbp_deployments(deployment_id) ON DELETE SET NULL,
    build_id uuid REFERENCES xbp_builds(build_id) ON DELETE SET NULL,
    artifact_type text NOT NULL CHECK (
        artifact_type IN ('docker_image', 'oci_image', 'tarball', 'binary', 'static_dir', 'unknown')
    ),
    storage_provider text NOT NULL,
    storage_ref text NOT NULL,
    checksum text,
    size_bytes bigint,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_artifacts_project_id ON xbp_artifacts (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_artifacts_deployment_id ON xbp_artifacts (deployment_id);

CREATE TABLE IF NOT EXISTS xbp_daemons (
    daemon_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    node_name text NOT NULL,
    hostname text NOT NULL,
    region text,
    public_ip text,
    internal_ip text,
    version text NOT NULL,
    status text NOT NULL DEFAULT 'offline' CHECK (status IN ('offline', 'online', 'draining', 'disabled', 'unknown')),
    last_heartbeat_at timestamptz,
    cpu_cores integer,
    memory_total_mb integer,
    disk_total_gb integer,
    labels jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (hostname)
);
CREATE INDEX IF NOT EXISTS idx_xbp_daemons_status ON xbp_daemons (status);
CREATE INDEX IF NOT EXISTS idx_xbp_daemons_region ON xbp_daemons (region);

CREATE TABLE IF NOT EXISTS xbp_deployment_jobs (
    deployment_job_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    deployment_id uuid NOT NULL REFERENCES xbp_deployments(deployment_id) ON DELETE CASCADE,
    project_id uuid NOT NULL REFERENCES xbp_projects(project_id) ON DELETE CASCADE,
    daemon_id uuid REFERENCES xbp_daemons(daemon_id) ON DELETE SET NULL,
    status text NOT NULL DEFAULT 'queued' CHECK (
        status IN ('queued', 'claimed', 'running', 'succeeded', 'failed', 'cancelled')
    ),
    priority integer NOT NULL DEFAULT 100,
    attempts integer NOT NULL DEFAULT 0,
    max_attempts integer NOT NULL DEFAULT 3,
    locked_by text,
    locked_at timestamptz,
    run_after timestamptz NOT NULL DEFAULT now(),
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_text text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_deployment_jobs_status ON xbp_deployment_jobs (status);
CREATE INDEX IF NOT EXISTS idx_xbp_deployment_jobs_daemon_id ON xbp_deployment_jobs (daemon_id);
CREATE INDEX IF NOT EXISTS idx_xbp_deployment_jobs_run_after ON xbp_deployment_jobs (run_after);

CREATE TABLE IF NOT EXISTS xbp_runtime_instances (
    runtime_instance_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    deployment_id uuid NOT NULL REFERENCES xbp_deployments(deployment_id) ON DELETE CASCADE,
    project_id uuid NOT NULL REFERENCES xbp_projects(project_id) ON DELETE CASCADE,
    daemon_id uuid NOT NULL REFERENCES xbp_daemons(daemon_id) ON DELETE CASCADE,
    runtime text NOT NULL CHECK (runtime IN ('container', 'native_node', 'native_rust', 'static')),
    process_id text,
    container_id text,
    port integer,
    status text NOT NULL CHECK (status IN ('starting', 'running', 'stopping', 'stopped', 'failed', 'unknown')),
    started_at timestamptz,
    stopped_at timestamptz,
    restart_count integer NOT NULL DEFAULT 0,
    health_status text NOT NULL DEFAULT 'unknown' CHECK (
        health_status IN ('unknown', 'passing', 'failing', 'degraded')
    ),
    last_healthcheck_at timestamptz,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE IF NOT EXISTS xbp_runner_hosts (
    runner_host_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES xbp_organizations(organization_id) ON DELETE CASCADE,
    daemon_id uuid REFERENCES xbp_daemons(daemon_id) ON DELETE SET NULL,
    host_kind text NOT NULL CHECK (host_kind IN ('daemon', 'rogue')),
    platform text NOT NULL CHECK (platform IN ('linux', 'windows', 'macos')),
    hostname text NOT NULL,
    runner_name_prefix text NOT NULL,
    display_name text,
    arch text,
    status text NOT NULL DEFAULT 'enrolled' CHECK (status IN ('enrolled', 'online', 'offline', 'draining', 'error')),
    enrollment_token text,
    enrolled_at timestamptz NOT NULL DEFAULT now(),
    last_heartbeat_at timestamptz,
    labels jsonb NOT NULL DEFAULT '{}'::jsonb,
    capabilities jsonb NOT NULL DEFAULT '{}'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (organization_id, hostname)
);
CREATE INDEX IF NOT EXISTS idx_xbp_runner_hosts_org_id ON xbp_runner_hosts (organization_id);
CREATE INDEX IF NOT EXISTS idx_xbp_runner_hosts_daemon_id ON xbp_runner_hosts (daemon_id);
CREATE INDEX IF NOT EXISTS idx_xbp_runner_hosts_status ON xbp_runner_hosts (status);

CREATE TABLE IF NOT EXISTS xbp_runner_host_preflights (
    runner_host_preflight_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    runner_host_id uuid NOT NULL REFERENCES xbp_runner_hosts(runner_host_id) ON DELETE CASCADE,
    platform text NOT NULL CHECK (platform IN ('linux', 'windows', 'macos')),
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'synced', 'stale', 'error')),
    docker_available boolean,
    service_manager text,
    checks jsonb NOT NULL DEFAULT '{}'::jsonb,
    checked_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_runner_host_preflights_host_id ON xbp_runner_host_preflights (runner_host_id);

CREATE TABLE IF NOT EXISTS xbp_runner_groups (
    runner_group_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES xbp_organizations(organization_id) ON DELETE CASCADE,
    github_installation_id text,
    github_runner_group_id bigint,
    group_name text NOT NULL,
    visibility text NOT NULL CHECK (visibility IN ('all', 'selected', 'private')),
    sync_state text NOT NULL DEFAULT 'pending' CHECK (sync_state IN ('pending', 'synced', 'stale', 'error')),
    sync_error text,
    inherited boolean NOT NULL DEFAULT false,
    allows_public_repositories boolean NOT NULL DEFAULT false,
    restricted_to_workflows boolean NOT NULL DEFAULT false,
    selected_workflows jsonb NOT NULL DEFAULT '[]'::jsonb,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (organization_id, group_name)
);
CREATE INDEX IF NOT EXISTS idx_xbp_runner_groups_org_id ON xbp_runner_groups (organization_id);
CREATE INDEX IF NOT EXISTS idx_xbp_runner_groups_github_group_id ON xbp_runner_groups (github_runner_group_id);

CREATE TABLE IF NOT EXISTS xbp_runner_group_repositories (
    runner_group_repository_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    runner_group_id uuid NOT NULL REFERENCES xbp_runner_groups(runner_group_id) ON DELETE CASCADE,
    repository_id uuid REFERENCES xbp_projects(project_id) ON DELETE SET NULL,
    repository_owner text NOT NULL,
    repository_name text NOT NULL,
    repository_full_name text NOT NULL,
    github_repository_id bigint,
    allowed boolean NOT NULL DEFAULT true,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (runner_group_id, repository_full_name)
);
CREATE INDEX IF NOT EXISTS idx_xbp_runner_group_repositories_group_id ON xbp_runner_group_repositories (runner_group_id);

CREATE TABLE IF NOT EXISTS xbp_runners (
    runner_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES xbp_organizations(organization_id) ON DELETE CASCADE,
    runner_host_id uuid REFERENCES xbp_runner_hosts(runner_host_id) ON DELETE SET NULL,
    runner_group_id uuid REFERENCES xbp_runner_groups(runner_group_id) ON DELETE SET NULL,
    github_runner_id bigint,
    runner_name text NOT NULL,
    platform text NOT NULL CHECK (platform IN ('linux', 'windows', 'macos')),
    os_name text,
    architecture text,
    status text NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'synced', 'stale', 'error')),
    busy boolean NOT NULL DEFAULT false,
    labels jsonb NOT NULL DEFAULT '{}'::jsonb,
    desired_config jsonb,
    runtime_state jsonb,
    registration_state text CHECK (registration_state IN ('pending', 'unregistered', 'registered', 'removing', 'failed')),
    install_phase text CHECK (install_phase IN ('pending', 'preflight', 'downloading', 'extracting', 'cleanup', 'registering', 'installing_service', 'starting_service', 'running', 'removing', 'failed')),
    installed_version text,
    registration_error text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    last_registration_attempt_at timestamptz,
    last_seen_at timestamptz,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (organization_id, runner_name)
);
CREATE INDEX IF NOT EXISTS idx_xbp_runners_org_id ON xbp_runners (organization_id);
CREATE INDEX IF NOT EXISTS idx_xbp_runners_host_id ON xbp_runners (runner_host_id);
CREATE INDEX IF NOT EXISTS idx_xbp_runners_group_id ON xbp_runners (runner_group_id);
CREATE INDEX IF NOT EXISTS idx_xbp_runners_github_runner_id ON xbp_runners (github_runner_id);

CREATE TABLE IF NOT EXISTS xbp_runner_jobs (
    runner_job_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES xbp_organizations(organization_id) ON DELETE CASCADE,
    runner_host_id uuid REFERENCES xbp_runner_hosts(runner_host_id) ON DELETE SET NULL,
    daemon_id uuid REFERENCES xbp_daemons(daemon_id) ON DELETE SET NULL,
    runner_id uuid REFERENCES xbp_runners(runner_id) ON DELETE SET NULL,
    job_kind text NOT NULL CHECK (job_kind IN ('deploy', 'remove', 'sync')),
    status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'claimed', 'running', 'succeeded', 'failed', 'cancelled')),
    priority integer NOT NULL DEFAULT 100,
    attempts integer NOT NULL DEFAULT 0,
    max_attempts integer NOT NULL DEFAULT 3,
    locked_by text,
    locked_at timestamptz,
    run_after timestamptz NOT NULL DEFAULT now(),
    phase text CHECK (phase IN ('pending', 'preflight', 'downloading', 'extracting', 'cleanup', 'registering', 'installing_service', 'starting_service', 'running', 'removing', 'failed')),
    payload jsonb NOT NULL DEFAULT '{}'::jsonb,
    details jsonb NOT NULL DEFAULT '{}'::jsonb,
    error_text text,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_runner_jobs_org_id ON xbp_runner_jobs (organization_id);
CREATE INDEX IF NOT EXISTS idx_xbp_runner_jobs_host_id ON xbp_runner_jobs (runner_host_id);
CREATE INDEX IF NOT EXISTS idx_xbp_runner_jobs_status ON xbp_runner_jobs (status);
CREATE INDEX IF NOT EXISTS idx_xbp_runner_jobs_run_after ON xbp_runner_jobs (run_after);
CREATE INDEX IF NOT EXISTS idx_xbp_runtime_instances_deployment_id ON xbp_runtime_instances (deployment_id);
CREATE INDEX IF NOT EXISTS idx_xbp_runtime_instances_daemon_id ON xbp_runtime_instances (daemon_id);
CREATE INDEX IF NOT EXISTS idx_xbp_runtime_instances_status ON xbp_runtime_instances (status);

CREATE TABLE IF NOT EXISTS xbp_deployment_logs (
    deployment_log_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL REFERENCES xbp_projects(project_id) ON DELETE CASCADE,
    deployment_id uuid REFERENCES xbp_deployments(deployment_id) ON DELETE CASCADE,
    runtime_instance_id uuid REFERENCES xbp_runtime_instances(runtime_instance_id) ON DELETE SET NULL,
    daemon_id uuid REFERENCES xbp_daemons(daemon_id) ON DELETE SET NULL,
    stream text NOT NULL CHECK (stream IN ('stdout', 'stderr', 'build', 'system')),
    log_level text,
    message text NOT NULL,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    occurred_at timestamptz NOT NULL DEFAULT now(),
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_deployment_logs_project_id ON xbp_deployment_logs (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_deployment_logs_deployment_id ON xbp_deployment_logs (deployment_id);
CREATE INDEX IF NOT EXISTS idx_xbp_deployment_logs_occurred_at ON xbp_deployment_logs (occurred_at DESC);

CREATE TABLE IF NOT EXISTS xbp_domains (
    domain_row_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES xbp_organizations(organization_id) ON DELETE CASCADE,
    project_id uuid REFERENCES xbp_projects(project_id) ON DELETE SET NULL,
    environment_id uuid REFERENCES xbp_environments(environment_id) ON DELETE SET NULL,
    domain text NOT NULL,
    target_type text NOT NULL CHECK (target_type IN ('project', 'deployment', 'environment', 'alias')),
    target_id uuid NOT NULL,
    verified boolean NOT NULL DEFAULT false,
    verification_token text,
    ssl_status text NOT NULL DEFAULT 'pending' CHECK (
        ssl_status IN ('pending', 'verifying', 'issued', 'failed')
    ),
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (domain)
);
CREATE INDEX IF NOT EXISTS idx_xbp_domains_org_id ON xbp_domains (organization_id);
CREATE INDEX IF NOT EXISTS idx_xbp_domains_project_id ON xbp_domains (project_id);

CREATE TABLE IF NOT EXISTS xbp_aliases (
    alias_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id uuid NOT NULL REFERENCES xbp_projects(project_id) ON DELETE CASCADE,
    environment_id uuid REFERENCES xbp_environments(environment_id) ON DELETE SET NULL,
    alias text NOT NULL,
    deployment_id uuid NOT NULL REFERENCES xbp_deployments(deployment_id) ON DELETE CASCADE,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now(),
    UNIQUE (alias)
);
CREATE INDEX IF NOT EXISTS idx_xbp_aliases_project_id ON xbp_aliases (project_id);
CREATE INDEX IF NOT EXISTS idx_xbp_aliases_deployment_id ON xbp_aliases (deployment_id);

CREATE TABLE IF NOT EXISTS xbp_audit_log (
    audit_log_id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id uuid NOT NULL REFERENCES xbp_organizations(organization_id) ON DELETE CASCADE,
    actor_id text,
    actor_type text NOT NULL CHECK (actor_type IN ('user', 'token', 'github_app', 'daemon', 'system')),
    action text NOT NULL,
    resource_type text NOT NULL,
    resource_id text,
    metadata jsonb NOT NULL DEFAULT '{}'::jsonb,
    created_at timestamptz NOT NULL DEFAULT now(),
    updated_at timestamptz NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_xbp_audit_log_org_id ON xbp_audit_log (organization_id);
CREATE INDEX IF NOT EXISTS idx_xbp_audit_log_created_at ON xbp_audit_log (created_at DESC);
