# -*- encoding=utf-8 -*-

from builtins import object
import os
import string
import logging

from contextlib import contextmanager
import sqlite3
from sqlite3.dbapi2 import Connection, Cursor

from .errors import BuilderError
from .logger import log_progress


class SQLNotFoundError(BuilderError):
    pass


class InvalidDatabaseError(BuilderError):
    pass


def connect_database(db_path: str, check: bool = True):
    con = sqlite3.dbapi2.connect(db_path)
    con.isolation_level = None
    con.enable_load_extension(True)
    cur = con.cursor()
    libs = [
        # SpatiaLite >= 4.2 and Sqlite >= 3.7.17, should work on all platforms
        ("mod_spatialite", "sqlite3_modspatialite_init"),
        # SpatiaLite >= 4.2 and Sqlite < 3.7.17 (Travis)
        ("mod_spatialite.so", "sqlite3_modspatialite_init"),
        # SpatiaLite < 4.2 (linux)
        ("libspatialite.so", "sqlite3_extension_init")
    ]

    found = False
    for lib, entry_point in libs:
        try:
            cur.execute(f"select load_extension('{lib}', '{entry_point}')")
        except sqlite3.OperationalError:
            continue
        else:
            found = True
            break

    if not found:
        raise RuntimeError("Cannot find any suitable spatialite module")

    cur.close()
    
    cur = con.cursor()
    cur.execute("PRAGMA temp_store=MEMORY")
    if check:
        logging.info("Testing spatialite metadata")
        [check_metadata] = cur.execute("select CheckSpatialMetaData()").fetchone()
        if check_metadata == 0:
            raise InvalidDatabaseError(f"{db_path} has no spatial metadata")

    cur.close()
    
    con.enable_load_extension(False)

    return con


def SQL(sql, **kwargs):
    """ Wrap SQL statement 
    """
    sql = sql.format(**kwargs)
    logging.debug(sql)
    return sql


def create_database(db_path: str):
    """ Create an empty database
    """
    if not os.path.exists(db_path):
        logging.info(f"Creating database {db_path}")
#        db = initialize_spatialite()
#        conn = db.connect(dbname)
        conn = connect_database(db_path, False)
        conn.execute('SELECT initspatialmetadata(1)')
        conn.commit()
        conn.close()
 

def load_sql(name: str, **kwargs):
    """ Load graph builder sql

        sql file is first searched as package data. If not
        found then __file__ path  is searched

        :raises: SQLNotFoundError
    """
    # Try to get schema from ressources
    try:
        import pkg_resources    
        srcpath = pkg_resources.resource_filename("morpheo", "core")
    except ImportError:
        # If we are executed as qgis plugin, the moprheo package does not exists
        # try to load resource from module path
        srcpath = os.path.dirname(__file__)

    sqlfile = os.path.join(srcpath, name)
    if not os.path.exists(sqlfile):
        # If we are not in standard python installation,
        # try to get file locally
        lookupdir = os.path.dirname(__file__)
        logging.info(f"Builder: looking for sql file in {lookupdir}")
        sqlfile = os.path.join(lookupdir)

    if not os.path.exists(sqlfile):
        raise SQLNotFoundError(f"Cannot find file {sqlfile}")

    with open(sqlfile, 'r') as f:
        sql = string.Template(f.read()).substitute(**kwargs)
    return sql


def execute_sql(conn: Connection, name: str, quiet: bool = False, **kwargs) -> None:
    """ Execute statements from sql file

        All extra named arguments will be used as substitution parameters
        for $<name> expressions in the sql file.

        :param conn: the database connection
        :param name: of the sql file to execute
        :param quiet: log progress when set to true
    """
    statements = load_sql(name, **kwargs).split(';')
    count = len(statements)
    cur = conn.cursor()
    for i, statement in enumerate(statements):
        if not quiet:
            log_progress(i+1, count)
        if statement:
            cur.execute(SQL(statement))
    conn.commit()


def table_exists(cur: Cursor, name: str) -> bool:
    """ Test if table exists
    """
    cur.execute(SQL(f"SELECT Count(*) FROM sqlite_master WHERE type='table' AND name='{name}'"))
    return int(cur.fetchone()[0]) == 1


def create_indexed_table(cur: Cursor, table: str, geomtype: str, table_ref: str) -> None:
    """ Create a spatially indexed table 
    """
    if not table_exists(cur, table):
        cur.execute(SQL(f"CREATE TABLE {table}(OGC_FID integer primary key)"))

        select_request = f"""
                            SELECT AddGeometryColumn(
                                '{table}',
                                'GEOMETRY',
                                (
                                    SELECT CAST(srid AS integer)
                                    FROM geometry_columns
                                    WHERE f_table_name='{table_ref}'
                                ),
                                '{geomtype}',
                                (
                                    SELECT coord_dimension
                                    FROM geometry_columns
                                    WHERE f_table_name='{table_ref}'
                                )
                           )
                         """

        cur.execute(SQL(select_request))
        cur.execute(SQL(f"SELECT CreateSpatialIndex('{table}', 'GEOMETRY')"))

    cur.execute(SQL(f"DELETE FROM {table}"))


def delete_table(cur: Cursor, table: str) -> None:
    """ Safely delete spatialite table """
    if table_exists(cur, table):
        cur.execute(SQL(f"SELECT DisableSpatialIndex('{table}', 'GEOMETRY')"))
        cur.execute(SQL(f"SELECT DiscardGeometryColumn('{table}', 'GEOMETRY')"))
        cur.execute(SQL(f"DROP TABLE idx_{table}_GEOMETRY"))
        cur.execute(SQL(f"DROP TABLE {table}"))
        cur.execute(SQL("VACUUM"))


def set_srid(cur: Cursor, table: str, from_table: str) -> None:
    """ Force srid for table
    """
    [srid_to] = cur.execute(SQL(f"SELECT srid FROM geometry_columns WHERE f_table_name='{from_table}'")).fetchone()
    [srid_from] = cur.execute(SQL(f"SELECT srid FROM geometry_columns WHERE f_table_name='{table}'")).fetchone()

    if srid_to != srid_from:
        cur.execute(SQL(f"UPDATE geometry_columns SET srid = {srid_to} WHERE f_table_name = '{table}'"))
        cur.execute(SQL(f"UPDATE {table} SET GEOMETRY = SetSRID(GEOMETRY, {srid_to})"))


def create_attribute_table(cur: Cursor, name: str,  dtype: str = 'real') -> str:
    """ Create a table for storing temporary results 
    """
    cur.execute(SQL(f"CREATE TABLE IF NOT EXISTS {name}(ID integer,VALUE {dtype})"))
    cur.execute(SQL(f"CREATE INDEX IF NOT EXISTS {name}_ID_idx ON {name}(ID)"))
    cur.execute(SQL(f"DELETE FROM {name}"))
    return name


def fill_attribute_table(cur: Cursor, name: str, rows: list) -> None:
    """ Update attribute table with data
    """
    cur.execute(SQL(f"DELETE FROM {name}"))
    cur.executemany(SQL(f"INSERT INTO {name}(ID,VALUE) SELECT ?,?"), rows)


def delete_attr_table(cur: Cursor, name: str) -> None:
    """ Delete attribute table
    """
    if table_exists(cur, name):
        cur.execute(SQL(f"DROP INDEX {name}_ID_idx"))
        cur.execute(SQL(f"DROP TABLE {name}"))
        cur.execute(SQL("VACUUM"))


class AttrTable(object):
    def __init__(self, cur: Cursor, name: str, dtype: str = 'real'):
        self._cur = cur
        self._name = create_attribute_table(cur, name, dtype=dtype)

    def fill(self, rows: list) -> None:
        fill_attribute_table(self._cur, self._name, rows)

    @property
    def name(self) -> str:
        return self._name

    def update(self, dest_table: str, dest_id: str, dest_colunm: str, rows: list = None) -> None:
        """ Update destination table
        """
        if rows is not None:
            self.fill(rows)
        update = f"""
                     UPDATE {dest_table} 
                     SET {dest_colunm} = (SELECT VALUE FROM {self._name} WHERE ID={dest_table}.{dest_id})
                  """
        self._cur.execute(SQL(update))


@contextmanager
def attr_table(cur: Cursor, name: str, dtype: str = 'real'):
    attr_tab = AttrTable(cur, name, dtype=dtype) 
    try:
        yield attr_tab
    finally:
        delete_attr_table(cur, name)


