import os
import csv
import MySQLdb
import warnings
from django.db import connection
from calaccess_raw import get_download_directory
from django.utils.datastructures import SortedDict
from calaccess_campaign_browser.models import Summary
from calaccess_campaign_browser.management.commands import CalAccessCommand


class Command(CalAccessCommand):
    help = "Load refined CAL-ACCESS campaign filing summaries"

    def handle(self, *args, **options):
        self.header("Loading summary totals")
        self.data_dir = get_download_directory()
        self.source_csv = os.path.join(self.data_dir, 'csv', 'smry_cd.csv')
        self.target_csv = os.path.join(
            self.data_dir,
            'csv',
            'smry_cd_transformed.csv'
        )
        self.transform_csv()
        self.load_csv()

    def load_csv(self):
        self.log(" Loading transformed CSV")
        # Ignore MySQL warnings so this can be run with DEBUG=True
        warnings.filterwarnings("ignore", category=MySQLdb.Warning)
        c = connection.cursor()
        sql = """
            LOAD DATA LOCAL INFILE '%s'
            INTO TABLE %s
            FIELDS TERMINATED BY ','
            OPTIONALLY ENCLOSED BY '"'
            LINES TERMINATED BY '\\r\\n'
            IGNORE 1 LINES (
                filing_id_raw,
                amend_id,
                itemized_monetary_contributions,
                unitemized_monetary_contributions,
                total_monetary_contributions,
                non_monetary_contributions,
                total_contributions,
                itemized_expenditures,
                unitemized_expenditures,
                total_expenditures,
                ending_cash_balance,
                outstanding_debts
            )
        """ % (self.target_csv, Summary._meta.db_table)
        c.execute(sql)

    def transform_csv(self):
        self.log(" Transforming source CSV")
        grouped = {}
        form2field = {
            # F460
            'A-1': 'itemized_monetary_contributions',
            'A-2': 'unitemized_monetary_contributions',
            'A-3': 'total_monetary_contributions',
            'F460-4': 'non_monetary_contributions',
            'F460-5': 'total_contributions',
            'E-1': 'itemized_expenditures',
            'E-2': 'unitemized_expenditures',
            'E-4': 'total_expenditures',
            'F460-16': 'ending_cash_balance',
            'F460-19': 'outstanding_debts',
            # F450
            'F450-7': 'total_monetary_contributions',
            'F450-8': 'non_monetary_contributions',
            'F450-10': 'total_contributions',
            'F450-1': 'itemized_expenditures',
            'F450-2': 'unitemized_expenditures',
            'E-6': 'total_expenditures',
        }
        self.log("  Regrouping")
        for r in csv.DictReader(open(self.source_csv, 'rb')):
            uid = "%s-%s" % (r['FILING_ID'], r['AMEND_ID'])
            formkey = "%s-%s" % (r['FORM_TYPE'], r['LINE_ITEM'])
            try:
                field = form2field[formkey]
            except KeyError:
                continue
            try:
                grouped[uid][field] = self.safeamt(r['AMOUNT_A'])
            except KeyError:
                grouped[uid] = SortedDict((
                    ("itemized_monetary_contributions", "\N"),
                    ("unitemized_monetary_contributions", "\N"),
                    ("total_monetary_contributions", "\N"),
                    ("non_monetary_contributions", "\N"),
                    ("total_contributions", "\N"),
                    ("itemized_expenditures", "\N"),
                    ("unitemized_expenditures", "\N"),
                    ("total_expenditures", "\N"),
                    ("ending_cash_balance", "\N"),
                    ("outstanding_debts", "\N")
                ))
                grouped[uid][field] = self.safeamt(r['AMOUNT_A'])
        self.log("  Writing to filesystem")
        out = csv.writer(open(self.target_csv, "wb"))
        outheaders = (
            "filing_id_raw",
            "amend_id",
            "itemized_monetary_contributions",
            "unitemized_monetary_contributions",
            "total_monetary_contributions",
            "non_monetary_contributions",
            "total_contributions",
            "itemized_expenditures",
            "unitemized_expenditures",
            "total_expenditures",
            "ending_cash_balance",
            "outstanding_debts"
        )
        out.writerow(outheaders)
        for uid, data in grouped.items():
            outrow = uid.split("-") + data.values()
            out.writerow(outrow)

    def safeamt(self, num):
        if not num:
            return "\N"
        return num