"""
Licensed under the Apache License, Version 2.0 (the "License");
you may not use this file except in compliance with the License.
You may obtain a copy of the License at

http://www.apache.org/licenses/LICENSE-2.0

Unless required by applicable law or agreed to in writing, software
distributed under the License is distributed on an "AS IS" BASIS,
WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
See the License for the specific language governing permissions and
limitations under the License.
"""
import sqlite3
import pandas as pd
import csv
import re
import os
import psycopg2

from sqlalchemy import over
from recovery.lib.overrides.Overrider import overrider


class csvwrite:
    def __init__(self):
        self.override = overrider()

    def create_csv(self, csvfilename, csvcolumns, csvresults):
        csvcolumnlist = csvcolumns.upper().replace('"', "").split(",")
        with open(csvfilename, "w", newline="", encoding="utf8") as f:
            write = csv.writer(f, delimiter=",", quoting=csv.QUOTE_ALL)
            write.writerow(csvcolumnlist)
            write.writerows(csvresults)
        f.close()

    def create_tables(
        self,
        utilconn,
        utilcur,
        fromconn,
        toconn,
        from_schema,
        tableinfo,
        getddl,
        session,
    ):
        tablestatement = (
            getddl
            .replace("create or replace TABLE", "CREATE TABLE")
            .replace("BINARY(256)", "BYTEA")
            .replace("BINARY(16000)", "BYTEA")
            .replace("BINARY(7000)", "BYTEA")
            .replace("BINARY(7168000)", "BYTEA")
            .replace("NUMBER", "DECIMAL")
            .replace("TIMESTAMP_NTZ", "TIMESTAMP")
            .replace("'en-ci'", "pg_catalog.\"default\"")
            .replace("\"", "")
        )
        
        sourcedb = session['sourcedb']
        targetdb = session['targetdb']
        
        i = 0
        tableinfo = self.override.override_table(tableinfo, minustable_config=0)
        table_ddls = {}
        actualtable = tableinfo
        fromtable = f"\"{from_schema}\".{sourcedb}_{tableinfo}"
        totable = f"\"{from_schema}\".{targetdb}_{tableinfo}"
        minustable = f"\"{from_schema}\".{tableinfo}_MINUSTABLE"
        recovertable = f"\"{from_schema}\".RECOVERED_{tableinfo}"
        data_columns = []
        all_columns = []
        exclude_columns = []

        utilcur.execute(f"SELECT * FROM public.excluded_columns ORDER BY id ASC ;")
        excluderesults = utilcur.fetchall()
        for excludecolumn in excluderesults:
            exclude_columns.append(excludecolumn[1])
        session['exclude_list'] = ",".join(exclude_columns)
        
        utilcur.execute(f"DROP TABLE IF EXISTS {fromtable};")
        utilcur.execute(f"DROP TABLE IF EXISTS {totable};")
        # utilcur.execute(f"DROP TABLE IF EXISTS {minustable};")
        # utilcur.execute(f"DROP TABLE IF EXISTS {recovertable};")
        utilconn.commit()
        primary_key = ""
        match = re.search(
            r'primary key [_\(\)\w",\s]+', tablestatement, re.MULTILINE
        )
        
        if match:
            result = match.group()
            primary_key = result.replace("primary key (", "").replace(")", "")
        else:
            primary_key = "NOT DEFINED"
        tempcreatetable = tablestatement.replace(f"CREATE TABLE {actualtable}",f"CREATE TABLE {fromtable}")
        createtable = re.sub(
            r'constraint\s[\w_]+\s', f"CONSTRAINT {sourcedb}_{tableinfo}_PKY " , tempcreatetable, flags=re.IGNORECASE
        )
        i = i + 1
        utilcur.execute(createtable)
        utilconn.commit()
        tempcreatetable = tablestatement.replace(f"CREATE TABLE {actualtable}",f"CREATE TABLE {totable}")
        createtable = re.sub(
            r'constraint\s[\w_]+\s', f"CONSTRAINT {targetdb}_{tableinfo}_PKY " , tempcreatetable, flags=re.IGNORECASE
        )
        i = i + 1
        utilcur.execute(createtable)
        utilconn.commit()
        tempcreatetable = tablestatement.replace(f"CREATE TABLE {actualtable}",f"CREATE TABLE {minustable}")
        createtable1 = re.sub("  COLLATE pg_catalog.default", "", tempcreatetable, flags=re.IGNORECASE)
        createtable2 = re.sub(r'constraint\s[\w_]+\s', "" , createtable1, flags=re.IGNORECASE)
        createtable3 = re.sub(r'NOT NULL', "" , createtable2, flags=re.IGNORECASE)
        createtable4 = re.sub(r'primary key\s[_\(\),\w\s]+', "" , createtable3, flags=re.IGNORECASE)
        createtable5 = re.sub("ODS_AUDIT_ID DECIMAL(18,0),","ODS_AUDIT_ID DECIMAL(18,0))", createtable4, flags=re.IGNORECASE)
        createtable6 = re.sub("LM_BESTTIMETOCONTACTCODE VARCHAR(40) COLLATE pg_catalog.default,", "LM_BESTTIMETOCONTACTCODE VARCHAR(40)", createtable5, flags=re.IGNORECASE).rstrip()
        createtable = re.sub(";", ");", createtable6, flags=re.IGNORECASE).strip()
        # try:
        #     utilcur.execute(createtable)
        # except Exception as err:
        #    createtable4 = createtable3.replace("ODS_AUDIT_ID DECIMAL(18,0))", "ODS_AUDIT_ID DECIMAL(18,0),")
        #    createtable5 = createtable4.replace("ADMIN_X VARCHAR(1) COLLATE pg_catalog.default,", "ADMIN_X VARCHAR(1) COLLATE pg_catalog.default)")
        #    createtable6 = createtable5.replace("LM_BESTTIMETOCONTACTCODE VARCHAR(40) COLLATE pg_catalog.default,", "LM_BESTTIMETOCONTACTCODE VARCHAR(40) COLLATE pg_catalog.default").rstrip()
        #    createtable7 = createtable6.replace("  COLLATE pg_catalog.default", "")
        #    createtable8 = re.sub(r'primary key\s[_\(\),\w\s]+', "" , createtable7, flags=re.IGNORECASE)
        #    createtable = createtable8.replace(";", ");")
        #    utilcur.execute(createtable)
        tempcreatetable = tablestatement.replace(f"CREATE TABLE {actualtable}",f"CREATE TABLE {recovertable}")
        createtable1 = re.sub(r'constraint\s[\w_]+\s', "" , tempcreatetable, flags=re.IGNORECASE)
        createtable2 = re.sub(r'NOT NULL', "" , createtable1, flags=re.IGNORECASE)
        createtable3 = re.sub(r'primary key\s[_\(\),\w\s]+', "" , createtable2, flags=re.IGNORECASE)
        createtable4 = createtable3.replace("ODS_AUDIT_ID DECIMAL(18,0),","ODS_AUDIT_ID DECIMAL(18,0))")
        createtable = createtable4.replace(";", ");")
        # utilcur.execute(createtable)
        utilconn.commit()
        
        return (
            fromtable,
            totable,
            minustable,
            recovertable,
        )

    def primary_whereclause(self, primary_key):
        whereclause = ""
        if "," in primary_key:
            keyfield = primary_key.split(",")
            whereclause = (
                "A."
                + keyfield[0]
                + " = B."
                + keyfield[0]
                + " AND A."
                + keyfield[1]
                + " = B."
                + keyfield[1]
            )
        else:
            whereclause = "A." + primary_key + " = B." + primary_key
        return whereclause

    def data_whereclause(self, data_columns):
        whereclauselist = []
        whereclause = ""
        for columnname in data_columns:
            whereclauselist.append("A." + columnname + " = B." + columnname)
        whereclause = ",".join(whereclauselist)
        return whereclause

    def generate_impactedtables(
        self, impacted_tables, from_database, to_database, schema
    ):
        table_ddl = []
        for tablename in impacted_tables:
            table_ddl.append(
                [
                    tablename,
                    from_database + "_" + tablename,
                    to_database + "_" + tablename,
                ]
            )

    def __del__(self):
        pass
