#!/usr/bin/env node

var fs = require('fs'),
    parse = require('csv-parse'),
    _ = require('underscore'),
    moment = require('moment'),
    dbFile = __dirname +  '/dc-ocf.sqlite',
    knex = require('knex')({
        client: 'sqlite3',
        connection: {
            filename: dbFile
        }
    }),
    csvOptions = {columns: true},
    currentCommittees = {},
    batchSize = 10,
    tableName = 'contributions';

knex.schema.dropTableIfExists(tableName)
    .createTable(
        tableName,
        function (table) {
            var columnNames = [
                    'committee_name',
                    'contributor_name',
                    'contributor_address',
                    'contributor_type',
                    'contribution_type',
                    'employer_name',
                    'employer_address',
                    'occupation',
                    'receipt_date',
                    'amount'
                ];
            table.increments();
            columnNames.forEach(function (columnName) {
                if (/date/.test(columnName)) {
                    table.date(columnName);
                }
                else if (/amount|total/.test(columnName)) {
                    table.float(columnName);
                }
                else {
                    table.string(columnName);
                }
            });
        }
    )
    .then(readCommittees);

function readCommittees() {
    var parser = parse(csvOptions),
        input = fs.createReadStream(__dirname + '/committees.csv');
    parser.on('readable', function () {
        var record;
        while(record = parser.read()) {
            record = transformRecord(record);
            currentCommittees[record.committee_name] = true;
        }
    });
    parser.on('error', function (err) {
        console.error(err.message);
        throw err;
    });
    parser.on('finish', function () {
        console.log('Finished reading committees');
        console.log(Object.keys(currentCommittees).sort());
        readContributions();
    });
    input.pipe(parser);
}

function readContributions() {
    var parser = parse(csvOptions),
        input = fs.createReadStream(__dirname + '/' + tableName + '.csv'),
        seen = {},
        unrecognized = [],
        totalCount = 0,
        currentCount = 0,
        batch = [];
    parser.on('readable', function () {
        var record;
        while (record = parser.read()) {
            var name;
            totalCount++;
            record = transformRecord(record);
            name = record.committee_name;
            if (!seen[name]) {
                seen[name] = true;
                console.log(name);
                if (!currentCommittees[name]) {
                    unrecognized.push(name);
                }
            }
            if (!currentCommittees[name]) {
                continue;
            }
            batch.push(record);
            if (batch.length >= batchSize) {
                batchInsert(batch);
                batch = [];
            }
            currentCount++;
        }
    });
    parser.on('error', function (err) {
        console.error(err.message);
        throw err;
    });
    parser.on('finish', function () {
        if (batch.length) {
            batchInsert(batch);
        }
        console.log('Finished reading %d contributions', totalCount);
        console.log('Inserted %d contributions', currentCount);
        console.log('Unrecognized committees:\n', unrecognized.sort());
    });
    input.pipe(parser);
}

function transformRecord(record) {
    var newRecord = {};
    _.each(record, function (value, key) {
        value = trim(value);
        key = nameToCode(key);
        if (/date/.test(key) && /^\d\d?\/\d\d?\/\d{4}$/.test(value)) {
            value = moment(value, 'MM/DD/YYYY').format('YYYY-MM-DD');
        }
        else if (/amount|total/.test(key) && /^[\$\.,\d]+$/.test(value)) {
            value = value.replace(/[\$,]/g, '');
        }
        newRecord[key] = value;
    });
    return newRecord;
}

function nameToCode(s) {
    return s.toLowerCase()
        .replace(/\W+/g, '_')
        .replace(/^_|_$/g, '');
}

function trim(s) {
    if (s == null) {
        return null;
    }
    return s.replace(/\s+/g, ' ')
        .replace(/^ | $/g, '');
}

function batchInsert(batch) {
    knex.batchInsert(tableName, batch, batchSize)
        .returning('id')
        .then(function () {});
}

@@ -386,25 +386,76 @@
 e = 10,%0A    
-t
+committeeTableName = 'committees',%0A    contributionT
 ableName = '
@@ -505,46 +505,704 @@
 sts(
-tableName)%0A    .createTable(%0A        t
+contributionTableName)%0A    .dropTableIfExists(committeeTableName)%0A    .createTable(%0A        committeeTableName,%0A        function (table) %7B%0A            var columnNames = %5B%0A                'committee_name',%0A                'candidate_name',%0A                'election_year',%0A                'status',%0A                'office'%0A            %5D;%0A            table.increments();%0A            columnNames.forEach(function (columnName) %7B%0A                if (/year/.test(columnName)) %7B%0A                    table.integer(columnName);%0A                %7D%0A                else %7B%0A                    table.string(columnName);%0A                %7D%0A            %7D);%0A        %7D%0A    )%0A    .createTable(%0A        contributionT
 able
@@ -2244,16 +2244,20 @@
 ame + '/
+' + 
 committe
@@ -2261,15 +2261,49 @@
 ttee
-s.csv')
+TableName + '.csv'),%0A        records = %5B%5D
 ;%0A  
@@ -2499,32 +2499,66 @@
 e_name%5D = true;%0A
+            records.push(record);%0A
         %7D%0A    %7D)
@@ -2820,16 +2820,120 @@
         
+knex.batchInsert(committeeTableName, records, batchSize)%0A            .returning('id')%0A            .then(
 readCont
@@ -2941,17 +2941,16 @@
 ibutions
-(
 );%0A    %7D
@@ -3100,17 +3100,29 @@
 + '/' + 
-t
+contributionT
 ableName
@@ -5265,17 +5265,29 @@
 hInsert(
-t
+contributionT
 ableName

create committees table