-- Migration 003: Udvid customers tabel med felter fra OmniSync
-- Dato: 6. december 2025

-- Tilføj nye kolonner til customers tabel
ALTER TABLE customers 
ADD COLUMN IF NOT EXISTS cvr_number VARCHAR(20) UNIQUE,
ADD COLUMN IF NOT EXISTS email_domain VARCHAR(255),
ADD COLUMN IF NOT EXISTS city VARCHAR(100),
ADD COLUMN IF NOT EXISTS postal_code VARCHAR(10),
ADD COLUMN IF NOT EXISTS country VARCHAR(2) DEFAULT 'DK',
ADD COLUMN IF NOT EXISTS website VARCHAR(255),
ADD COLUMN IF NOT EXISTS is_active BOOLEAN DEFAULT TRUE,

-- vTiger integration
ADD COLUMN IF NOT EXISTS vtiger_id VARCHAR(50) UNIQUE,
ADD COLUMN IF NOT EXISTS vtiger_account_no VARCHAR(50),
ADD COLUMN IF NOT EXISTS last_synced_at TIMESTAMP,

-- E-conomic integration
ADD COLUMN IF NOT EXISTS economic_customer_number INTEGER,
ADD COLUMN IF NOT EXISTS payment_terms_number INTEGER,
ADD COLUMN IF NOT EXISTS payment_terms_type VARCHAR(50),
ADD COLUMN IF NOT EXISTS vat_zone_number INTEGER,
ADD COLUMN IF NOT EXISTS customer_group_number INTEGER,
ADD COLUMN IF NOT EXISTS barred BOOLEAN DEFAULT FALSE,
ADD COLUMN IF NOT EXISTS currency_code VARCHAR(3) DEFAULT 'DKK',
ADD COLUMN IF NOT EXISTS ean VARCHAR(13),
ADD COLUMN IF NOT EXISTS public_entry_number VARCHAR(50),

-- Additional contact info
ADD COLUMN IF NOT EXISTS invoice_email VARCHAR(255),
ADD COLUMN IF NOT EXISTS mobile_phone VARCHAR(50),

-- Customer relationships
ADD COLUMN IF NOT EXISTS parent_customer_id INTEGER REFERENCES customers(id),
ADD COLUMN IF NOT EXISTS billing_customer_id INTEGER REFERENCES customers(id);

-- Opret nye indexes
CREATE INDEX IF NOT EXISTS idx_customers_cvr ON customers(cvr_number);
CREATE INDEX IF NOT EXISTS idx_customers_vtiger ON customers(vtiger_id);
CREATE INDEX IF NOT EXISTS idx_customers_economic ON customers(economic_customer_number);
CREATE INDEX IF NOT EXISTS idx_customers_active ON customers(is_active);
CREATE INDEX IF NOT EXISTS idx_customers_parent ON customers(parent_customer_id);
CREATE INDEX IF NOT EXISTS idx_customers_billing ON customers(billing_customer_id);
CREATE INDEX IF NOT EXISTS idx_customers_city ON customers(city);
CREATE INDEX IF NOT EXISTS idx_customers_name ON customers(name);

-- Opdater trigger function hvis den ikke findes
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Tilføj trigger til customers (hvis ikke allerede eksisterer)
DROP TRIGGER IF EXISTS update_customers_updated_at ON customers;
CREATE TRIGGER update_customers_updated_at
    BEFORE UPDATE ON customers
    FOR EACH ROW
    EXECUTE FUNCTION update_updated_at_column();

-- Tilføj kommentarer til dokumentation
COMMENT ON COLUMN customers.cvr_number IS 'Danish CVR (company registration) number';
COMMENT ON COLUMN customers.email_domain IS 'Primary email domain for customer';
COMMENT ON COLUMN customers.vtiger_id IS 'Reference to vTiger CRM account ID';
COMMENT ON COLUMN customers.economic_customer_number IS 'Reference to e-conomic customer number';
COMMENT ON COLUMN customers.parent_customer_id IS 'Parent company if part of a group';
COMMENT ON COLUMN customers.billing_customer_id IS 'Billing account if different from this customer';
COMMENT ON COLUMN customers.barred IS 'Whether customer is barred from ordering';