#!/usr/bin/env python3

"""
This module aims to reproduce a bug in an emacs package.
"""

import time
import psycopg2
from elsewhere import error, warning, debug

class DB():
    """
    Under heavy network load, the connection to the database fails.
    Do not die then; reconnect.  Much of this code comes straight from
    https://gist.github.com/cabecada/da8913830960a644755b18a02b65e184
    """
    def __init__(self):
        self.conn = None
        self.cur = None
        self.user = 'Fred'
        self.pw = 'Secret of Fred'
        self.dbname = 'Database of Fred'
        self.limit_retries = 'Never give up, Fred.'
        self.host = 'Host of Fred'
        self.reconnect = True

    def connect(self, retries=0):
        """ Connect to the database with 5 second retries """
        if self.conn:
            return self.conn
        try:
            self.conn = psycopg2.connect(
                dbname=self.dbname,
                user=self.user,
                password=self.pw,
                host=self.host,
            )
            retries = 0
        except psycopg2.OperationalError as e:
            if not self.reconnect or retries >= self.limit_retries:
                error(f"CONNECTION FAIL: retries={retries}, "
                      f"reconnect={self.reconnect}: {e}")
                raise e

            retries += 1
            warning(f"Failure {e} connecting to {self.dbname}: retry {retries}")
            time.sleep(5)
            self.connect(retries)
        except Exception as e:
            raise Exception(f"Failed to connect to {self.dbname}: {e}")
        self.conn.set_session(autocommit=True)
        return self.conn

    def cursor(self):
        """ Try hard to provide a cursor into the database """
        if not self.cur or self.cur.closed:
            if not self.conn or self.conn.closed:
                self.connect()
            self.cur = self.conn.cursor()
        return self.cur

    def reset(self):
        """ If things went wrong, reconnect """
        self.close()
        self.connect()
        self.cursor()

    def close(self):
        """ We really need to close database connections to not run out. """
        if self.conn:
            if self.cur:
                self.cur.close()
            self.conn.close()
            debug("PostgreSQL connection is closed")
        self.conn = None
        self.cur = None

    def init(self):
        """ What we do when we start up """
        self.connect()
        self.cursor()

    def do(self, sql, values=None, many=False, num_rows=20):
        """
        Assumes the SQL is either an insert, delete or update.
        Assumes the SQL ends with the equivalent of: RETURNING pk,
        or perhaps: RETURNING pk1, pk2
        Returns a tuple containing the resulting value(s)
        even if the sql returns only one value.
        """
        if (values is not None
            and (not hasattr(values, '__len__') or isinstance(values, str))):
            values = (values,)

        for retry in range(1, self.limit_retries + 1):
            try:
                self.cur.execute(sql, values)
            except psycopg2.errors.UniqueViolation as e:
                # Then we are trying to insert something that already exists.
                debug(f"UNIQUE: Failed to do {sql} {values}: {e}")
                return None
            except (psycopg2.DatabaseError, psycopg2.OperationalError) as e:
                if retry >= self.limit_retries:
                    error(f"FAIL: retry={retry} for {sql} {values}: {e}")
                    raise e

                warning(f"Failure {str(e).strip()} doing {sql} {values}; "
                        f"retry {retry}")
                time.sleep(5)
                self.reset()
            except Exception as e:
                # Some other error that may result from a failed select
                # or any of the many other things that can go wrong.
                raise Exception(f"OTHER: Failed to do {sql} {values}: {e}")
            else:
                return (
                  self.cur.fetchmany(num_rows) if many else self.cur.fetchone()
                )
        return None

    def do_many(self, sql, values, num_rows=100):
        """
        Assumes the SQL is either an insert, delete or update.
        Assumes the SQL ends with the equivalent of: RETURNING pk,
        or perhaps: RETURNING pk1, pk2
        Returns a tuple of the resulting value
        even if the sql returns only one value.
        """
        return self.do(sql, values, True, num_rows)

    def exec_or_die(self, sql, values):
        """
        When we want to fail if the SQL doesn't work.
        """
        for retry in range(1, self.limit_retries + 1):
            try:
                self.cur.execute(sql, values)
            except (psycopg2.DatabaseError, psycopg2.OperationalError) as e:
                if retry >= self.limit_retries:
                    raise e

                warning(f"Failure {str(e).strip()} doing {sql} {values}; "
                        f"retry {retry}")
                time.sleep(1)
                self.reset()
            except Exception as e:
                raise Exception(f"Failed to do {sql} {values}: {e}")
            else:
                return self.cur
        return None