import sqlite3

from PyQt5 import QtWidgets, uic, QtCore
import sys

from PyQt5.QtCore import QTime, QDate, QTimer, QRegExp, Qt
from PyQt5.QtGui import QRegExpValidator, QDoubleValidator
from PyQt5.QtSql import QSqlDatabase
from PyQt5.QtWidgets import QTableWidgetItem
from qtpy import QtSql


class Ui(QtWidgets.QMainWindow):
    def __init__(self):
        super(Ui, self).__init__()
        uic.loadUi('date.ui', self)  # UI file hminga thlak zel tur
        self.show()

        validator = QDoubleValidator(0.99, 99.99, 2)
        self.lineEdit.setValidator(validator)
        self.lineEdit_2.setValidator(validator)

        validator2 = QDoubleValidator(0.99, 99.99, 2)
        self.itemName.setValidator(validator2)
        self.itemPrice.setValidator(validator2)

        self.le_date.hide()
        self.le_time.hide()
        self.lbl_sum.hide()
        self.lineEdit.textChanged.connect(self.validate)
        self.lineEdit_2.textChanged.connect(self.validate)
        self.pushButton.setEnabled(False)
        self.pushButton.clicked.connect(self._insertDB)

        self.itemName.textChanged.connect(self.validate_2)
        self.itemPrice.textChanged.connect(self.validate_2)
        self.pushButton_2.setEnabled(True)
        self.pushButton_2.clicked.connect(self._insertDB_2)

        self.populate_table()
        self.populate_table_2()
        self.populate_combobox()

        self.itemName.editingFinished.connect(lambda: self.checkstatus(self.itemName))

        # date & time parameter
        timer = QTimer(self)
        timer.timeout.connect(self.showtime)
        timer.start()

    def showtime(self):
        now = QDate.currentDate()
        self.le_date.setText(now.toString('MMM-dd, yyyy'))
        time = QTime.currentTime()
        time.start()
        self.le_time.setText(time.toString('h:mm:ss AP'))

    def _insertDB(self):
        conn = sqlite3.connect('DB.db')
        c = conn.cursor()
        print("Connected to SQLite")

        Field2 = self.lineEdit.text()
        Field3 = self.lineEdit_2.text()
        date = self.le_date.text()
        time = self.le_time.text()

        c.execute("INSERT INTO table_1(Field2, Field3, date, time) VALUES (?,?,?,?)",
                  (Field2, Field3, date, time))

        conn.commit()
        conn.close()
        print("Python Variables inserted successfully into database table")
        self.lbl_notice.setText("<font color='blue'>Successfully insert data into records.</font>")
        self._clearTextEdit()

    def _clearTextEdit(self):
        self.lineEdit.clear()
        self.lineEdit_2.clear()
        self.lineEdit.setFocus()

    def validate(self):
        value1 = self.lineEdit.text()
        value2 = self.lineEdit_2.text()

        sum = (value1) + (value2)
        self.lbl_sum.setText(sum)
        if sum == "":
            self.pushButton.setEnabled(False)
        else:
            self.pushButton.setEnabled(True)

        print(type(sum))
        print(self.lbl_sum.text())

    def populate_table(self):
        conn = sqlite3.connect('DB.db')
        c = conn.cursor()
        c.execute('''SELECT id, Field2, Field3, date, time FROM table_1 ORDER BY id Desc''')
        data = c.fetchall()

        self.tableWidget.setRowCount(0)
        self.tableWidget.insertRow(0)
        self.tableWidget.resizeColumnsToContents()
        self.tableWidget.horizontalHeaderItem(0).setTextAlignment(Qt.AlignLeft)
        self.tableWidget.horizontalHeaderItem(1).setTextAlignment(Qt.AlignLeft)
        self.tableWidget.horizontalHeaderItem(2).setTextAlignment(Qt.AlignLeft)
        self.tableWidget.horizontalHeaderItem(3).setTextAlignment(Qt.AlignLeft)
        self.tableWidget.horizontalHeaderItem(4).setTextAlignment(Qt.AlignLeft)

        for row, form in enumerate(data):
            for column, item in enumerate(form):
                self.tableWidget.setItem(row, column, QTableWidgetItem(str(item)))
                column += 1

            row_position = self.tableWidget.rowCount()
            self.tableWidget.insertRow(row_position)

        conn.close()
        QtCore.QTimer.singleShot(100, self.populate_table)  # updates data at regular interval (instantly)

    ########################
    def populate_table_2(self):
        conn = sqlite3.connect('DB.db')
        c = conn.cursor()
        c.execute('''SELECT ref, itemName, itemPrice, date1, time1 FROM table_2 ORDER BY time1 Asc''')
        data = c.fetchall()

        self.tableWidget_2.setRowCount(0)
        self.tableWidget_2.insertRow(0)
        self.tableWidget_2.resizeColumnsToContents()
        self.tableWidget_2.horizontalHeaderItem(0).setTextAlignment(Qt.AlignLeft)
        self.tableWidget_2.horizontalHeaderItem(1).setTextAlignment(Qt.AlignLeft)
        self.tableWidget_2.horizontalHeaderItem(2).setTextAlignment(Qt.AlignLeft)
        self.tableWidget_2.horizontalHeaderItem(3).setTextAlignment(Qt.AlignLeft)
        self.tableWidget_2.horizontalHeaderItem(4).setTextAlignment(Qt.AlignLeft)

        for row, form in enumerate(data):
            for column, item in enumerate(form):
                self.tableWidget_2.setItem(row, column, QTableWidgetItem(str(item)))
                column += 1

            row_position = self.tableWidget_2.rowCount()
            self.tableWidget_2.insertRow(row_position)

        conn.close()
        QtCore.QTimer.singleShot(100, self.populate_table_2)  # updates data at regular interval (instantly)

    def _insertDB_2(self):
        conn = sqlite3.connect('DB.db')
        c = conn.cursor()
        print("Connected to SQLite")

        v2 = self.itemName.text()
        v3 = self.itemPrice.text()
        date1 = self.le_date.text()
        time1 = self.le_time.text()

        c.execute("INSERT INTO table_2 select max(id), ?, ?, ?, ? from table_1",
                  (v2, v3, date1, time1))

        conn.commit()
        conn.close()
        print("Python Variables inserted successfully into database table")
        self.lbl_notice_2.setText("<font color='red'>Successfully insert data into records.</font>")
        self._clearTextEdit_2()

    def _clearTextEdit_2(self):
        self.itemName.clear()
        self.itemPrice.clear()
        self.itemName.setFocus()

    def validate_2(self):
        # if self.itemName.text() == "":
        #     self.label_5.setText("<font color='red'>Empty!.</font>")
        #     self.pushButton_2.setEnabled(False)
        # elif self.itemPrice.text() == "":
        #     self.label_6.setText("<font color='red'>Empty!.</font>")
        #     self.pushButton_2.setEnabled(False)


        v1 = self.itemName.text()
        v2 = self.itemPrice.text()
        s = (v1) + (v2)
        self.lbl_check.setText(s)
        if s == "":
            self.pushButton_2.setEnabled(False)
        else:
            self.pushButton_2.setEnabled(True)

        print(type(sum))
        print(self.lbl_check.text())

#############################
    def populate_combobox(self):
        conn = sqlite3.connect('DB.db')
        c = conn.cursor()

        c.execute("SELECT itemName, itemPrice, date1, time1 FROM table_2")

        data = c.fetchall()

        self.combo1.clear()
        for itemName in data:
            self.combo1.addItem(itemName[0])
            #self.combo1.addItem(itemName[1])
            print(itemName[0])  # item_name
            print(itemName[1])  # item_code
            print(itemName[2])  # item_qty
            print(itemName[3])  # item_price
            #display item price here in label_7
            #display item price here in label_8
            #display item price here in label_9



########################


app = QtWidgets.QApplication(sys.argv)
window = Ui()
app.exec_()