import datetime
import logging
import random
import socket
import time

from dotenv import load_dotenv
from sqlalchemy.sql import text  # Import text from SQLAlchemy

from utils.dynamo_db import DynamoDB
from utils.mysql_connector import MySQLConnector

load_dotenv()

dynamodb = DynamoDB()

# Initialize MySQLConnector
mysql_connector = MySQLConnector()

# Configure logging
logging.basicConfig(
    level=logging.INFO, format="%(asctime)s - %(levelname)s - %(message)s"
)


async def get_last_cron_run(job_name):
    """Fetch last cron job execution time from MySQL"""
    try:
        async with mysql_connector.get_session() as session:
            result = await session.execute(
                "SELECT last_run FROM cron_job_log WHERE job_name=%s ORDER BY last_run DESC LIMIT 1",
                (job_name,),
            )
            row = result.fetchone()
            print(
                f"Last cron run fetched for {job_name}: {row['last_run'] if row else 'None'}"
            )
            return row["last_run"] if row else None
    except Exception as e:
        logging.error(f"Error fetching last cron run for {job_name}: {e}")
        return None


async def update_cron_log(job_name):
    """Log the cron job execution time in MySQL"""
    async with mysql_connector.get_session() as session:
        await session.execute(
            "INSERT INTO cron_job_log (job_name, last_run) VALUES (%s, NOW())",
            (job_name,),
        )
        await session.commit()


async def migrate_data(dynamo_table_name, mysql_table_name, job_name):
    """Fetch new and updated records from DynamoDB and insert/update MySQL"""
    last_run = await get_last_cron_run(job_name)

    # Ensure at least 1-hour gap before running again
    if last_run and (datetime.datetime.now() - last_run).seconds < 3600:
        print(
            f"Skipping execution for {job_name}: Last cron job ran less than an hour ago."
        )
        return

    print(f"Fetching only new and updated records from DynamoDB for {job_name}...")

    dydb_table = dynamodb.get_table(dynamo_table_name)
    last_evaluated_key = None
    new_records_count = 0
    has_more_records = True  # Flag to control the loop

    while has_more_records:
        # Convert last_run to DynamoDB timestamp format
        last_run_str = (
            last_run.strftime("%Y-%m-%dT%H:%M:%SZ")
            if last_run
            else "1970-01-01T00:00:00Z"
        )
        print(f"Scanning DynamoDB with last_run: {last_run_str}")

        # Fetch only new (`created_date > last_run`) and updated (`modified_date > last_run`) records
        scan_kwargs = {
            "FilterExpression": "(created_date > :last_run) OR (modified_date > :last_run)",
            "ExpressionAttributeValues": {":last_run": last_run_str},
        }

        if last_evaluated_key:
            scan_kwargs["ExclusiveStartKey"] = last_evaluated_key

        # Implement exponential backoff
        max_retries = 5
        retry_count = 0
        while retry_count < max_retries:
            try:
                # Use the DynamoConnector to scan the table
                response = dydb_table.scan(**scan_kwargs)
                print(
                    f"Scan response received with {len(response.get('Items', []))} items."
                )
                break  # Exit retry loop if successful
            except (
                dydb_table.meta.client.exceptions.ProvisionedThroughputExceededException
            ):
                wait_time = (2**retry_count) + random.uniform(0, 1)
                print(
                    f"Provisioned throughput exceeded, retrying in {wait_time:.2f} seconds..."
                )
                time.sleep(wait_time)
                retry_count += 1
        else:
            print("Max retries reached. Exiting.")
            return

        async with mysql_connector.get_session() as session:
            for item in response.get("Items", []):
                root_url = item.get("root_url")
                company_name = item.get("company_name")

                # Skip records where company_name is NULL or empty
                if not company_name:
                    print(
                        f"Skipping record with root_url: {root_url} due to missing company_name"
                    )
                    continue

                print(
                    f"Processing record: root_url={root_url}, company_name={company_name}"
                )

                hostname = socket.gethostname()  # Get the hostname

                sql = text(
                    f"""
                INSERT INTO {mysql_table_name} (root_url, company_name, created_by, updated_at)
                VALUES (:root_url, :company_name, :created_by, NOW())
                ON DUPLICATE KEY UPDATE company_name=:company_name, modified_by=:modified_by, updated_at=NOW()
                """
                )
                await session.execute(
                    sql,
                    {
                        "root_url": root_url,
                        "company_name": company_name,
                        "created_by": hostname,
                        "modified_by": hostname,
                    },
                )
                new_records_count += 1

            await session.commit()
            print(
                f"Batch of records committed to MySQL. Total new/updated records so far: {new_records_count}"
            )

        # Check if there are more records to fetch
        last_evaluated_key = response.get("LastEvaluatedKey")
        has_more_records = bool(
            last_evaluated_key
        )  # Update the flag based on the presence of more data

    await update_cron_log(job_name)
    print(f"Cron log updated for {job_name}.")

    if new_records_count > 0:
        print(
            f"Data migration completed for {job_name}. {new_records_count} new/updated records processed."
        )
    else:
        print(f"No new records found for {job_name}.")


if __name__ == "__main__":
    import asyncio

    # Migrate company information
    asyncio.run(
        migrate_data("company_information", "company_info", "company_info_migration")
    )

    # Migrate fund information
    # asyncio.run(migrate_data("fund_information", "fund_info", "fund_info_migration"))

    asyncio.run(mysql_connector.close())