import json
from datetime import datetime
from typing import Optional

import aiosqlite

from utils.db import get_db
from utils.rib import generer_rib, valider_rib, decoder_rib

GUIICHET_PARTICULIER = 4
GUIICHET_ENTREPRISE = 3
GUIICHET_BANQUE = 2
GUIICHET_INSTITUTION = 1


class BankManager:

    async def creer_banque(self, guild_id: int, name: str, role_id: int) -> dict:
        db = await get_db()
        try:
            cursor = await db.execute(
                "SELECT COALESCE(MAX(establishment_code), 9999) FROM banks WHERE guild_id = ?",
                (guild_id,),
            )
            row = await cursor.fetchone()
            last_code = row[0]
            new_code = last_code + 1
            if new_code > 89999:
                raise ValueError("Plus de codes d'établissement disponibles.")

            cursor = await db.execute(
                "INSERT INTO banks (guild_id, name, role_id, establishment_code) VALUES (?, ?, ?, ?)",
                (guild_id, name, role_id, new_code),
            )
            await db.commit()
            bank_id = cursor.lastrowid
            return {"id": bank_id, "name": name, "role_id": role_id, "establishment_code": new_code}
        finally:
            await db.close()

    async def supprimer_banque(self, guild_id: int, bank_id: int) -> bool:
        db = await get_db()
        try:
            cursor = await db.execute(
                "DELETE FROM banks WHERE id = ? AND guild_id = ?", (bank_id, guild_id)
            )
            await db.commit()
            return cursor.rowcount > 0
        finally:
            await db.close()

    async def get_banque(self, guild_id: int, bank_id: int) -> Optional[dict]:
        db = await get_db()
        try:
            cursor = await db.execute(
                "SELECT * FROM banks WHERE id = ? AND guild_id = ?", (bank_id, guild_id)
            )
            row = await cursor.fetchone()
            return dict(row) if row else None
        finally:
            await db.close()

    async def get_banque_by_role(self, guild_id: int, role_id: int) -> Optional[dict]:
        db = await get_db()
        try:
            cursor = await db.execute(
                "SELECT * FROM banks WHERE guild_id = ? AND role_id = ?", (guild_id, role_id)
            )
            row = await cursor.fetchone()
            return dict(row) if row else None
        finally:
            await db.close()

    async def get_banque_by_name(self, guild_id: int, name: str) -> Optional[dict]:
        db = await get_db()
        try:
            cursor = await db.execute(
                "SELECT * FROM banks WHERE guild_id = ? AND name = ?", (guild_id, name)
            )
            row = await cursor.fetchone()
            return dict(row) if row else None
        finally:
            await db.close()

    async def lister_banques(self, guild_id: int) -> list:
        db = await get_db()
        try:
            cursor = await db.execute(
                "SELECT * FROM banks WHERE guild_id = ? ORDER BY name", (guild_id,)
            )
            rows = await cursor.fetchall()
            return [dict(r) for r in rows]
        finally:
            await db.close()

    async def ouvrir_compte(
        self,
        guild_id: int,
        bank_id: int,
        user_id: int,
        account_type: str,
        tva_rate: float = 0.0,
        password: str = None,
    ) -> dict:
        db = await get_db()
        try:
            cursor = await db.execute(
                "SELECT * FROM banks WHERE id = ? AND guild_id = ?", (bank_id, guild_id)
            )
            bank = await cursor.fetchone()
            if not bank:
                raise ValueError("Banque introuvable.")

            guichet = GUIICHET_ENTREPRISE if account_type == "entreprise" else GUIICHET_PARTICULIER

            cursor = await db.execute(
                "SELECT COUNT(*) FROM accounts WHERE guild_id = ? AND bank_id = ? AND user_id = ? AND account_type = ?",
                (guild_id, bank_id, user_id, account_type),
            )
            count = (await cursor.fetchone())[0]
            if count > 0:
                raise ValueError(f"Ce membre a déjà un compte {account_type} dans cette banque.")

            rib = generer_rib(bank["establishment_code"], guichet)

            cursor = await db.execute(
                "INSERT INTO accounts (guild_id, bank_id, user_id, rib, account_type, tva_rate, password) VALUES (?, ?, ?, ?, ?, ?, ?)",
                (guild_id, bank_id, user_id, rib, account_type, tva_rate, password),
            )
            await db.commit()
            return {"id": cursor.lastrowid, "rib": rib, "account_type": account_type, "tva_rate": tva_rate}
        finally:
            await db.close()

    async def get_compte_by_rib(self, guild_id: int, rib: str) -> Optional[dict]:
        db = await get_db()
        try:
            cursor = await db.execute(
                """SELECT a.*, b.name as bank_name, b.establishment_code, b.role_id as bank_role_id
                   FROM accounts a JOIN banks b ON a.bank_id = b.id
                   WHERE a.rib = ? AND a.guild_id = ?""",
                (rib, guild_id),
            )
            row = await cursor.fetchone()
            return dict(row) if row else None
        finally:
            await db.close()

    async def get_comptes_user(self, guild_id: int, user_id: int) -> list:
        db = await get_db()
        try:
            cursor = await db.execute(
                """SELECT a.*, b.name as bank_name, b.establishment_code
                   FROM accounts a JOIN banks b ON a.bank_id = b.id
                   WHERE a.guild_id = ? AND a.user_id = ?
                   ORDER BY b.name, a.account_type""",
                (guild_id, user_id),
            )
            rows = await cursor.fetchall()
            return [dict(r) for r in rows]
        finally:
            await db.close()

    async def get_comptes_banque(self, guild_id: int, bank_id: int) -> list:
        db = await get_db()
        try:
            cursor = await db.execute(
                """SELECT a.*, b.name as bank_name
                   FROM accounts a JOIN banks b ON a.bank_id = b.id
                   WHERE a.guild_id = ? AND a.bank_id = ?
                   ORDER BY a.user_id, a.account_type""",
                (guild_id, bank_id),
            )
            rows = await cursor.fetchall()
            return [dict(r) for r in rows]
        finally:
            await db.close()

    async def modifier_tva(self, guild_id: int, rib: str, nouveau_taux: float) -> bool:
        db = await get_db()
        try:
            cursor = await db.execute(
                "UPDATE accounts SET tva_rate = ? WHERE rib = ? AND guild_id = ? AND account_type = 'entreprise'",
                (nouveau_taux, rib, guild_id),
            )
            await db.commit()
            return cursor.rowcount > 0
        finally:
            await db.close()

    async def virement(
        self,
        guild_id: int,
        rib_source: str,
        rib_dest: str,
        montant: float,
        emetteur_id: int = None,
        destinataire_id: int = None,
    ) -> dict:
        if montant <= 0:
            raise ValueError("Le montant doit être supérieur à 0.")
        if rib_source == rib_dest:
            raise ValueError("Impossible de se virer à soi-même.")

        db = await get_db()
        try:
            source = await self._get_account(db, guild_id, rib_source)
            if not source:
                raise ValueError("Compte source introuvable.")
            dest = await self._get_account(db, guild_id, rib_dest)
            if not dest:
                raise ValueError("Compte destination introuvable.")

            if source["solde"] < montant:
                cursor = await db.execute(
                    "SELECT client_negative_floor FROM banks WHERE id = ? AND guild_id = ?",
                    (source["bank_id"], guild_id),
                )
                bank_row = await cursor.fetchone()
                floor = bank_row[0] if bank_row else 0.0
                nouveau_solde = source["solde"] - montant
                if nouveau_solde < -floor:
                    raise ValueError(
                        f"Solde insuffisant. Disponible : {source['solde']:.2f}€, plafond négatif : {-floor:.2f}€"
                    )

            tva_montant = 0.0
            montant_net = montant

            if source["account_type"] == "particulier" and dest["account_type"] == "entreprise":
                tva_rate = dest["tva_rate"] or 0.0
                if tva_rate > 0:
                    tva_montant = round(montant * tva_rate / 100, 2)
                    montant_net = round(montant - tva_montant, 2)

            cursor = await db.execute(
                "UPDATE accounts SET solde = solde - ? WHERE rib = ? AND guild_id = ?",
                (montant, rib_source, guild_id),
            )
            cursor = await db.execute(
                "UPDATE accounts SET solde = solde + ? WHERE rib = ? AND guild_id = ?",
                (montant_net, rib_dest, guild_id),
            )

            if tva_montant > 0:
                fisc_rib = await self._get_fisc_rib(db, guild_id)
                if fisc_rib:
                    cursor = await db.execute(
                        "UPDATE accounts SET solde = solde + ? WHERE rib = ? AND guild_id = ?",
                        (tva_montant, fisc_rib, guild_id),
                    )

            cursor = await db.execute(
                "UPDATE banks SET clients_total_balance = clients_total_balance - ? WHERE id = ? AND guild_id = ?",
                (montant, source["bank_id"], guild_id),
            )
            cursor = await db.execute(
                "UPDATE banks SET clients_total_balance = clients_total_balance + ? WHERE id = ? AND guild_id = ?",
                (montant_net, dest["bank_id"], guild_id),
            )

            now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            await db.execute(
                """INSERT INTO transactions (guild_id, emetteur_rib, destinataire_rib, emetteur_id, destinataire_id, montant, type, date)
                   VALUES (?, ?, ?, ?, ?, ?, ?, ?)""",
                (guild_id, rib_source, rib_dest, emetteur_id or source["user_id"], destinataire_id or dest["user_id"], montant, "virement", now),
            )
            if tva_montant > 0 and fisc_rib:
                await db.execute(
                    """INSERT INTO transactions (guild_id, destinataire_rib, montant, type, date, metadata)
                       VALUES (?, ?, ?, ?, ?, ?)""",
                    (guild_id, fisc_rib, tva_montant, "tva_prelevee", now, json.dumps({"source_rib": rib_source, "dest_rib": rib_dest})),
                )

            await db.commit()

            result = {
                "montant_envoye": montant,
                "montant_recu": montant_net,
                "tva_prelevee": tva_montant,
            }
            if tva_montant > 0:
                result["tva_dest"] = fisc_rib
            return result
        finally:
            await db.close()

    async def depot_banquier(
        self, guild_id: int, rib: str, montant: float, banquier_id: int
    ) -> dict:
        if montant <= 0:
            raise ValueError("Le montant doit être supérieur à 0.")

        db = await get_db()
        try:
            compte = await self._get_account(db, guild_id, rib)
            if not compte:
                raise ValueError("Compte introuvable.")

            cursor = await db.execute(
                "SELECT vault_balance FROM banks WHERE id = ? AND guild_id = ?",
                (compte["bank_id"], guild_id),
            )
            bank_row = await cursor.fetchone()
            if not bank_row:
                raise ValueError("Banque introuvable.")
            vault = bank_row[0]
            if vault < montant:
                raise ValueError(
                    f"Réserve bancaire insuffisante. Disponible : {vault:.2f}€"
                )

            cursor = await db.execute(
                "UPDATE accounts SET solde = solde + ? WHERE rib = ? AND guild_id = ?",
                (montant, rib, guild_id),
            )
            cursor = await db.execute(
                "UPDATE banks SET clients_total_balance = clients_total_balance + ?, vault_balance = vault_balance - ? WHERE id = ? AND guild_id = ?",
                (montant, montant, compte["bank_id"], guild_id),
            )

            now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            await db.execute(
                """INSERT INTO transactions (guild_id, destinataire_rib, destinataire_id, montant, type, date, metadata)
                   VALUES (?, ?, ?, ?, ?, ?, ?)""",
                (guild_id, rib, compte["user_id"], montant, "depot_banquier", now, json.dumps({"banquier_id": banquier_id})),
            )
            await db.commit()
            return {"nouveau_solde": compte["solde"] + montant}
        finally:
            await db.close()

    async def retrait_banquier(
        self, guild_id: int, rib: str, montant: float, banquier_id: int
    ) -> dict:
        if montant <= 0:
            raise ValueError("Le montant doit être supérieur à 0.")

        db = await get_db()
        try:
            compte = await self._get_account(db, guild_id, rib)
            if not compte:
                raise ValueError("Compte introuvable.")

            cursor = await db.execute(
                "SELECT client_negative_floor FROM banks WHERE id = ? AND guild_id = ?",
                (compte["bank_id"], guild_id),
            )
            bank_row = await cursor.fetchone()
            floor = bank_row[0] if bank_row else 0.0
            nouveau_solde = compte["solde"] - montant
            if nouveau_solde < -floor:
                raise ValueError(
                    f"Plafond négatif atteint ({-floor:.2f}€). Solde actuel : {compte['solde']:.2f}€"
                )

            cursor = await db.execute(
                "UPDATE accounts SET solde = solde - ? WHERE rib = ? AND guild_id = ?",
                (montant, rib, guild_id),
            )
            cursor = await db.execute(
                "UPDATE banks SET clients_total_balance = clients_total_balance - ?, vault_balance = vault_balance + ? WHERE id = ? AND guild_id = ?",
                (montant, montant, compte["bank_id"], guild_id),
            )

            now = datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            await db.execute(
                """INSERT INTO transactions (guild_id, emetteur_rib, emetteur_id, montant, type, date, metadata)
                   VALUES (?, ?, ?, ?, ?, ?, ?)""",
                (guild_id, rib, compte["user_id"], montant, "retrait_banquier", now, json.dumps({"banquier_id": banquier_id})),
            )
            await db.commit()
            return {"nouveau_solde": nouveau_solde}
        finally:
            await db.close()

    async def get_transactions(self, guild_id: int, rib: str, limit: int = 10) -> list:
        db = await get_db()
        try:
            cursor = await db.execute(
                """SELECT * FROM transactions
                   WHERE guild_id = ? AND (emetteur_rib = ? OR destinataire_rib = ?)
                   ORDER BY date DESC LIMIT ?""",
                (guild_id, rib, rib, limit),
            )
            rows = await cursor.fetchall()
            return [dict(r) for r in rows]
        finally:
            await db.close()

    async def creer_institution(
        self, guild_id: int, name: str, role_id: int = None, can_audit_all: bool = False
    ) -> dict:
        db = await get_db()
        try:
            cursor = await db.execute(
                "INSERT INTO institutions (guild_id, name, role_id, can_audit_all) VALUES (?, ?, ?, ?)",
                (guild_id, name, role_id, int(can_audit_all)),
            )
            await db.commit()
            return {"id": cursor.lastrowid, "name": name, "can_audit_all": can_audit_all}
        finally:
            await db.close()

    async def get_institution(self, guild_id: int, institution_id: int) -> Optional[dict]:
        db = await get_db()
        try:
            cursor = await db.execute(
                "SELECT * FROM institutions WHERE id = ? AND guild_id = ?",
                (institution_id, guild_id),
            )
            row = await cursor.fetchone()
            return dict(row) if row else None
        finally:
            await db.close()

    async def get_institution_by_role(self, guild_id: int, role_id: int) -> Optional[dict]:
        db = await get_db()
        try:
            cursor = await db.execute(
                "SELECT * FROM institutions WHERE guild_id = ? AND role_id = ?",
                (guild_id, role_id),
            )
            row = await cursor.fetchone()
            return dict(row) if row else None
        finally:
            await db.close()

    async def lister_institutions(self, guild_id: int) -> list:
        db = await get_db()
        try:
            cursor = await db.execute(
                "SELECT * FROM institutions WHERE guild_id = ? ORDER BY name", (guild_id,)
            )
            rows = await cursor.fetchall()
            return [dict(r) for r in rows]
        finally:
            await db.close()

    async def configurer_fisc(self, guild_id: int, institution_id: int) -> bool:
        db = await get_db()
        try:
            inst = await self.get_institution(guild_id, institution_id)
            if not inst:
                return False

            guichet = GUIICHET_INSTITUTION
            bank = await self._get_gouvernement_bank(db, guild_id)
            if not bank:
                cursor = await db.execute(
                    "INSERT INTO banks (guild_id, name, role_id, establishment_code) VALUES (?, ?, ?, ?)",
                    (guild_id, "Gouvernement", 0, 1),
                )
                bank_id = cursor.lastrowid
                bank = {"id": bank_id, "establishment_code": 1}
            else:
                bank_id = bank["id"]

            rib = generer_rib(bank["establishment_code"], guichet)

            existing = await db.execute(
                "SELECT rib FROM accounts WHERE guild_id = ? AND bank_id = ? AND user_id = ? AND account_type = 'entreprise'",
                (guild_id, bank_id, institution_id),
            )
            existing_row = await existing.fetchone()
            if existing_row:
                rib = existing_row[0]
            else:
                cursor = await db.execute(
                    "INSERT INTO accounts (guild_id, bank_id, user_id, rib, account_type, solde) VALUES (?, ?, ?, ?, 'entreprise', 0.0)",
                    (guild_id, bank_id, institution_id, rib),
                )

            await db.execute(
                "INSERT OR REPLACE INTO fisc_config (guild_id, institution_id) VALUES (?, ?)",
                (guild_id, institution_id),
            )
            await db.commit()
            return True
        finally:
            await db.close()

    async def get_fisc_config(self, guild_id: int) -> Optional[dict]:
        db = await get_db()
        try:
            cursor = await db.execute(
                """SELECT fc.*, i.name as institution_name, i.role_id as institution_role_id
                   FROM fisc_config fc
                   JOIN institutions i ON fc.institution_id = i.id
                   WHERE fc.guild_id = ?""",
                (guild_id,),
            )
            row = await cursor.fetchone()
            return dict(row) if row else None
        finally:
            await db.close()

    async def get_next_bank_codes(self, guild_id: int) -> list:
        db = await get_db()
        try:
            cursor = await db.execute(
                "SELECT establishment_code FROM banks WHERE guild_id = ? ORDER BY establishment_code",
                (guild_id,),
            )
            rows = await cursor.fetchall()
            return [r[0] for r in rows]
        finally:
            await db.close()

    async def _get_account(self, db: aiosqlite.Connection, guild_id: int, rib: str) -> Optional[dict]:
        cursor = await db.execute(
            "SELECT * FROM accounts WHERE rib = ? AND guild_id = ?", (rib, guild_id)
        )
        row = await cursor.fetchone()
        return dict(row) if row else None

    async def _get_fisc_rib(self, db: aiosqlite.Connection, guild_id: int) -> Optional[str]:
        cursor = await db.execute(
            """SELECT a.rib FROM accounts a
               JOIN fisc_config fc ON a.user_id = fc.institution_id
               WHERE fc.guild_id = ? AND a.account_type = 'entreprise'""",
            (guild_id,),
        )
        row = await cursor.fetchone()
        return row[0] if row else None

    async def _get_gouvernement_bank(self, db: aiosqlite.Connection, guild_id: int) -> Optional[dict]:
        cursor = await db.execute(
            "SELECT * FROM banks WHERE guild_id = ? AND name = 'Gouvernement'",
            (guild_id,),
        )
        row = await cursor.fetchone()
        return dict(row) if row else None

    async def set_negative_floor(self, guild_id: int, bank_id: int, floor: float) -> bool:
        db = await get_db()
        try:
            cursor = await db.execute(
                "UPDATE banks SET client_negative_floor = ? WHERE id = ? AND guild_id = ?",
                (floor, bank_id, guild_id),
            )
            await db.commit()
            return cursor.rowcount > 0
        finally:
            await db.close()

    async def set_vault_balance(self, guild_id: int, bank_id: int, montant: float) -> bool:
        db = await get_db()
        try:
            cursor = await db.execute(
                "UPDATE banks SET vault_balance = ? WHERE id = ? AND guild_id = ?",
                (montant, bank_id, guild_id),
            )
            await db.commit()
            return cursor.rowcount > 0
        finally:
            await db.close()