import os
import django

companies = Page.objects.filter(
    mangoblogger_points__gt=3, is_homepage=True, contact_email__isnull=False
).exclude(contact_email='')  # Ensure contact_email is not an empty string

result = companies.values(
    "website",
    "business_name",
    "billing_city",
    "contact_email",
    "no_of_pages",
    "created_on",
    "updated"
)

import sqlalchemy
from sqlalchemy import create_engine
DATABASES = {
    'default': {
        "ENGINE": "django.db.backends.postgresql",
        'PORT': '5432',
        'NAME': 'django-crm',
        'USER': 'postgres',
        'PASSWORD': 'DummyPassword123',
        'HOST': 'localhost',
    }
}
engine = create_engine(
    f"postgresql+psycopg2://{DATABASES['default']['USER']}:{DATABASES['default']['PASSWORD']}@{DATABASES['default']['HOST']}:{DATABASES['default']['PORT']}/{DATABASES['default']['NAME']}"
)

df = pd.DataFrame(list(result))  # Convert ValuesQuerySet to a list before creating DataFrame

df = df.rename(columns={
    "website": "website",
    "business_name": "full_name",
    "billing_city": "city_name",
    "contact_email": "email",
    "no_of_pages": "registration_number",
    "created_on": "creation_date",
    "updated": "update_date"
})

with engine.connect() as connection:
    try:
        for index, row in df.iterrows():
            row = row.to_dict()
            company_exists = connection.execute(
                sqlalchemy.text("SELECT * FROM crm_company WHERE website = :website"), {"website": row["website"]}
            ).fetchone()
            if not company_exists:
                connection.execute(
                    sqlalchemy.text(
                        "INSERT INTO crm_company (website, full_name, city_name, email, registration_number,creation_date,update_date) VALUES (:website, :full_name, :city_name, :email, :registration_number, :creation_date,:update_date)"
                    ),
                    row
                )
                print(f"Inserted: {row['website']}")
            else:
                print(f"Company already exists: {row['website']}")
        connection.commit()  # Commit all the changes at the end
        count = connection.execute(sqlalchemy.text("SELECT COUNT(*) FROM crm_company")).scalar()
        print(f"Total companies in crm_company table: {count}")
    except Exception as e:
        connection.rollback()
        print(f"An error occurred: {e}")