import os
import csv
import subprocess
import pytz
from datetime import datetime, timedelta
from glob import glob
from ftplib import FTP
from zipfile import ZipFile

from django.utils import timezone
from django.core.management.base import BaseCommand
from django.contrib.auth.models import User
from django.core.exceptions import ObjectDoesNotExist
from django.conf import settings

from api.models import (
    DisasterType,
    Country,
    FieldReport,
    Action,
    ActionsTaken,
    FieldReportContact,
    SourceType,
    Source,
    Event,
)
from api.fixtures.dtype_map import PK_MAP
from api.event_sources import SOURCES
from api.logger import logger

REPORT_DATE_FORMAT = '%m/%d/%y %H:%M:%S'


def extract_table(dbfile, table):
    """ Extract a table from the Access database """
    cmd = 'mdb-export %s %s' % (dbfile, table)
    try:
        output = subprocess.check_output(cmd.split(' ')).splitlines()
    except Exception as e:
        logger.error(e)
    output = [o.decode('utf-8') for o in output]
    reader = csv.reader(output, delimiter=',', quotechar='"')
    records = []
    for i, row in enumerate(reader):
        if i == 0:
            header = row
        else:
            d = {header[i]: l for i, l in enumerate(row)}
            records.append(d)
    return records


def contact_is_valid(contact, field):
    for suffix in ['Name', 'Function', 'Contact']:
        if contact['%s%s' % (field, suffix)] is None:
            return False
    return True


def get_dbfile():
    ftphost = settings.GO_FTPHOST
    ftpuser = settings.GO_FTPUSER
    ftppass = settings.GO_FTPPASS
    dbpass = settings.GO_DBPASS
    if ftphost is None or ftpuser is None or ftppass is None:
        if os.path.exists('URLs.mdb'):
            logger.info('No credentials in env, using local MDB database file')
            logger.warning('If this occurs outside development, contact an administrator')
            return 'URLs.mdb'
        else:
            raise Exception('FTP credentials not provided (GO_FTPHOST, GO_FTPUSER, GO_FTPPASS)')
    if dbpass is None:
        raise Exception('Database encryption password not provided (GO_DBPASS)')
    logger.info('Attempting connection to FTP')
    ftp = FTP(ftphost)
    ftp.login(user=ftpuser, passwd=ftppass)
    ftp.cwd('/dmis/')
    data = []
    ftp.dir('-t', data.append)
    filename = data[-1].split()[3]

    # check if we already have this file
    files = glob('URLs*zip')
    if filename in files and os.path.exists('URLs.mdb'):
        ftp.quit()
        return 'URLs.mdb'

    # clean up old files
    for f in files:
        os.remove(f)

    logger.info('Fetching %s' % filename)
    with open(filename, 'wb') as f:
        ftp.retrbinary('RETR ' + filename, f.write, 2014)
    ftp.quit()

    logger.info('Unzipping database file')
    zp = ZipFile(filename)
    zp.extractall('./', pwd=dbpass.encode('cp850', 'replace'))
    return 'URLs.mdb'


def fetch_relation(records, rid):
    """ Find record with matching ReportID to rid """
    matches = [r for r in records if r['ReportID'] == rid]
    results = []
    for match in matches:
        results.append({key: (value if value != '' else None) for key, value in match.items()})
    return results


class Command(BaseCommand):
    help = 'Add new entries from Access database file'

    def handle(self, *args, **options):
        # get latest
        filename = get_dbfile()

        # numeric details records
        details_rc = extract_table(filename, 'EW_Report_NumericDetails')
        # check for 1 record for each field report
        fids = [r['ReportID'] for r in details_rc]
        if len(set(fids)) != len(fids):
            raise Exception('More than one NumericDetails record for a field report')
        # numeric details records
        details_gov = extract_table(filename, 'EW_Report_NumericDetails_GOV')
        # check for 1 record for each field report
        fids = [r['ReportID'] for r in details_gov]
        if len(set(fids)) != len(fids):
            raise Exception('More than one NumericDetails record for a field report')

        # information
        info_table = extract_table(filename, 'EW_Report_InformationManagement')
        fids = [r['ReportID'] for r in info_table]
        if len(set(fids)) != len(fids):
            raise Exception('More than one InformationManagement record for a field report')

        # ## many-to-many

        # actions taken
        actions_national = extract_table(filename, 'EW_Report_ActionTakenByRedCross')
        actions_foreign = extract_table(filename, 'EW_Report_ActionTakenByPnsRC')
        actions_federation = extract_table(filename, 'EW_Report_ActionTakenByFederationRC')

        # source types
        source_types = extract_table(filename, 'EW_lofSources')
        for s in source_types:
            SourceType.objects.get_or_create(pk=s['SourceID'], defaults={'name': s['SourceName']})

        source_table = extract_table(filename, 'EW_Reports_Sources')

        # disaster response
        dr_table = extract_table(filename, 'EW_DisasterResponseTools')
        # check for 1 record for each field report
        fids = [r['ReportID'] for r in dr_table]
        if len(set(fids)) != len(fids):
            raise Exception('More than one DisasterResponseTools record for a field report')

        # contacts
        contacts = extract_table(filename, 'EW_Report_Contacts')

        # field report
        reports = extract_table(filename, 'EW_Reports')
        rids = [r.rid for r in FieldReport.objects.all()]
        num_reports_created = 0
        logger.info('%s reports in database' % len(reports))
        for i, report in enumerate(reports):

            # Skip reports that we've already ingested.
            # We don't have to update them because field reports can't be updated in DMIS.
            rid = report['ReportID']
            if rid in rids:
                continue

            report_name = report['Summary']
            report_description = report['BriefSummary']
            report_dtype = DisasterType.objects.get(pk=PK_MAP[report['DisasterTypeID']])
            record = {
                'rid': rid,
                'summary': report_name,
                'description': report_description,
                'dtype': report_dtype,
                'status': report['StatusID'],
                'request_assistance': report['GovRequestsInternAssistance'],
                'actions_others': report['ActionTakenByOthers'],
                'report_date': datetime.strptime(report['Inserted'], REPORT_DATE_FORMAT).replace(tzinfo=pytz.utc),
            }
            details = fetch_relation(details_rc, report['ReportID'])
            assert(len(details) <= 1)
            if len(details) > 0:
                details = details[0]
                record.update({
                    'num_injured': details['NumberOfInjured'],
                    'num_dead': details['NumberOfCasualties'],
                    'num_missing': details['NumberOfMissing'],
                    'num_affected': details['NumberOfAffected'],
                    'num_displaced': details['NumberOfDisplaced'],
                    'num_assisted': details['NumberOfAssistedByRC'],
                    'num_localstaff': details['NumberOfLocalStaffInvolved'],
                    'num_volunteers': details['NumberOfVolunteersInvolved'],
                    'num_expats_delegates': details['NumberOfExpatsDelegates']
                })
            details = fetch_relation(details_gov, report['ReportID'])
            assert(len(details) <= 1)
            if len(details) > 0:
                details = details[0]
                record.update({
                    'gov_num_injured': details['NumberOfInjured_GOV'],
                    'gov_num_dead': details['NumberOfDead_GOV'],
                    'gov_num_missing': details['NumberOfMissing_GOV'],
                    'gov_num_affected': details['NumberOfAffected_GOV'],
                    'gov_num_displaced': details['NumberOfDisplaced_GOV'],
                    'gov_num_assisted': details['NumberOfAssistedByGov_GOV']
                })
            info = fetch_relation(info_table, report['ReportID'])
            if len(info) > 0:
                info = {k: '' if v is None else v for k, v in info[0].items()}
                record.update({
                    'bulletin': {'': 0, 'None': 0, 'Planned': 2, 'Published': 3}[info['InformationBulletin']],
                    'dref': {'': 0, 'No': 0, 'Planned': 2, 'Yes': 3}[info['DREFRequested']],
                    'dref_amount': 0 if info['DREFRequestedAmount'] == '' else float(info['DREFRequestedAmount']),
                    'appeal': {'': 0, 'Planned': 2, 'Yes': 3, 'NB': 0, 'No': 0, 'YES': 3}[info['EmergencyAppeal']],
                    'appeal_amount': 0 if info['EmergencyAppealAmount'] == '' else float(info['EmergencyAppealAmount']),
                })
            # disaster response
            response = fetch_relation(dr_table, report['ReportID'])

            if len(response) > 0:
                response = {k: '' if v is None else v for k, v in response[0].items()}
                record.update({
                    'rdrt': {'': 0, 'No': 0, 'Yes': 3, 'Planned/Requested': 2}[response['RDRT']],
                    'fact': {'': 0, 'No': 0, 'Yes': 3, 'Planned/Requested': 2}[response['FACT']],
                    'eru_relief': {'': 0, 'Yes': 3, 'Planned/Requested': 2, 'No': 0}[response['ERU']]
                })

            field_report = FieldReport(**record)

            # Create an associated event object
            event_record = {
                'name': report_name if len(report_name) else report_dtype.name,
                'summary': report_description,
                'dtype': report_dtype,
                'disaster_start_date': datetime.utcnow().replace(tzinfo=timezone.utc),
                'auto_generated': True,
                'auto_generated_source': SOURCES['report_ingest'],
            }
            event = Event(**event_record)
            event.save()

            field_report.event = event
            field_report.save()
            num_reports_created = num_reports_created + 1

            try:
                country = Country.objects.select_related().get(pk=report['CountryID'])
            except ObjectDoesNotExist:
                logger.warning('Could not find a matching country for %s' % report['CountryID'])
                country = None

            if country is not None:
                field_report.countries.add(country)
                event.countries.add(country)
                if country.region is not None:
                    # No need to add a field report region, as that happens through a trigger.
                    field_report.regions.add(country.region)
                    event.regions.add(country.region)

            # ## add items with foreignkeys to report
            # national red cross actions
            actions = fetch_relation(actions_national, report['ReportID'])
            if len(actions) > 0:
                txt = ' '.join([a['Value'] for a in actions if a['Value'] is not None])
                act = ActionsTaken(organization='NTLS', summary=txt, field_report=field_report)
                act.save()
                for pk in [a['ActionTakenByRedCrossID'] for a in actions]:
                    act.actions.add(*Action.objects.filter(pk=pk))

            # foreign red cross actions
            actions = fetch_relation(actions_foreign, report['ReportID'])
            if len(actions) > 0:
                txt = ' '.join([a['Value'] for a in actions if a['Value'] is not None])
                act = ActionsTaken(organization='PNS', summary=txt, field_report=field_report)
                act.save()
                for pk in [a['ActionTakenByRedCrossID'] for a in actions]:
                    act.actions.add(*Action.objects.filter(pk=pk))

            # federation red cross actions
            actions = fetch_relation(actions_federation, report['ReportID'])
            if len(actions) > 0:
                txt = ' '.join([a['Value'] for a in actions if a['Value'] is not None])
                act = ActionsTaken(organization='FDRN', summary=txt, field_report=field_report)
                act.save()
                for pk in [a['ActionTakenByRedCrossID'] for a in actions]:
                    act.actions.add(*Action.objects.filter(pk=pk))

            # sources
            sources = fetch_relation(source_table, report['ReportID'])
            for s in sources:
                spec = '' if s['Specification'] is None else s['Specification']
                Source.objects.create(
                    stype=SourceType.objects.get(pk=s['SourceID']),
                    spec=spec, field_report=field_report
                )

            # disaster response
            response = fetch_relation(dr_table, report['ReportID'])

            # contacts
            contact = fetch_relation(contacts, report['ReportID'])
            if len(contact) > 0:
                # make sure just one contacts record
                assert(len(contact) == 1)
                contact = contact[0]
                fields = ['Originator', 'Primary', 'Federation', 'NationalSociety', 'MediaNationalSociety', 'Media']
                for f in fields:
                    if contact_is_valid(contact, f):
                        FieldReportContact.objects.create(
                            ctype=f,
                            name=contact['%sName' % f],
                            title=contact['%sFunction' % f],
                            email=contact['%sContact' % f],
                            field_report=field_report,
                        )
        total_reports = FieldReport.objects.all()
        logger.info('%s reports created' % num_reports_created)
        logger.info('%s reports in database' % total_reports.count())

        # org type mapping
        org_types = {
            '1': 'NTLS',
            '2': 'DLGN',
            '3': 'SCRT',
            '4': 'ICRC',
        }
        last_login_threshold = timezone.now() - timedelta(days=365)

        # add users
        user_records = extract_table(filename, 'DMISUsers')
        processed_users = 0
        for i, user_data in enumerate(user_records):
            if user_data['LoginLastSuccess'] == '':
                continue

            last_login = datetime.strptime(user_data['LoginLastSuccess'], REPORT_DATE_FORMAT,)
            last_login = pytz.UTC.localize(last_login)

            # skip users who haven't logged in for a year
            if last_login < last_login_threshold:
                continue

            try:
                user = User.objects.get(username=user_data['UserName'])
            except ObjectDoesNotExist:
                user = None

            if user is None:
                name = user_data['RealName'].split()
                first_name = name[0]
                last_name = ' '.join(name[1:]) if len(name) > 1 else ''
                user = User.objects.create(
                    username=user_data['UserName'],
                    first_name=first_name if len(first_name) <= 30 else '',
                    last_name=last_name if len(last_name) <= 30 else '',
                    email=user_data['EmailAddress'],
                    last_login=last_login,
                )
                user.set_password(user_data['Password'])
                user.is_staff = True if user_data['UserIsSysAdm'] == '1' else False

            # set user profile info
            user.profile.org = user_data['OrgTypeSpec'] if len(user_data['OrgTypeSpec']) <= 100 else ''
            user.profile.org_type = org_types.get(user_data['OrgTypeID'])
            # print(i, user_data['CountryID']) # - for debug purposes. Check go-api/data/Countries.csv for details.
            if user_data['CountryID'] in ['275', '281']:
                user_data['CountryID'] = '47'  # Hong Kong or Taiwan should be handled as China. Macao (279) is other case.
            elif user_data['CountryID'] in ['284']:
                user_data['CountryID'] = '292'  # Zone Central and West Africa -> Central Africa Country Cluster
            user.profile.country = Country.objects.get(pk=user_data['CountryID'])
            user.profile.city = user_data['City'] if len(user_data['City']) <= 100 else ''
            user.profile.department = user_data['Department'] if len(user_data['Department']) <= 100 else ''
            user.profile.position = user_data['Position'] if len(user_data['Position']) <= 100 else ''
            user.profile.phone_number = user_data['PhoneNumberProf'] if len(user_data['PhoneNumberProf']) <= 100 else ''
            user.save()
            processed_users = processed_users + 1
        logger.info('%s updated active user records' % processed_users)
