import asyncio
import os
import glob
import pandas as pd
from datetime import datetime
import re

import sys
sys.path.append(os.path.join(os.path.dirname(__file__), '..'))

from app.core.database import AsyncSessionLocal
from app.models.maintenance_models import (
    IncomingMaterial, EquipmentRental, DieselRecord, LPGGasRecord, SolarMaintenanceRecord
)

def parse_date(d):
    if pd.isna(d): return None
    if isinstance(d, datetime): return d.date()
    if isinstance(d, str):
        try:
            return pd.to_datetime(d, dayfirst=True).date()
        except:
            return None
    return None

def parse_float(val):
    if pd.isna(val): return None
    if isinstance(val, (int, float)): return float(val)
    m = re.search(r'[-+]?\d*\.\d+|\d+', str(val))
    if m: return float(m.group())
    return None

async def ingest():
    BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
    folder = os.path.join(BASE_DIR, "Maintainance", "incoming material")
    files = glob.glob(os.path.join(folder, "*.xls*"))

    async with AsyncSessionLocal() as db:
        for f in files:
            basename = os.path.basename(f).lower()
            print(f"Processing {basename}...")
            
            try:
                xls = pd.ExcelFile(f)
            except Exception as e:
                print(f"Failed to read {basename}: {e}")
                continue

            # 1. DESAL AND LPG GAS
            if 'desal and lpg' in basename:
                for sheet in xls.sheet_names:
                    df = pd.read_excel(f, sheet_name=sheet)
                    header_idx = None
                    for i, row in df.iterrows():
                        if any('DATE' in str(v).upper() for v in row.values):
                            header_idx = i
                            break
                    if header_idx is not None:
                        df = pd.read_excel(f, sheet_name=sheet, skiprows=header_idx + 1)
                    df.columns = df.columns.astype(str).str.strip().str.upper()
                    
                    if 'DISAL' in sheet.upper():
                        for _, row in df.iterrows():
                            dt = parse_date(row.get('DATE', row.get('DATE ')))
                            if not dt: continue
                            dg_set = str(row.get('DG SET', row.get('DG SET ', '')))
                            if not dg_set or dg_set.lower() == 'nan': continue
                            ltr = parse_float(row.get('LTR', row.get('LTR '))) or 0.0
                            oil_ltr = parse_float(row.get('OIL /LTR ', row.get('OIL /LTR')))
                            db.add(DieselRecord(entry_date=dt, dg_set_no=dg_set, diesel_ltr=ltr, oil_ltr=oil_ltr))
                            
                    elif 'LPG GAS' in sheet.upper():
                        for _, row in df.iterrows():
                            dt = parse_date(row.get('DATE', row.get('DATE ')))
                            if not dt: continue
                            gas_name = str(row.get('GAS', row.get('GAS ', '')))
                            qty = parse_float(row.get('QTY', row.get('QTY '))) or 0
                            kg = parse_float(row.get('KG', row.get('KG '))) or 0.0
                            price = parse_float(row.get('[PRICE', row.get('PRICE')))
                            bill_no = str(row.get('BILL NO ', row.get('BILL NO')))
                            if bill_no.lower() == 'nan': bill_no = None
                            total = (kg * price) if price else None
                            db.add(LPGGasRecord(entry_date=dt, gas_name=gas_name, quantity=int(qty), kg=kg, price=price, total_amount=total, bill_no=bill_no))
                            
                    elif 'TEHTI ENERGY' in sheet.upper():
                        for _, row in df.iterrows():
                            dt = parse_date(row.get('DATE', row.get('DATE ')))
                            if not dt: continue
                            part = str(row.get('PART', row.get('PART ', '')))
                            qty = parse_float(row.get('QTY', row.get('QTY '))) or 0.0
                            price = parse_float(row.get('PRICE', row.get('PRICE '))) or 0.0
                            bill_no = str(row.get('BILL NO ', row.get('BILL NO')))
                            if bill_no.lower() == 'nan': bill_no = None
                            db.add(SolarMaintenanceRecord(entry_date=dt, part_name=part, quantity=qty, price=price, total_amount=qty*price, bill_no=bill_no))

            # 2. hedra record.xlsx
            elif 'hedra record' in basename:
                df = pd.read_excel(f, sheet_name=0)
                header_idx = None
                for i, row in df.iterrows():
                    if any('DATE' in str(v).upper() for v in row.values):
                        header_idx = i
                        break
                if header_idx is not None:
                    df = pd.read_excel(f, sheet_name=0, skiprows=header_idx + 1)
                df.columns = df.columns.astype(str).str.strip().str.upper()
                date_col = next((c for c in df.columns if 'DATE' in c), None)
                if date_col:
                    current_vendor = 'Other'
                    current_eq_type = 'Hydra Crane'
                    for _, row in df.iterrows():
                        dt = parse_date(row.get(date_col))
                        if not dt: continue
                        
                        equip_raw = str(row.get('MATERIYAL', '')).strip()
                        if equip_raw and equip_raw.lower() != 'nan':
                            l_equip = equip_raw.lower()
                            if 'juneja' in l_equip and 'hydra' in l_equip:
                                current_vendor, current_eq_type = 'Juneja Hydra', 'Hydra Crane'
                            elif 'juneja' in l_equip and ('forcilip' in l_equip or 'forklift' in l_equip):
                                current_vendor, current_eq_type = 'Juneja Forklift', 'Forklift'
                            elif 'mukati' in l_equip:
                                current_vendor, current_eq_type = 'Mukati Crane', 'Hydra Crane'
                            elif 'hydra' in l_equip:
                                current_vendor, current_eq_type = 'Other', 'Hydra Crane'
                            elif 'forcilip' in l_equip or 'forklift' in l_equip:
                                current_vendor, current_eq_type = 'Other', 'Forklift'
                            else:
                                current_vendor = equip_raw
                                current_eq_type = 'Other'

                        in_t = str(row.get('IN TIME', row.get('IN TIME ')))
                        out_t = str(row.get('OUT TIME', row.get('OUT TIME ')))
                        desc = str(row.get('WORK', row.get('WORK DETAILS')))
                        hrs = parse_float(row.get('HOUR', row.get('HOURS')))
                        bill = str(row.get('BILL NO', row.get('BILL NO.')))
                        amount = parse_float(row.get('AMOUNT ', row.get('AMOUNT')))

                        db.add(EquipmentRental(
                            rental_date=dt, vendor=current_vendor, equipment_type=current_eq_type,
                            in_time=in_t if in_t.lower()!='nan' else None,
                            out_time=out_t if out_t.lower()!='nan' else None,
                            hours=hrs,
                            work_description=desc if desc.lower()!='nan' else None,
                            bill_no=bill if bill.lower()!='nan' else None,
                            amount=amount
                        ))

            # 3. Incoming materials Co2, Plasma, Burner, Harsh trading.xls
            elif 'co2, plasma, burner, harsh trading' in basename:
                for sheet in xls.sheet_names:
                    df = pd.read_excel(f, sheet_name=sheet)
                    # Find header row
                    header_idx = None
                    for i, row in df.iterrows():
                        if any('DATE' in str(v).upper() for v in row.values) and any('MATERIAL' in str(v).upper() for v in row.values):
                            header_idx = i
                            break
                    if header_idx is not None:
                        df = pd.read_excel(f, sheet_name=sheet, skiprows=header_idx + 1)
                    df.columns = df.columns.astype(str).str.strip().str.upper()
                    
                    date_col = next((c for c in df.columns if 'DATE' in c), None)
                    mat_col = next((c for c in df.columns if 'MATERIAL' in c or 'PART' in c), None)
                    qty_col = next((c for c in df.columns if 'QTY' in c or 'QUANTITY' in c), None)
                    rate_col = next((c for c in df.columns if 'RATE' in c or 'RS' in c), None)
                    
                    if not date_col or not mat_col: continue
                    
                    current_dt = None
                    current_vendor = sheet if sheet.lower() != 'other materials' else 'Unknown'
                    
                    for _, row in df.iterrows():
                        dt = parse_date(row.get(date_col))
                        if dt: current_dt = dt
                        
                        mat = str(row.get(mat_col, ''))
                        if not current_dt or not mat or mat.lower() == 'nan': continue
                        
                        qty = parse_float(row.get(qty_col)) if qty_col else 1.0
                        if qty is None: qty = 1.0
                        rate = parse_float(row.get(rate_col)) if rate_col else None
                        
                        bill_no = str(row.get('BILL NO.', row.get('BILL NO', '')))
                        discount = parse_float(row.get('DISCOUNT', row.get('DIS.', None)))
                        challan = str(row.get('CHALLAN NO', ''))
                        
                        dealer_val = str(row.get('DELLER', ''))
                        if dealer_val.lower() != 'nan' and dealer_val.strip() != '':
                            current_vendor = dealer_val
                        
                        db.add(IncomingMaterial(
                            entry_date=current_dt, vendor=current_vendor,
                            category='CO2_PLASMA_BURNER', material_name=mat, quantity=qty, rate=rate,
                            total_amount=(qty*rate if rate else None),
                            bill_no=bill_no if bill_no.lower()!='nan' else None,
                            discount=discount, challan_no=challan if challan.lower()!='nan' else None
                        ))

            # 4. Incoming materials compressors, hoists.xlsx
            elif 'compressors, hoists' in basename:
                for sheet in xls.sheet_names:
                    df = pd.read_excel(f, sheet_name=sheet)
                    df.columns = df.columns.astype(str).str.strip().str.upper()
                    date_col = next((c for c in df.columns if 'DATE' in c), None)
                    mat_col = next((c for c in df.columns if 'MATERIAL' in c), None)
                    if not date_col or not mat_col: continue
                    
                    current_dt = None
                    current_vendor = 'Unknown'
                    for _, row in df.iterrows():
                        dt = parse_date(row.get(date_col))
                        if dt: current_dt = dt
                        mat = str(row.get(mat_col, ''))
                        if not current_dt or not mat or mat.lower() == 'nan': continue
                        
                        qty = parse_float(row.get('QUANTITY')) or 1.0
                        rate = parse_float(row.get('PRIZE EACH'))
                        
                        dealer = str(row.get('DEALERS NAME', ''))
                        if dealer.lower() != 'nan' and dealer.strip() != '':
                            current_vendor = dealer
                            
                        bill_col = next((c for c in df.columns if 'BILL' in c), None)
                        bill = str(row.get(bill_col, '')) if bill_col else ''
                        
                        db.add(IncomingMaterial(
                            entry_date=current_dt, vendor=current_vendor,
                            category=sheet, material_name=mat, quantity=qty, rate=rate,
                            total_amount=(qty*rate if rate else None), bill_no=bill if bill.lower()!='nan' else None
                        ))

            # 5. PNEUAMATIC INCOMING MATARIALS.xlsx
            elif 'pneuamatic' in basename:
                for sheet in xls.sheet_names:
                    df = pd.read_excel(f, sheet_name=sheet)
                    header_idx = None
                    for i, row in df.iterrows():
                        if any('DATE' in str(v).upper() for v in row.values):
                            header_idx = i
                            break
                    if header_idx is not None:
                        df = pd.read_excel(f, sheet_name=sheet, skiprows=header_idx + 1)
                    df.columns = df.columns.astype(str).str.strip().str.upper()
                    
                    date_col = next((c for c in df.columns if 'DATE' in c), None)
                    mat_col = next((c for c in df.columns if 'MATERIAL' in c), None)
                    if not date_col or not mat_col: continue
                    
                    current_dt = None
                    for _, row in df.iterrows():
                        dt = parse_date(row.get(date_col))
                        if dt: current_dt = dt
                        mat = str(row.get(mat_col, ''))
                        if not current_dt or not mat or mat.lower() == 'nan': continue
                        
                        qty = parse_float(row.get('QUANTITY')) or 1.0
                        rate = parse_float(row.get('RATE EACH'))
                        bill = str(row.get('BILL NO', row.get('BILL NO.', '')))
                        
                        db.add(IncomingMaterial(
                            entry_date=current_dt, vendor=sheet, category='PNEUMATIC', material_name=mat,
                            quantity=qty, rate=rate, total_amount=(qty*rate if rate else None),
                            bill_no=bill if bill.lower()!='nan' else None
                        ))

            # 6. RGP MATERIAL PUSHPAK ELE.1.xls
            elif 'rgp material pushpak' in basename:
                for sheet in xls.sheet_names:
                    if 'FIRE' in sheet.upper(): continue # Not parts tracking
                    
                    df = pd.read_excel(f, sheet_name=sheet)
                    header_idx = None
                    for i, row in df.iterrows():
                        if any('DATE' in str(v).upper() for v in row.values) and any('DISCRIPTION' in str(v).upper() for v in row.values):
                            header_idx = i
                            break
                    if header_idx is not None:
                        df = pd.read_excel(f, sheet_name=sheet, skiprows=header_idx)
                    df.columns = df.columns.astype(str).str.strip().str.upper()
                    
                    date_col = next((c for c in df.columns if 'DATE' in c), None)
                    mat_col = next((c for c in df.columns if 'DISCRIPTION' in c), None)
                    if not date_col or not mat_col: continue
                    
                    current_dt = None
                    for _, row in df.iterrows():
                        dt = parse_date(row.get(date_col))
                        if dt: current_dt = dt
                        mat = str(row.get(mat_col, ''))
                        if not current_dt or not mat or mat.lower() == 'nan': continue
                        
                        qty = parse_float(row.get('QTY.', row.get('QTY'))) or 1.0
                        rate = parse_float(row.get('RATE', row.get('COST')))
                        bill = str(row.get('BILL NO', row.get('BILL NO ')))
                        
                        db.add(IncomingMaterial(
                            entry_date=current_dt, vendor=sheet, category='RGP_ELECTRICAL', material_name=mat,
                            quantity=qty, rate=rate, total_amount=(qty*rate if rate else None),
                            bill_no=bill if bill.lower()!='nan' else None
                        ))

        try:
            await db.commit()
            print("Successfully ingested incoming materials v2!")
        except Exception as e:
            print(f"Error during commit: {e}")
            await db.rollback()

if __name__ == "__main__":
    asyncio.run(ingest())