import sqlite3


class MySqLite():
    def __init__(self, db_name=None):
        if db_name:
            db_name = db_name + ".db"
        else:
            db_name = ":memory:"
        self.db_name = db_name

    def start_conn(self):
        self.conn = sqlite3.connect(self.db_name)

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

    def creat_table(self, table_name="", fields="", sql="", index=""):
        """
        fileds,index 均為以逗號(hào)連接的字段形成的字符串
        """
        self.start_conn()
        cursor = self.conn.cursor()

        if not sql:
            sql = f"""
            CREATE TABLE `{table_name}`
                (
                id integer   PRIMARY KEY AUTOINCREMENT,
                insert_time timestamp NULL default CURRENT_TIMESTAMP,
                {" varchar(1000), ".join(fields.split(",")) + " varchar(1000)"}     
                 );
            """
        cursor.execute(sql)
        if index:
            for col_name in index.split(","):
                cursor.execute(f"""CREATE INDEX {col_name} ON `{table_name}` ({col_name})""")

        self.conn.commit()
        self.conn.close()

    def insert(self, table_name, data):
        """
        data 可以是字典插入一行吝梅，也可以是列表插入多行
        data = {"name":"aaa","age":18}
        data = [{"name":"aaa","age":18},{"name":"bb","age":19}]

        """
        self.start_conn()
        cursor = self.conn.cursor()
        if isinstance(data, dict):
            fields = ",".join(data.keys())
            values = "(\"" + "\",\"".join([str(data.get(k)) for k in data.keys()]) + "\")"

        if isinstance(data, list):
            fields = ",".join(data[0].keys())
            values = ",".join(["(\"" + "\",\"".join([str(dic.get(k)) for k in data[0].keys()]) + "\")" for dic in data])

        cursor.execute(f"INSERT INTO `{table_name}` ({fields}) VALUES {values}")
        self.conn.commit()
        self.conn.close()

    def query(self, table_name, files="", where=""):
        """
        只返回前100條
        有files則返回json格式，無files全部查詢返回列表形式
        """
        self.start_conn()
        cursor = self.conn.cursor()
        files = "*" if not files else files
        sql = f"SELECT {files} from `{table_name}`"
        if where:
            sql += "where " + where
        res = []
        for i in cursor.execute(sql):
            if len(res) > 100:
                break
            res.append(i)
        if files != "*":
            res = [dict(zip(files.split(","), i)) for i in res]
        self.conn.close()
        return res

    def execute(self, sql):
        """
         sql 語句
        "SELECT id, name, address, salary  from COMPANY"
        "UPDATE COMPANY set SALARY = 25000.00 where ID=1"
        "DELETE from COMPANY where ID=2;"

        """
        self.start_conn()
        cursor = self.conn.cursor()
        cursor.execute(sql)
        self.conn.commit()
        self.conn.close()


if __name__ == '__main__':
    slite = MySqLite("test")
    slite.creat_table(table_name="user", fields="name,age,class,job", index="name,class")