import requests
from bs4 import BeautifulSoup
from datetime import datetime
import pandas as pd
import time
from typing import List, Dict
import threading
from app.logger import get_app_logger

logger = get_app_logger("scraper")

# Global event to signal stopping of scraping tasks
stop_event = threading.Event()

BASE_URL = "https://komoditas.batangkab.go.id/statistik_komo"

KOMODITAS_MAPPING = {
    34: {"id": 1, "nama": "Bawang Merah"},
    35: {"id": 2, "nama": "Bawang Putih"},
    3: {"id": 3, "nama": "Beras IR-64 (KW Medium)"},
    46: {"id": 4, "nama": "Beras IR-64 (KW Premium)"},
    30: {"id": 5, "nama": "Cabe Merah Besar Keriting"},
    31: {"id": 6, "nama": "Cabe Merah Besar Teropong"},
    32: {"id": 7, "nama": "Cabe Rawit Merah"},
    33: {"id": 8, "nama": "Cabe Rawit Hijau"},
    11: {"id": 9, "nama": "Daging Ayam Ras"},
    12: {"id": 10, "nama": "Daging Ayam Kampung"},
    10: {"id": 11, "nama": "Daging Sapi Murni (Has)"},
    14: {"id": 12, "nama": "Telur Ayam Ras"},
    15: {"id": 13, "nama": "Telur Ayam Kampung"},
    63: {"id": 14, "nama": "Jeruk (1 kg)"},
    23: {"id": 15, "nama": "Jagung Pipilan Kering"},
    42: {"id": 16, "nama": "Kacang Hijau"},
    43: {"id": 17, "nama": "Kacang Tanah"},
    27: {"id": 18, "nama": "Kacang Kedelai Import"},
    28: {"id": 19, "nama": "Kacang Kedelai Lokal"},
    44: {"id": 20, "nama": "Ketela Pohon"},
    60: {"id": 21, "nama": "Pisang Ambon/Pisang Lokal (1 kg)"},
    48: {"id": 22, "nama": "Ikan Bandeng"},
    64: {"id": 23, "nama": "Ikan Kembung"},
    49: {"id": 24, "nama": "Ikan Asin Teri"},
    57: {"id": 25, "nama": "Udang Jerbung"},
    24: {"id": 26, "nama": "Tepung Terigu"},
    25: {"id": 27, "nama": "Tepung Terigu Segitiga Biru (KW medium)"},
    5: {"id": 28, "nama": "Gula Pasir Lokal (KW Medium)"},
    38: {"id": 29, "nama": "Garam Beryodium Bata"},
    39: {"id": 30, "nama": "Garam Beryodium Halus"},
    45: {"id": 31, "nama": "LPG 3 KG"},
    7: {"id": 32, "nama": "Minyak Goreng Bimoli Botol"},
    8: {"id": 33, "nama": "Minyak Goreng Curah (Tanpa Merk)"},
    41: {"id": 34, "nama": "Mie Instan Merk Indomie Kari Ayam"},
    18: {"id": 35, "nama": "Susu Kental Manis Merk Bendera"},
    19: {"id": 36, "nama": "Susu Kental Manis Merk Indomilk"},
    21: {"id": 37, "nama": "Susu Bubuk Merk Indomilk Coklat"},
    22: {"id": 38, "nama": "Susu Bubuk Merk Indomilk Full Cream"},
    61: {"id": 39, "nama": "Dancow Vanila (400 - 500 gram)"},
    62: {"id": 40, "nama": "Susu Balita (SGM atau sejenisnya 400 gram)"},
    50: {"id": 41, "nama": "Pupuk UREA"},
    51: {"id": 42, "nama": "Pupuk SP-36"},
    52: {"id": 43, "nama": "Pupuk ZA"},
    53: {"id": 44, "nama": "Pupuk KCL"},
    54: {"id": 45, "nama": "Semen Tiga Roda"},
    55: {"id": 46, "nama": "Semen Gresik"},
    56: {"id": 47, "nama": "Semen Holcim"},
    58: {"id": 48, "nama": "Tempe (1 kg)"},
    59: {"id": 49, "nama": "Tahu Mentah (1 kg)"}
}

BULAN_MAP = {
    "januari": "01", "februari": "02", "maret": "03", "april": "04",
    "mei": "05", "juni": "06", "juli": "07", "agustus": "08",
    "september": "09", "oktober": "10", "november": "11", "desember": "12"
}

NAMA_PASAR = {
    1: "Pasar Batang",
    2: "Pasar Bandar",
    3: "Pasar Limpung"
}

def convert_tanggal_indo(tanggal_text: str) -> str:
    try:
        parts = tanggal_text.strip().lower().split()
        hari = parts[0].zfill(2)
        bulan = BULAN_MAP[parts[1]]
        tahun = parts[2]
        return f"{tahun}-{bulan}-{hari}"
    except:
        return None

def clean_harga(value: str) -> float:
    """
    Cleans price string into float. Handles Indonesian formats (e.g., 13.500,00 or 13500,00).
    """
    if value is None:
        return 0.0
    value = str(value).strip()
    if value in ("", "-", "0"):
        return 0.0
    
    # Remove currency symbols and other non-numeric chars except comma and dot
    import re
    value = re.sub(r'[^\d,.]', '', value)
    
    # Handle Indonesian format: dots as thousands, comma as decimal
    # Or common web format: commas as thousands, dot as decimal
    # If both present, assume last one is decimal
    if "," in value and "." in value:
        if value.rfind(",") > value.rfind("."):
            # Dot is thousands, comma is decimal
            value = value.replace(".", "").replace(",", ".")
        else:
            # Comma is thousands, dot is decimal
            value = value.replace(",", "")
    elif "," in value:
        # Only comma: if it looks like decimal (e.g. 13500,00), replace with dot
        # If it looks like thousands (e.g. 13,500), remove it
        if len(value.split(",")[-1]) <= 2: # Likely decimal
            value = value.replace(",", ".")
        else:
            value = value.replace(",", "")
            
    try:
        return float(value)
    except (ValueError, TypeError):
        return 0.0

def is_summary_row(text: str) -> bool:
    text = text.lower()
    keywords = ["harga tertinggi", "harga rata", "harga terendah"]
    return any(k in text for k in keywords)

def scraping_hari_ini(target_date: datetime = None) -> pd.DataFrame:
    if target_date is None:
        target_date = datetime.now()
    hasil = []
    
    logger.info("=" * 50)
    logger.info(f"🕸️ Memulai proses scraping untuk tanggal {target_date.strftime('%Y-%m-%d')}")
    logger.info("=" * 50)
    
    stop_event.clear()
    
    for scraping_id, info in KOMODITAS_MAPPING.items():
        database_id = info["id"]
        nama_komoditas = info["nama"]
        
        if stop_event.is_set():
            logger.warning("🛑 Scraping dihentikan atas permintaan pengguna.")
            break
            
        params = {
            "id_komoditi": scraping_id,
            "bulan": target_date.month,
            "tahun": target_date.year
        }
        
        try:
            response = requests.get(BASE_URL, params=params, timeout=30)
            if response.status_code != 200:
                logger.warning(f"⚠️ Gagal mengambil data ID {scraping_id} (HTTP {response.status_code})")
                continue
            
            soup = BeautifulSoup(response.text, "html.parser")
            table = soup.find("table")
            if not table:
                continue
            
            rows = table.find_all("tr")
            found_data = False
            for row in rows[1:]:
                cols = row.find_all(["td", "th"])
                data = [col.get_text(strip=True) for col in cols]
                
                if len(data) < 4 or is_summary_row(data[0]):
                    continue
                
                tanggal = convert_tanggal_indo(data[0])
                if not tanggal or tanggal != target_date.strftime("%Y-%m-%d"):
                    continue
                
                found_data = True
                for pasar_id, harga in enumerate([clean_harga(data[1]), clean_harga(data[2]), clean_harga(data[3])], start=1):
                    hasil.append({
                        "komoditas_id": database_id,
                        "komoditas_nama": nama_komoditas,
                        "pasar_id": pasar_id,
                        "tanggal": tanggal,
                        "harga": harga
                    })
            
            if found_data:
                logger.info(f"✅ Scraping sukses untuk {nama_komoditas} (ID {scraping_id} -> DB ID {database_id})")
            time.sleep(0.5)
        except Exception as e:
            logger.error(f"❌ Error scraping ID {scraping_id}: {e}")
    
    df = pd.DataFrame(hasil)
    logger.info(f"📊 Total baris hasil scraping: {len(df)}")
    return df

def upload_to_mysql(df: pd.DataFrame, engine) -> Dict:
    pasar_berhasil = set()
    pasar_harga_0 = set()
    komoditas_berhasil = []
    komoditas_harga_0 = []
    
    stats = {
        "total_insert": 0,
        "total_update": 0,
        "total_skip": 0,
        "total_harga_0": 0,
        "pasar_berhasil": [],
        "pasar_harga_0": [],
        "komoditas_berhasil_count": 0,
        "komoditas_harga_0_count": 0
    }
    
    logger.info("🚀 Memulai proses upload ke database MySQL...")
    
    if df.empty:
        logger.warning("⚠️ DataFrame kosong, tidak ada data yang diupload.")
        return stats
    
    with engine.begin() as conn:
        for _, row in df.iterrows():
            if stop_event.is_set():
                logger.warning("🛑 Upload database dihentikan atas permintaan pengguna.")
                break
                
            komoditas_id = int(row["komoditas_id"])
            komoditas_nama = row["komoditas_nama"]
            pasar_id = int(row["pasar_id"])
            tanggal = row["tanggal"]
            harga_baru = float(row["harga"])
            pasar_nama = NAMA_PASAR.get(pasar_id, f"Pasar {pasar_id}")
            
            if harga_baru <= 0:
                stats["total_harga_0"] += 1
                komoditas_harga_0.append(komoditas_id)
                pasar_harga_0.add(pasar_nama)
                continue
            
            check_query = """
            SELECT id, harga FROM harga_harian
            WHERE komoditas_id = %s AND pasar_id = %s AND tanggal = %s LIMIT 1
            """
            result = conn.exec_driver_sql(check_query, (komoditas_id, pasar_id, tanggal)).fetchone()
            
            if result:
                existing_id, harga_lama = result[0], float(result[1])
                if harga_lama <= 0 and harga_baru > 0:
                    conn.exec_driver_sql("UPDATE harga_harian SET harga = %s WHERE id = %s", (harga_baru, existing_id))
                    stats["total_update"] += 1
                    komoditas_berhasil.append(komoditas_id)
                    pasar_berhasil.add(pasar_nama)
                    logger.info(f"✏️ Update sukses: {komoditas_nama} di {pasar_nama} -> Rp {harga_baru:,.0f}")
                else:
                    stats["total_skip"] += 1
            else:
                conn.exec_driver_sql(
                    "INSERT INTO harga_harian (komoditas_id, pasar_id, tanggal, harga) VALUES (%s, %s, %s, %s)",
                    (komoditas_id, pasar_id, tanggal, harga_baru)
                )
                stats["total_insert"] += 1
                komoditas_berhasil.append(komoditas_id)
                pasar_berhasil.add(pasar_nama)
                logger.info(f"📥 Insert sukses: {komoditas_nama} di {pasar_nama} -> Rp {harga_baru:,.0f}")
    
    stats["pasar_berhasil"] = sorted(list(pasar_berhasil))
    stats["pasar_harga_0"] = sorted(list(pasar_harga_0))
    stats["komoditas_berhasil_count"] = len(set(komoditas_berhasil))
    stats["komoditas_harga_0_count"] = len(set(komoditas_harga_0))
    
    logger.info("=" * 50)
    logger.info("📈 Ringkasan Upload Database:")
    logger.info(f"  • Insert Baru   : {stats['total_insert']}")
    logger.info(f"  • Update Data   : {stats['total_update']}")
    logger.info(f"  • Diabaikan     : {stats['total_skip']}")
    logger.info(f"  • Harga Nol (0) : {stats['total_harga_0']}")
    logger.info(f"  • Pasar Sukses  : {', '.join(stats['pasar_berhasil']) or '-'}")
    logger.info("=" * 50)
    
    return stats