import win32service
import win32serviceutil
import win32event
import psycopg2

class PG_Janitor(win32serviceutil.ServiceFramework):
    # you can NET START/STOP the service by the following name
    _svc_name_ = "PG_Janitor"
    # this text shows up as the service name in the Service
    # Control Manager (SCM)
    _svc_display_name_ = "Pulitore connessioni"
    # this text shows up as the description in the SCM
    _svc_description_ = "Elimina le connessioni idle di polyedro"

    def __init__(self, args):

        self.conn = psycopg2.connect(database='trasferte_anas', host='localhost', user='postgres', password='postgres')
        self.cur = self.conn.cursor()
        win32serviceutil.ServiceFramework.__init__(self,args)
        # create an event to listen for stop requests on
        self.hWaitStop = win32event.CreateEvent(None, 0, 0, None)

    # core logic of the service
    def SvcDoRun(self):
        import servicemanager
        # list of services to monitor
        ls_svc = ('TpDB', 'TpHttpd')
        # if this event is fired evebefore we havestarted the first operation, we break
        rc = None
        # check if our list of prerequisites is started
        started = False
        while not started and rc != win32event.WAIT_OBJECT_0:
            # check every 30 seconds, I intentionally "waste" 30 seconds here to err on the safe side
            started = all([win32serviceutil.QueryServiceStatus(svc)[1] == win32service.SERVICE_RUNNING for svc in ls_svc])
            rc = win32event.WaitForSingleObject(self.hWaitStop, 30000)

        sql = """\
            SELECT pg_terminate_backend(pid)
            FROM pg_stat_activity
            WHERE datname in ('dbportale', 'trasferte_anas')
                AND pid <> pg_backend_pid()
                AND state = 'idle'
                AND state_change < current_timestamp - INTERVAL '30' MINUTE
            UNION ALL
            SELECT pg_terminate_backend(pid)
            FROM pg_stat_activity
            WHERE datname = 'dbportale'
                AND pid <> pg_backend_pid()
                AND age(clock_timestamp(), query_start) > INTERVAL '120' SECOND
                AND QUERY ILIKE 'SELECT bp098_id, bp098_name, bp098_key, bp098_value, bp098_time, bp098_idses FROM bp098_tbcache WHERE bp098_key = % AND bp098_name = ''licenze_check'' AND bp098_idses  IS NULL'; """
        # if the stop event hasn't been fired keep looping
        while rc != win32event.WAIT_OBJECT_0:
            self.cur.execute(sql)
            self.conn.commit()
            # block for 2 minutes and listen for a stop event
            rc = win32event.WaitForSingleObject(self.hWaitStop, 120000)

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

    # called when we're being shut down
    def SvcStop(self):
        # tell the SCM we're shutting down
        self.ReportServiceStatus(win32service.SERVICE_STOP_PENDING)
        # fire the stop event
        win32event.SetEvent(self.hWaitStop)

if __name__ == '__main__':
    win32serviceutil.HandleCommandLine(PG_Janitor)