#!/usr/bin/env python3.2

from pgtoolkit import toolbase, pgbrowser

from pgtoolkit.highlight import highlight, WHITE, YELLOW, RED, BOLD

class ListSerialTool(toolbase.SimpleTool):

    max_int = 2147483647

    def __init__(self):

        toolbase.SimpleTool.__init__(self, name='listserial', desc='List sequences attached to column of type integer with dangerous last_value.')

        self.init()

    def main(self):

        conn = self.pgm.get_conn('target')

        browser = pgbrowser.PgBrowser(conn)

        rows = browser.list_sequences()

        sequences = []

        for row in rows:

            if row['related_column_type'] == 'integer':

                # read sequence attributes like last_value

                q = 'SELECT * FROM "%s"."%s"' % (row['sequence_schema'], row['sequence_name'])

                curs = conn.cursor()

                curs.execute(q)

                attrs = curs.fetchone_dict()

                # skip this sequence if its cycled and has safe max_value

                if attrs['is_cycled'] and attrs['max_value'] <= self.max_int:

                    continue

                # skip sequences with last_value not yet in half of max_int

                if attrs['last_value'] < self.max_int / 2:

                    continue

                # remember rest of sequences

                row['attrs'] = attrs

                sequences.append(row)

        # sort most dangerous on top

        sequences.sort(key=lambda x: x['attrs']['last_value'], reverse=True)

        # print out what we've found

        for seq in sequences:

            print('Sequence:', seq['sequence_schema'] + '.' + seq['sequence_name'])

            print(' Related:', seq['sequence_schema'] + '.' + seq['related_table'], seq['related_column'], '(' + seq['related_column_type'] + ')')

            print('  integer max', '2147483647')

            # colorize last value

            last_val = seq['attrs']['last_value']

            col = WHITE + BOLD

            if last_val > self.max_int * 0.9:

                # near max

                col = YELLOW + BOLD

            if last_val > self.max_int:

                # over max

                col = RED + BOLD

            print('   last_value', highlight(1, col) + str(last_val) + highlight(0))

            for key in ('min_value', 'max_value', 'is_cycled'):

                print('   ', key, seq['attrs'][key])

            print()

tool = ListSerialTool()

tool.main()