CREATE TABLE IF NOT EXISTS onboarding_generation_jobs (
    uuid            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id       UUID NOT NULL REFERENCES tenants(uuid),
    employee_uuid   UUID NOT NULL REFERENCES employees(uuid),
    requested_by    UUID NOT NULL REFERENCES users(uuid),
    type            VARCHAR(50) NOT NULL CHECK (type IN ('manager_brief', 'onboarding_plan')),
    status          VARCHAR(40) NOT NULL DEFAULT 'queued' CHECK (status IN ('queued', 'processing', 'completed', 'failed')),
    input_context   JSONB NOT NULL DEFAULT '{}',
    output          JSONB,
    error_message   TEXT,
    created_at      TIMESTAMP DEFAULT NOW(),
    updated_at      TIMESTAMP DEFAULT NOW(),
    completed_at    TIMESTAMP
);

CREATE INDEX IF NOT EXISTS onboarding_generation_jobs_tenant_status_idx
    ON onboarding_generation_jobs (tenant_id, status, created_at DESC);

CREATE INDEX IF NOT EXISTS onboarding_generation_jobs_employee_idx
    ON onboarding_generation_jobs (tenant_id, employee_uuid, created_at DESC);

ALTER TABLE onboarding_generation_jobs ENABLE ROW LEVEL SECURITY;
ALTER TABLE onboarding_generation_jobs FORCE ROW LEVEL SECURITY;
DROP POLICY IF EXISTS tenant_isolation ON onboarding_generation_jobs;
CREATE POLICY tenant_isolation ON onboarding_generation_jobs
    USING (tenant_id = current_setting('app.tenant_id')::uuid);

GRANT SELECT, INSERT, UPDATE ON onboarding_generation_jobs TO diwall_core_app;
