"""

Run this query via terminal:

python -m query -o "C:/data" -s "83377" --start '2020-01-01' --end '2022-01-01'

"""

import argparse
import sqlite3
import numpy as np
import pandas as pd
import geopandas as gpd
from pathlib import Path


# ---------------------------------------------------------------------
# Global paths
# ---------------------------------------------------------------------
here = Path(__file__).resolve()
FOLDER_T2 = here.parent


# ---------------------------------------------------------------------
# Core functions
# ---------------------------------------------------------------------
def reset_data(df, cd_station):
    """Standardize dataframe fields and add station code."""
    ls_cols = list(df.columns)

    df["cd_station"] = cd_station
    df["datetime"] = pd.to_datetime(df["datetime"])

    f_fields = FOLDER_T2 / "DATABASE.gpkg"
    df_fields = gpd.read_file(f_fields, layer='fields')
    df_fields = df_fields[["name_field", "dtype", "scale", "nodata"]].dropna()

    for _, row in df_fields.iterrows():
        c = row["name_field"]
        d = row["dtype"].lower()
        f = row["scale"]
        n = row["nodata"]
        if c in df.columns:
            df[c] = df[c].astype(d)
            df[c] = df[c].replace(n, np.nan)
            df[c] = df[c] / f

    df = df[["cd_station"] + ls_cols].copy()
    df.drop(columns=["id_station", "fid"], errors="ignore", inplace=True)

    return df


def run_query(id_station, date_start, date_end):
    """Run query on GeoPackage filtering by station and date range."""
    

    conn = sqlite3.connect(FOLDER_T2 / "DATABASE.gpkg")
    query = f"""
        SELECT *
        FROM data
        WHERE id_station = {id_station}
          AND datetime >= '{date_start}'
          AND datetime < '{date_end}';
    """
    df = pd.read_sql_query(query, conn)
    conn.close()

    return df


# ---------------------------------------------------------------------
# Main processing
# ---------------------------------------------------------------------
def process(folder_output, cd_station, date_start=None, date_end=None):
    """Main data extraction and processing logic."""
    
    date_start = date_start or "1900-01-01"
    date_end = date_end or "2050-01-01"
    
    folder_output = Path(folder_output)
    folder_output.mkdir(parents=True, exist_ok=True)

    # Read station index
    df_index = gpd.read_file(FOLDER_T2 / "DATABASE.gpkg", layer="stations")
    id_station = df_index.loc[df_index["cd_station"] == cd_station, "id_station"].values[0]

    df = run_query(id_station, date_start, date_end)
    df = reset_data(df, cd_station)

    # Prepare output path
    ds = df['datetime'].min().strftime('%Y%m%d%H')
    de = df['datetime'].max().strftime('%Y%m%d%H')
    fio = folder_output / f"BDMEP-AUTO_{cd_station}_{ds}U{de}.csv"

    df.to_csv(fio, sep=";", index=False)
    print(f"\n Output saved to: {fio}")


# ---------------------------------------------------------------------
# CLI interface
# ---------------------------------------------------------------------
def parse_args():
    parser = argparse.ArgumentParser(
        description="Extract and process station data from GeoPackage database."
    )

    parser.add_argument(
        "-o", "--folder_output",
        required=True,
        help="Output folder where the resulting CSV will be saved."
    )

    parser.add_argument(
        "-s", "--cd_station",
        required=True,
        help="Station code (e.g., A001)."
    )

    parser.add_argument(
        "--start",
        help="Start date in YYYY-MM-DD format (optional)."
    )

    parser.add_argument(
        "--end",
        help="End date in YYYY-MM-DD format (optional)."
    )

    return parser.parse_args()


def main():
    args = parse_args()
    process(
        folder_output=args.folder_output,
        cd_station=args.cd_station,
        date_start=args.start,
        date_end=args.end
    )


if __name__ == "__main__":
    main()