#!/bin/bash
PROGNAME=$(basename $0)
REGEXP='000$'
DO_TABLES=true
DO_ACCOUNTS=true
DO_HOLDINGS=true
DO_PRICES=true
DO_BALANCES=true

if $DO_TABLES; then
    echo "CREATE TABLE accounts (timestamp INTEGER, id CHAR(128), type CHAR(32), currency CHAR(32), quoted_currency CHAR(32), status CHAR(16), PRIMARY KEY (timestamp, id));"
    echo "CREATE TABLE balances (timestamp INTEGER, currency CHAR(32), balance REAL, PRIMARY KEY (timestamp));"
    echo "CREATE TABLE holdings (timestamp INTEGER, id CHAR(128), quantity REAL, PRIMARY KEY (timestamp, id));"
    echo "CREATE TABLE prices (timestamp INTEGER, source_currency CHAR(32), destination_currency CHAR(32), price REAL, PRIMARY KEY (timestamp, source_currency, destination_currency));"
    echo "CREATE VIEW latest_accounts_keys AS SELECT max(timestamp) AS timestamp, id FROM accounts GROUP BY id;"
    echo "CREATE VIEW latest_accounts AS SELECT accounts.timestamp AS timestamp, accounts.id AS id, accounts.type AS type, accounts.currency AS currency, accounts.quoted_currency AS quoted_currency, accounts.status AS status FROM latest_accounts_keys, accounts WHERE latest_accounts_keys.id == accounts.id AND latest_accounts_keys.timestamp == accounts.timestamp;"
    echo "CREATE VIEW latest_balances_keys AS SELECT max(timestamp) AS timestamp, currency FROM balances GROUP BY currency;"
    echo "CREATE VIEW latest_balances AS SELECT balances.timestamp AS timestamp, balances.currency AS currency, balances.balance AS balance FROM latest_balances_keys, balances WHERE latest_balances_keys.currency == balances.currency AND latest_balances_keys.timestamp == balances.timestamp;"
    echo "CREATE VIEW latest_holdings_keys AS SELECT max(timestamp) AS timestamp, id FROM holdings GROUP BY id;"
    echo "CREATE VIEW latest_holdings AS SELECT holdings.timestamp AS timestamp, holdings.id AS id, holdings.quantity AS quantity FROM latest_holdings_keys, holdings WHERE latest_holdings_keys.id == holdings.id AND latest_holdings_keys.timestamp == holdings.timestamp;"
    echo "CREATE VIEW latest_prices_keys AS SELECT max(timestamp) AS timestamp, source_currency, destination_currency FROM prices GROUP BY source_currency, destination_currency;"
    echo "CREATE VIEW latest_prices AS SELECT prices.timestamp AS timestamp, prices.source_currency AS source_currency, prices.destination_currency AS destination_currency, prices.price AS price FROM latest_prices_keys, prices WHERE latest_prices_keys.source_currency == prices.source_currency AND latest_prices_keys.destination_currency == prices.destination_currency AND latest_prices_keys.timestamp == prices.timestamp;"
fi

#Natwest current@Wed Apr 21 10:45:59 CEST 2004@CURRENCY@GBP@RUNTIMESPECIFIED
#Natwest credit@Wed Apr 21 10:45:59 CEST 2004@CURRENCY@GBP@RUNTIMESPECIFIED
#Natwest credit@Tue Apr 12 19:51:42 CEST 2005@closed@NOT_USED_FOR_CLOSED_ACCOUNTS@NOT_USED_FOR_CLOSED_ACCOUNTS
#INSERT INTO "accounts" VALUES(1208891251,'Natwest','CURRENCY','GBP','','OPEN');
#INSERT INTO "accounts" VALUES(1208891300,'NTL Incorporated','LISTED_STOCK','NASDAQ/NTLI','USD','OPEN');
#INSERT INTO "accounts" VALUES(1208891322,'Natwest','NULL','NULL',NULL,'CLOSED');

if $DO_ACCOUNTS; then
    echo "$PROGNAME: converting accounts ..." >&2
    I=0
    wc -l < ~/var/iofdb/accounts >&2
    grep -v '^#' ~/var/iofdb/accounts | while read LINE; do
        [[ $I =~ "$REGEXP" ]] && echo -e "$I ..." >&2
        [[ "$LINE" =~ '^([^@]*)@([^@]*)@([^@]*)@([^@]*)@([^@]*)$' ]]
        ID="${BASH_REMATCH[1]}"
        TIMESTAMP="${BASH_REMATCH[2]}"
        TYPE_STATUS="${BASH_REMATCH[3]}"
        CURRENCY="${BASH_REMATCH[4]}"
        QUOTED_CURRENCY="${BASH_REMATCH[5]}"
    
        ID="'$ID'"
        TIMESTAMP="$(date -d "$TIMESTAMP" '+%s')"   
        CURRENCY="'$CURRENCY'"
        QUOTED_CURRENCY="'$QUOTED_CURRENCY'"
        case "$TYPE_STATUS" in
            UNLISTED_STOCK) TYPE="'UNLISTED_STOCK'"
                      STATUS="'OPEN'" ;;
            LISTED_STOCK) TYPE="'LISTED_STOCK'"
                      STATUS="'OPEN'" ;;
            CURRENCY) TYPE="'CURRENCY'"
                      STATUS="'OPEN'" ;;
            closed)   TYPE=NULL
                      STATUS="'CLOSED'" 
                      CURRENCY=NULL
                      QUOTED_CURRENCY=NULL ;;
        esac
        echo "INSERT INTO \"accounts\" VALUES($TIMESTAMP,$ID,$TYPE,$CURRENCY,$QUOTED_CURRENCY,$STATUS);"
        I=$(($I + 1))
    done 
fi

#INSERT INTO "holdings" VALUES(1208891251,'Natwest',0.0);
#INSERT INTO "holdings" VALUES(1208891300,'NTL Incorporated',0.0);
# NTLI shares@Mon Apr 19 07:57:34 CEST 2004@12

if $DO_HOLDINGS; then
    echo "$PROGNAME: converting holdings ..." >&2
    I=0
    wc -l < ~/var/iofdb/holdings >&2
    grep -v '^#' ~/var/iofdb/holdings | while read LINE; do
        [[ $I =~ "$REGEXP" ]] && echo -e "$I ..." >&2
        [[ "$LINE" =~ '^([^@]*)@([^@]*)@([^@]*)$' ]]
        ID="${BASH_REMATCH[1]}"
        TIMESTAMP="${BASH_REMATCH[2]}"
        QUANTITY="${BASH_REMATCH[3]}"
    
        ID="'$ID'"
        TIMESTAMP="$(date -d "$TIMESTAMP" '+%s')"   
        echo "INSERT INTO \"holdings\" VALUES($TIMESTAMP,$ID,$QUANTITY);"
        I=$(($I + 1))
    done 
fi


#nasdaq/NTLI@USD@Wed Apr 21 10:37:00 CEST 2004@58.99
#nasdaq/NTLI@USD@Wed Apr 21 12:21:01 CEST 2004@58.99

if $DO_PRICES; then
    echo "$PROGNAME: converting prices ..." >&2
    I=0
    wc -l < ~/var/iofdb/prices >&2
    grep -v '^#' ~/var/iofdb/prices | while read LINE; do
        [[ $I =~ "$REGEXP" ]] && echo -e "$I ..." >&2
        [[ "$LINE" =~ '^([^@]*)@([^@]*)@([^@]*)@([^@]*)$' ]]
        SOURCE="${BASH_REMATCH[1]}"
        DESTINATION="${BASH_REMATCH[2]}"
        TIMESTAMP="${BASH_REMATCH[3]}"
        PRICE="${BASH_REMATCH[4]}"
   
        #  Occassionally the price is blank due to retrieval errors; skip such records.
        [ "X$PRICE" != X ] || continue
    
        SOURCE="'$SOURCE'"
        DESTINATION="'$DESTINATION'"
        TIMESTAMP="$(date -d "$TIMESTAMP" '+%s')"   
        echo "INSERT INTO \"prices\" VALUES($TIMESTAMP,$SOURCE,$DESTINATION,$PRICE);"
        I=$(($I + 1))
    done 
fi


#Sat Feb  3 18:15:07 CET 2007@EUR@263105.434390
#Sat Feb  3 20:46:01 CET 2007@EUR@282074.39282

if $DO_BALANCES; then
    echo "$PROGNAME: converting balances ..." >&2
    I=0
    wc -l < ~/var/iofdb/balances >&2
    grep -v '^#' ~/var/iofdb/balances | while read LINE; do
        [[ $I =~ "$REGEXP" ]] && echo -e "$I ..." >&2
        [[ "$LINE" =~ '^([^@]*)@([^@]*)@([^@]*)$' ]]
        TIMESTAMP="${BASH_REMATCH[1]}"
        CURRENCY="${BASH_REMATCH[2]}"
        BALANCE="${BASH_REMATCH[3]}"
    
        TIMESTAMP="$(date -d "$TIMESTAMP" '+%s')"   
        CURRENCY="'$CURRENCY'"
        echo "INSERT INTO \"balances\" VALUES($TIMESTAMP,$CURRENCY,$BALANCE);"
        I=$(($I + 1))
    done 
fi