import os, json, asyncio, requests, paramiko
from datetime import datetime
from lxml import etree
from playwright.async_api import async_playwright

# =======================
# Config
# =======================
QUARTERLY_API = "https://www.nseindia.com/api/integrated-filing-results?index=equities&period_ended=30-Sep-2025&type=Integrated%20Filing-%20Financials&size=5000"
QUARTER_CODE = 127
LOCAL_JSON_PATH = "/var/www/html/stocksupdates/quarterly/127.json"
SERVER_IP = "194.163.33.221"
USERNAME = "u613550457"
PASSWORD = "OOPc@123"
PORT = 65002
REMOTE_BASE = "/home/u613550457/domains/stocknewshub.com/public_html/quarterly"
REMOTE_JSON_UPLOAD_PATH = "/home/u613550457/domains/stocknewshub.com/public_html/quarterly/view/127.json"
SKIPPED_JSON_PATH = "/var/www/html/stocksupdates/quarterly/skipped_127.json"


def get_first(result): return result[0].text.strip() if result and result[0].text else None
def sanitize(name): import re; return re.sub(r"(limited|ltd|\.|,)", "", name or "", flags=re.I).strip()

def parse_nse_date(raw_date):
    for fmt in ("%d-%b-%Y", "%Y-%m-%d"):
        try:
            return datetime.strptime(raw_date, fmt).strftime("%d-%b-%y")
        except ValueError:
            continue
    raise ValueError(f"Unrecognized date format: {raw_date}")

def sftp_makedirs(sftp, remote_path):
    dirs = remote_path.strip("/").split("/")
    current = ""
    for d in dirs:
        current += "/" + d
        try:
            sftp.stat(current)
        except FileNotFoundError:
            sftp.mkdir(current)

def upload_to_multiple_folders(local_file, subfolders):
    try:
        transport = paramiko.Transport((SERVER_IP, PORT))
        transport.connect(username=USERNAME, password=PASSWORD)
        sftp = paramiko.SFTPClient.from_transport(transport)

        filename = os.path.basename(local_file)
        for folder in subfolders:
            full_path = os.path.join(REMOTE_BASE, folder)
            sftp_makedirs(sftp, full_path)
            remote_file = os.path.join(full_path, filename)
            sftp.put(local_file, remote_file)
            print(f"📤 Uploaded to {remote_file}")

        sftp.close()
        transport.close()
    except Exception as e:
        print(f"❌ Upload to multiple folders failed: {e}")


# ==========================
#  XBRL Extraction
# ==========================
def extract_data(xml_bytes):
    root = etree.fromstring(xml_bytes)
    ns = {
        'in-capmkt': "http://www.sebi.gov.in/xbrl/2025-01-31/in-capmkt",
        'xbrli': "http://www.xbrl.org/2003/instance"
    }

    xp = lambda path: root.xpath(path, namespaces=ns)
    def gstr(path): return get_first(xp(path)) or ""
    def gnum(path):
        val = get_first(xp(path))
        try:
            return str(round(float(val.replace(',', '')) / 1_000_000, 2)) if val else "0"
        except Exception:
            return "0"

    def _period_info(ctx_id="OneD"):
        start = root.xpath(f"//xbrli:context[@id='{ctx_id}']/xbrli:period/xbrli:startDate/text()", namespaces=ns)
        end   = root.xpath(f"//xbrli:context[@id='{ctx_id}']/xbrli:period/xbrli:endDate/text()", namespaces=ns)
        if start and end:
            try:
                d1 = datetime.fromisoformat(start[0].strip())
                d2 = datetime.fromisoformat(end[0].strip())
                return start[0].strip(), end[0].strip(), (d2 - d1).days
            except Exception:
                return start[0].strip(), end[0].strip(), None
        return None, None, None

    p_start, p_end, p_days = _period_info("OneD")

    if not p_start or not p_end:
        print("⚠️ No valid OneD context found → skipping")
        return None
    if p_days is None or p_days < 80 or p_days > 100:
        print(f"⚠️ Period length {p_days} days → likely half-year or invalid, skipping")
        return None
    if p_start.startswith("2025-04-01"):
        print("⚠️ Period starts 1-Apr → halfyearly, skipping")
        return None

    data = {
        'bsecode': gstr("//in-capmkt:ScripCode[@contextRef='OneD']"),
        'isin_number': gstr("//in-capmkt:ISIN[@contextRef='OneD']"),
        'company_name': gstr("//in-capmkt:NameOfTheCompany[@contextRef='OneD']"),
        'Net Sales': gnum("//in-capmkt:RevenueFromOperations[@contextRef='OneD']"),
        'Other Income': gnum("//in-capmkt:OtherIncome[@contextRef='OneD']"),
        'Total Income': gnum("//in-capmkt:Income[@contextRef='OneD']"),
        'Expenditure': gnum("//in-capmkt:Expenses[@contextRef='OneD']"),
        'Cost of Materials Consumed': gnum("//in-capmkt:CostOfMaterialsConsumed[@contextRef='OneD']"),
        'Finance Costs': gnum("//in-capmkt:FinanceCosts[@contextRef='OneD']"),
        'Changes in inventories of finished goods, work-in-progress and stock-in-trade':
            gnum("//in-capmkt:ChangesInInventoriesOfFinishedGoodsWorkInProgressAndStockInTrade[@contextRef='OneD']"),
        'Purchases of stock-in-trade': gnum("//in-capmkt:PurchasesOfStockInTrade[@contextRef='OneD']"),
        'Employee benefit expense': gnum("//in-capmkt:EmployeeBenefitExpense[@contextRef='OneD']"),
        'Depreciation and amortisation expense': gnum("//in-capmkt:DepreciationDepletionAndAmortisationExpense[@contextRef='OneD']"),
        'Profit after Interest but before Exceptional Items': gnum("//in-capmkt:ProfitBeforeExceptionalItemsAndTax[@contextRef='OneD']"),
        'Exceptional Item': gnum("//in-capmkt:ExceptionalItemsBeforeTax[@contextRef='OneD']"),
        'Profit (+)/ Loss (-) from Ordinary Activities before Tax': gnum("//in-capmkt:ProfitBeforeTax[@contextRef='OneD']"),
        'Tax': gnum("//in-capmkt:TaxExpense[@contextRef='OneD']"),
        'Net Profit (+)/ Loss (-) from Ordinary Activities after Tax': gnum("//in-capmkt:ProfitLossForPeriodFromContinuingOperations[@contextRef='OneD']"),
        'Net Profit': gnum("//in-capmkt:ProfitLossForPeriod[@contextRef='OneD']"),
        'Current tax': gnum("//in-capmkt:CurrentTax[@contextRef='OneD']"),
        'Deferred tax': gnum("//in-capmkt:DeferredTax[@contextRef='OneD']"),
        'Other Comprehensive Income Net of Taxes': gnum("//in-capmkt:OtherComprehensiveIncomeNetOfTaxes[@contextRef='OneD']"),
        'Total Comprehensive Income for the Period': gnum("//in-capmkt:ComprehensiveIncomeForThePeriod[@contextRef='OneD']"),
        'Share of profit(loss) of associates and joint ventures': gnum("//in-capmkt:ShareOfProfitLossOfAssociatesAndJointVenturesAccountedForUsingEquityMethod[@contextRef='OneD']"),
        'Basic EPS for continuing operation': gstr("//in-capmkt:BasicEarningsLossPerShareFromContinuingOperations[@contextRef='OneD']"),
        'Diluted EPS for continuing operation': gstr("//in-capmkt:DilutedEarningsLossPerShareFromContinuingOperations[@contextRef='OneD']"),
        'Basic for discontinued & continuing operation': gstr("//in-capmkt:BasicEarningsLossPerShareFromContinuingAndDiscontinuedOperations[@contextRef='OneD']"),
        'Diluted for discontinued & continuing operation': gstr("//in-capmkt:DilutedEarningsLossPerShareFromContinuingAndDiscontinuedOperations[@contextRef='OneD']"),
        'Data Type': gstr("//in-capmkt:NatureOfReportStandaloneConsolidated[@contextRef='OneD']"),
        '_period_start': p_start,
        '_period_end': p_end,
        '_period_days': p_days
    }

    found = False
    for i in range(1, 50):
        ctx = f"OneOperatingExpenses0{i}D"
        desc = gstr(f"//in-capmkt:DescriptionOfOtherExpenses[@contextRef='{ctx}']")
        if desc.lower() in ["other expenses", "other expense", "others"]:
            data["Other Expenses"] = gnum(f"//in-capmkt:OtherExpenses[@contextRef='{ctx}']")
            found = True
            break
    if not found:
        data["Other Expenses"] = gnum("//in-capmkt:OtherExpenses[@contextRef='OneD']")

    print(f"✅ Valid OneD context ({p_start} → {p_end}, {p_days} days)")
    return data


# ======================================
#  Fetch NSE quarterly filings (LOCAL JSON)
# ======================================
async def fetch_nse_quarterly():
    print("📥 Loading 127.json locally...")
    try:
        with open(LOCAL_JSON_PATH, "r") as f:
            isin_raw = json.load(f)
        print(f"✅ Loaded {len(isin_raw)} ISINs from local file.")
    except Exception as e:
        print(f"❌ Failed to load local 127.json: {e}")
        return

    isin_map = {x['symbol']: x for x in isin_raw if 'symbol' in x}

    async with async_playwright() as p:
        browser = await p.firefox.launch(headless=True)
        context = await browser.new_context(user_agent="Mozilla/5.0")
        page = await context.new_page()

        print("🌐 Visiting NSE homepage to solve Akamai...")
        await page.goto("https://www.nseindia.com", timeout=60000)
        await page.wait_for_timeout(3000)

        print("📦 Fetching Quarterly Results API via browser...")
        page2 = await context.new_page()
        await page2.goto("https://www.nseindia.com/companies-listing/corporate-integrated-filing", timeout=60000)
        await page2.wait_for_timeout(5000)

        cookies = await context.cookies()
        cookie_dict = {cookie['name']: cookie['value'] for cookie in cookies}
        user_agent = await page2.evaluate("() => navigator.userAgent")

        session = requests.Session()
        headers = {
            "User-Agent": user_agent,
            "Referer": "https://www.nseindia.com/",
            "Accept": "application/json",
            "Accept-Language": "en-US,en;q=0.9",
            "X-Requested-With": "XMLHttpRequest"
        }

        try:
            response = session.get(QUARTERLY_API, headers=headers, cookies=cookie_dict)
            if response.status_code == 200:
                json_data = response.json()
                print(f"✅ Fetched {len(json_data['data'])} results from API.")
            else:
                print(f"❌ API Error: {response.status_code}")
                return
        except Exception as e:
            print(f"❌ Failed to fetch Quarterly API: {str(e)}")
            return

        # --- Build a clean case-insensitive symbol list from local 127.json ---
        local_symbols = {str(x.get("symbol", "")).strip().upper() for x in isin_raw if "symbol" in x}

        # --- Filter the NSE API data to include only those symbols still pending locally ---
        valid = []
        for x in json_data["data"]:
            if not isinstance(x, dict):
                continue
            symbol_key = str(x.get("symbol", "")).strip().upper()
            if symbol_key in local_symbols and x.get("bank") != 'B':
                valid.append(x)

        print(f"✅ Valid entries to process (only from local 127.json): {len(valid)}")


        skipped_isins = []
        processed_symbols = set()

        for item in valid:
            symbol_key = item["symbol"].strip().upper()
            if symbol_key in processed_symbols:
                print(f"⏩ Skipping duplicate symbol already processed: {symbol_key}")
                continue
            processed_symbols.add(symbol_key)

            xml_url = item["xbrl"].split("<br>")[0]
            print(f"➡️ Processing: {item['symbol']} | {xml_url}")
            try:
                response = await page.goto(xml_url, timeout=90000)
                if not response or not response.ok:
                    print("❌ Failed to load XML")
                    continue
                xml_text = await response.text()
                xml_bytes = xml_text.encode("utf-8")
            except Exception as e:
                print(f"❌ Error fetching XML: {e}")
                continue

            data = extract_data(xml_bytes)
            if not data:
                skipped_isins.append(isin_map[item['symbol']])
                continue

            meta = isin_map[item['symbol']]
            data.update({
                "Date Begin": "01-Jul-25",
                "symbol": item["symbol"],
                "Date End": parse_nse_date(item["qe_Date"]),
                "Description": "Amount (Rs. million)",
                "qtr_code": QUARTER_CODE,
                "image_id": meta.get("image_id"),
                "image_url": meta.get("image_url", ""),
                "BSE/NSE": "NSE"
            })

            required_keys = [
                "Net Sales", "Net Profit",
                "Basic EPS for continuing operation", "Diluted EPS for continuing operation",
                "Basic for discontinued & continuing operation", "Diluted for discontinued & continuing operation"
            ]
            if all(data.get(k) in ["", "0", 0, None] for k in required_keys):
                print(f"⚠️ Skipping {item['symbol']} as all key financial values are empty or zero.")
                skipped_isins.append(meta)
                continue

            if data.get("bsecode") in ["000000", "123456"]:
                data.pop("bsecode", None)
                continue

            bse = data.get("bsecode")
            filename = f"/var/www/html/stocksupdates/quarterly/json/{bse}.json"
            with open(filename, "w") as f:
                json.dump(data, f, indent=4)

            upload_to_multiple_folders(filename, ["post/json", "push/json", "video/json", "view/json", "video/shorts/json", "socialMedia/json"])
            print(f"✅ Saved & uploaded: {bse} - {data['company_name']}")

            # 🧹 Remove processed entry locally
            try:
                processed_oid = None
                if symbol_key in isin_map and "_id" in isin_map[symbol_key] and "$oid" in isin_map[symbol_key]["_id"]:
                    processed_oid = isin_map[symbol_key]["_id"]["$oid"]

                if processed_oid:
                    isin_raw = [i for i in isin_raw if i.get("_id", {}).get("$oid") != processed_oid]
                    print(f"🗑 Removed entry by _id: {processed_oid}")
                else:
                    isin_raw = [i for i in isin_raw if str(i.get("symbol", "")).strip().upper() != symbol_key]
                    print(f"🗑 Removed entry by symbol: {symbol_key}")

                with open(LOCAL_JSON_PATH, "w") as f:
                    json.dump(isin_raw, f, indent=4)
                print(f"💾 Updated local 127.json after processing {symbol_key}")

            except Exception as e:
                print(f"⚠️ Failed to update local 127.json after {item.get('symbol')}: {e}")

        # skipped file
        if skipped_isins:
            existing_skipped = []
            if os.path.exists(SKIPPED_JSON_PATH):
                with open(SKIPPED_JSON_PATH, "r") as f:
                    try:
                        existing_skipped = json.load(f)
                    except json.JSONDecodeError:
                        existing_skipped = []
            existing_symbols = {entry['symbol'] for entry in existing_skipped if 'symbol' in entry}
            new_entries = [entry for entry in skipped_isins if entry['symbol'] not in existing_symbols]
            combined_skipped = existing_skipped + new_entries
            with open(SKIPPED_JSON_PATH, "w") as f:
                json.dump(combined_skipped, f, indent=4)

        print("🏁 Done (local JSON mode)!")


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