import csv
import sqlite3

from qgis.core import QgsTask
from qgis.PyQt.QtCore import pyqtSignal
import requests

from ..compat import MESSAGE_LEVEL_CRITICAL, MESSAGE_LEVEL_SUCCESS, MESSAGE_LEVEL_WARNING, QGSTASK_CAN_CANCEL
from ..QRiS.settings import Settings

DOWNLOAD_TIMEOUT = 120  # seconds (2 minutes)

# https://waterservices.usgs.gov/rest/Site-Service.html
# https://waterservices.usgs.gov/rest/Site-Test-Tool.html
# https://github.com/ENV859/UsingAPIs/blob/master/1-NWIS-discharge-data-as-API.ipynb


# The code is configured to pull daily values. The quarterly configuation is
# a holdover from when the code was first developed.
REQUESTS = {
    # 15 minute data
    "quarterly": {"url": "http://waterdata.usgs.gov/nwis/uv", "fields": {"datetime": "datetime", "discharge": "89062_00060", "discharge_code": "89062_00060_cd", "gage_height": "89063_00065", "gage_height_code": "89063_00065_cd"}},
    # daily data
    "daily": {
        "url": "https://waterservices.usgs.gov/nwis/dv/",
        "fields": {"datetime": "datetime", "discharge": "84956_00060_00003", "discharge_code": "84956_00060_00003_cd", "gage_height": "84959_00065_00003", "gage_height_code": "84959_00065_00003_cd"},
    },
}


# Daily value columns
# agency_cd,site_no,datetime,84956_00060_00003,84956_00060_00003_cd,84959_00065_00003,84959_00065_00003_cd


class StreamGageDischargeTask(QgsTask):
    # Signal to notify when done and return the PourPoint and whether it should be added to the map
    on_task_complete = pyqtSignal(bool, int)

    """
    https://docs.qgis.org/3.22/en/docs/pyqgis_developer_cookbook/tasks.html
    """

    def __init__(self, db_path: str, site_code, site_id, start_date, end_date):
        super().__init__(f"Stream Gage Discharge API Request for {site_code}", QGSTASK_CAN_CANCEL)
        # self.duration = duration
        self.db_path = db_path
        self.site_code = site_code
        self.site_id = site_id
        self.start_date = start_date
        self.end_date = end_date
        self.inserted_discharge_records = 0

    def run(self):
        """Heavy lifting and periodically check for isCanceled() and gracefully abort.
        Must return True or False. Raising exceptions will crash QGIS"""

        Settings().log("Started Stream Gage Discharge API Request ")

        try:
            params = {"format": "rdb", "sites": self.site_code, "startDT": self.start_date.strftime("%Y-%m-%d"), "endDT": self.end_date.strftime("%Y-%m-%d")}

            request_meta = REQUESTS["daily"]
            response = requests.get(request_meta["url"], params=params, timeout=DOWNLOAD_TIMEOUT)

            if response.status_code == 200:
                csv_raw = [line for line in response.text.splitlines() if line and not line.startswith("#") and not line.startswith("5s")]

                headers = csv_raw[0].split("\t")
                csv_data = csv.DictReader(csv_raw, delimiter="\t")
                # agency_cd,site_no,datetime,tz_cd,89062_00060,89062_00060_cd,89063_00065,89063_00065_cd
                if len(headers) > 3:
                    sql_data = [(self.site_id, row["datetime"], row[headers[3]], row[headers[4]], row[headers[5]] if len(headers) > 6 else None, row[headers[6]] if len(headers) > 7 else None) for row in csv_data]
                else:
                    sql_data = []

                with sqlite3.connect(self.db_path) as conn:
                    curs = conn.cursor()

                    count_before = self.get_discharge_record_count(curs)

                    curs.executemany(
                        """
                    INSERT INTO stream_gage_discharges (
                        stream_gage_id,
                        measurement_date,
                        discharge,
                        discharge_code,
                        gage_height,
                        gage_height_code)
                    VALUES (?, ?, ?, ?, ?, ?)
                    ON CONFLICT (stream_gage_id, measurement_date)
                    DO UPDATE SET
                        discharge = excluded.discharge,
                        discharge_code = excluded.discharge_code,
                        gage_height = excluded.gage_height,
                        gage_height_code = excluded.gage_height_code""",
                        sql_data,
                    )

                    conn.commit()
                    conn.execute("PRAGMA wal_checkpoint(TRUNCATE)")

                    count_after = self.get_discharge_record_count(curs)

                self.inserted_discharge_records = count_after - count_before

        except Exception as ex:
            self.exception = ex
            return False

        return True

    def get_discharge_record_count(self, curs):

        curs.execute("SELECT count(*) FROM stream_gage_discharges")
        return curs.fetchone()[0]

    # def get_gage_data(self):

    #     # url = BASE_REQUEST.format(self.min_lng, self.min_lat, self.max_lng, self.max_lat)
    #     params = {
    #         'format': 'rdb',
    #         'site_no': '02085070',  # self.site_code,
    #         # 'begin_date': self.start_date,
    #         # 'end_date': self.end_date
    #     }
    #     response = requests.get(BASE_REQUEST, params=params)

    #     if response.status_code == 200:
    #         csv_raw = [line for line in response.text.split('\n') if not (line.startswith('#') or line.startswith('5s'))]
    #         return csv.DictReader(csv_raw, delimiter='\t')
    #     else:
    #         raise Exception(response)

    # def save_gage_data(self, gage_data):

    #     conn = sqlite3.connect(self.db_path)
    #     curs = conn.cursor()

    #     curs.execute('SELECT fid FROM stream_gages WHERE site_name = ?', [self.site_code])
    #     gage_id = curs.fetchone()[0]

    #     for data in gage_data:
    #         print('none')

    def finished(self, result: bool):
        """
        This function is automatically called when the task has completed (successfully or not).
        You implement finished() to do whatever follow-up stuff should happen after the task is complete.
        finished is always called from the main thread, so it's safe to do GUI operations and raise Python exceptions here.
        result is the return value from self.run.
        """
        if result:
            Settings().log(f"Discharge Download Complete. {self.inserted_discharge_records} records downloaded.", MESSAGE_LEVEL_SUCCESS)
        else:
            if self.exception is None:
                Settings().log(f'Stream Stats API Request "{self.description()}" not successful but without exception (probably the task was manually canceled by the user)', MESSAGE_LEVEL_WARNING)
            else:
                Settings().log(f'Stream Statistics API Request "{self.description()}" Exception: {self.exception}', MESSAGE_LEVEL_CRITICAL)
                raise self.exception

        self.on_task_complete.emit(result, self.inserted_discharge_records)

    def cancel(self):
        Settings().log(f'Stream Statistics "{self.description()}" was canceled', MESSAGE_LEVEL_WARNING)
        super().cancel()
