#!/usr/bin/env python3
"""
Import nowych członków NORDA z pliku Excel
Źródło: Aktualna lista kontaktow wraz z data przystapienia.xlsx
Data importu: 2026-01-13
Przesłane przez: Artur Wiertel

Ten skrypt:
1. Dodaje nowe źródło danych "Excel - Lista członków NORDA (2026-01-13)"
2. Importuje nowe firmy z oznaczeniem źródła
3. Aktualizuje istniejące firmy (dla starych 80 firm oznacza źródło)
"""

import os
import sys
import re
from datetime import datetime

# Dodaj ścieżkę do głównego katalogu aplikacji
sys.path.insert(0, os.path.dirname(os.path.dirname(os.path.abspath(__file__))))

from sqlalchemy import create_engine, text
from sqlalchemy.orm import sessionmaker

# Konfiguracja bazy danych - używaj zmiennej środowiskowej
DATABASE_URL = os.getenv('DATABASE_URL', 'postgresql://nordabiz_app:CHANGE_ME@127.0.0.1:5432/nordabiz')

# Źródło danych
DATA_SOURCE_NAME = "Excel - Lista członków NORDA"
DATA_SOURCE_DESC = "Import z pliku Excel od Artura Wiertela (2026-01-13). Plik: Aktualna lista kontaktow wraz z data przystapienia.xlsx"
DATA_SOURCE_TYPE = "excel"
IMPORT_DATE = "2026-01-13"

# Nowe firmy do dodania (z Excela)
NEW_COMPANIES = [
    {
        "name": "Dom Dziecka Pro-Sport",
        "legal_name": "Dom Dziecka",
        "contact_person": "Marek Mroske",
        "phone": "604178533",
        "email": "marekm@prosport.wejher.pl",
        "address_full": "ul. Sobieskiego 215, 84-200 Wejherowo",
        "address_city": "Wejherowo",
        "address_postal": "84-200",
        "joined": "1997-10-20",
        "category": "Other",
    },
    {
        "name": "Event Investycje",
        "legal_name": "Event Investycje Sp. z o.o.",
        "contact_person": "Krzysztof Bombera",
        "phone": "790272510",
        "email": "krzysztof@bombera.pl",
        "address_full": "ul. Kręckiego 3/5, 80-318 Gdańsk",
        "address_city": "Gdańsk",
        "address_postal": "80-318",
        "joined": "2016-11-06",
        "category": "Services",
    },
    {
        "name": "Kancelaria Notarialna Henryk Mizak",
        "legal_name": "Kancelaria Notarialna",
        "contact_person": "Henryk Mizak",
        "phone": None,
        "email": "notariat306@wp.pl",
        "address_full": "ul. Sobieskiego 306, 84-200 Wejherowo",
        "address_city": "Wejherowo",
        "address_postal": "84-200",
        "joined": None,
        "category": "Services",
    },
    {
        "name": "Orlex Design",
        "legal_name": "Orlex Design",
        "contact_person": "Michał Gębarowski",
        "phone": "601318227",
        "email": "biuro@orlexbeton.pl",
        "address_full": "ul. Mickiewicza 7, 84-241 Gościcino",
        "address_city": "Gościcino",
        "address_postal": "84-241",
        "joined": "2018-06-26",
        "category": "Construction",
    },
    {
        "name": "PTHU Cezary Mazur",
        "legal_name": "PTHU Cezary Mazur",
        "contact_person": "Cezary Mazur",
        "phone": "601652556",
        "email": "pthumazur@gmail.com",
        "address_full": "ul. Grunwaldzka 12, 84-230 Rumia",
        "address_city": "Rumia",
        "address_postal": "84-230",
        "joined": "2023-04-05",
        "category": "Trade",
    },
    {
        "name": "Podróże i My",
        "legal_name": "Podróże i My",
        "contact_person": "Witold Gulcz",
        "phone": "602697969",
        "email": "w.gulcz@itakarumia.pl",
        "address_full": "ul. Wierzbięcice 44A/21B, 61-583 Poznań",
        "address_city": "Poznań",
        "address_postal": "61-583",
        "joined": "2018-11-06",
        "category": "Services",
    },
    {
        "name": "Kancelaria Radcy Prawnego Joanna Ostrowska",
        "legal_name": "Radca Prawny Joanna Ostrowska",
        "contact_person": "Joanna Ostrowska",
        "phone": None,
        "email": None,
        "address_full": None,
        "address_city": "Wejherowo",
        "address_postal": None,
        "joined": None,
        "category": "Services",
    },
    {
        "name": "PZU TFI",
        "legal_name": "PZU SA Towarzystwo Funduszy Inwestycyjnych",
        "contact_person": "Łukasz Antoniak",
        "phone": "887875775",
        "email": "lantoniak@pzu.pl",
        "address_full": "Rondo I. Daszyński 4, 00-843 Warszawa",
        "address_city": "Warszawa",
        "address_postal": "00-843",
        "joined": "2019-02-05",
        "category": "Services",
    },
    {
        "name": "Rozsądni Bracia",
        "legal_name": "Rozsądni Bracia Sp. z o.o.",
        "contact_person": "Robert Kiereś, Piotr Kiereś, Michał Kiereś",
        "phone": "737856865",
        "email": "kontakt@rozsandnibracia.pl",
        "address_full": "ul. Wałowa 30/8, 84-200 Wejherowo",
        "address_city": "Wejherowo",
        "address_postal": "84-200",
        "joined": "2024-05-08",
        "category": "Services",
    },
    {
        "name": "TERMO",
        "legal_name": "TERMO Sp. z o.o.",
        "contact_person": "Bartłomiej Komkowski",
        "phone": "503177099",
        "email": "biuro@termocenter.pl",
        "address_full": "ul. Tulipanowa 14, 84-252 Góra",
        "address_city": "Góra",
        "address_postal": "84-252",
        "joined": "2025-04-01",
        "category": "Construction",
    },
    {
        "name": "Thai Union Poland",
        "legal_name": "Thai Union",
        "contact_person": "Piotr Sadowski",
        "phone": None,
        "email": None,
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": None,
        "category": "Production",
    },
    {
        "name": "Usługi Ogólnobudowlane Sławomir Grula",
        "legal_name": "Usługi Ogólnobudowlane",
        "contact_person": "Sławomir Grula",
        "phone": "502919634",
        "email": "slawek1055@onet.pl",
        "address_full": "ul. Wiejska 6, 84-218 Łęczyce",
        "address_city": "Łęczyce",
        "address_postal": "84-218",
        "joined": None,
        "category": "Construction",
    },
    {
        "name": "Wodmel",
        "legal_name": "Wodmel Sp. J.",
        "contact_person": "Tomasz Potrykus, Jan Derra",
        "phone": None,
        "email": "wodmel@op.pl",
        "address_full": "ul. Przemysłowa 31A, 84-200 Wejherowo",
        "address_city": "Wejherowo",
        "address_postal": "84-200",
        "joined": "1997-10-14",
        "category": "Construction",
    },
    {
        "name": "IT Space",
        "legal_name": "IT Space B & Łaga Bartosz",
        "contact_person": "Bartosz Łaga",
        "phone": None,
        "email": "vwmania6@gmail.com",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-08-01",
        "category": "IT",
    },
    {
        "name": "Aertom",
        "legal_name": "Aertom",
        "contact_person": "Tomasz Dzienisz",
        "phone": None,
        "email": "dzienisztomasz@gmail.com",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-08-01",
        "category": "Other",
    },
    {
        "name": "Wikęd",
        "legal_name": "Wikęd",
        "contact_person": "Grzegorz Wiśniewski",
        "phone": None,
        "email": "gwiked@wp.pl",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-08-01",
        "category": "Production",
    },
    {
        "name": "Unimot",
        "legal_name": "Unimot",
        "contact_person": "Łukasz Pawłowski, Tomasz Barejka",
        "phone": "509109999",
        "email": "Tomasz.barejka@unimot-eig.pl",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-11-01",
        "category": "Trade",
    },
    {
        "name": "Omega Energy",
        "legal_name": "Omega",
        "contact_person": "Piotr Karbowski",
        "phone": "601679494",
        "email": "p.karbowski@omega-energy.eu",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-11-01",
        "category": "Production",
    },
    {
        "name": "Nowatel",
        "legal_name": "Nowatel",
        "contact_person": "Łukasz Dominik, Stanisław Czech",
        "phone": "607623100",
        "email": "biuro@nowatel.com",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-11-01",
        "category": "IT",
    },
    {
        "name": "Informatyk1",
        "legal_name": "Informatyk1",
        "contact_person": "Mateusz Kurpet",
        "phone": "602354351",
        "email": "mateusz@informatyk1.pl",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-11-01",
        "category": "IT",
    },
    {
        "name": "Piotrex",
        "legal_name": "Piotrex",
        "contact_person": "Piotr Wieczorek",
        "phone": "518842122",
        "email": "biuro@piotrex.info",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-12-01",
        "category": "Other",
    },
    {
        "name": "Kancelaria Ostrowski i Wspólnicy",
        "legal_name": "Kancelaria Ostrowski i wspólnicy",
        "contact_person": "Paweł Cioban",
        "phone": "727591189",
        "email": "p.cioban@ostrowski-legal.net",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-12-01",
        "category": "Services",
    },
    {
        "name": "Family Art",
        "legal_name": "Family Art",
        "contact_person": "Artur Czaja",
        "phone": "531972354",
        "email": "artczaj@gmail.com",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-12-01",
        "category": "Other",
    },
    {
        "name": "Renk Hurtownie",
        "legal_name": "Renk – Pomorskie Hurtownie Centrum Rolno-Spożywcze",
        "contact_person": "Marcin Bulczak",
        "phone": "504228997",
        "email": "sekretariat@renk.pl",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-12-01",
        "category": "Trade",
    },
    {
        "name": "Elzit",
        "legal_name": "Elzit",
        "contact_person": "Bartosz Lang",
        "phone": "603089984",
        "email": "b.lang@elzit.pl",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-11-01",
        "category": "IT",
    },
    {
        "name": "Your Welcome",
        "legal_name": "Your Welcome",
        "contact_person": "Agnieszka Kulinkowska",
        "phone": "519345844",
        "email": "agnieszka@yourewelcome.pl",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-11-01",
        "category": "Services",
    },
    {
        "name": "3W",
        "legal_name": "3W",
        "contact_person": "Maciej Kasyna",
        "phone": "664019585",
        "email": "maciej.kasyna@3wdb.pl",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-11-01",
        "category": "IT",
    },
    {
        "name": "Wakat",
        "legal_name": "Wakat",
        "contact_person": "Dariusz Krasiński",
        "phone": "600447635",
        "email": "darek.krasinski@wp.pl",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-11-01",
        "category": "Other",
    },
    {
        "name": "BIS Maszyny",
        "legal_name": "BIS Maszyny",
        "contact_person": "Jarosław Rohraff",
        "phone": "602228400",
        "email": "jarek@bis-bau.pl",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-11-01",
        "category": "Trade",
    },
    {
        "name": "Elgreen EM",
        "legal_name": "Elgreen EM P.S.A.",
        "contact_person": "Krzysztof Zanaboni",
        "phone": "514254267",
        "email": "kz@elgreen.pl",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2025-10-01",
        "category": "Production",
    },
    {
        "name": "Iwona Spaleniak Coaching",
        "legal_name": "Iwona Spaleniak Coach 4 you",
        "contact_person": "Iwona Spaleniak",
        "phone": "608336529",
        "email": "kontakt@iwonaspaleniak.pl",
        "address_full": None,
        "address_city": None,
        "address_postal": None,
        "joined": "2026-01-01",
        "category": "Services",
    },
]

# Kategorie (mapowanie nazwa -> id)
CATEGORY_MAP = {
    "IT": 1,
    "Construction": 2,
    "Services": 3,
    "Production": 4,
    "Trade": 5,
    "Other": 6,
}


def slugify(text):
    """Generuje slug z nazwy firmy"""
    text = text.lower()
    # Zamień polskie znaki
    replacements = {
        'ą': 'a', 'ć': 'c', 'ę': 'e', 'ł': 'l', 'ń': 'n',
        'ó': 'o', 'ś': 's', 'ź': 'z', 'ż': 'z'
    }
    for pl, en in replacements.items():
        text = text.replace(pl, en)
    # Usuń znaki specjalne, zostaw tylko litery, cyfry i spacje
    text = re.sub(r'[^a-z0-9\s-]', '', text)
    # Zamień spacje na myślniki
    text = re.sub(r'\s+', '-', text.strip())
    # Usuń podwójne myślniki
    text = re.sub(r'-+', '-', text)
    return text


def clean_phone(phone):
    """Czyści numer telefonu"""
    if not phone:
        return None
    # Usuń wszystko oprócz cyfr i spacji
    phone = re.sub(r'[^\d\s]', '', str(phone))
    phone = phone.strip()
    return phone if phone else None


def clean_email(email):
    """Czyści email - bierze pierwszy jeśli jest kilka"""
    if not email:
        return None
    email = str(email).strip()
    # Jeśli jest kilka emaili, weź pierwszy
    if ';' in email:
        email = email.split(';')[0].strip()
    if ',' in email:
        email = email.split(',')[0].strip()
    # Wyciągnij email z formatu "Nazwa <email>"
    match = re.search(r'<([^>]+)>', email)
    if match:
        email = match.group(1)
    # Sprawdź czy wygląda jak email
    if '@' not in email:
        return None
    return email.lower().strip()


def main():
    print("=" * 60)
    print("IMPORT CZŁONKÓW NORDA Z EXCELA")
    print(f"Data: {IMPORT_DATE}")
    print(f"Źródło: {DATA_SOURCE_NAME}")
    print("=" * 60)

    # Połącz z bazą
    engine = create_engine(DATABASE_URL)
    Session = sessionmaker(bind=engine)
    session = Session()

    try:
        # 1. Dodaj nowe źródło danych jeśli nie istnieje
        print("\n[1/4] Dodawanie źródła danych...")
        result = session.execute(text(
            "SELECT id FROM data_sources WHERE name = :name"
        ), {"name": DATA_SOURCE_NAME})
        existing = result.fetchone()

        if existing:
            source_id = existing[0]
            print(f"  Źródło już istnieje (ID: {source_id})")
        else:
            session.execute(text("""
                INSERT INTO data_sources (name, type, description, reliability_score, is_active)
                VALUES (:name, :type, :desc, 4, true)
            """), {
                "name": DATA_SOURCE_NAME,
                "type": DATA_SOURCE_TYPE,
                "desc": DATA_SOURCE_DESC
            })
            result = session.execute(text(
                "SELECT id FROM data_sources WHERE name = :name"
            ), {"name": DATA_SOURCE_NAME})
            source_id = result.fetchone()[0]
            print(f"  Dodano źródło (ID: {source_id})")

        # 2. Oznacz istniejące firmy źródłem
        print("\n[2/4] Oznaczanie istniejących firm źródłem importu...")
        # Najpierw sprawdź ile firm nie ma oznaczonego źródła
        result = session.execute(text(
            "SELECT COUNT(*) FROM companies WHERE data_source IS NULL OR data_source = ''"
        ))
        count_no_source = result.fetchone()[0]

        if count_no_source > 0:
            session.execute(text("""
                UPDATE companies
                SET data_source = 'norda-biznes.info (pierwotny import)'
                WHERE data_source IS NULL OR data_source = ''
            """))
            print(f"  Oznaczono {count_no_source} firm jako 'norda-biznes.info (pierwotny import)'")
        else:
            print("  Wszystkie firmy mają już oznaczone źródło")

        # 3. Importuj nowe firmy
        print(f"\n[3/4] Importowanie {len(NEW_COMPANIES)} nowych firm...")
        imported = 0
        skipped = 0

        for company in NEW_COMPANIES:
            # Sprawdź czy firma już istnieje
            slug = slugify(company["name"])
            result = session.execute(text(
                "SELECT id FROM companies WHERE slug = :slug"
            ), {"slug": slug})
            existing = result.fetchone()

            if existing:
                print(f"  ⏭ {company['name']} - już istnieje (slug: {slug})")
                skipped += 1
                continue

            # Pobierz ID kategorii
            category_id = CATEGORY_MAP.get(company.get("category"), 6)  # 6 = Other

            # Przygotuj dane
            email = clean_email(company.get("email"))
            phone = clean_phone(company.get("phone"))

            # Wstaw firmę
            session.execute(text("""
                INSERT INTO companies (
                    name, legal_name, slug, category_id,
                    email, phone,
                    address_full, address_city, address_postal,
                    data_source, data_quality, status,
                    created_at
                ) VALUES (
                    :name, :legal_name, :slug, :category_id,
                    :email, :phone,
                    :address_full, :address_city, :address_postal,
                    :data_source, 'basic', 'active',
                    :created_at
                )
            """), {
                "name": company["name"],
                "legal_name": company.get("legal_name"),
                "slug": slug,
                "category_id": category_id,
                "email": email,
                "phone": phone,
                "address_full": company.get("address_full"),
                "address_city": company.get("address_city"),
                "address_postal": company.get("address_postal"),
                "data_source": f"excel_norda_{IMPORT_DATE}",
                "created_at": datetime.now(),
            })

            # Pobierz ID nowej firmy
            result = session.execute(text(
                "SELECT id FROM companies WHERE slug = :slug"
            ), {"slug": slug})
            company_id = result.fetchone()[0]

            # Dodaj wpis do company_data_sources
            session.execute(text("""
                INSERT INTO company_data_sources (company_id, source_id, collected_at, fields_collected)
                VALUES (:company_id, :source_id, :collected_at, :fields)
            """), {
                "company_id": company_id,
                "source_id": source_id,
                "collected_at": datetime.now(),
                "fields": ["name", "email", "phone", "address"],
            })

            # Dodaj osobę kontaktową i datę przystąpienia do Nordy
            updates = []
            if company.get("contact_person"):
                updates.append(f"Osoba kontaktowa: {company['contact_person']}")
            if company.get("joined"):
                updates.append(f"Członek NORDA od: {company['joined']}")

            if updates:
                session.execute(text("""
                    UPDATE companies
                    SET description_short = :desc,
                        founding_history = :history
                    WHERE id = :company_id
                """), {
                    "company_id": company_id,
                    "desc": updates[0] if len(updates) == 1 else None,
                    "history": f"Członek NORDA od: {company.get('joined', 'brak danych')}. " + (f"Kontakt: {company.get('contact_person')}" if company.get('contact_person') else ""),
                })

            print(f"  ✓ {company['name']} (ID: {company_id})")
            imported += 1

        # 4. Podsumowanie
        print(f"\n[4/4] Podsumowanie:")
        print(f"  Zaimportowano: {imported} firm")
        print(f"  Pominięto (już istnieją): {skipped} firm")

        # Zatwierdź transakcję
        session.commit()
        print("\n✅ Import zakończony pomyślnie!")

    except Exception as e:
        session.rollback()
        print(f"\n❌ Błąd: {e}")
        raise
    finally:
        session.close()


if __name__ == "__main__":
    main()
