erp-core-database/ddl/16-partners.sql

-- =============================================================
-- ARCHIVO: 16-partners.sql
-- DESCRIPCION: Partners (clientes, proveedores, contactos)
-- VERSION: 1.0.0
-- PROYECTO: ERP-Core V2
-- FECHA: 2026-01-13
-- =============================================================

-- =====================
-- SCHEMA: partners
-- =====================
CREATE SCHEMA IF NOT EXISTS partners;

-- =====================
-- TABLA: partners
-- Clientes, proveedores, y otros socios comerciales
-- =====================
CREATE TABLE IF NOT EXISTS partners.partners (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id UUID NOT NULL REFERENCES auth.tenants(id) ON DELETE CASCADE,

    -- Identificacion
    code VARCHAR(30) NOT NULL,
    name VARCHAR(200) NOT NULL,
    display_name VARCHAR(200),

    -- Tipo de partner
    partner_type VARCHAR(20) NOT NULL DEFAULT 'customer', -- customer, supplier, both, contact

    -- Datos fiscales
    tax_id VARCHAR(50),          -- RFC en Mexico
    tax_id_type VARCHAR(20),     -- rfc_moral, rfc_fisica, extranjero
    legal_name VARCHAR(200),

    -- Contacto principal
    email VARCHAR(255),
    phone VARCHAR(30),
    mobile VARCHAR(30),
    website VARCHAR(255),

    -- Credito y pagos
    credit_limit DECIMAL(15, 2) DEFAULT 0,
    payment_term_days INTEGER DEFAULT 0,
    payment_method VARCHAR(50),  -- cash, transfer, credit_card, check

    -- Clasificacion
    category VARCHAR(50),        -- retail, wholesale, government, etc.
    tags TEXT[] DEFAULT '{}',

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMPTZ,

    -- Configuracion
    settings JSONB DEFAULT '{}',
    -- Ejemplo: {"send_reminders": true, "preferred_contact": "email"}

    -- Notas
    notes TEXT,

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    created_by UUID REFERENCES auth.users(id),
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_by UUID REFERENCES auth.users(id),
    deleted_at TIMESTAMPTZ,

    UNIQUE(tenant_id, code)
);

-- Indices para partners
CREATE INDEX IF NOT EXISTS idx_partners_tenant ON partners.partners(tenant_id);
CREATE INDEX IF NOT EXISTS idx_partners_code ON partners.partners(code);
CREATE INDEX IF NOT EXISTS idx_partners_type ON partners.partners(partner_type);
CREATE INDEX IF NOT EXISTS idx_partners_tax_id ON partners.partners(tax_id);
CREATE INDEX IF NOT EXISTS idx_partners_active ON partners.partners(is_active) WHERE is_active = TRUE;
CREATE INDEX IF NOT EXISTS idx_partners_email ON partners.partners(email);
CREATE INDEX IF NOT EXISTS idx_partners_name ON partners.partners USING gin(to_tsvector('spanish', name));

-- =====================
-- TABLA: partner_addresses
-- Direcciones de partners (facturacion, envio, etc.)
-- =====================
CREATE TABLE IF NOT EXISTS partners.partner_addresses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    partner_id UUID NOT NULL REFERENCES partners.partners(id) ON DELETE CASCADE,

    -- Tipo de direccion
    address_type VARCHAR(20) NOT NULL DEFAULT 'billing', -- billing, shipping, main, other

    -- Direccion
    address_line1 VARCHAR(200) NOT NULL,
    address_line2 VARCHAR(200),
    city VARCHAR(100) NOT NULL,
    state VARCHAR(100),
    postal_code VARCHAR(20),
    country VARCHAR(3) DEFAULT 'MEX',

    -- Contacto en esta direccion
    contact_name VARCHAR(100),
    contact_phone VARCHAR(30),
    contact_email VARCHAR(255),

    -- Referencia
    reference TEXT,

    -- Geolocalizacion
    latitude DECIMAL(10, 8),
    longitude DECIMAL(11, 8),

    -- Estado
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ
);

-- Indices para partner_addresses
CREATE INDEX IF NOT EXISTS idx_partner_addresses_partner ON partners.partner_addresses(partner_id);
CREATE INDEX IF NOT EXISTS idx_partner_addresses_type ON partners.partner_addresses(address_type);
CREATE INDEX IF NOT EXISTS idx_partner_addresses_default ON partners.partner_addresses(partner_id, is_default) WHERE is_default = TRUE;

-- =====================
-- TABLA: partner_contacts
-- Contactos individuales de un partner
-- =====================
CREATE TABLE IF NOT EXISTS partners.partner_contacts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    partner_id UUID NOT NULL REFERENCES partners.partners(id) ON DELETE CASCADE,

    -- Datos personales
    name VARCHAR(200) NOT NULL,
    job_title VARCHAR(100),
    department VARCHAR(100),

    -- Contacto
    email VARCHAR(255),
    phone VARCHAR(30),
    mobile VARCHAR(30),

    -- Rol
    contact_type VARCHAR(20) DEFAULT 'general', -- general, billing, purchasing, sales, technical
    is_primary BOOLEAN DEFAULT FALSE,

    -- Estado
    is_active BOOLEAN DEFAULT TRUE,

    -- Notas
    notes TEXT,

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ
);

-- Indices para partner_contacts
CREATE INDEX IF NOT EXISTS idx_partner_contacts_partner ON partners.partner_contacts(partner_id);
CREATE INDEX IF NOT EXISTS idx_partner_contacts_type ON partners.partner_contacts(contact_type);
CREATE INDEX IF NOT EXISTS idx_partner_contacts_primary ON partners.partner_contacts(partner_id, is_primary) WHERE is_primary = TRUE;
CREATE INDEX IF NOT EXISTS idx_partner_contacts_email ON partners.partner_contacts(email);

-- =====================
-- TABLA: partner_bank_accounts
-- Cuentas bancarias de partners
-- =====================
CREATE TABLE IF NOT EXISTS partners.partner_bank_accounts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    partner_id UUID NOT NULL REFERENCES partners.partners(id) ON DELETE CASCADE,

    -- Datos bancarios
    bank_name VARCHAR(100) NOT NULL,
    account_number VARCHAR(50),
    clabe VARCHAR(20),           -- CLABE para Mexico
    swift_code VARCHAR(20),
    iban VARCHAR(50),

    -- Titular
    account_holder VARCHAR(200),

    -- Estado
    is_default BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE,

    -- Metadata
    created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMPTZ
);

-- Indices para partner_bank_accounts
CREATE INDEX IF NOT EXISTS idx_partner_bank_accounts_partner ON partners.partner_bank_accounts(partner_id);
CREATE INDEX IF NOT EXISTS idx_partner_bank_accounts_default ON partners.partner_bank_accounts(partner_id, is_default) WHERE is_default = TRUE;

-- =====================
-- COMENTARIOS
-- =====================
COMMENT ON TABLE partners.partners IS 'Clientes, proveedores y otros socios comerciales del negocio';
COMMENT ON COLUMN partners.partners.partner_type IS 'Tipo: customer (cliente), supplier (proveedor), both (ambos), contact (contacto)';
COMMENT ON COLUMN partners.partners.tax_id IS 'Identificacion fiscal (RFC en Mexico)';
COMMENT ON COLUMN partners.partners.credit_limit IS 'Limite de credito en moneda local';
COMMENT ON COLUMN partners.partners.payment_term_days IS 'Dias de plazo para pago';

COMMENT ON TABLE partners.partner_addresses IS 'Direcciones asociadas a un partner (facturacion, envio, etc.)';
COMMENT ON COLUMN partners.partner_addresses.address_type IS 'Tipo: billing, shipping, main, other';

COMMENT ON TABLE partners.partner_contacts IS 'Personas de contacto individuales de un partner';
COMMENT ON COLUMN partners.partner_contacts.contact_type IS 'Rol: general, billing, purchasing, sales, technical';

COMMENT ON TABLE partners.partner_bank_accounts IS 'Cuentas bancarias de partners para pagos/cobros';