import asyncio
import sys
import re
import pandas as pd
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import text
from decimal import Decimal
import datetime
import uuid
import shutil
import tempfile

import os
BASE_DIR = os.path.dirname(os.path.dirname(os.path.dirname(os.path.abspath(__file__))))
sys.path.insert(0, os.path.join(BASE_DIR, 'backend'))
from app.core.database import AsyncSessionLocal
from app.models.maintenance_models import MachineConsumptionCost, EquipmentPurchase

FILEPATH = os.path.join(BASE_DIR, 'Maintainance', 'monthly cost', 'MONTHALY COST 2026.xlsx')

MONTH_MAP = {
    'JAN': 1, 'FEB': 2, 'MARCH': 3, 'APRILL': 4, 'MAY': 5, 'JUNE': 6,
    'JULY': 7, 'AUG': 8, 'SEP': 9, 'OCT': 10, 'NOV': 11, 'DEC': 12,
    'MAR': 3, 'APR': 4, 'APRIL': 4
}

def to_decimal(val):
    if pd.isna(val) or val is None or val == '':
        return Decimal('0.00')
    try:
        # Handle string numbers with commas
        if isinstance(val, str):
            val = val.replace(',', '').strip()
            if not val or val.lower() == 'nan':
                return Decimal('0.00')
        return Decimal(str(val))
    except:
        return Decimal('0.00')

async def ingest_machine_consumption(db: AsyncSession, filepath: str):
    print("Ingesting Machine Consumption Cost...")
    try:
        df = pd.read_excel(filepath, sheet_name='MAIN SHEET ', header=None)
    except ValueError:
        try:
             df = pd.read_excel(filepath, sheet_name='MAIN SHEET', header=None)
        except ValueError:
             print("Could not find MAIN SHEET")
             return

    # Delete existing data for 2026 to prevent duplicates
    await db.execute(text("DELETE FROM maintenance_machine_consumption_cost WHERE year = 2026"))
    await db.commit()

    # Data starts around row 6 (0-indexed)
    # Audi columns: Jan(3), Feb(5), Mar(7), Apr(9), May(11), Jun(13), Jul(15), Aug(17), Sep(19), Oct(21), Nov(23), Dec(25)
    
    count = 0
    for idx, row in df.iterrows():
        if idx < 6: # Skip headers
            continue
            
        machine_name = row[2]
        
        # Skip empty rows or totals
        m_lower = str(machine_name).strip().lower()
        if pd.isna(machine_name) or m_lower in ('', '0', 'nan'):
            continue
        if str(machine_name).strip().lower() == 'total':
            continue
            
        # Stop before reading Unit-3 data
        row_str = " ".join([str(x).lower() for x in row if pd.notna(x)])
        if "unit - 3" in row_str:
            break
            
        machine_name = str(machine_name).strip()
        
        jan = to_decimal(row[3])
        feb = to_decimal(row[5])
        mar = to_decimal(row[7])
        apr = to_decimal(row[9])
        may = to_decimal(row[11])
        jun = to_decimal(row[13])
        jul = to_decimal(row[15])
        aug = to_decimal(row[17])
        sep = to_decimal(row[19])
        oct = to_decimal(row[21])
        nov = to_decimal(row[23])
        dec = to_decimal(row[25])

        record = MachineConsumptionCost(
            id=uuid.uuid4(),
            machine_name=machine_name,
            display_order=count + 1,
            year=2026,
            jan_cost=jan,
            feb_cost=feb,
            mar_cost=mar,
            apr_cost=apr,
            may_cost=may,
            jun_cost=jun,
            jul_cost=jul,
            aug_cost=aug,
            sep_cost=sep,
            oct_cost=oct,
            nov_cost=nov,
            dec_cost=dec
        )
        db.add(record)
        count += 1
        
        if "%/ bus" in machine_name.lower():
            break

    await db.commit()
    print(f"Successfully ingested {count} Machine Consumption records.")

async def ingest_equipment_purchase(db: AsyncSession, filepath: str):
    print("Ingesting Equipment Purchase...")
    try:
        df = pd.read_excel(filepath, sheet_name='PLANT COST ', header=None)
    except ValueError:
        try:
             df = pd.read_excel(filepath, sheet_name='PLANT COST', header=None)
        except ValueError:
             print("Could not find PLANT COST sheet")
             return

    # Delete existing data for 2026 to prevent duplicates
    await db.execute(text("DELETE FROM maintenance_equipment_purchase WHERE purchase_year = 2026"))
    await db.commit()

    current_month = None
    count = 0

    for idx, row in df.iterrows():
        # Look for month headers like "PLANT COST JAN . 2026"
        val1 = str(row[1]).strip() if pd.notna(row[1]) else ""
        if "PLANT COST" in val1.upper():
            match = re.search(r'PLANT COST\s+([A-Za-z]+)\s*\.?\s*2026', val1.upper())
            if match:
                month_str = match.group(1)
                current_month = MONTH_MAP.get(month_str, None)
                if not current_month:
                    for k, v in MONTH_MAP.items():
                        if month_str.startswith(k):
                            current_month = v
                            break
            continue
            
        if not current_month:
            continue
            
        # Skip header rows
        if "Machin" in val1 or "Sr.No" in val1 or "total" in val1.lower():
            continue
            
        # Also check column 4 for total
        val4 = str(row[4]).strip() if pd.notna(row[4]) else ""
        if "total" in val4.lower():
            continue

        equip_name = str(row[2]).strip() if pd.notna(row[2]) else ""
        if not equip_name or equip_name.lower() == 'nan':
            continue

        qty = to_decimal(row[3])
        price = to_decimal(row[4])
        amount = to_decimal(row[5])
        
        if qty == 0 and amount == 0:
            continue
            
        record = EquipmentPurchase(
            id=uuid.uuid4(),
            purchase_month=current_month,
            purchase_year=2026,
            equipment_name=equip_name,
            quantity=qty,
            unit_price=price,
            total_amount=amount,
            remarks=None
        )
        db.add(record)
        count += 1
        
    await db.commit()
    print(f"Successfully ingested {count} Equipment Purchase records.")


async def main():
    if not os.path.exists(FILEPATH):
        print(f"Error: Could not find {FILEPATH}")
        sys.exit(1)
        
    # Copy to temp file to bypass "file in use" error
    tmp = tempfile.mktemp(suffix='.xlsx')
    shutil.copy2(FILEPATH, tmp)
    
    try:
        async with AsyncSessionLocal() as db:
            await ingest_machine_consumption(db, tmp)
            await ingest_equipment_purchase(db, tmp)
    finally:
        if os.path.exists(tmp):
            os.unlink(tmp)

if __name__ == '__main__':
    asyncio.run(main())