-- ============================================================
-- Migration: 020_external_contacts.sql
-- Description: Baza kontaktów zewnętrznych (urzędy, instytucje, partnerzy)
-- Author: Maciej Pienczyn
-- Date: 2026-01-27
-- ============================================================

-- Tabela kontaktów zewnętrznych
CREATE TABLE IF NOT EXISTS external_contacts (
    id SERIAL PRIMARY KEY,

    -- Dane osobowe
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    position VARCHAR(200),              -- Stanowisko (opcjonalne)
    photo_url VARCHAR(500),             -- Zdjęcie osoby (opcjonalne)

    -- Dane kontaktowe
    phone VARCHAR(50),
    phone_secondary VARCHAR(50),        -- Drugi numer telefonu
    email VARCHAR(255),
    website VARCHAR(500),               -- Strona osobista/wizytówka

    -- Social Media
    linkedin_url VARCHAR(500),
    facebook_url VARCHAR(500),
    twitter_url VARCHAR(500),

    -- Organizacja
    organization_name VARCHAR(300) NOT NULL,
    organization_type VARCHAR(50) DEFAULT 'other',
    -- Typy: government (urząd), agency (agencja), company (firma), ngo (organizacja), university (uczelnia), other

    organization_address VARCHAR(500),
    organization_website VARCHAR(500),
    organization_logo_url VARCHAR(500),

    -- Kontekst/Projekt
    project_name VARCHAR(300),          -- Nazwa projektu (Tytani, EJ Choczewo, itp.)
    project_description TEXT,           -- Krótki opis kontekstu

    -- Źródło kontaktu
    source_type VARCHAR(50),            -- announcement, forum_post, manual
    source_id INTEGER,                  -- ID ogłoszenia lub wpisu (opcjonalne)
    source_url VARCHAR(500),            -- URL do źródła

    -- Powiązane linki (artykuły, strony, dokumenty) - JSON array
    -- Format: [{"title": "Artykuł o...", "url": "https://...", "type": "article"}, ...]
    related_links JSONB DEFAULT '[]',

    -- Tagi do wyszukiwania
    tags VARCHAR(500),                  -- Tagi oddzielone przecinkami

    -- Notatki
    notes TEXT,

    -- Audyt
    created_by INTEGER REFERENCES users(id) ON DELETE SET NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

    -- Status
    is_active BOOLEAN DEFAULT TRUE,
    is_verified BOOLEAN DEFAULT FALSE   -- Zweryfikowany przez admina/moderatora
);

-- Indeksy dla szybkiego wyszukiwania
CREATE INDEX IF NOT EXISTS idx_external_contacts_name ON external_contacts(last_name, first_name);
CREATE INDEX IF NOT EXISTS idx_external_contacts_organization ON external_contacts(organization_name);
CREATE INDEX IF NOT EXISTS idx_external_contacts_org_type ON external_contacts(organization_type);
CREATE INDEX IF NOT EXISTS idx_external_contacts_project ON external_contacts(project_name);
CREATE INDEX IF NOT EXISTS idx_external_contacts_active ON external_contacts(is_active);

-- Full-text search index (PostgreSQL) - używamy 'simple' dla kompatybilności
CREATE INDEX IF NOT EXISTS idx_external_contacts_search ON external_contacts
USING gin(to_tsvector('simple',
    COALESCE(first_name, '') || ' ' ||
    COALESCE(last_name, '') || ' ' ||
    COALESCE(organization_name, '') || ' ' ||
    COALESCE(project_name, '') || ' ' ||
    COALESCE(tags, '')
));

-- Indeks na JSONB dla related_links
CREATE INDEX IF NOT EXISTS idx_external_contacts_links ON external_contacts USING gin(related_links);

-- Uprawnienia
GRANT ALL ON TABLE external_contacts TO nordabiz_app;
GRANT USAGE, SELECT ON SEQUENCE external_contacts_id_seq TO nordabiz_app;

-- Komentarze
COMMENT ON TABLE external_contacts IS 'Baza kontaktów zewnętrznych - urzędy, instytucje, partnerzy projektów';
COMMENT ON COLUMN external_contacts.organization_type IS 'Typ: government, agency, company, ngo, university, other';
COMMENT ON COLUMN external_contacts.source_type IS 'Źródło: announcement, forum_post, manual';
COMMENT ON COLUMN external_contacts.tags IS 'Tagi do wyszukiwania, oddzielone przecinkami';
COMMENT ON COLUMN external_contacts.related_links IS 'JSON array z linkami: [{"title": "...", "url": "...", "type": "article|document|video"}]';