import os
import re
import configparser
import logging
from urllib.request import urlretrieve
import cx_Oracle
import mysendmail
from tqdm import tqdm

# Constants
RELEASE = 1
PATH = "settings.ini"
DEFAULT_BLOCK_URL = r"http://fidelio.pro/selectBlocks.sql"
DEFAULT_URL = r"http://fidelio.pro/select.sql"
EXTENSION = ".csv"
DEFAULT_NLS_LANG = "AMERICAN_AMERICA.CL8MSWIN1251"
DEFAULT_FILE_ENCODING = "cp1251"

# Setup logging
logging.basicConfig(
    level=logging.INFO,
    format="%(asctime)s - %(levelname)s - %(message)s",
    handlers=[
        logging.FileHandler("hotellabexport.log", "w", "utf-8"),
        logging.StreamHandler(),
    ],
)

# Default values
blockurl = DEFAULT_BLOCK_URL
url = DEFAULT_URL
oracleconnectstring = r"v8live/live@v8"
port = r"25"
sendto = r"vataev.marat143@gmail.com"
sendfrom = r"radio-mosocw@hotellab.io"
mailserver = r"mail.hotellab.io"
pwd = r"7#B2cyi8"
ssl = r"0"
nls_lang = DEFAULT_NLS_LANG
file_encoding = DEFAULT_FILE_ENCODING

# Default values for new settings
do_main_export = "true"
do_block_export = "true"
update_scripts = "true"

# Create a new settings file if not exists
if not os.path.exists(PATH):
    logging.info(
        "settings.ini configuration file not found. Creating a new one with default settings."
    )
    config = configparser.ConfigParser()
    config.add_section("settings")
    with open(PATH, "w") as configfile:
        config.write(configfile)

# Read settings
config = configparser.ConfigParser()
config.read(PATH)
logging.info(f"Loading settings from {PATH}")


def get_or_set_config(config, section, option, default_value):
    try:
        return config.get(section, option)
    except configparser.NoOptionError:
        config.set(section, option, default_value)
        return default_value


# Load settings
blockurl = get_or_set_config(config, "settings", "block-url", blockurl)
url = get_or_set_config(config, "settings", "url", url)
sendfrom = get_or_set_config(config, "settings", "sendfrom", sendfrom)
sendto = get_or_set_config(config, "settings", "sendto", sendto)
port = get_or_set_config(config, "settings", "port", port)
oracleconnectstring = get_or_set_config(
    config, "settings", "oracleconnectstring", oracleconnectstring
)
mailserver = get_or_set_config(config, "settings", "mailserver", mailserver)
pwd = get_or_set_config(config, "settings", "pwd", pwd)
ssl = get_or_set_config(config, "settings", "ssl", ssl)
nls_lang = get_or_set_config(config, "settings", "nls_lang", nls_lang)
file_encoding = get_or_set_config(config, "settings", "file_encoding", file_encoding)

# Load new boolean settings
do_main_export = (
    get_or_set_config(config, "settings", "do_main_export", do_main_export) == "true"
)
do_block_export = (
    get_or_set_config(config, "settings", "do_block_export", do_block_export) == "true"
)
update_scripts = (
    get_or_set_config(config, "settings", "update_scripts", update_scripts) == "true"
)

# Write back the config file with any new settings
with open(PATH, "w") as configfile:
    config.write(configfile)


def execute_query_and_write_to_file(cursor, query, filename, chunk_size=1024):
    try:
        # First, get total count of rows
        count_query = f"SELECT COUNT(*) FROM ({query})"
        cursor.execute(count_query)
        total_rows = cursor.fetchone()[0]

        # Execute main query
        cursor.execute(query)

        # Initialize progress bar
        progress = tqdm(
            total=total_rows,
            desc=f"Exporting to {filename}",
            unit=" rows",
            unit_scale=True,
            bar_format="{l_bar}{bar}| {n_fmt}/{total_fmt} rows [{elapsed}<{remaining}]",
        )

        with open(filename, "w", encoding=file_encoding, errors="replace") as f:
            while True:
                chunk = cursor.fetchmany(chunk_size)
                if not chunk:
                    break

                for item in chunk:
                    f.write(f"{item[0]}\n")

                # Update progress bar by chunk size
                progress.update(len(chunk))

        progress.close()

    except cx_Oracle.DatabaseError as e:
        logging.error(f"Error accessing database: {e}")
        raise


# Get query filenames from URLs
qt = re.findall(r"\w*\.sql", url)[0]
qtb = re.findall(r"\w*\.sql", blockurl)[0]
logging.info("Query file {}".format(qt))
kq = "select wuss_value from wuss where upper(wuss_name) = upper('UDEF_Hotellabkey')"

logging.info("Fidelio.pro\nHOTELLAB Export Ver 0.22")
logging.info(f"Using file encoding: {file_encoding}")

# Download query files conditionally
if update_scripts:
    logging.info("Updating query files")
    try:
        urlretrieve(url, qt)
        urlretrieve(blockurl, qtb)
    except:
        logging.info("Failed to download new query file. The old one will be used")
    else:
        logging.info("Query file successfully updated")
else:
    logging.info("Not updating query files as per settings.")

logging.info("Opening query file")

os.environ["NLS_LANG"] = nls_lang

# Process queries and send emails
if not os.path.exists(qt):
    logging.error(f"Query file {qt} not found.")
    exit()
if not os.path.exists(qtb):
    logging.error(f"Block query file {qtb} not found.")
    exit()

with open(qt) as f, open(qtb) as fb:
    q = f.read()
    qb = fb.read()

    if len(q) == 0 or len(qb) == 0:
        logging.error("Query file is empty")
        exit()

    with cx_Oracle.connect(oracleconnectstring) as con:
        logging.info("Database connection established")
        cur = con.cursor()
        try:
            (key,) = cur.execute(kq).fetchone()
        except cx_Oracle.DatabaseError as e:
            logging.error(f"Error getting hotel key: {e}")
            raise
        else:
            logging.info(
                f"Hotel key {key}. Starting to generate export. This may take up to 20 minutes."
            )

        r = f"{key}"
        rb = f"BLOCKS-{key}"

        # Export and send main data conditionally
        if do_main_export:
            logging.info("Main export...")
            execute_query_and_write_to_file(cur, q, f"{r}{EXTENSION}")
            logging.info("Main export generated")
            mysendmail.send_mail(
                sendfrom,
                sendto,
                r,
                r,
                f"{r}{EXTENSION}",
                pwd,
                mailserver,
                port,
                int(ssl),
            )
            logging.info(f"Main export sent to address {sendto} with subject {r}")

        # Export and send blocks data conditionally
        if do_block_export:
            logging.info("BLOCKS export...")
            execute_query_and_write_to_file(cur, qb, f"{rb}{EXTENSION}", 1)
            logging.info("BLOCKS export generated successfully")
            mysendmail.send_mail(
                sendfrom,
                sendto,
                rb,
                rb,
                f"{rb}{EXTENSION}",
                pwd,
                mailserver,
                port,
                int(ssl),
            )
            logging.info(f"BLOCKS export sent to address {sendto} with subject <{rb}>")
