import sqlite3

from database.databases.armorDB import ArmorDB
from database.databases.weaponDB import WeaponDB
from database.databases.heroDB import HeroDB
from database.databases.monsterDB import MonsterDB

from models.heroes.hero import Hero

import os

FOLDER_DB = "data"
FILE_DB = "mydb.db"
FILE_PATH_DB = os.path.join(os.getcwd(), FOLDER_DB, FILE_DB)


class MainDB:
    def __init__(self) -> None:
        self.were_already_existing = True

        if not os.path.exists(FOLDER_DB):
            os.makedirs(FOLDER_DB)

        if not os.path.exists(FILE_PATH_DB):
            with open(FILE_PATH_DB, "w") as db_file:
                pass

            self.were_already_existing = False

        # Connexion à la base de données SQLite
        self.conn = sqlite3.connect(FILE_PATH_DB)
        self.cursor = self.conn.cursor()

        self.create_tables()

        self.add_databases()

        # print(
        #     f"Database '{FILE_PATH_DB}' and tables with initial data created successfully."
        # )

    def close_db(self):
        self.conn.close()

    def add_databases(self):
        self.weapon_db = WeaponDB(self)
        self.armor_db = ArmorDB(self)
        self.hero_db = HeroDB(self)
        self.monster_db = MonsterDB(self)

    def create_tables(self):
        # Création des tables
        self.cursor.executescript(
            """
            CREATE TABLE IF NOT EXISTS types (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT
            );

            CREATE TABLE IF NOT EXISTS monsters (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT,
                types_id INTEGER NOT NULL,
                health_points INTEGER,
                attack_power REAL,
                defense REAL,
                description TEXT,
                FOREIGN KEY (types_id) REFERENCES types (id) ON DELETE NO ACTION ON UPDATE NO ACTION
            );

            CREATE TABLE IF NOT EXISTS values_of_loots (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT,
                description TEXT
            );

            CREATE TABLE IF NOT EXISTS loots (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT,
                types_id INTEGER NOT NULL,
                values_of_loots_id INTEGER NOT NULL,
                value INTEGER NOT NULL,
                description TEXT,
                drop_rate REAL,
                FOREIGN KEY (types_id) REFERENCES types (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
                FOREIGN KEY (values_of_loots_id) REFERENCES values_of_loots (id) ON DELETE NO ACTION ON UPDATE NO ACTION
            );

            CREATE TABLE IF NOT EXISTS heroes (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                title TEXT,
                types_id INTEGER NOT NULL,
                health_points INTEGER,
                attack_power REAL,
                defense REAL,
                mana INTEGER,
                description TEXT,
                FOREIGN KEY (types_id) REFERENCES types (id) ON DELETE NO ACTION ON UPDATE NO ACTION
            );

            CREATE TABLE IF NOT EXISTS characters_has_loots (
                character_type INTEGER NOT NULL,
                loot_type INTEGER NOT NULL,
                PRIMARY KEY (character_type, loot_type),
                FOREIGN KEY (character_type) REFERENCES heroes (id) ON DELETE NO ACTION ON UPDATE NO ACTION,
                FOREIGN KEY (loot_type) REFERENCES loots (id) ON DELETE NO ACTION ON UPDATE NO ACTION
            );
        """
        )

        if not self.were_already_existing:
            self.add_tables()

        # print(f"Database and tables with expanded data created successfully.")

        self.conn.commit()

    def add_tables(self):

        # Insertion des données dans la table types
        self.cursor.executemany(
            """
        INSERT INTO types (title) VALUES (?);
        """,
            [
                ("archer",),
                ("warrior",),
                ("mage",),
                ("weapons",),
                ("armor",),
                ("consumable",),
                ("beast",),
                ("humanoid",),
                ("undead",),
                ("dragon",),
                ("elemental",),
            ],
        )

        # Insertion des données dans la table values_of_loots
        self.cursor.executemany(
            """
            INSERT INTO values_of_loots (title, description) VALUES (?, ?);
            """,
            [
                ("attack", "The ability to deal damage to an opponent."),
                ("defence", "The ability to reduce or block incoming damage."),
                ("Healing", "The ability to restore lost health."),
                ("Mana Boost", "Restores or increases mana."),
                ("Critical Hit", "Increases chance of dealing critical damage."),
            ],
        )

        # Insertion des données dans la table loots
        self.cursor.executemany(
            """
            INSERT INTO loots (title, types_id, values_of_loots_id, value, description, drop_rate)
            VALUES (?, ?, ?, ?, ?, ?);
            """,
            [
                (
                    "Rusty Sword",
                    4,
                    1,
                    14,
                    "A worn-out sword, barely sharp enough to be effective.",
                    40,
                ),
                (
                    "Enchanted Bow",
                    4,
                    1,
                    5,
                    "A magical bow with increased accuracy and damage.",
                    25,
                ),
                (
                    "Mystic Robe",
                    5,
                    2,
                    8,
                    "A robe that enhances the wearer's defense against magic.",
                    30,
                ),
                (
                    "Health Potion",
                    6,
                    3,
                    0,
                    "A potion that restores 50 health points.",
                    60,
                ),  # Ajout de `0` pour value
                (
                    "Iron Shield",
                    5,
                    2,
                    7,
                    "A sturdy shield made of iron, providing excellent defense.",
                    40,
                ),
                (
                    "Fireball Scroll",
                    6,
                    1,
                    14,
                    "A scroll that teaches the fireball spell to a mage.",
                    20,
                ),
                (
                    "Dragon Scale Armor",
                    5,
                    2,
                    20,
                    "Armor crafted from dragon scales, highly durable.",
                    15,
                ),
                (
                    "Thunder Sword",
                    4,
                    1,
                    8,
                    "A sword infused with thunder magic.",
                    20,
                ),
                (
                    "Arcane Scepter",
                    4,
                    1,
                    9,
                    "A powerful scepter that amplifies magic.",
                    15,
                ),
                (
                    "Potion of damaging Power",
                    4,
                    1,
                    3,
                    "Temporarily increases spell power.",
                    30,
                ),
                (
                    "Potion of Devastation",
                    4,
                    1,
                    7,
                    "Boosts magical damage for a short time.",
                    25,
                ),
            ],
        )

        # Insertion des données dans la table heroes
        self.cursor.executemany(
            """
        INSERT INTO heroes (title, types_id, health_points, attack_power, defense, mana, description)
        VALUES (?, ?, ?, ?, ?, ?, ?);
        """,
            [
                (
                    "Eldon the Brave",
                    2,
                    150,
                    25.0,
                    20.0,
                    10,
                    "A seasoned warrior known for his resilience and strength.",
                ),
                (
                    "Lyra the Swift",
                    1,
                    100,
                    30.0,
                    15.0,
                    50,
                    "An archer with unmatched speed and precision.",
                ),
                (
                    "Morgana the Wise",
                    3,
                    80,
                    20.0,
                    10.0,
                    100,
                    "A mage with a deep knowledge of arcane spells.",
                ),
                (
                    "Tharok the Shieldbearer",
                    2,
                    200,
                    15.0,
                    35.0,
                    5,
                    "A warrior who specializes in defensive tactics.",
                ),
                (
                    "Selene the Healer",
                    3,
                    90,
                    10.0,
                    10.0,
                    120,
                    "A mage dedicated to supporting her allies with healing magic.",
                ),
                (
                    "Ragnar the Fierce",
                    2,
                    170,
                    35.0,
                    25.0,
                    15,
                    "A barbarian warrior with immense strength and ferocity.",
                ),
                (
                    "Elara the Mystic",
                    3,
                    85,
                    25.0,
                    15.0,
                    110,
                    "A mage who commands the elements with finesse.",
                ),
                (
                    "Galdor the Archer",
                    1,
                    120,
                    40.0,
                    20.0,
                    30,
                    "A master archer with keen eyesight and precision.",
                ),
                (
                    "Fenrir the Wild",
                    2,
                    160,
                    28.0,
                    18.0,
                    20,
                    "A warrior with a primal fighting style.",
                ),
                (
                    "Aurora the Lightbringer",
                    3,
                    95,
                    18.0,
                    12.0,
                    130,
                    "A mage who wields the power of light.",
                ),
            ],
        )

        # Insertion des données dans la table monsters
        self.cursor.executemany(
            """
        INSERT INTO monsters (title, types_id, health_points, attack_power, defense, description)
        VALUES (?, ?, ?, ?, ?, ?);
        """,
            [
                (
                    "Goblin Grunt",
                    7,
                    50,
                    10.0,
                    5.0,
                    "A small but vicious creature, often found in caves.",
                ),
                (
                    "Forest Troll",
                    7,
                    200,
                    30.0,
                    25.0,
                    "A massive troll that roams the dense forests.",
                ),
                (
                    "Dark Sorcerer",
                    8,
                    150,
                    40.0,
                    10.0,
                    "A rogue magician who practices forbidden dark magic.",
                ),
                (
                    "Shadow Wolf",
                    7,
                    100,
                    20.0,
                    10.0,
                    "A wolf with fur as dark as the night, moving silently.",
                ),
                (
                    "Skeleton Warrior",
                    9,
                    80,
                    15.0,
                    10.0,
                    "An animated skeleton armed with rusted weapons.",
                ),
                (
                    "Fire Drake",
                    10,
                    250,
                    50.0,
                    30.0,
                    "A small dragon with the ability to breathe fire.",
                ),
                (
                    "Ice Elemental",
                    11,
                    180,
                    35.0,
                    20.0,
                    "A being composed of ice, freezing everything it touches.",
                ),
                (
                    "Bandit Leader",
                    8,
                    120,
                    25.0,
                    15.0,
                    "The cunning leader of a group of bandits.",
                ),
                (
                    "Cave Spider",
                    7,
                    60,
                    15.0,
                    5.0,
                    "A giant spider that dwells in dark caves.",
                ),
                (
                    "Wraith",
                    9,
                    110,
                    20.0,
                    10.0,
                    "A ghostly figure that haunts abandoned places.",
                ),
                (
                    "Lava Golem",
                    10,
                    300,
                    60.0,
                    40.0,
                    "A hulking creature made of molten rock.",
                ),
                (
                    "Thunderbird",
                    10,
                    220,
                    45.0,
                    25.0,
                    "A mythical bird that controls storms.",
                ),
                (
                    "Necromancer",
                    8,
                    160,
                    50.0,
                    20.0,
                    "A dark mage who raises the dead to do their bidding.",
                ),
                (
                    "Dire Bear",
                    7,
                    240,
                    35.0,
                    25.0,
                    "A massive bear with unparalleled strength.",
                ),
                (
                    "Sea Serpent",
                    10,
                    280,
                    55.0,
                    30.0,
                    "A serpentine monster that lurks in deep waters.",
                ),
                (
                    "Harpy",
                    8,
                    100,
                    20.0,
                    15.0,
                    "A winged monster with sharp claws and a screeching cry.",
                ),
                (
                    "Stone Guardian",
                    11,
                    320,
                    40.0,
                    50.0,
                    "A statue brought to life to guard ancient ruins.",
                ),
                (
                    "Venomous Scorpion",
                    7,
                    90,
                    25.0,
                    10.0,
                    "A giant scorpion with a deadly stinger.",
                ),
                (
                    "Zombie Horde",
                    9,
                    150,
                    10.0,
                    5.0,
                    "A group of mindless zombies seeking flesh.",
                ),
                (
                    "Phoenix",
                    10,
                    200,
                    40.0,
                    20.0,
                    "A mythical bird that rises from its ashes.",
                ),
            ],
        )

        # Insertion des données dans la table monsters_has_loots
        self.cursor.executemany(
            """
        INSERT INTO characters_has_loots (character_type, loot_type)
        VALUES (?, ?);
        """,
            [
                (Hero.WARRIOR, 1),
                (Hero.WARRIOR, 5),
                (Hero.WARRIOR, 8),
                (Hero.ARCHERY, 2),
                (Hero.ARCHERY, 5),
                (Hero.WIZARD, 9),
                (Hero.WIZARD, 10),
                (Hero.WIZARD, 11),
                (Hero.WIZARD, 3),
            ],
        )

    def sql_query(self, query: str, values: tuple, is_selecting=False):
        try:
            self.cursor.execute(query, values)
            if not is_selecting:
                self.conn.commit()
            return True
        except sqlite3.IntegrityError as ie:
            print(f"Insert DB error {ie}")
        except sqlite3.OperationalError as oe:
            print(f"Operational error: {oe}")
            print("Parameters type:", [type(v) for v in values])
            print("Parameters value:", values)
        except sqlite3.DatabaseError as e:
            print("DB error:", e)
        except sqlite3.InterfaceError as ie:
            print(f"Type Error: {ie}")
            print("Parameters type:", [type(v) for v in values])
            print("Parameters value:", values)
        except Exception as e:
            print("An SQL error occured:", e)
        return False