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