# -*- coding: utf-8 -*-
import click
import logging
from pathlib import Path
from dotenv import find_dotenv, load_dotenv
from sqlalchemy import create_engine, text
import os
import numpy as np
import pandas as pd
import warnings
import itertools
warnings.filterwarnings("ignore")

MF_CODES = ["002", "003", "059", "061", "067"]
TAX_RATES = {"0": 0.03195986, "D": 0.0405}
#os.chdir(os.path.dirname(__file__))

# find .env automagically by walking up directories until it's found, then
# load up the .env entries as environment variables
load_dotenv(find_dotenv())
database_url = os.getenv("DATABASE_URL")
engine = create_engine(database_url)
try:
    df = pd.read_sql("select * from mftax.asmt", engine)
except:
    pass

@click.group()
def main():
    pass

@main.command()
@click.argument("city", default="0")
@click.option("--num-units", default=list(range(2,21)),
        help="List containing the number of units to be used in analysis.")
@click.option("--rates", default=[np.round(i, 3) for i in list(np.arange(.25, .4, .025))],
        help="List containing the tax rates to be used in the analysis.")
def table_revenue_estimate(city, num_units, rates):
    """
    Creates table in project schema containing the results of estimated tax generated by
    changing multi-family tax rates.

    Args:
        city (str): City code derived from the first character of a parcelid. The analysis
            is developed primarily to evaluate Memphis and Shelby County, but additional 
            values include:

            "0": City of Memphis, "D": Shelby County, "A": Arlington, "B": Bartlett,
            "M": Millington, "G": Germantown, "L": Lakeland

    Returns:
        None

    """
    logger = logging.getLogger(__name__)
    logger.info("building table mftax.revenue_estimate")


    tot = current_revenue(city) #current total city
    tot_co = current_revenue(city_rate="D") #current total county
    p_rates = []
    p_units = []
    taxes = []
    taxes_co = [] #estimated taxes county
    max_units = max(num_units) 
    min_units = min(num_units)
    for rate, unit in itertools.product(rates, num_units):
        #projected city tax revenue if rate reduced for all units of size unit and below
        p_taxes = (df[(df.parcelid.str[0] == city) &
                      (df["class"] == "C") &
                      (df.luc.isin(MF_CODES)) & 
                      # (df.livunit.between(2,unit))].rtotapr.sum() 
                      (df.livunit == unit)].rtotapr.sum() 
                      * rate * TAX_RATES[city]
                  )
        #current city tax revenue
        c_taxes = (df[(df.parcelid.str[0] == city) &
                      (df["class"] == "C") &
                      (df.luc.isin(MF_CODES)) & 
                      (df.livunit != unit) &
                      (df.livunit.between(min_units, max_units))].rtotasmt.sum() 
                      * TAX_RATES[city]
                  )
        #projected county tax revenue
        p_taxes_co = (df[(df.parcelid.str[0] == city) &
                      (df["class"] == "C") &
                      (df.luc.isin(MF_CODES)) & 
                      # (df.livunit.between(2,unit))].rtotapr.sum() 
                      (df.livunit == unit)].rtotapr.sum() 
                      * rate * TAX_RATES["D"]
                  )
        #current county tax revenue
        c_taxes_co = (df[(df.parcelid.str[0] == city) &
                      (df["class"] == "C") &
                      (df.luc.isin(MF_CODES)) & 
                      (df.livunit != unit) &
                      #(df.livunit > unit)].rtotasmt.sum() * TAX_RATES["D"]
                      (df.livunit.between(min_units, max_units))].rtotasmt.sum() 
                      * TAX_RATES["D"]
                  )



        taxes.append(p_taxes + c_taxes)
        p_units.append(unit)
        p_rates.append(rate)
        taxes_co.append(c_taxes_co + p_taxes_co)
    data = {"tax_rate":p_rates, "num_units":p_units, "est_tax":taxes, "est_tax_co": taxes_co}
    tax = pd.DataFrame(data)
    tax["pct_diff"] = (np.abs(tax.est_tax-tot)/((tax.est_tax+tot)/2))
    tax["pct_diff_co"] =  (np.abs(tax.est_tax_co-tot_co)/((tax.est_tax_co+tot_co)/2))
    tax.to_sql("revenue_estimates", engine, schema="mftax", 
              if_exists="replace", index_label="fid")
    tax.to_csv("../../reports/estimated_tax_single.csv", index=False)

# @click.command()
# @click.argument('input_filepath', type=click.Path(exists=True))
# @click.argument('output_filepath', type=click.Path())
@main.command()
def table_asmt():#(input_filepath, output_filepath):
    """
    pulls data from relevant sources (../raw, postgresql, etc.) and adds it to the schema
    for this analysis (i.e. mftax)

    Args:
        None

    Returns:
        None
    """
    logger = logging.getLogger(__name__)
    logger.info('building schema and loading data')
    q_schema = ("create schema if not exists mftax")
    engine.execute(q_schema)
    q_drop = ("drop table if exists mftax.asmt")
    engine.execute(q_drop)
    q_load = ("create table mftax.asmt as "
              "select row_number() over() fid, parcelid, "
              "concat(adrno, ' ', adrstr, ' ', adrsuf, ' ', zip1) addr, "
              "a.class, a.luc, livunit, zoning, rtotasmt, rtotapr, "
              "tractid, sca_parcels.wkb_geometry "
              "from sca_parcels "
              "left join sca_asmt a on parcelid = a.parid "
              "left join sca_pardat pd on parcelid = pd.parid "
              "left join (select wkb_geometry, geoid10 tractid from geography.tiger_tract_2010) t "
              "on st_intersects(st_centroid(sca_parcels.wkb_geometry), t.wkb_geometry)"
              )
    engine.execute(q_load)
    q_idx = ("create index ix_asmt_parcelid on mftax.asmt (parcelid);"
             "create index gix_asmt_wkb_geometry on mftax.asmt using gist(wkb_geometry);"
             "alter table mftax.asmt add primary key (fid);"
             )
    engine.execute(q_idx)

@main.command()
@click.option("--unit-step", default=1, required=False, 
    help="Set the step value to be used to increment the number of living units.")
@click.option("--rate-step", default=.025, required=False, 
    help="Set the step value to be used to increment the tax rate.")
def table_tax_rates_livunits(unit_step, rate_step, **kwargs):
    """
    Creates tables in project schema to hold the number of living units and the tax
    rates to be used throughout the analysis.

    Keyword Arguments:
        unit_min (int): minimum number of living units
        unit_max (int): maximum number of living units
        rate_min (float): minimum tax rate to be considered
        rate_max (float): maximum tax rate to be considered

    Returns:
        None
        
    """
    logger = logging.getLogger(__name__)
    logger.info('loading livunits and tax rate tables')

    if "unit_min" in kwargs:
        unit_min = kwargs["unit_min"]
    else:
        unit_min = 2
    if "unit_max" in kwargs:
        unit_max = kwargs["unit_max"]
    else:
        unit_max = 21
    if "rate_min" in kwargs:
        rate_min = kwargs["rate_min"]
    else:
        rate_min = .25
    if "rate_max" in kwargs:
        rate_max = kwargs["rate_max"]
    else:
        rate_max = .4

    rates = [np.round(i, 3) for i in list(np.arange(rate_min, rate_max, rate_step))]
    units = list(range(unit_min, unit_max, unit_step))
    df_rates = pd.DataFrame(rates, columns=["tax_rate"])
    df_rates.to_sql("tax_rates", engine, schema="mftax", if_exists="replace", index_label="fid")
    df_units = pd.DataFrame(units, columns=["livunit"])
    df_units.to_sql("livunits", engine, schema="mftax", if_exists="replace", index_label="fid")

@main.command()
def update_zoning():
    """
    Adds a new column to the asmt table and populates it with the zoning type that the
    parcel centroid falls in. This is necessary because there is some discrepancy
    between the value in zoning column that the parcel has listed and the zone in which
    the parcel actually resides
    """
    logger = logging.getLogger(__name__)
    logger.info("adding and updating column zoning_actual to mftax.asmt")

    update = ("alter table mftax.asmt "
              "drop column if exists zoning_actual;"
              "alter table mftax.asmt "
              "add column zoning_actual text;"
              "update mftax.asmt a "
              "set zoning_actual = (regexp_split_to_array(zone_type, '\('))[1] "
              "from mftax.zoning z "
              "where st_intersects(st_centroid(a.wkb_geometry), z.wkb_geometry);"
              )
    engine.execute(update)

@main.command()
def table_mdn_apr_by_luc():
    logger = logging.getLogger(__name__)
    logger.info("building table mftax.mdn_apr_by_luc")

    q = ("create table mftax.mdn_apr_by_luc as "
         "select row_number() over() fid, t.geoid10 tractid, median(rtotapr) mdn_apr, "
         "luc, median(livunit) mdn_livunit, "
         "count(luc)/sum(count(luc)) over (partition by geoid10) as pct_luc "
         "from mftax.asmt a, geography.tiger_tract_2010 t "
         "where st_intersects(a.wkb_geometry, t.wkb_geometry) "
  	 "and luc in ('{}') "
         "group by  t.geoid10, luc "
         "order by geoid10, luc "
        )
    engine.execute("drop table if exists mftax.mdn_apr_by_luc")
    engine.execute(q.format("', '".join(MF_CODES+['062'])))
    q_idx = ("create index idx_tractid_mdn_apr_by_luc on mftax.mdn_apr_by_luc (tractid);"
             "alter table mftax.mdn_apr_by_luc add primary key (fid);"
             )
    engine.execute(q_idx)


@main.command()
def table_vacancy_by_zoning():
    logger = logging.getLogger(__name__)
    logger.info("building table mftax.vacancy_by_zoning")
    q = ("create table mftax.vacancy_by_zoning as "
         "select geoid10 tractid, zoning_actual, count(parcelid) num_vac "
         "from mftax.asmt a, geography.tiger_tract_2010 t "
         "where st_intersects(st_centroid(a.wkb_geometry), t.wkb_geometry) "
         "and zoning_actual similar to '%(RU|CMU|CMP|RW|OG|CBD)%' "
         "and luc = '000' "
         "group by geoid10, zoning_actual "
         "order by geoid10, zoning_actual "
         )
    engine.execute(text(q))


@main.command()
@click.option("--rates", default=[np.round(i, 3) for i in list(np.arange(.25, .4, .025))],
        help="List containing the tax rates to be used in the analysis.")
def table_revenue_projections(rates):
    """
    
    """
    vacancy = pd.read_sql("select * from mftax.vacancy_by_zoning", engine)
    luc_value = pd.read_sql("select * from mftax.mdn_apr_by_luc", engine)

    #uses lookup table to identify lucs that are compatible with each zone
    #luc values are in order by number of units with high rise apartments (059) first
    #and duplexes (059) last so that land use with greatest potential for density is
    #evaluated first
    luc_mf_value = luc_value[luc_value.luc != '062']
    zoning = pd.read_csv("../../data/raw/luc_zoning_lookup.csv", dtype=str)
    taxes_mf_scenario = np.zeros(len(rates))
    for i in vacancy.index:
        tract, zone, vac = vacancy.loc[i]
        #compare the compatible lucs in zoning to find index position of best luc 
        #as defined above
        compatible_lucs = zoning[zone].tolist()
        tract_lucs = set(luc_mf_value[luc_mf_value.tractid == tract].luc)
        luc_idx = [compatible_lucs.index(i) for i in set(compatible_lucs).intersection(tract_lucs)]
        if luc_idx: #there's a compatible land use, so get the median appraisal for that tract
            best_luc = compatible_lucs[min(luc_idx)]
            mdn_val = luc_mf_value[(luc_mf_value.tractid == tract) &
                                (luc_mf_value.luc == best_luc)].mdn_apr.values[0]
        else: #no compatible zone, so get median value for that zone from entire city
            best_luc = zoning[zone][0]
            mdn_val = luc_mf_value[luc_mf_value.luc == best_luc].mdn_apr.median()
        taxes_mf_scenario = [tax + vac*mdn_val*rate*TAX_RATES[city] 
                             for tax, rate 
                             in zip(taxes_mf_scenario, rates)
                             ]
    #calculate potential revenue using current land use distribution patterns    
    vacancy_totals = vacancy.groupby("tractid").num_vac.sum()
    luc_value = luc_value.join(vacancy_totals, on="tractid")
    taxes_current_trends = np.zeros(len(rates))
    m = lambda row, rate: row.mdn_apr*row.pct_luc*row.num_vac*rate*TAX_RATES[city]
    taxes_current_trends = [tax + luc_value.apply(m, axis=1, args=(rate,)).sum()
                            for tax,rate
                            in zip(taxes_current_trends, rates)
                            ]
    df_comparison = pd.DataFrame(data={"current_trends":taxes_current_trends,
                                       "mf_projections":taxes_mf_scenario},
                                 index=rates)
    df_comparison.to_csv("../../data/processed/current_projected_tax.csv", index_label="rate")


def current_revenue(city="0", city_rate=None, max_units=20):
    """
    Estimates current tax revenue generated by multi-family units for a given city.

    Args:
        city (str): City code derived from the first character of a parcelid. The analysis
            is developed primarily to evaluate Memphis and Shelby County, but additional 
            values include:

            "0": City of Memphis
            "D": Shelby County
            "A": Arlington
            "B": Bartlett
            "M": Millington
            "G": Germantown
            "L": Lakeland

    Optional:
        city_rate (str): Can specify second city code if the amount of Shelby County tax
            is needed.
        max_units (int): If provided, will change the range of living units to be used
            to calculate the total. If not, the default maximum of 20 is used.
    Returns:
        Float value representing the total mount of tax revenue currently generated by
            multi-family units.


    """
    rate = city_rate if city_rate else city
    # df = pd.read_sql("select * from mftax.asmt", engine)
    return df[(df.parcelid.str[0] == city) &
              (df["class"] == "C") &
              (df.luc.isin(MF_CODES)) & 
              #(df.livunit >= 2)].rtotasmt.sum() 
              (df.livunit.between(2, max_units))].rtotasmt.sum() * TAX_RATES[rate]

@main.command()
@click.argument("city", default="0")
@click.option("--num-units", default=list(range(2,21)),
        help="List containing the number of units to be used in analysis.")
@click.option("--rates", default=[np.round(i, 3) for i in list(np.arange(.25, .4, .025))],
        help="List containing the tax rates to be used in the analysis.")
@click.option("--unit-step", default=1, required=False, 
    help="Set the step value to be used to increment the number of living units.")
@click.option("--rate-step", default=.025, required=False, 
    help="Set the step value to be used to increment the tax rate.")
def rebuild_all(city, num_units, rates, unit_step, rate_step):
    """
    Rebuilds all tables in project schema
    """
    table_asmt()
    table_revenue_estimate(city, num_units, rates)
    set_units_and_rates(unit_step, rate_step, **kwargs)
    update_zoning()
    table_mdn_apr_by_luc()
    table_vacancy_by_zoning()

if __name__ == '__main__':
    log_fmt = '%(asctime)s - %(name)s - %(levelname)s - %(message)s'
    logging.basicConfig(level=logging.INFO, format=log_fmt)

    # not used in this stub but often useful for finding various files
    project_dir = Path(__file__).resolve().parents[2]
    os.chdir(os.path.dirname(__file__))
    main()


    
