import logging

import psycopg2
from psycopg2 import (
    extras,
    sql,
)

from ..exceptions import NdffLibError
from .base import DataSource

log = logging.getLogger(__name__)


class PostgresDataSource(DataSource):
    """
    A Datasource to retrieve records from a Postgres/Postgis Database.
    Mostly to be used in (cli or R)-scripts to get data from a database without using QGIS.

    A settings object could be something like:

    NOTE: always use 'postgres' as 'datasource_type'

    settings = {
        'datasource_type': 'postgres',
        'postgres_host': 'localhost',
        'postgres_port': '5432',
        'postgres_user': 'postgres',
        'postgres_password': 'postgres',
        'postgres_dbname': 'gis',
        'postgres_timeout': '3',
        'postgres_schema': 'public',
        'postgres_table': 'notatio_waarnemingen'
    }

    Within the lib the settings/datasource probably will be read from a csv and created via
    NdffConnector.create_from_directories()

    Note that using the QGIS-NdffConnectorPlugin you can also get data from Postgres, but then QGIS will handle the
    connection and retrieving of the records.
    """

    def __init__(self, settings: dict = None):
        """
        Constructor

        :param settings:
        """
        super().__init__(settings)
        log.debug(f"Init PostgresDataSource, settings: {self.settings}")
        self.host = "localhost"
        self.port = 5432
        self.user = None
        self.password = None
        self.dbname = None
        self.timeout = None
        self.schema = None
        self.table = None
        # save and test connection
        self.conn = None
        self._set_settings()

    def _set_settings(self):
        if "postgres_host" in self.settings:
            self.host = self.settings["postgres_host"]
        if "postgres_port" in self.settings:
            self.port = self.settings["postgres_port"]
        if "postgres_user" in self.settings:
            self.user = self.settings["postgres_user"]
        if "postgres_password" in self.settings:
            self.password = self.settings["postgres_password"]
        if "postgres_dbname" in self.settings:
            self.dbname = self.settings["postgres_dbname"]
        if "postgres_timeout" in self.settings:
            self.timeout = self.settings["postgres_timeout"]
        if "postgres_schema" in self.settings:
            self.schema = self.settings["postgres_schema"]
        if "postgres_table" in self.settings:
            self.table = self.settings["postgres_table"]

        try:
            self.conn = psycopg2.connect(
                host=self.host,
                port=self.port,
                user=self.user,
                password=self.password,
                dbname=self.dbname,
                connect_timeout=self.timeout,
            )
        except NdffLibError:
            log.error(
                'Unable to connect to {} database using "{}@{}:{}" within {} seconds'.format(
                    self.host, self.user, self.dbname, self.port, self.timeout
                )
            )

    def get_records(self):
        """
        Get the records (as an iterator) from the database

        We need ordering, else it's impossible to write tests, as the order is not defined.
        That is the reason we try to look up the primary key and use that for ordering.

        https://stackoverflow.com/questions/12379221/sql-query-to-find-primary-key-of-a-table/12379241

        SELECT k.COLUMN_NAME
        FROM information_schema.table_constraints t
        LEFT JOIN information_schema.key_column_usage k
        USING(constraint_name,table_schema,table_name)
        WHERE t.constraint_type='PRIMARY KEY'
            AND t.table_schema='schema'
            AND t.table_name='table';
        """
        if self.schema:
            schema = self.schema
        else:
            schema = "public"
        table = self.table

        primary_key_query = """SELECT k.COLUMN_NAME
        FROM information_schema.table_constraints t
        LEFT JOIN information_schema.key_column_usage k
        USING(constraint_name,table_schema,table_name)
        WHERE t.constraint_type='PRIMARY KEY'
            AND t.table_schema=%s
            AND t.table_name=%s;
        """
        cur = self.conn.cursor()
        cur.execute(primary_key_query, (schema, table))
        primary_key_column = cur.fetchone()
        order_column = None
        if primary_key_column:
            # not sure what happens if we have a primary key consisting of multiple rows
            # but think/hope we will not get those...
            if len(primary_key_column) > 1:
                log.warning(
                    f"Postgresql Datasource table {self.table} does seem to have a primary key consisting of {len(primary_key_column)} columns, only use first one... proceed with caution"
                )
            order_column = f"{primary_key_column[0]}"
        else:
            log.warning(
                f"Postgresql Datasource table {self.table} does not seem to have a primary key... proceed with caution"
            )

        cur = self.conn.cursor(cursor_factory=extras.RealDictCursor)
        if order_column:
            select_query = sql.SQL(
                "select * from {schema}.{table} order by {order_column}"
            ).format(
                schema=sql.Identifier(self.schema),
                table=sql.Identifier(self.table),
                order_column=sql.Identifier(order_column),
            )
            cur.execute(select_query)
        else:
            select_query = "select * from {table}"
            cur.execute(sql.SQL(select_query).format(table=self.table))

        return cur.__iter__()
