import wx, sqlite3, MainFrame
from functools import partial
border = 10

#intial databse operations
conn = sqlite3.connect('data.sqlite')
cur = conn.cursor()

cur.execute("""SELECT * FROM people""")
list = list(cur.fetchall())
index = range(len(list))

class PickupFrame(wx.Frame):

    def __init__(self, id):

        wx.Frame.__init__(self, parent = None, id = wx.ID_ANY, size = (600, 500), title = 'Pickup')
        panel = wx.Panel(self)
        self.Centre()

        if id == 1:
            self.Maximize()
        self.id = id

        self.search_array = []

#---------------------------------------------------------------------------------------------------

        #create input panel
        input_panel = wx.Panel(panel, style = wx.SIMPLE_BORDER)
        name_msg = wx.StaticText(input_panel, label = 'Name:')
        self.name_box = wx.TextCtrl(input_panel, wx.ID_ANY, '')
        phone_msg = wx.StaticText(input_panel, label = 'Phone Number:')
        self.phone_box = wx.TextCtrl(input_panel, wx.ID_ANY, '')

        self.name_box.Bind(wx.EVT_TEXT, partial(self.Search, id = 0))
        self.phone_box.Bind(wx.EVT_TEXT, partial(self.Search, id = 1))

        #create operation buttons
        save_button = wx.Button(input_panel, label = 'Save')
        ok_button = wx.Button(input_panel, label = 'OK')
        delete_button = wx.Button(input_panel, label = 'Delete')
        cancel_button = wx.Button(input_panel, label = 'Cancel')
        menu_button = wx.Button(input_panel, label = 'Main Menu')

        #bind buttons to event handlers
        save_button.Bind(wx.EVT_BUTTON, self.SaveContacts)
        delete_button.Bind(wx.EVT_BUTTON, self.DeleteContact)
        cancel_button.Bind(wx.EVT_BUTTON, self.CloseFrame)
        menu_button.Bind(wx.EVT_BUTTON, self.Menu)

        #layout buttons
        btn_sizer = wx.BoxSizer(wx.HORIZONTAL)
        btn_sizer.Add(save_button, 1, wx.EXPAND | wx.LEFT, border)
        btn_sizer.Add(delete_button, 1, wx.EXPAND, border)
        btn_sizer.Add(ok_button, 1, wx.EXPAND, border)
        btn_sizer.Add(cancel_button, 1, wx.EXPAND, border)

        #layout messages and input boxes
        msg_box_sizer = wx.BoxSizer(wx.VERTICAL)
        msg_box_sizer.Add(name_msg, 0, wx.LEFT, border)
        msg_box_sizer.Add(self.name_box, 0, wx.LEFT | wx.BOTTOM, border)
        msg_box_sizer.Add(phone_msg, 0, wx.LEFT, border)
        msg_box_sizer.Add(self.phone_box, 0, wx.LEFT | wx.BOTTOM, border)
        msg_box_sizer.Add(btn_sizer)
        msg_box_sizer.Add(menu_button, 0, wx.ALL, border)

        input_panel.SetSizer(msg_box_sizer)

#-------------------------------------------------------------------------

        #create display panel
        display_panel = wx.Panel(panel, style = wx.SUNKEN_BORDER)

        #create table
        self.grid = wx.ListCtrl(display_panel, style = wx.LC_REPORT)
        self.grid.InsertColumn(0, 'Name')
        self.grid.InsertColumn(1, 'Phone')

        #insert existing values
        for count in index:
            self.grid.InsertStringItem(count, list[count][0])
            self.grid.SetStringItem(count, 1, list[count][1])

        #layout display panel
        d_panel_sizer = wx.BoxSizer(wx.VERTICAL)
        d_panel_sizer.Add(self.grid, 1, wx.EXPAND, border)
        display_panel.SetSizer(d_panel_sizer)

        #layout whole panel
        panel_sizer = wx.BoxSizer(wx.HORIZONTAL)
        panel_sizer.Add(input_panel, 1, wx.EXPAND | wx.ALL, border)
        panel_sizer.Add(display_panel, 1, wx.EXPAND | wx.ALL, border)
        panel.SetSizer(panel_sizer)

    def SaveContacts(self, event):

        if self.name_box.GetValue() == '' or self.phone_box.GetValue() == '':

            error_frame = wx.Frame(None, wx.ID_ANY, 'Error')
            error_panel = wx.Panel(error_frame)
            error_text = wx.StaticText(error_panel, wx.ID_ANY, 'Neither field can be blank. Please check again.')

            txt_sizer = wx.BoxSizer(wx.VERTICAL)
            txt_sizer2 = wx.BoxSizer(wx.HORIZONTAL)
            txt_sizer.Add(error_text, 0, wx.ALIGN_CENTRE | wx.EXPAND)
            txt_sizer2.Add(txt_sizer, 0, wx.ALIGN_CENTRE | wx.EXPAND)
            error_panel.SetSizer(txt_sizer2)
            error_frame.Show()

        else:

            #assigns user input to variables
            name = self.name_box.GetValue()
            phone = self.phone_box.GetValue()

            cur.execute("""INSERT INTO people (name, phone) VALUES (?, ?)""", (name, phone))

            conn.commit()

            #clears input boxes
            self.name_box.Clear()
            self.phone_box.Clear()

            #refreshes grid with new contact
            self.grid.DeleteAllItems()
            cur.execute("""SELECT * FROM people""")
            items = cur.fetchall()
            for i in range(len(items)):
                self.grid.InsertStringItem(i, items[i][0])
                self.grid.SetStringItem(i, 1, items[i][1])

    def DeleteContact(self, event):

        #delete row from grid
        item_index = self.grid.GetFocusedItem()
        self.grid.DeleteItem(item_index)

        #creates array to correspond grid index and database index (row_id)
        cur.execute("""SELECT rowid FROM people""")
        ids = cur.fetchall()
        no = range(len(ids))

        array = []

        for count in no:
            array.append(ids[count][0])

        cur.execute("""DELETE FROM people WHERE rowid = ('%i')""" % (array[item_index]))

        conn.commit()

    def Search(self, event, id):

        self.grid.DeleteAllItems()

        cur.execute("""SELECT * FROM people""")
        all = cur.fetchall()

        if id == 0:
            criteria = self.name_box.GetValue()
            if criteria <> '':
                cur.execute("""SELECT * FROM people WHERE name LIKE ('%%%s%%')""" % (criteria))
                items = cur.fetchall()
            else:
                items = []
        else:
            criteria = self.phone_box.GetValue()
            if criteria <> '':
                cur.execute("""SELECT * FROM people WHERE phone LIKE ('%%%s%%')""" % (criteria))
                items = cur.fetchall()
            else:
                items = []

        if items == []:
            for i in range(len(all)):
                self.grid.InsertStringItem(i, all[i][0])
                self.grid.SetStringItem(i, 1, all[i][1])
        else:
            for i in range(len(items)):
                self.grid.InsertStringItem(i, items[i][0])
                self.grid.SetStringItem(i, 1, items[i][1])

    def Menu(self, event):

        cur.close()
        self.Destroy()
        menu = MainFrame.MainFrame()
        menu.Show()
        menu.Centre()

    def OK(self, event):

        global n, p
        n = self.name_box.GetValue()
        p = self.phone_box.GetValue()

    def CloseFrame(self, event):

        cur.close()
        self.Destroy()

class Result():

    def Result(self):

        return n,p