'''
/***************************************************************************
Name	   :  database_utilities.py
Description:  Database utility classes for FEWSTools plugin,
              updated from QGIS2
copyright  :  (C) 2019-2023 by FEWS
email      :  minxuansun@contractor.usgs.gov
Modified   :  12/16/2020 - cholen - Added query_db_columns
              07/25/2022 - cholen - Add database_startup_updates
 ***************************************************************************/

/***************************************************************************
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 ***************************************************************************/
'''
import sqlite3

from fews_tools import fews_tools_config as config


def query_db(sql, params=None, multi=False, db_name=None):
    '''
    Run a sql query ( for SELECT )

    :param(string) sql: Input query
    :param(string) params: Parameters for input query
    :param(boolean) multi: Expect multilple records returned?
    :param(string)db_name: Database name (default=None, will use config)
    :return list: Query result
    '''
    if db_name is None:
        db_name = config.DB_FILE
    db_conn = sqlite3.connect(db_name)
    cursor_map = db_conn.cursor()
    if params:
        cursor_map.execute(sql, params)
    else:
        cursor_map.execute(sql)
    if multi is False:
        fetch = cursor_map.fetchone()
    else:
        fetch = cursor_map.fetchall()
    db_conn.close()
    return fetch


def query_db_columns(sql, db_name=None):
    '''
    Run a sql query ( for SELECT )

    :param(string) sql: Input query
    :param(string)db_name: Database name (default=None, will use config)
    :return list: Query result
    '''
    if db_name is None:
        db_name = config.DB_FILE
    db_conn = sqlite3.connect(db_name)
    cursor_map = db_conn.cursor()
    cursor_map.execute(sql)
    col_names = [description[0] for description in cursor_map.description]
    db_conn.close()
    return col_names


def write_db(sql, params=None, db_name=None):
    '''
    Write to db using sql stmt

    :param(string) sql: Input query
    :param(string) params: Parameters for input query
    :param(string)db_name: Database name (default=None, will use config)
    '''
    if db_name is None:
        db_name = config.DB_FILE
    db_conn = sqlite3.connect(db_name)
    cursor_map = db_conn.cursor()
    if not params:
        cursor_map.execute(sql)
    else:
        cursor_map.execute(sql, params)
    db_conn.commit()
    db_conn.close()
