# ========================================================================
# Easy Database
#

import mysql.connector
from mysql.connector import Error
import re
import logging
from datetime import datetime

params = {}
params['db_host'] = 'localhost'
params['db_name'] = 'dailyrate'
params['db_username'] = 'root'
params['db_password'] = ''
params['db_port'] = 3306


class EasySQL:
    def __init__(self):
        self.db_conn = None
        self.db_cursor = None
        self.enable_log = True
        self.log_file = 'log/EasySQL.log'
        self.error_function = self.default_error_function
        self.e_msg_start = "EasySQL Error -> "

        logging.basicConfig(filename=self.log_file, level=logging.ERROR)

    def throw_error(self, msg):
        full_msg = self.e_msg_start + msg
        if self.enable_log:
            timestamp = datetime.now().strftime('%d/%m/%Y %H:%M:%S')
            logging.error(f"{timestamp} -> {full_msg}")
        self.error_function(full_msg)

    def default_error_function(self, msg):
        print(f"DEFAULTS :) => {msg}")

    def db_init(self):
        self.db_host = params['db_host']
        self.db_name = params['db_name']
        self.db_username = params['db_username']
        self.db_password = params['db_password']
        self.db_port = params['db_port']
        return True

    def db_connect(self):
        try:
            self.db_conn = mysql.connector.connect(
                host=self.db_host,
                user=self.db_username,
                password=self.db_password,
                database=self.db_name,
                port=self.db_port
            )
            self.db_cursor = self.db_conn.cursor(dictionary=True)
            return True
        except Error as e:
            self.throw_error(f"DB Connection Error : {str(e)}")
            return False

    def get_bind_type(self, value):
        if isinstance(value, int):
            return 'i'
        elif isinstance(value, float):
            return 'd'
        elif isinstance(value, str):
            return 's'
        else:
            return 'b'

    def sanitise_string(self, string):
        return string if re.match(r'^[a-zA-Z0-9_]+$', string) else False

    def build_columns(self, columns=""):
        if not columns:
            return "*"
        elif isinstance(columns, str):
            return self.sanitise_string(columns)
        else:
            return ", ".join([f"`{col}`" for col in columns])

    def build_conditions(self, conditions, logic_type="AND"):
        if not conditions:
            return "", [], ""
        sql_conditions = []
        values = []
        for key, value in conditions.items():
            sql_conditions.append(f"{key} = %s")
            values.append(value)
        return f" {logic_type} ".join(sql_conditions), values

    def validate_columns(self, table_name, columns):
        try:
            self.db_cursor.execute(f"SHOW COLUMNS FROM `{table_name}`")
            valid = [row['Field'] for row in self.db_cursor.fetchall()]
            if isinstance(columns, dict):
                check = list(columns.keys())
            else:
                check = columns
            return all(col in valid for col in check)
        except Exception as e:
            self.throw_error(f"validate_columns error: {str(e)}")
            return False

    def select(self, table_name, columns="", conditions=None, logic_type="AND"):
        table_name = self.sanitise_string(table_name)
        if not table_name:
            self.throw_error("Invalid table name")
            return False

        # Fix: handle "select all" early
        if not columns or columns == "" or columns == [""]:
            columns = []
            col_str = "*"
        else:
            if isinstance(columns, str):
                columns = [columns]

            if not self.validate_columns(table_name, columns):
                self.throw_error("Column Name Error")
                return False

            col_str = self.build_columns(columns)

        sql = f"SELECT {col_str} FROM {table_name}"

        params = []
        if conditions:
            cond_str, params = self.build_conditions(conditions, logic_type)
            sql += f" WHERE {cond_str}"

        try:
            self.db_cursor.execute(sql, params)
            return self.db_cursor.fetchall()
        except Exception as e:
            self.throw_error(f"Statement Execution Failure: {str(e)}")
            return False

    def insert(self, table_name, data):
        table_name = self.sanitise_string(table_name)
        if not table_name or not self.validate_columns(table_name, data):
            self.throw_error("Invalid table or column name")
            return False

        keys = ", ".join(data.keys())
        placeholders = ", ".join(["%s"] * len(data))
        values = list(data.values())
        sql = f"INSERT INTO {table_name} ({keys}) VALUES ({placeholders})"

        try:
            self.db_cursor.execute(sql, values)
            self.db_conn.commit()
            return True
        except Exception as e:
            self.throw_error(f"Insert Serious Error: {str(e)}")
            return False

    def update(self, table_name, data, conditions, logic_type="AND"):
        table_name = self.sanitise_string(table_name)
        if not table_name or not self.validate_columns(table_name, data):
            self.throw_error("Invalid table or column name")
            return False

        set_clause = ", ".join([f"{k} = %s" for k in data])
        values = list(data.values())

        cond_clause, cond_values = self.build_conditions(conditions, logic_type)
        sql = f"UPDATE {table_name} SET {set_clause} WHERE {cond_clause}"

        try:
            self.db_cursor.execute(sql, values + cond_values)
            self.db_conn.commit()
            return self.db_cursor.rowcount
        except Exception as e:
            self.throw_error(f"Update Serious Error: {str(e)}")
            return False

    def delete(self, table_name, conditions, logic_type="AND"):
        table_name = self.sanitise_string(table_name)
        if not table_name:
            self.throw_error("Invalid table name")
            return False

        if not self.validate_columns(table_name, conditions):
            self.throw_error("Invalid column name(s) in conditions")
            return False

        cond_clause, cond_values = self.build_conditions(conditions, logic_type)
        sql = f"DELETE FROM {table_name} WHERE {cond_clause}"

        try:
            self.db_cursor.execute(sql, cond_values)
            self.db_conn.commit()
            return self.db_cursor.rowcount  # returns number of rows deleted
        except Exception as e:
            self.throw_error(f"Delete Serious Error: {str(e)}")
            return False