template-saas-database-v2/ddl/schemas/sales/02-tables.sql

-- ============================================
-- TEMPLATE-SAAS: Sales Tables
-- Version: 1.0.0
-- Module: SAAS-018
-- ============================================

-- ============================================
-- Pipeline Stages (configurable stages per tenant)
-- ============================================
CREATE TABLE sales.pipeline_stages (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,

    -- Stage info
    name VARCHAR(100) NOT NULL,
    position INT NOT NULL DEFAULT 0,
    color VARCHAR(7) DEFAULT '#3B82F6',

    -- Stage type flags
    is_won BOOLEAN DEFAULT FALSE,
    is_lost BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,

    -- Constraints
    CONSTRAINT unique_stage_position UNIQUE (tenant_id, position),
    CONSTRAINT check_win_lost_exclusive CHECK (NOT (is_won AND is_lost))
);

COMMENT ON TABLE sales.pipeline_stages IS 'Configurable pipeline stages per tenant';

-- ============================================
-- Leads (prospects)
-- ============================================
CREATE TABLE sales.leads (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,

    -- Contact info
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255),
    phone VARCHAR(50),

    -- Company info
    company VARCHAR(200),
    job_title VARCHAR(150),
    website VARCHAR(255),

    -- Lead classification
    source sales.lead_source DEFAULT 'other',
    status sales.lead_status DEFAULT 'new' NOT NULL,
    score INT DEFAULT 0 CHECK (score >= 0 AND score <= 100),

    -- Assignment
    assigned_to UUID REFERENCES users.users(id) ON DELETE SET NULL,

    -- Notes
    notes TEXT,

    -- Conversion tracking
    converted_at TIMESTAMPTZ,
    converted_to_opportunity_id UUID,

    -- Address
    address_line1 VARCHAR(255),
    address_line2 VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(100),

    -- Custom fields (JSON)
    custom_fields JSONB DEFAULT '{}'::jsonb,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    created_by UUID REFERENCES users.users(id) ON DELETE SET NULL,
    deleted_at TIMESTAMPTZ
);

COMMENT ON TABLE sales.leads IS 'Sales leads/prospects';
COMMENT ON COLUMN sales.leads.score IS 'Lead score from 0-100';
COMMENT ON COLUMN sales.leads.custom_fields IS 'Flexible custom fields per tenant';

-- ============================================
-- Opportunities (deals)
-- ============================================
CREATE TABLE sales.opportunities (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,

    -- Basic info
    name VARCHAR(255) NOT NULL,
    description TEXT,

    -- Lead reference (optional)
    lead_id UUID REFERENCES sales.leads(id) ON DELETE SET NULL,

    -- Pipeline stage
    stage sales.opportunity_stage DEFAULT 'prospecting' NOT NULL,
    stage_id UUID REFERENCES sales.pipeline_stages(id) ON DELETE SET NULL,

    -- Value
    amount DECIMAL(15, 2) DEFAULT 0,
    currency VARCHAR(3) DEFAULT 'USD',
    probability INT DEFAULT 0 CHECK (probability >= 0 AND probability <= 100),

    -- Timeline
    expected_close_date DATE,
    actual_close_date DATE,

    -- Assignment
    assigned_to UUID REFERENCES users.users(id) ON DELETE SET NULL,

    -- Win/Loss tracking
    won_at TIMESTAMPTZ,
    lost_at TIMESTAMPTZ,
    lost_reason VARCHAR(500),

    -- Contact info (can be different from lead)
    contact_name VARCHAR(200),
    contact_email VARCHAR(255),
    contact_phone VARCHAR(50),
    company_name VARCHAR(200),

    -- Notes
    notes TEXT,

    -- Custom fields
    custom_fields JSONB DEFAULT '{}'::jsonb,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    created_by UUID REFERENCES users.users(id) ON DELETE SET NULL,
    deleted_at TIMESTAMPTZ,

    -- Constraints
    CONSTRAINT check_close_status CHECK (
        (won_at IS NULL OR lost_at IS NULL) -- Cannot be both won and lost
    )
);

COMMENT ON TABLE sales.opportunities IS 'Sales opportunities/deals';
COMMENT ON COLUMN sales.opportunities.probability IS 'Win probability percentage (0-100)';

-- ============================================
-- Activities (calls, meetings, tasks, etc.)
-- ============================================
CREATE TABLE sales.activities (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES tenants.tenants(id) ON DELETE CASCADE,

    -- Activity type
    type sales.activity_type NOT NULL,
    status sales.activity_status DEFAULT 'pending' NOT NULL,

    -- Content
    subject VARCHAR(255) NOT NULL,
    description TEXT,

    -- Related records (at least one should be set)
    lead_id UUID REFERENCES sales.leads(id) ON DELETE CASCADE,
    opportunity_id UUID REFERENCES sales.opportunities(id) ON DELETE CASCADE,

    -- Scheduling
    due_date TIMESTAMPTZ,
    due_time TIME,
    duration_minutes INT,

    -- Completion
    completed_at TIMESTAMPTZ,
    outcome TEXT,

    -- Assignment
    assigned_to UUID REFERENCES users.users(id) ON DELETE SET NULL,
    created_by UUID REFERENCES users.users(id) ON DELETE SET NULL,

    -- For calls
    call_direction VARCHAR(10), -- 'inbound' or 'outbound'
    call_recording_url VARCHAR(500),

    -- For meetings
    location VARCHAR(255),
    meeting_url VARCHAR(500),
    attendees JSONB DEFAULT '[]'::jsonb,

    -- Reminder
    reminder_at TIMESTAMPTZ,
    reminder_sent BOOLEAN DEFAULT FALSE,

    -- Custom fields
    custom_fields JSONB DEFAULT '{}'::jsonb,

    -- Audit
    created_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    updated_at TIMESTAMPTZ DEFAULT NOW() NOT NULL,
    deleted_at TIMESTAMPTZ,

    -- Constraints
    CONSTRAINT check_related_record CHECK (
        lead_id IS NOT NULL OR opportunity_id IS NOT NULL
    )
);

COMMENT ON TABLE sales.activities IS 'Sales activities: calls, meetings, tasks, emails, notes';
COMMENT ON COLUMN sales.activities.attendees IS 'JSON array of meeting attendees';

-- ============================================
-- Triggers for updated_at
-- ============================================
CREATE OR REPLACE FUNCTION sales.update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_pipeline_stages_updated_at
    BEFORE UPDATE ON sales.pipeline_stages
    FOR EACH ROW
    EXECUTE FUNCTION sales.update_updated_at();

CREATE TRIGGER trg_leads_updated_at
    BEFORE UPDATE ON sales.leads
    FOR EACH ROW
    EXECUTE FUNCTION sales.update_updated_at();

CREATE TRIGGER trg_opportunities_updated_at
    BEFORE UPDATE ON sales.opportunities
    FOR EACH ROW
    EXECUTE FUNCTION sales.update_updated_at();

CREATE TRIGGER trg_activities_updated_at
    BEFORE UPDATE ON sales.activities
    FOR EACH ROW
    EXECUTE FUNCTION sales.update_updated_at();