#!/usr/bin/env python3
"""
Import dat przystąpienia firm do Izby NORDA
Źródło: .private/inbox-artur/załączniki/Aktualna lista kontaktow wraz z data przystapienia.xlsx
"""

import os
import sys
from datetime import datetime
from pathlib import Path

# Add parent directory to path
sys.path.insert(0, str(Path(__file__).parent.parent))

import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker

# Database connection
DATABASE_URL = os.getenv('DATABASE_URL', 'postgresql://nordabiz_app:dev_password@localhost:5433/nordabiz')
engine = create_engine(DATABASE_URL)
Session = sessionmaker(bind=engine)

# Path to Excel file
EXCEL_PATH = Path(__file__).parent.parent / '.private' / 'inbox-artur' / 'załączniki' / 'Aktualna lista kontaktow wraz z data przystapienia.xlsx'


def normalize_name(name):
    """Normalize company name for matching"""
    if not name:
        return ''
    name = str(name).strip().lower()
    # Remove common suffixes
    for suffix in [' sp. z o.o.', ' sp.z o.o.', ' spółka z o.o.', ' s.a.', ' sp. j.', ' s.c.']:
        name = name.replace(suffix, '')
    # Remove extra spaces
    name = ' '.join(name.split())
    return name


# Manual aliases for companies with different names in Excel vs database
NAME_ALIASES = {
    'agat': 'jubiler agat',
    'eura tech': 'eura-tech',
    'hotel wieniawa': 'hotel spa wieniawa',
    'hebel, masiak i wspólnicy': 'hebel masiak i wspólnicy adwokaci i radcowie prawni',
    'coolair hvac systems': 'coolair',
    'agis': 'agis nieruchomości',
    'cris tap': 'cristap',
    'ekod h.a.p.': 'ekod',
    'gren house systems': 'green house systems',
    'hill ob.': 'hill obiekt',
    'kammet': 'kammet',
    'kbs': 'kbs instalacje',
    'kornix phu': 'kornix',
    'kupsa coathing': 'kupsa coating',
    'korporacja budowlana kbms': 'kbms',
    'mkonsult m. matuszak': 'mkonsult',
    'mesan grupa sbs': 'mesan',
    'nowak chłodnictwo klimatyzacja': 'nowak chłodnictwo',
    'porta kmi poland': 'porta kmi',
    'rubinsolar': 'rubinsolar',
    'technika budowlana biłas': 'technika budowlana',
    'kancelaria rachunkowa gawin &wojnowska': 'kancelaria rachunkowa gawin wojnowska',
    'chopin telewizja kablowa': 'chopin',
    'pix lab': 'pixlab softwarehouse',
    'porta kmi sa': 'porta kmi',
    'rotor': 'rotor',
    'scrol': 'scrol',
    'przedsiębiorstwo budowlane sigma': 'sigma budownictwo',
    'ttm, tk chopin': 'chopin',
    'ultramare': 'ultramare',
    'pgk': 'pucka gospodarka komunalna',
    'perfekta biuro rachunkowe': 'biuro rachunkowości perfekta',
    'portal usługi ogólnobudowlane': 'portal',
    'riela polska': 'riela polska',
    's&k tobaacco sklepy lord': 'phu s&k tobacco',
    'kancelaria radcy prawnego': 'kancelaria radcy prawnego łukasz gilewicz',
}


def parse_date(date_val):
    """Parse date from various formats"""
    if pd.isna(date_val):
        return None

    if isinstance(date_val, datetime):
        return date_val.date()

    if isinstance(date_val, str):
        # Try different formats
        for fmt in ['%Y-%m-%d', '%d.%m.%Y', '%Y-%m-%d %H:%M:%S']:
            try:
                return datetime.strptime(date_val.strip(), fmt).date()
            except ValueError:
                continue

    return None


def main(dry_run=True):
    print(f"{'[DRY RUN] ' if dry_run else ''}Import dat przystąpienia do Izby NORDA")
    print(f"Źródło: {EXCEL_PATH}")
    print("=" * 60)

    # Read Excel
    df = pd.read_excel(EXCEL_PATH, header=1)
    print(f"Wczytano {len(df)} wierszy z pliku Excel\n")

    # Get companies from database
    session = Session()

    from database import Company
    companies = session.query(Company).all()

    # Create mapping by normalized name
    company_map = {}
    for c in companies:
        norm_name = normalize_name(c.name)
        company_map[norm_name] = c
        # Also map by legal_name if different
        if c.legal_name and normalize_name(c.legal_name) != norm_name:
            company_map[normalize_name(c.legal_name)] = c

    print(f"Firm w bazie: {len(companies)}")
    print(f"Unikalnych nazw do matchowania: {len(company_map)}\n")

    # Process Excel data
    updated = 0
    not_found = []
    already_set = 0
    no_date = 0

    for _, row in df.iterrows():
        firma = row.get('Firma')
        date_val = row.get('Data przystąpienia')

        if pd.isna(firma):
            continue

        norm_firma = normalize_name(firma)
        member_since = parse_date(date_val)

        if not member_since:
            no_date += 1
            continue

        # Check for alias
        if norm_firma in NAME_ALIASES:
            norm_firma = NAME_ALIASES[norm_firma]

        if norm_firma in company_map:
            company = company_map[norm_firma]

            if company.member_since:
                already_set += 1
                if company.member_since != member_since:
                    print(f"  ⚠️  {company.name}: różne daty ({company.member_since} vs {member_since})")
                continue

            print(f"  ✅ {company.name} → {member_since}")

            if not dry_run:
                company.member_since = member_since

            updated += 1
        else:
            not_found.append((firma, member_since))

    if not dry_run:
        session.commit()

    session.close()

    # Summary
    print("\n" + "=" * 60)
    print(f"PODSUMOWANIE:")
    print(f"  Zaktualizowano: {updated}")
    print(f"  Już ustawione:  {already_set}")
    print(f"  Bez daty:       {no_date}")
    print(f"  Nie znaleziono: {len(not_found)}")

    if not_found:
        print(f"\nFirmy nie znalezione w bazie:")
        for firma, date in not_found[:20]:
            print(f"  - {firma} ({date})")
        if len(not_found) > 20:
            print(f"  ... i {len(not_found) - 20} więcej")

    if dry_run:
        print(f"\n[DRY RUN] Aby zapisać zmiany, uruchom z --apply")


if __name__ == '__main__':
    dry_run = '--apply' not in sys.argv
    main(dry_run=dry_run)