BEGIN;

-- ── Pipeline status on customers ──
ALTER TABLE customers ADD COLUMN pipeline_status VARCHAR(20) DEFAULT 'lead'
    CHECK (pipeline_status IN ('lead', 'quoted', 'scheduled', 'completed'));

-- ── Reminders ──
CREATE TABLE reminders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    assigned_to UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    created_by UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    due_at TIMESTAMPTZ NOT NULL,
    note TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'done', 'dismissed')),
    completed_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_reminders_assigned ON reminders(assigned_to, status, due_at);
CREATE INDEX idx_reminders_customer ON reminders(customer_id);
CREATE INDEX idx_reminders_due ON reminders(due_at) WHERE status = 'pending';

-- ── Customer Activity Log ──
CREATE TABLE customer_activity (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    customer_id UUID NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR(30) DEFAULT 'note' CHECK (type IN ('note', 'call', 'visit', 'email', 'status_change', 'system')),
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_activity_customer ON customer_activity(customer_id, created_at DESC);

VALUES ('004', 'CRM: pipeline status, reminders, activity log');

COMMIT;
