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);
