-- Additive contact onboarding/offboarding planning. Existing contacts and cases are unchanged.
CREATE TABLE IF NOT EXISTS contact_lifecycle_events (
    id BIGSERIAL PRIMARY KEY,
    contact_id INTEGER NOT NULL REFERENCES contacts(id),
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    event_type TEXT NOT NULL CHECK (event_type IN ('onboarding', 'offboarding')),
    event_date DATE NOT NULL,
    lead_days INTEGER NOT NULL DEFAULT 14 CHECK (lead_days BETWEEN 0 AND 3650),
    auto_create BOOLEAN NOT NULL DEFAULT FALSE,
    template_id INTEGER REFERENCES task_templates(id),
    responsible_user_id INTEGER REFERENCES users(user_id),
    parent_case_id INTEGER REFERENCES sag_sager(id),
    case_id INTEGER UNIQUE REFERENCES sag_sager(id),
    status TEXT NOT NULL DEFAULT 'draft' CHECK (status IN
        ('draft','scheduled','needs_parent','created','manual_review','cancelled','error')),
    last_error TEXT,
    created_by INTEGER NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE INDEX IF NOT EXISTS contact_lifecycle_contact_idx ON contact_lifecycle_events(contact_id);
CREATE INDEX IF NOT EXISTS contact_lifecycle_due_idx ON contact_lifecycle_events(event_date)
    WHERE auto_create AND case_id IS NULL AND status IN ('scheduled','error');