import asyncio
import pandas as pd
import traceback
import time
import os
import mysql.connector
from patchright.async_api import async_playwright

# Define user profile path (for session persistence)
user_profile_path = r"C:\Users\User\AppData\Local\Google\Chrome\User Data\Profile 4"

# Define login credentials
USERNAME = "chandraelectronics"
PASSWORD = "Aaaa2222@"

# MySQL Database Configuration
DB_CONFIG = {
    "host": "localhost",
    "user": "root",
    "password": "root",
    "database": "bank_scrape"
}

BANK_NAME = "Citizens Bank"

async def setup_database():
    """Creates necessary database tables if they don't exist."""
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()

        create_transactions_table = """
        CREATE TABLE IF NOT EXISTS bank_transactions (
            id INT AUTO_INCREMENT PRIMARY KEY,
            transaction_date VARCHAR(20),
            value_date VARCHAR(20),
            cheque_no VARCHAR(50),
            description TEXT,
            amount DECIMAL(15,2),
            balance DECIMAL(15,2),
            bank VARCHAR(50),
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
        );
        """
        cursor.execute(create_transactions_table)

        create_error_log_table = """
        CREATE TABLE IF NOT EXISTS error_logs (
            id INT AUTO_INCREMENT PRIMARY KEY,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
            error_message TEXT
        );
        """
        cursor.execute(create_error_log_table)

        conn.commit()
        cursor.close()
        conn.close()
        print("Database tables are set up successfully.")

    except mysql.connector.Error as err:
        print(f"MySQL Error during setup: {err}")

async def save_to_database(transactions):
    """Saves only new transactions into MySQL database."""
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()

        cursor.execute("SELECT transaction_date, value_date, cheque_no, description, amount, balance FROM bank_transactions WHERE bank = %s", (BANK_NAME,))
        existing_transactions = {tuple(str(col).strip() for col in row) for row in cursor.fetchall()}

        new_transactions = []
        for t in transactions:
            t_normalized = tuple(str(col).strip() for col in t)
            if t_normalized not in existing_transactions:
                new_transactions.append(list(t) + [BANK_NAME])

        if not new_transactions:
            print("✅ No new transactions to insert.")
        else:
            insert_query = """
            INSERT INTO bank_transactions 
            (transaction_date, value_date, cheque_no, description, amount, balance, bank) 
            VALUES (%s, %s, %s, %s, %s, %s, %s);
            """
            cursor.executemany(insert_query, new_transactions)
            conn.commit()
            print(f"✅ Inserted {len(new_transactions)} new transactions.")

        cursor.close()
        conn.close()

    except mysql.connector.Error as err:
        print(f"❌ MySQL Error: {err}")

async def log_error_to_db(error_message):
    """Logs errors into the MySQL database."""
    try:
        conn = mysql.connector.connect(**DB_CONFIG)
        cursor = conn.cursor()
        
        insert_query = "INSERT INTO error_logs (error_message) VALUES (%s);"
        cursor.execute(insert_query, (error_message,))

        conn.commit()
        cursor.close()
        conn.close()
        print("Error logged into MySQL database.")

    except mysql.connector.Error as err:
        print(f"Failed to log error: {err}")

async def scrape_transactions():
    await setup_database()

    restart_time = time.time() + 600  # Restart script every 10 minutes
    first_run = True  # Track if it's the first run

    while True:
        try:
            async with async_playwright() as p:
                print("\nStarting Playwright session...")

                browser = await p.chromium.launch_persistent_context(
                    user_data_dir=user_profile_path,
                    channel="chrome",
                    headless=False,
                    no_viewport=True,
                )

                page = await browser.new_page()
                await page.goto("https://www.ctznbank.com.np/#/login")

                await page.wait_for_selector("#username", state="visible")
                await page.wait_for_selector("#password", state="visible")
                await page.fill("#username", USERNAME)
                await page.fill("#password", PASSWORD)

                login_button = await page.wait_for_selector("button[type='submit']", state="visible")
                await login_button.click()

                await page.wait_for_selector(".main-wrapper", state="visible")

                while True:
                    if time.time() > restart_time:
                        print("🔄 Restarting script after 10 minutes...")
                        await browser.close()
                        return  # Exit function to trigger full restart

                    print("🔄 Refreshing page...")
                    await page.reload()
                    await page.wait_for_selector(".main-wrapper", state="visible")

                    if first_run:
                        print("🔍 Clicking 'View All' before going to statements...")
                        await page.wait_for_selector("button[ng-click^='dashboardCtrl.viewAllActivity']", state="attached")
                        view_all_button = await page.wait_for_selector("button[ng-click^='dashboardCtrl.viewAllActivity']", state="attached")
                        await view_all_button.click(force=True)

                    print("🔍 Clicking 'Statement' button...")
                    await page.wait_for_selector("button[ng-click^='accountCtrl.getAccountStatement']", state="visible")
                    statement_button = await page.wait_for_selector("button[ng-click^='accountCtrl.getAccountStatement']", state="visible")
                    await statement_button.click(force=True)

                    first_run = False  # Disable "View All" on subsequent loops

                    await page.wait_for_selector("#fromDateId", state="visible")
                    await page.wait_for_selector("#toDateId", state="visible")

                    custom_date_yesterday = "2025/01/16"
                    custom_date_today = "2025/01/17"

                    await page.fill("#fromDateId", custom_date_yesterday)
                    await page.fill("#toDateId", custom_date_today)
                    print(f"Set date range: {custom_date_yesterday} - {custom_date_today}")

                    show_button = await page.wait_for_selector("button[type='submit']", state="visible")
                    await show_button.click()
                    await page.wait_for_timeout(5000)

                    credit_label = await page.wait_for_selector("label:has(input[type='radio'][value='CREDIT'])")
                    await credit_label.click()
                    await page.wait_for_timeout(2000)

                    transactions = []
                    last_transaction = None

                    while True:
                        await page.wait_for_selector(".statement-table", state="visible")

                        rows = await page.query_selector_all(".statement-table tbody tr")
                        page_transactions = []

                        for row in rows:
                            cols = await row.query_selector_all("td")
                            if len(cols) >= 6:
                                transaction_date = await cols[0].inner_text()
                                value_date = await cols[1].inner_text()
                                cheque_no = await cols[2].inner_text()
                                description = await cols[3].inner_text()
                                amount = await cols[4].inner_text()
                                balance = await cols[5].inner_text()

                                page_transactions.append([transaction_date, value_date, cheque_no, description, amount.replace(',', ''), balance.replace(',', '')])

                        if not page_transactions or page_transactions[-1] == last_transaction:
                            break

                        last_transaction = page_transactions[-1]
                        transactions.extend(page_transactions)
                        print(f"Scraped {len(transactions)} transactions so far...")

                        next_button = await page.query_selector("a.pagination-button.right")
                        if next_button:
                            await next_button.click()
                        else:
                            break

                    await save_to_database(transactions)
                    print("\n✅ Scraping cycle completed. Restarting in 5 seconds...\n")
                    await asyncio.sleep(5)  # Wait 1 minute before next cycle

        except Exception as e:
            error_message = f"{time.strftime('%Y-%m-%d %H:%M:%S')} - ERROR: {str(e)}\n{traceback.format_exc()}"
            print(error_message)
            await log_error_to_db(error_message)

            print("Restarting in 10 seconds...")
            await asyncio.sleep(10)

# Run the async function with periodic restart
while True:
    asyncio.run(scrape_transactions())