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