import sys
import os
import pypinyin
from PySide6.QtCore import QModelIndex, QObject, QPersistentModelIndex, Qt
from PySide6.QtWidgets import *
from PySide6.QtGui import *
from PySide6.QtCore import *
from PySide6.QtSql import *

class User(QObject):
    def __init__(self, id,name,city):
        super().__init__()
        self.Id=id
        self.Name=name
        self.NamePinyin=pypinyin.pinyin(name)
        self.City=city
        self.CityPinyin=pypinyin.pinyin(city)
    def __hash__(self) -> int:
        return super(User,self).__hash__()   
    
    def __eq__(self, other) -> bool:
        return id(self)==id(other)

class UserTableModel(QAbstractTableModel):
    _columns=['Id','Name','City']
    _data=[]
    def __init__(self, dbFile:str):
        super(UserTableModel,self).__init__()
        self.fileName=dbFile
        
    def rowCount(self,index:QModelIndex=QModelIndex()):
        return len(self._data)
    def columnCount(self,index:QModelIndex=QModelIndex()):
        return len(self._columns)
    
    def headerData(self, section: int, orientation: Qt.Orientation, role:Qt.ItemDataRole=Qt.ItemDataRole.DisplayRole):
        if orientation==Qt.Orientation.Horizontal:
            if role==Qt.ItemDataRole.DisplayRole:
                if section>=0 and section<len(self._columns):
                    return self._columns[section]#显示列名
                return f'Column{int(section+1)}'#显示列号
            elif role==Qt.ItemDataRole.TextAlignmentRole:
                return Qt.AlignmentFlag.AlignHCenter|Qt.AlignmentFlag.AlignVCenter
        else:
            if role==Qt.ItemDataRole.DisplayRole:
                return f'Row{int(section+1)}'#显示行号
            elif role==Qt.ItemDataRole.TextAlignmentRole:
                return Qt.AlignmentFlag.AlignLeft|Qt.AlignmentFlag.AlignVCenter

    def data(self,index:QModelIndex,role:Qt.ItemDataRole=Qt.ItemDataRole.DisplayRole):
        if index.isValid()==False or index.row()>len(self._data):
            return None
        user=self._data[index.row()]
        column=index.column()
        if role==Qt.ItemDataRole.DisplayRole:
            attrname=self._columns[column]
            if hasattr(user, attrname):
                return getattr(user,attrname)
            else:
                return 'Attribute not found' 
    def sort(self,column,sort:Qt.SortOrder=Qt.SortOrder.AscendingOrder):
        self.beginResetModel()
        if sort==Qt.SortOrder.AscendingOrder:
            if column==0:#id顺序排序
                self._data=sorted(self._data,key=lambda u:(u.Id,u.Id))
            elif column==2:#城市拼音顺序排序
                self._data=sorted(self._data,key=lambda u:(u.CityPinyin,u.CityPinyin))
            else:#名字拼音顺序排序
                self._data=sorted(self._data,key=lambda u:(u.NamePinyin,u.NamePinyin))
        else:
            if column==0:#id倒序排序
                self._data=sorted(self._data,key=lambda u:(u.Id,u.Id),reverse=True)
            elif column==2:#城市拼音倒序排序
                self._data=sorted(self._data,key=lambda u:(u.CityPinyin,u.CityPinyin),reverse=True)
            else:#名字拼音倒序排序
                self._data=sorted(self._data,key=lambda u:(u.NamePinyin,u.NamePinyin),reverse=True)
        self.endResetModel()  

    '''表格支持编辑功能时需要重写flags和setData'''
    def flags(self, index: QModelIndex | QPersistentModelIndex) -> Qt.ItemFlag:
        if not index.isValid():
            return Qt.ItemFlag.ItemIsEnabled
        return Qt.ItemFlag(QAbstractTableModel.flags(self,index)|Qt.ItemFlag.ItemIsEditable)  
    def setData(self, index:QModelIndex, value, role:Qt.ItemDataRole=Qt.ItemDataRole.EditRole):
        if index.isValid()==True and 0<=index.row()<len(self._data) and role==Qt.ItemDataRole.EditRole:
            user=self._data[index.row()]            
            attrname=self._columns[index.column()]
            if hasattr(user, attrname):
                setattr(user,attrname,value)
            self.emit(SIGNAL('dataChanged(QModelIndex,QModelIndex)'),index,index)
            return True
        return False
                  
    def LoadData(self): 
        
        db=QSqlDatabase().addDatabase("QSQLITE")
        db.setDatabaseName(self.fileName)
        db.open()
        model=QSqlRelationalTableModel(db=db)
        model.setTable('User') 
        model.setRelation(2,QSqlRelation('City','Id','Name'))#设置外键       
        model.select()#执行查询
        for row in range(model.rowCount()):
            id=model.record(row).field(0).value()            
            name=model.record(row).field(1).value()
            city=model.record(row).field(2).value()
            self._data.append(User(id,name,city))
        db.close()        

class Demo(QWidget):
    def __init__(self, parent=None):
        super().__init__(parent)
        self.parent = parent
        self.dbFile=os.path.join(self.parent.getAppDir(),'test.db')
        layout = QVBoxLayout()
        
        self.model=UserTableModel(self.dbFile)  
        self.model.LoadData()   
        self.view=QTableView()
        #self.view.setEditTriggers(QAbstractItemView.EditTrigger.NoEditTriggers)
        self.view.setSortingEnabled(True)#支持排序
        self.view.setModel(self.model)
        layout.addWidget(self.view)        
        self.setLayout(layout)

def runDemo(parent):
    wigdet =  Demo(parent)
    return wigdet