import os
import sqlite3


import pandas as pd
from PyQt5.QtCore import pyqtSignal
import chardet
from .cyanlove_config_read_intfile import readconfig

# 配置文件int格式
pathsaveint = os.path.join(os.path.dirname(__file__), 'config.ini')


class CyanloveConfigCSVExcelRead:

    def __init__(self, filepaths, tbnames, updatelabel):
        super().__init__()  # 初始化父类
        self.filepath = filepaths
        self.tbname = tbnames
        self.updatelabel = updatelabel  # 声明一个更新label新增信号

    def readcsvpath(self):
        try:
            self.updatelabel.emit('开始读取中...')
            # 自动检测 CSV 文件的编码

            with open(self.filepath, 'rb') as f:
                raw_data = f.read(2000)  # 读取前 1000 个字节
                result = chardet.detect(raw_data)
                encoding = result['encoding']
                print(encoding)
            if encoding.lower() == 'gb2312':
                encoding = 'gbk'
            db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')
            # 连接到 SQLite 数据库
            conn = sqlite3.connect(db_path)
            cursor = conn.cursor()
            query = f"drop table if exists [{self.tbname}]"
            print(query)
            cursor.execute(query)
            # 定义每个块的大小
            chunksize = 1000000  # 每次读取10000行
            # 使用迭代器读取CSV文件并逐块写入数据库
            for chunk in pd.read_csv(self.filepath, chunksize=chunksize, encoding=encoding):  # 指定编码
                chunk.to_sql(self.tbname, conn, if_exists='append', index=False)  # 追加插入
            print('ok')
            self.updatelabel.emit(f"写入完毕！")
            # 关闭连接
            cursor.close()
            conn.close()
        except Exception as e:
            self.updatelabel.emit(f"读取文件时出错: {e}")
            print(f"读取文件时出错: {e}")

    def readexcelpath(self):
        try:
            self.updatelabel.emit('开始读取中...')
            db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')
            # 连接到 SQLite 数据库
            conn = sqlite3.connect(db_path)
            cursor = conn.cursor()
            df = pd.read_excel(self.filepath, sheet_name=0)  # 使用0读取第一个工作表
            df.to_sql(self.tbname, conn, if_exists='replace', index=False)
            print('ok')
            self.updatelabel.emit(f"写入完毕！")
            # 关闭连接
            conn.close()
        except Exception as e:
            self.updatelabel.emit(f"读取文件时出错: {e}")
            print(f"读取文件时出错: {e}")


class CyanloveConfigCSVExcelReadV2:

    def __init__(self, filepaths, tbnames,
                 isfirstcol, isfirstfile_createsheet, strtext,
                 updatelabel):

        super().__init__()  # 初始化父类
        self.filepaths = filepaths
        self.tbname = tbnames
        self.pathsaveint = pathsaveint
        self.updatelabel = updatelabel  # 声明一个更新label新增信号
        self.isfirstcol = isfirstcol
        self.isfirstfile_createsheet = isfirstfile_createsheet
        self.strtext = strtext

    def readcsvpath(self):
        self.updatelabel.emit('开始读取中...')
        # 自动检测 CSV 文件的编码
        k1 = 0
        db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')
        # 连接到 SQLite 数据库
        conn = sqlite3.connect(db_path)
        cursor = conn.cursor()

        for filepath in self.filepaths:
            k1 = k1 + 1
            self.updatelabel.emit('导入' + str(k1) + "/" + str(len(self.filepaths)))
            try:
                with open(filepath, 'rb') as f:
                    raw_data = f.read(2000)  # 读取前 1000 个字节
                    result = chardet.detect(raw_data)
                    encoding = result['encoding']
                    print(encoding)
                if encoding.lower() == 'gb2312':
                    encoding = 'gbk'

                # 如果勾选了第一行为表头
                if self.isfirstcol:
                    # 读取csv文件第一行,默认表头
                    df_first_row = pd.read_csv(filepath, encoding=encoding, quotechar='"', nrows=0)
                    # 将第一行转换为列表
                    header = df_first_row.columns.tolist()
                    print(header)
                    if len(header) == 0:
                        self.updatelabel.emit('没有发现csv文件表头！')
                        return
                    # 如果选择了是否根据第一个文件创建表
                    if self.isfirstfile_createsheet and k1 == 1:
                        query = f"drop table if exists [{self.tbname}]"
                        cursor.execute(query)
                        headerstr = ','.join([f'{x} text ' for x in header])
                        query = f"create table [{self.tbname}]({headerstr})"
                        cursor.execute(query)
                    else:
                        print("不需要删除表，不是第一个文件")
                    # 定义每个块的大小，开始读取文件写入
                    chunksize = 1000000  # 每次读取10000行
                    # 使用迭代器读取CSV文件并逐块写入数据库
                    for chunk in pd.read_csv(filepath, chunksize=chunksize, quotechar='"', encoding=encoding):  # 指定编码
                        chunk.to_sql(self.tbname, conn, if_exists='append', index=False)  # 追加插入
                else:
                    # 判断文件是否包含指定的字段
                    start_index, header = self.read_contain_text(filepath, encoding, self.strtext)
                    print(header)
                    if len(header) == 0:
                        self.updatelabel.emit('没有发现csv文件表头！')
                        return
                    # 如果选择了是否根据第一个文件创建表
                    if self.isfirstfile_createsheet and k1 == 1:
                        query = f"drop table if exists [{self.tbname}]"
                        cursor.execute(query)
                        headerstr = ','.join([f'{x} text ' for x in header])
                        query = f"create table [{self.tbname}]({headerstr})"
                        cursor.execute(query)
                    else:
                        print("不需要删除表，不是第一个文件")
                    # 定义每个块的大小，开始读取文件写入
                    chunksize = 1000000  # 每次读取10000行
                    # 使用迭代器读取CSV文件并逐块写入数据库
                    for chunk in pd.read_csv(filepath, chunksize=chunksize, header=None,
                                             quotechar='"', encoding=encoding,
                                             skiprows=int(start_index + 1)):  # 指定编码
                        chunk.columns = header  # 设置列名称
                        chunk.to_sql(self.tbname, conn, if_exists='append', index=False)  # 追加插入

            except Exception as e:
                self.updatelabel.emit(f"读取文件时出错: {e}")
                print(f"读取文件时出错: {e}")
        self.updatelabel.emit(f"写入完毕！")
        # 关闭连接
        cursor.close()
        conn.close()

    def readexcelpath(self):
        try:
            self.updatelabel.emit('开始读取中...')
            db_path = readconfig.read_ini_file('Settings', 'sqlite_栅格分析')
            # 连接到 SQLite 数据库
            conn = sqlite3.connect(db_path)
            k1 = 0
            for filepath in self.filepaths:
                k1 = k1 + 1
                self.updatelabel.emit('导入' + str(k1) + "/" + str(len(self.filepaths)))
                if k1 == 1:
                    df = pd.read_excel(filepath, sheet_name=0)  # 使用0读取第一个工作表
                    df.to_sql(self.tbname, conn, if_exists='replace', index=False)
                else:
                    df = pd.read_excel(filepath, sheet_name=0)  # 使用0读取第一个工作表
                    df.to_sql(self.tbname, conn, if_exists='append', index=False)

            self.updatelabel.emit(f"写入完毕！")
            # 关闭连接
            conn.close()
        except Exception as e:
            self.updatelabel.emit(f"读取文件时出错: {e}")
            print(f"读取文件时出错: {e}")

    @staticmethod
    def read_contain_text(filepath, encoding, containstr):
        start_index = None
        collist = None
        try:
            with open(filepath, 'r', encoding=encoding) as file:
                for index, line in enumerate(file):
                    if containstr in line:
                        start_index = index + 1  # 从包含字符串的下一行开始
                        # 使用 pandas 读取指定行
                        df = pd.read_csv(filepath, skiprows=index, nrows=1, header=None, quotechar='"',
                                         encoding=encoding)
                        # 将 DataFrame 转换为列表,df.values 返回的是一个 NumPy 数组，默认情况下它会保持二维结构[[]]
                        # 如果使用collist =df.values,则出来为[[]]结构
                        collist = df.iloc[0].tolist()  # 取第一行
                        break
            if start_index is None:
                print(f"'{containstr}' not found in the file.")
        except FileNotFoundError:
            print(f"File not found: {filepath}")
        except Exception as e:
            print(f"An error occurred: {e}")

        return start_index, collist
