CRM

CREATE TYPE deal_status AS ENUM ('open', 'won', 'lost');

CREATE TYPE activity_kind AS ENUM ('call', 'email', 'meeting', 'note', 'task');

CREATE TABLE users (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    email text NOT NULL UNIQUE,
    name text NOT NULL
);

CREATE TABLE companies (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL,
    website text,
    industry text,
    owner_id uuid,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX companies_owner_id_idx ON companies (owner_id);

CREATE TABLE contacts (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    company_id uuid,
    full_name text NOT NULL,
    email text,
    phone text,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX contacts_company_id_idx ON contacts (company_id);

CREATE TABLE pipelines (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE pipeline_stages (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    pipeline_id uuid NOT NULL,
    name text NOT NULL,
    position int NOT NULL,
    win_probability int DEFAULT 0 NOT NULL,
    UNIQUE (pipeline_id, position),
    CHECK (win_probability between 0 and 100)
);

CREATE TABLE deals (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    company_id uuid,
    contact_id uuid,
    stage_id uuid NOT NULL,
    owner_id uuid,
    title text NOT NULL,
    amount numeric(12,2),
    status deal_status DEFAULT 'open' NOT NULL,
    expected_close date,
    created_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX deals_stage_idx ON deals (stage_id);
CREATE INDEX deals_company_id_idx ON deals (company_id);
CREATE INDEX deals_contact_id_idx ON deals (contact_id);
CREATE INDEX deals_owner_id_idx ON deals (owner_id);

CREATE TABLE activities (
    id uuid DEFAULT gen_random_uuid() PRIMARY KEY,
    deal_id uuid,
    contact_id uuid,
    user_id uuid,
    kind activity_kind NOT NULL,
    summary text NOT NULL,
    happened_at timestamptz DEFAULT now() NOT NULL
);
CREATE INDEX activities_deal_idx ON activities (deal_id, happened_at);
CREATE INDEX activities_contact_id_idx ON activities (contact_id);
CREATE INDEX activities_user_id_idx ON activities (user_id);

ALTER TABLE companies ADD FOREIGN KEY (owner_id) REFERENCES users (id) ON DELETE SET NULL;

ALTER TABLE contacts ADD FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE SET NULL;

ALTER TABLE pipeline_stages ADD FOREIGN KEY (pipeline_id) REFERENCES pipelines (id) ON DELETE CASCADE;

ALTER TABLE deals ADD FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE SET NULL;

ALTER TABLE deals ADD FOREIGN KEY (contact_id) REFERENCES contacts (id) ON DELETE SET NULL;

ALTER TABLE deals ADD FOREIGN KEY (stage_id) REFERENCES pipeline_stages (id);

ALTER TABLE deals ADD FOREIGN KEY (owner_id) REFERENCES users (id) ON DELETE SET NULL;

ALTER TABLE activities ADD FOREIGN KEY (deal_id) REFERENCES deals (id) ON DELETE CASCADE;

ALTER TABLE activities ADD FOREIGN KEY (contact_id) REFERENCES contacts (id) ON DELETE CASCADE;

ALTER TABLE activities ADD FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL;