-- Migration: Company Contacts with Source and Purpose
-- Date: 2025-12-29
-- Description: Adds table for multiple company contacts (phones, emails) with metadata

-- ============================================================
-- 1. COMPANY CONTACTS TABLE
-- ============================================================
CREATE TABLE IF NOT EXISTS company_contacts (
    id SERIAL PRIMARY KEY,
    company_id INTEGER NOT NULL REFERENCES companies(id) ON DELETE CASCADE,

    -- Contact type: 'phone', 'email', 'fax', 'mobile'
    contact_type VARCHAR(20) NOT NULL,

    -- Contact value (phone number or email address)
    value VARCHAR(255) NOT NULL,

    -- Purpose/description: 'Biuro', 'Sprzedaż', 'Właściciel', 'Transport', 'Serwis', etc.
    purpose VARCHAR(100),

    -- Is this the primary contact of this type?
    is_primary BOOLEAN DEFAULT FALSE,

    -- Source of this contact data
    source VARCHAR(100), -- 'website', 'krs', 'google_business', 'facebook', 'manual', 'brave_search'
    source_url VARCHAR(500), -- URL where the contact was found
    source_date DATE, -- When the contact was found/verified

    -- Validation
    is_verified BOOLEAN DEFAULT FALSE,
    verified_at TIMESTAMP,
    verified_by VARCHAR(100),

    -- Metadata
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),

    -- Unique constraint to avoid duplicates
    UNIQUE (company_id, contact_type, value)
);

-- Indexes
CREATE INDEX IF NOT EXISTS idx_company_contacts_company ON company_contacts(company_id);
CREATE INDEX IF NOT EXISTS idx_company_contacts_type ON company_contacts(contact_type);
CREATE INDEX IF NOT EXISTS idx_company_contacts_primary ON company_contacts(company_id, contact_type, is_primary) WHERE is_primary = TRUE;

-- ============================================================
-- 2. COMMENTS
-- ============================================================
COMMENT ON TABLE company_contacts IS 'Multiple contacts (phones, emails) per company with source tracking';
COMMENT ON COLUMN company_contacts.contact_type IS 'Type: phone, email, fax, mobile';
COMMENT ON COLUMN company_contacts.purpose IS 'Purpose: Biuro, Sprzedaż, Właściciel, Transport, Serwis, Księgowość, etc.';
COMMENT ON COLUMN company_contacts.source IS 'Source: website, krs, google_business, facebook, manual, brave_search';

-- ============================================================
-- 3. Verify migration
-- ============================================================
DO $$
BEGIN
    RAISE NOTICE 'Migration 20251229_company_contacts completed successfully';
END $$;