leocrm/migration_0021.sql

-- Migration 0021: Unified contacts model

DROP TABLE IF EXISTS company_contacts CASCADE;
DROP TABLE IF EXISTS contacts CASCADE;
DROP TABLE IF EXISTS companies CASCADE;

-- Create contacts table (without contactperson FKs first)
CREATE TABLE contacts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    type VARCHAR(20) NOT NULL DEFAULT 'company',
    displayname VARCHAR(255) NOT NULL DEFAULT '',
    name VARCHAR(255),
    firstname VARCHAR(100),
    surname VARCHAR(100),
    surfix VARCHAR(50),
    ext_name_line VARCHAR(255),
    gender VARCHAR(20),
    code VARCHAR(100),
    accounting_code VARCHAR(100),
    vendor_accounting_code VARCHAR(100),
    mailing_street VARCHAR(255),
    mailing_number VARCHAR(20),
    mailing_unit_number VARCHAR(50),
    mailing_district VARCHAR(100),
    mailing_extra_address_line VARCHAR(255),
    mailing_postalcode VARCHAR(20),
    mailing_city VARCHAR(100),
    mailing_state VARCHAR(100),
    mailing_country VARCHAR(2),
    visit_street VARCHAR(255),
    visit_number VARCHAR(20),
    visit_unit_number VARCHAR(50),
    visit_district VARCHAR(100),
    visit_extra_address_line VARCHAR(255),
    visit_postalcode VARCHAR(20),
    visit_city VARCHAR(100),
    visit_state VARCHAR(100),
    invoice_street VARCHAR(255),
    invoice_number VARCHAR(20),
    invoice_unit_number VARCHAR(50),
    invoice_district VARCHAR(100),
    invoice_extra_address_line VARCHAR(255),
    invoice_postalcode VARCHAR(20),
    invoice_city VARCHAR(100),
    invoice_state VARCHAR(100),
    invoice_country VARCHAR(2),
    country VARCHAR(2),
    phone_1 VARCHAR(50),
    phone_2 VARCHAR(50),
    email_1 VARCHAR(255),
    email_2 VARCHAR(255),
    website VARCHAR(500),
    vat_code VARCHAR(50),
    fiscal_code VARCHAR(50),
    commerce_code VARCHAR(100),
    purchase_number VARCHAR(100),
    bic VARCHAR(50),
    bank_account VARCHAR(50),
    discount_crew FLOAT NOT NULL DEFAULT 0,
    discount_transport FLOAT NOT NULL DEFAULT 0,
    discount_rental FLOAT NOT NULL DEFAULT 0,
    discount_sale FLOAT NOT NULL DEFAULT 0,
    discount_subrent FLOAT NOT NULL DEFAULT 0,
    discount_total FLOAT NOT NULL DEFAULT 0,
    latitude FLOAT,
    longitude FLOAT,
    projectnote TEXT,
    projectnote_title VARCHAR(255),
    contact_warning TEXT,
    tags VARCHAR(500),
    image TEXT,
    custom JSON DEFAULT '{}'::json,
    search_tsv TSVECTOR GENERATED ALWAYS AS (
        to_tsvector('german',
            coalesce(name, '') || ' ' ||
            coalesce(displayname, '') || ' ' ||
            coalesce(firstname, '') || ' ' ||
            coalesce(surname, '') || ' ' ||
            coalesce(email_1, '') || ' ' ||
            coalesce(email_2, '') || ' ' ||
            coalesce(code, '') || ' ' ||
            coalesce(phone_1, '') || ' ' ||
            coalesce(phone_2, '') || ' ' ||
            coalesce(mailing_city, '') || ' ' ||
            coalesce(mailing_postalcode, '') || ' ' ||
            coalesce(tags, '')
        )
    ) STORED,
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ
);

CREATE INDEX ix_contacts_tenant_deleted ON contacts (tenant_id, deleted_at);
CREATE INDEX ix_contacts_tenant_type ON contacts (tenant_id, type);
CREATE INDEX ix_contacts_tenant_name ON contacts (tenant_id, name);
CREATE INDEX ix_contacts_tenant_displayname ON contacts (tenant_id, displayname);
CREATE INDEX ix_contacts_email ON contacts (email_1);
CREATE INDEX ix_contacts_code ON contacts (code);
CREATE INDEX ix_contacts_search_vec ON contacts USING gin (search_tsv);

-- Create contactpersons table
CREATE TABLE contactpersons (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL,
    contact_id UUID NOT NULL REFERENCES contacts(id) ON DELETE CASCADE,
    displayname VARCHAR(255) NOT NULL DEFAULT '',
    firstname VARCHAR(100),
    middle_name VARCHAR(100),
    lastname VARCHAR(100),
    function VARCHAR(255),
    phone VARCHAR(50),
    mobilephone VARCHAR(50),
    email VARCHAR(255),
    street VARCHAR(255),
    number VARCHAR(20),
    postalcode VARCHAR(20),
    city VARCHAR(100),
    state VARCHAR(100),
    country VARCHAR(2),
    tags VARCHAR(500),
    custom JSON DEFAULT '{}'::json,
    created_by UUID REFERENCES users(id) ON DELETE SET NULL,
    updated_by UUID REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    deleted_at TIMESTAMPTZ
);

CREATE INDEX ix_contactpersons_tenant_deleted ON contactpersons (tenant_id, deleted_at);
CREATE INDEX ix_contactpersons_contact ON contactpersons (contact_id);
CREATE INDEX ix_contactpersons_email ON contactpersons (email);

-- Add FK columns to contacts referencing contactpersons
ALTER TABLE contacts ADD COLUMN default_person_id UUID REFERENCES contactpersons(id) ON DELETE SET NULL;
ALTER TABLE contacts ADD COLUMN admin_contactperson_id UUID REFERENCES contactpersons(id) ON DELETE SET NULL;

-- Update alembic version
UPDATE alembic_version SET version_num = '0021_unified_contacts';