import os
import csv
import copy
import tempfile
import warnings
from optparse import make_option

import MySQLdb

from django.db import connection

from calaccess_raw.models import ExpnCd
from calaccess_raw import get_download_directory
from calaccess_campaign_browser.management.commands import CalAccessCommand
from calaccess_campaign_browser.models import Expenditure, Filing, Committee


custom_options = (
    make_option(
        "--skip-transform-quarterly",
        action="store_false",
        dest="transform_quarterly",
        default=True,
        help="Skip transforming quarterly CSV"
    ),
    make_option(
        "--skip-load-quarterly",
        action="store_false",
        dest="load_quarterly",
        default=True,
        help="Skip loading quarterly CSV to db"
    ),
)


class Command(CalAccessCommand):
    help = "Load refined campaign expenditures from CAL-ACCESS raw data"

    option_list = CalAccessCommand.option_list + custom_options

    def set_options(self, *args, **kwargs):
        self.data_dir = os.path.join(get_download_directory(), 'csv')

        # Make sure directory exists
        os.path.exists(self.data_dir) or os.mkdir(self.data_dir)

        self.cursor = connection.cursor()
        # Quarterlies stuff
        self.quarterly_tmp_csv = tempfile.NamedTemporaryFile().name
        self.quarterly_target_csv = os.path.join(
            self.data_dir,
            'expn_cd_transformed.csv'
        )

    def transform_quarterly_expenditures_csv(self):
        self.log("  Marking duplicates")
        self.log("   Dumping CSV sorted by unique identifier")
        sql = """
        SELECT
            `agent_namf`,
            `agent_naml`,
            `agent_nams`,
            `agent_namt`,
            `amend_id`,
            `amount`,
            `bakref_tid`,
            `bal_juris`,
            `bal_name`,
            `bal_num`,
            `cand_namf`,
            `cand_naml`,
            `cand_nams`,
            `cand_namt`,
            `cmte_id`,
            `cum_oth`,
            `cum_ytd`,
            `dist_no`,
            `entity_cd`,
            `expn_chkno`,
            `expn_code`,
            `expn_date`,
            `expn_dscr`,
            `filing_id`,
            `form_type`,
            `g_from_e_f`,
            `juris_cd`,
            `juris_dscr`,
            `line_item`,
            `memo_code`,
            `memo_refno`,
            `off_s_h_cd`,
            `offic_dscr`,
            `office_cd`,
            `payee_city`,
            `payee_namf`,
            `payee_naml`,
            `payee_nams`,
            `payee_namt`,
            `payee_st`,
            `payee_zip4`,
            `rec_type`,
            `sup_opp_cd`,
            `tran_id`,
            `tres_city`,
            `tres_namf`,
            `tres_naml`,
            `tres_nams`,
            `tres_namt`,
            `tres_st`,
            `tres_zip4`,
            `xref_match`,
            `xref_schnm`
        FROM %(raw_model)s
        ORDER BY filing_id, tran_id, amend_id DESC
        INTO OUTFILE '%(tmp_csv)s'
        FIELDS TERMINATED BY ','
        ENCLOSED BY '"'
        LINES TERMINATED BY '\n'
        """ % dict(
            raw_model=ExpnCd._meta.db_table,
            tmp_csv=self.quarterly_tmp_csv,
        )
        self.cursor.execute(sql)

        INHEADERS = [
            "agent_namf",
            "agent_naml",
            "agent_nams",
            "agent_namt",
            "amend_id",
            "amount",
            "bakref_tid",
            "bal_juris",
            "bal_name",
            "bal_num",
            "cand_namf",
            "cand_naml",
            "cand_nams",
            "cand_namt",
            "cmte_id",
            "cum_oth",
            "cum_ytd",
            "dist_no",
            "entity_cd",
            "expn_chkno",
            "expn_code",
            "expn_date",
            "expn_dscr",
            "filing_id",
            "form_type",
            "g_from_e_f",
            "juris_cd",
            "juris_dscr",
            "line_item",
            "memo_code",
            "memo_refno",
            "off_s_h_cd",
            "offic_dscr",
            "office_cd",
            "payee_city",
            "payee_namf",
            "payee_naml",
            "payee_nams",
            "payee_namt",
            "payee_st",
            "payee_zip4",
            "rec_type",
            "sup_opp_cd",
            "tran_id",
            "tres_city",
            "tres_namf",
            "tres_naml",
            "tres_nams",
            "tres_namt",
            "tres_st",
            "tres_zip4",
            "xref_match",
            "xref_schnm"
        ]
        OUTHEADERS = copy.copy(INHEADERS)
        OUTHEADERS.append("is_duplicate")

        self.log("   Marking duplicates in a new CSV")
        # `rU` is read Universal
        # see: https://docs.python.org/2/library/functions.html#open
        with open(self.quarterly_tmp_csv, 'rU') as fin:
            fout = csv.DictWriter(
                open(self.quarterly_target_csv, 'wb'),
                fieldnames=OUTHEADERS
            )
            fout.writeheader()
            last_uid = ''

            reader = csv.DictReader(fin, fieldnames=INHEADERS)

            for row in reader:
                row.pop(None, None)
                uid = '{}-{}'.format(
                    row['filing_id'],
                    row['tran_id']
                )

                if uid != last_uid:
                    row['is_duplicate'] = 0
                    last_uid = uid
                else:
                    row['is_duplicate'] = 1

                try:
                    fout.writerow(row)

                except ValueError:
                    continue

    def load_quarterly_expenditures(self):
        self.log("  Loading CSV")
        self.cursor.execute("DROP TABLE IF EXISTS TMP_EXPN_CD;")
        sql = """
        CREATE TABLE `TMP_EXPN_CD` (
            `AGENT_NAMF` varchar(45),
            `AGENT_NAML` varchar(200),
            `AGENT_NAMS` varchar(10),
            `AGENT_NAMT` varchar(10),
            `AMEND_ID`   int(11),
            `AMOUNT`     decimal(14,2),
            `BAKREF_TID` varchar(20),
            `BAL_JURIS`  varchar(40),
            `BAL_NAME`   varchar(200),
            `BAL_NUM`    varchar(7),
            `CAND_NAMF`  varchar(45),
            `CAND_NAML`  varchar(200),
            `CAND_NAMS`  varchar(10),
            `CAND_NAMT`  varchar(10),
            `CMTE_ID`    varchar(9),
            `CUM_OTH`    decimal(14,2) DEFAULT NULL,
            `CUM_YTD`    decimal(14,2) DEFAULT NULL,
            `DIST_NO`    varchar(3),
            `ENTITY_CD`  varchar(3),
            `EXPN_CHKNO` varchar(20),
            `EXPN_CODE`  varchar(3),
            `EXPN_DATE`  date DEFAULT NULL,
            `EXPN_DSCR`  varchar(400),
            `FILING_ID`  int(11),
            `FORM_TYPE`  varchar(6),
            `G_FROM_E_F` varchar(1),
            `JURIS_CD`   varchar(3),
            `JURIS_DSCR` varchar(40),
            `LINE_ITEM`  int(11),
            `MEMO_CODE`  varchar(1),
            `MEMO_REFNO` varchar(20),
            `OFF_S_H_CD` varchar(1),
            `OFFIC_DSCR` varchar(40),
            `OFFICE_CD`  varchar(3),
            `PAYEE_CITY` varchar(30),
            `PAYEE_NAMF` varchar(45),
            `PAYEE_NAML` varchar(200),
            `PAYEE_NAMS` varchar(10),
            `PAYEE_NAMT` varchar(10),
            `PAYEE_ST`   varchar(2),
            `PAYEE_ZIP4` varchar(10),
            `REC_TYPE`   varchar(4),
            `SUP_OPP_CD` varchar(1),
            `TRAN_ID`    varchar(20),
            `TRES_CITY`  varchar(30),
            `TRES_NAMF`  varchar(45),
            `TRES_NAML`  varchar(200),
            `TRES_NAMS`  varchar(10),
            `TRES_NAMT`  varchar(10),
            `TRES_ST`    varchar(2),
            `TRES_ZIP4`  varchar(10),
            `XREF_MATCH` varchar(1),
            `XREF_SCHNM` varchar(2),
            `IS_DUPLICATE` bool
        )
        """

        self.cursor.execute(sql)

        sql = """
            LOAD DATA LOCAL INFILE '%s'
            INTO TABLE TMP_EXPN_CD
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '"'
            LINES TERMINATED BY '\\n'
            IGNORE 1 LINES (
            `agent_namf`,
            `agent_naml`,
            `agent_nams`,
            `agent_namt`,
            `amend_id`,
            `amount`,
            `bakref_tid`,
            `bal_juris`,
            `bal_name`,
            `bal_num`,
            `cand_namf`,
            `cand_naml`,
            `cand_nams`,
            `cand_namt`,
            `cmte_id`,
            `cum_oth`,
            `cum_ytd`,
            `dist_no`,
            `entity_cd`,
            `expn_chkno`,
            `expn_code`,
            `expn_date`,
            `expn_dscr`,
            `filing_id`,
            `form_type`,
            `g_from_e_f`,
            `juris_cd`,
            `juris_dscr`,
            `line_item`,
            `memo_code`,
            `memo_refno`,
            `off_s_h_cd`,
            `offic_dscr`,
            `office_cd`,
            `payee_city`,
            `payee_namf`,
            `payee_naml`,
            `payee_nams`,
            `payee_namt`,
            `payee_st`,
            `payee_zip4`,
            `rec_type`,
            `sup_opp_cd`,
            `tran_id`,
            `tres_city`,
            `tres_namf`,
            `tres_naml`,
            `tres_nams`,
            `tres_namt`,
            `tres_st`,
            `tres_zip4`,
            `xref_match`,
            `xref_schnm`,
            `is_duplicate`
            )
        """ % (
            self.quarterly_target_csv
        )
        self.cursor.execute(sql)

        self.log("  Merging CSV data with other tables")
        sql = """
            INSERT INTO %(expenditure_model)s (
                cycle_id,
                committee_id,
                filing_id,
                filing_id_raw,
                transaction_id,
                amend_id,
                backreference_transaction_id,
                is_crossreference,
                crossreference_schedule,
                is_duplicate,
                date_received,
                expenditure_description,
                amount,
                candidate_full_name,
                candidate_is_person,
                candidate_committee_id,
                candidate_prefix,
                candidate_first_name,
                candidate_last_name,
                candidate_suffix,
                candidate_entity_type,
                candidate_expense_code,
                payee_prefix,
                payee_first_name,
                payee_last_name,
                payee_suffix,
                payee_city,
                payee_state,
                payee_zipcode,
                payee_committee_id
            )
            SELECT
                f.cycle_id as cycle_id,
                f.committee_id as committee_id,
                f.id as filing_id,
                f.filing_id_raw,
                e.tran_id,
                e.amend_id,
                e.bakref_tid,
                e.xref_match,
                e.xref_schnm,
                e.is_duplicate,
                e.expn_date,
                e.expn_dscr,
                e.amount,
                CASE
                    WHEN e.cand_namf <> '' THEN e.cand_naml
                END as candidate_full_name,
                CASE
                    WHEN e.cand_namf <> '' THEN true
                    ELSE false
                END as candidate_is_person,
                c.id,
                COALESCE(e.cand_namt, ''),
                COALESCE(e.cand_namf, ''),
                COALESCE(e.cand_naml, ''),
                COALESCE(e.cand_nams, ''),
                COALESCE(e.entity_cd, ''),
                COALESCE(e.expn_code, ''),
                COALESCE(e.payee_namt, ''),
                COALESCE(e.payee_namf, ''),
                COALESCE(e.payee_naml, ''),
                COALESCE(e.payee_nams, ''),
                COALESCE(e.payee_city, ''),
                COALESCE(e.payee_st, ''),
                COALESCE(e.payee_zip4, ''),
                ''
            FROM %(filing_model)s as f
            INNER JOIN %(raw_model)s as e
            ON f.filing_id_raw = e.filing_id
            AND f.amend_id = e.amend_id
            LEFT OUTER JOIN %(committee_model)s as c
            ON e.cmte_id = c.xref_filer_id
        """ % dict(
            expenditure_model=Expenditure._meta.db_table,
            filing_model=Filing._meta.db_table,
            raw_model='TMP_EXPN_CD',
            committee_model=Committee._meta.db_table,
        )
        self.cursor.execute(sql)
        self.cursor.execute('DROP TABLE TMP_EXPN_CD;')

    def handle(self, *args, **options):
        self.header("Loading expenditures")
        self.set_options(*args, **options)

        warnings.filterwarnings("ignore", category=MySQLdb.Warning)

        self.log(" Quarterly filings")
        if options['transform_quarterly']:
            self.transform_quarterly_expenditures_csv()

        if options['load_quarterly']:
            self.load_quarterly_expenditures()