add_properties.py

"""
Скрипт для добавления объектов недвижимости из pars_samolet.sql в базу данных Render
"""
import psycopg2
import re
import sqlite3
import uuid
import sys
from datetime import datetime

# Данные подключения к БД на Render
DB_CONFIG = {
    'host': 'dpg-d5ht8vi4d50c739akh2g-a.virginia-postgres.render.com',
    'port': 5432,
    'database': 'lead_exchange_bk',
    'user': 'lead_exchange_bk_user',
    'password': '8m2gtTRBW0iAr7nY2Aadzz0VcZBEVKYM'
}

# ID администратора (который мы создали ранее)
ADMIN_USER_ID = None  # Будет получен из БД

def get_admin_user_id(conn):
    """Получить ID администратора из БД"""
    cursor = conn.cursor()

    # Получаем всех администраторов
    cursor.execute("""
        SELECT user_id, email, first_name, last_name
        FROM users
        WHERE role = 'ADMIN'
        ORDER BY created_at DESC
    """)
    admins = cursor.fetchall()

    if not admins:
        raise Exception("Admin user not found in database. Please run add_admin_user.py first.")

    if len(admins) == 1:
        admin_id, email, first_name, last_name = admins[0]
        print(f"  Found admin: {email} ({first_name} {last_name})")
        return str(admin_id)

    # Если несколько админов, показываем их и выбираем последнего созданного (самый новый)
    print(f"  Found {len(admins)} admin users:")
    for admin_id, email, first_name, last_name in admins:
        print(f"    - {email} ({first_name} {last_name}) - ID: {admin_id}")

    # Используем самого нового администратора (первый в списке, т.к. ORDER BY created_at DESC)
    selected_admin = admins[0]
    print(f"  ✅ Using: {selected_admin[1]}")
    return str(selected_admin[0])

def parse_sql_file():
    """Парсинг SQL файла с объектами недвижимости"""
    print("Reading pars_samolet.sql...")

    with open('pars_samolet.sql', 'r', encoding='utf-8') as f:
        sql_content = f.read()

    # Извлекаем CREATE TABLE
    create_match = re.search(r'CREATE TABLE[^;]+;', sql_content, re.DOTALL)
    if not create_match:
        raise ValueError("CREATE TABLE not found")
    create_stmt = create_match.group(0)

    # Извлекаем все INSERT
    insert_pattern = r'INSERT INTO mytable\([^)]+\) VALUES\s*\([^;]+\);'
    inserts = re.findall(insert_pattern, sql_content, re.DOTALL)

    print(f"Found {len(inserts)} INSERT statements")

    # Создаем временную базу для парсинга
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()
    cursor.execute(create_stmt)

    # Выполняем INSERT
    successful = 0
    for i, insert in enumerate(inserts):
        try:
            cursor.execute(insert)
            successful += 1
        except sqlite3.Error as e:
            print(f"Warning: Could not parse INSERT #{i+1}: {e}")
            continue

    print(f"Successfully parsed {successful}/{len(inserts)} records")

    # Получаем данные
    cursor.execute('SELECT * FROM mytable')
    rows = cursor.fetchall()

    # Получаем имена колонок
    cursor.execute("PRAGMA table_info(mytable)")
    columns = [col[1] for col in cursor.fetchall()]

    # Создаем список словарей
    objects = [dict(zip(columns, row)) for row in rows]
    conn.close()

    return objects

def map_property_type(old_type):
    """Маппинг типов недвижимости"""
    mapping = {
        'Квартира': 'APARTMENT',
        'Дом': 'HOUSE',
        'Коммерческая': 'COMMERCIAL',
        'Участок': 'LAND'
    }
    return mapping.get(old_type, 'APARTMENT')

def map_status(old_status):
    """Маппинг статусов"""
    mapping = {
        'Доступно': 'PUBLISHED',
        'Продано': 'SOLD',
        'Архив': 'ARCHIVED'
    }
    return mapping.get(old_status, 'PUBLISHED')

def insert_properties(objects, owner_user_id, auto_confirm=False):
    """Вставка объектов недвижимости в БД"""
    print(f"\nConnecting to database at {DB_CONFIG['host']}...")

    try:
        conn = psycopg2.connect(**DB_CONFIG)
        cursor = conn.cursor()

        print("Connected successfully!")

        # Проверяем, есть ли уже данные
        cursor.execute("SELECT COUNT(*) FROM properties")
        existing_count = cursor.fetchone()[0]
        print(f"Current properties in database: {existing_count}")

        if existing_count > 0:
            if auto_confirm:
                print(f"\nAuto-confirm enabled: Deleting {existing_count} existing properties...")
                cursor.execute("DELETE FROM properties")
                conn.commit()
                print("Deleted existing properties")
            else:
                try:
                    response = input(f"\nDatabase already has {existing_count} properties. Delete them? (yes/y/no/n): ")
                    if response.lower() in ['yes', 'y']:
                        cursor.execute("DELETE FROM properties")
                        conn.commit()
                        print("Deleted existing properties")
                except EOFError:
                    print("\n⚠️  Input interrupted. Keeping existing properties.")
                    print("Run with --yes flag to auto-confirm or run interactively.")

        # Вставляем объекты
        inserted = 0
        failed = 0

        insert_query = """
        INSERT INTO properties (
            property_id, title, description, address, property_type,
            area, price, rooms, status, owner_user_id, created_user_id,
            created_at, updated_at
        ) VALUES (
            %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s
        )
        """

        print(f"\nInserting {len(objects)} properties...")

        for i, obj in enumerate(objects):
            try:
                # Подготавливаем данные
                property_id = obj.get('property_id', str(uuid.uuid4()))
                title = obj.get('title', '')[:255]  # Ограничиваем длину
                description = obj.get('description', '')
                address = obj.get('address', '')
                property_type = map_property_type(obj.get('property_type', 'Квартира'))
                area = float(obj['area']) if obj.get('area') else None
                price = int(obj['price']) if obj.get('price') else None
                rooms = int(obj['rooms']) if obj.get('rooms') else None
                status = map_status(obj.get('status', 'Доступно'))

                # Даты
                created_at = obj.get('created_at', datetime.now().isoformat())
                updated_at = obj.get('updated_at', datetime.now().isoformat())

                cursor.execute(insert_query, (
                    property_id, title, description, address, property_type,
                    area, price, rooms, status, owner_user_id, owner_user_id,
                    created_at, updated_at
                ))

                inserted += 1

                if (i + 1) % 50 == 0:
                    print(f"  Inserted {i + 1}/{len(objects)}...")

            except Exception as e:
                failed += 1
                print(f"  Failed to insert property {obj.get('property_id', 'unknown')}: {e}")
                continue

        # Коммитим изменения
        conn.commit()

        print(f"\n✅ Successfully inserted {inserted} properties")
        if failed > 0:
            print(f"⚠️  Failed to insert {failed} properties")

        # Проверяем финальное количество
        cursor.execute("SELECT COUNT(*) FROM properties")
        final_count = cursor.fetchone()[0]
        print(f"\nTotal properties in database: {final_count}")

        cursor.close()
        conn.close()

    except psycopg2.Error as e:
        print(f"❌ Database error: {e}")
        raise
    except Exception as e:
        print(f"❌ Error: {e}")
        raise

def main():
    print("=" * 60)
    print("Adding Properties to Database")
    print("=" * 60)

    # Проверяем параметры командной строки
    auto_confirm = '--yes' in sys.argv or '-y' in sys.argv

    if auto_confirm:
        print("🤖 Auto-confirm mode enabled")

    # Парсим SQL файл
    objects = parse_sql_file()

    if not objects:
        print("No objects found in pars_samolet.sql")
        return

    print(f"\nParsed {len(objects)} properties from file")
    print(f"Sample property: {objects[0].get('title', 'N/A')}")

    # Подключаемся к БД и получаем ID администратора
    print("\nGetting admin user ID...")
    try:
        conn = psycopg2.connect(**DB_CONFIG)
        admin_id = get_admin_user_id(conn)
        conn.close()
        print(f"Admin user ID: {admin_id}")
    except Exception as e:
        print(f"❌ Error: {e}")
        return

    # Подтверждение
    if not auto_confirm:
        print(f"\nReady to insert {len(objects)} properties into database")
        print(f"Database: {DB_CONFIG['host']}/{DB_CONFIG['database']}")
        try:
            response = input("\nProceed? (yes/y/no/n): ")
            if response.lower() not in ['yes', 'y']:
                print("Cancelled by user")
                return
        except EOFError:
            print("\n❌ Error: EOF when reading input")
            print("Run with --yes flag to auto-confirm: python add_properties.py --yes")
            return
    else:
        print(f"\n✅ Auto-confirming insertion of {len(objects)} properties")

    # Вставляем объекты
    insert_properties(objects, admin_id, auto_confirm)

    print("\n" + "=" * 60)
    print("✅ Done!")
    print("=" * 60)

if __name__ == '__main__':
    main()