"""Insert data into database

Functions:

INSERT

- pais
- unidade
- sh
- ppe
- ppi
- fat_agreg
- cuci
- cgce
- siit
- isic
- grupo
- ncm
- nbm
- nbm_ncm
- uf
- uf_mun
- urf
- via
- agronegocio
- insert_all
- trade
- trade_nbm

"""


import os
import pathlib
from typing import Union

import pandas as pd
from comexdown import fs
from sqlalchemy.engine import Engine

from comexdb.reader import get_pais, get_sh, get_trade, get_trade_nbm, read


def pais(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    pais_df, pais_bloco_df = get_pais(root)
    pais_df.to_sql(
        "pais",
        engine,
        index=False,
        if_exists="append",
    )
    pais_bloco_df.to_sql(
        "pais_bloco",
        engine,
        index=False,
        if_exists="append",
    )


def unidade(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "unidade"))
    df.to_sql("unidade", engine, index=False, if_exists="append")


def sh(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    ncm_secrom, sh2, sh4, sh6 = get_sh(root)
    ncm_secrom.to_sql(
        "ncm_secrom",
        engine,
        index=False,
        if_exists="append",
    )
    sh2.to_sql(
        "sh2",
        engine,
        index=False,
        if_exists="append",
    )
    sh4.to_sql(
        "sh4",
        engine,
        index=False,
        if_exists="append",
    )
    sh6.to_sql(
        "sh6",
        engine,
        index=False,
        if_exists="append",
    )


def ppe(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "ppe"))
    df.to_sql("ppe", engine, index=False, if_exists="append")


def ppi(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "ppi"))
    df.to_sql("ppi", engine, index=False, if_exists="append")


def fat_agreg(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "fat_agreg"))
    df.to_sql("fat_agreg", engine, index=False, if_exists="append")


def cuci(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "cuci"))
    df.to_sql("cuci", engine, index=False, if_exists="append")


def cgce(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "cgce"))
    df.to_sql("cgce", engine, index=False, if_exists="append")


def siit(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "siit"))
    df.to_sql("siit", engine, index=False, if_exists="append")


def isic(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "isic"))
    df.to_sql("isic", engine, index=False, if_exists="append")


def grupo(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "grupo"))
    df.to_sql("grupo", engine, index=False, if_exists="append")


def ncm(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "ncm"))
    df.to_sql("ncm", engine, index=False, if_exists="append")


def nbm(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df_nbm = read(fs.path_aux(root, "nbm"))
    df_nbm.to_sql("nbm", engine, index=False, if_exists="append")


def nbm_ncm(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df_nbm_ncm = read(fs.path_aux(root, "nbm_ncm"))
    df_nbm_ncm.to_sql("nbm_ncm", engine, index=False, if_exists="append")


def uf(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "uf"))
    df.to_sql("uf", engine, index=False, if_exists="append")


def uf_mun(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    uf = read(fs.path_aux(root, "uf"))
    uf_mun = read(fs.path_aux(root, "uf_mun"))
    uf_data = pd.merge(uf_mun, uf[["sg_uf", "co_uf"]], on="sg_uf")
    uf_data = uf_data.drop(columns=["sg_uf"])
    uf_data.to_sql(
        "uf_mun",
        engine,
        index=False,
        if_exists="append",
    )


def urf(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "urf"))
    df.to_sql("urf", engine, index=False, if_exists="append")


def via(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    df = read(fs.path_aux(root, "via"))
    df.to_sql("via", engine, index=False, if_exists="append")


def agronegocio(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    if not isinstance(root, pathlib.PurePath):
        root = pathlib.Path(root)
    d = pd.read_csv(
        root / "auxiliary_tables" / "ncm-agronegocio.csv",
        sep=",",
        encoding="utf-8",
        usecols=range(4),
    )
    d = d.rename(columns={col: col.lower() for col in d.columns})
    d.to_sql(
        name="agronegocio",
        con=engine,
        index=False,
        if_exists="append",
    )


def insert_all(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
) -> None:
    pais(engine, root)
    unidade(engine, root)
    sh(engine, root)
    ppe(engine, root)
    ppi(engine, root)
    fat_agreg(engine, root)
    cuci(engine, root)
    cgce(engine, root)
    siit(engine, root)
    isic(engine, root)
    grupo(engine, root)
    ncm(engine, root)
    nbm(engine, root)
    nbm_ncm(engine, root)
    uf(engine, root)
    uf_mun(engine, root)
    urf(engine, root)
    via(engine, root)
    agronegocio(engine, root)


def trade(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
    direction: str,
    year: int,
    month: int = None,
    mun: bool = False,
) -> None:
    if mun:
        d = get_trade(
            root=root,
            direction=direction,
            year=year,
            month=month,
            mun=True,
        )
        direction = direction + "_mun"
    else:
        d = get_trade(
            root=root,
            direction=direction,
            year=year,
            month=month,
            mun=False,
        )
    d.to_sql(
        name=direction,
        con=engine,
        index=False,
        if_exists="append",
        chunksize=1000,
    )


def trade_nbm(
    engine: Engine,
    root: Union[pathlib.Path, str, os.PathLike],
    direction: str,
    year: int,
) -> None:
    d = get_trade_nbm(
        root=root,
        direction=direction,
        year=year,
    )
    d.to_sql(
        name=direction + "_nbm",
        con=engine,
        index=False,
        if_exists="append",
        chunksize=1000,
    )
