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