Bakefile

init:
    # step 1
    wget "https://open.canada.ca/data/api/action/package_show?id=90fed587-1364-4f33-a9ee-208181dc0b97" --no-hsts -c -O out.json
    rows convert "https://www.canada.ca/en/immigration-refugees-citizenship/services/immigrate-canada/express-entry/eligibility/find-national-occupation-code.html" noc.csv

start:
    # step 2
    set -e
    declare -a FILES=`cat out.json  | jq -r ".result.resources[].url" | grep -ve "FR\|fr"` 
    for f in ${FILES}; do
        red " "
        red  " Download $f"
        wget -nc --no-hsts --quiet $f
    done
    for f  in *.xlsx; do
        xlsx2csv $f > "${f%.xlsx}.csv"
    done
    red " "
    red "Remove shit"
    sed -i.orig.orig 1d *.csv
    # sed -i.orig.orig 1d 2015_Positive_Employers_EN.csv
    rm -f 2015_positive_employers_en.csv*

fillempty:
    set -xe
    rm -f 2015_positive_employers_en.csv*
    rm -f positive_employers_en.csv
    red "Fill empty columns"


    for f in *[^processed].csv; do
        red "Proccessing $f"
        python fillempty.py $f
    done

concat:
    set -xe
    rm -rf all.processed.csv
    declare -a FILES=`find . -iname '*.processed.csv' | xargs`
    red "merging files"
    rows csv-merge ${FILES} pre-all.processed.csv
    rows query "select province_territory, employer, address, occupations,positions_approved,phase,zipcode, city,stream from table1" pre-all.processed.csv --output all.processed.csv
    rm pre-all.processed.csv
    red "obtain schema"
    rows schema all.processed.csv


convert:
    rm -rf *.db
    red "Finish step, generating db files"
    rows csv-to-sqlite all.processed.csv all.db 
    sqlite-utils enable-fts --fts4 all.db all employer occupations address  phase  province_territory  stream
    #rows csv-to-sqlite global_stream,sk.csv global_stream.db
    #rows csv-to-sqlite reduced_global_stream.csv reduced_global_stream.db
#    rows csv-to-sqlite reduced_global_stream.csv.loc.csv reduced_global_stream.csv.loc.db
#   rows csv-to-sqlite reduced_global_stream.csv.joburl.csv reduced_global_stream.csv.joburl.db

replacestr:
    red "Replace heads"
    sed -i.orig s/occupations_under_noc_2011/occupations/g *.processed.csv
    sed -i.orig s/approved_positions/positions_approved/g *.processed.csv
    sed -i.orig s/employer_name/employer/g *.processed.csv
    sed -i.origin s/newfoundland_and_labrador/employer/g *.processed.csv

    red "Replace head ocupations"
    sed -i.orig s/occupaitons_under_noc_2012/occupations/g *.processed.csv
    sed -i.orig s/occupaitons_under_noc_2011/occupations/g *.processed.csv
    sed -i.orig s/,occupation,/,occupations,/g *.processed.csv
    red "Replace head stream"
    sed -i.orig s/program_stream/stream/g *.processed.csv

globalstream:
    SQL="select * from table1 where stream like '%Global%'"
    rows query "${SQL}" all.processed.csv --output global_stream.processed.csv --samples 0

reduce:
    SQL=" select * from table1 where"
    SQL+=" ( lower(address) not like '%toronto%' and "
    SQL+=" lower(address) not like '%qu%bec%' and "
    SQL+=" lower(address) not like '%montr%al%' and "
    SQL+=" lower(address) not like '%vancouver%' ) and "
    SQL+=" ( lower(occupations) like '%comp%' or"
    SQL+=" lower(occupations) like '%soft%' )"
	echo ${SQL}
    rows query "${SQL}" global_stream.processed.csv --output reduced_global_stream.processed.csv
    rows query "select address,occupations from table1" reduced_global_stream.processed.csv

fil:
    rows query "select distinct(employer) as e from table1 order by e desc " reduced_global_stream.processed.csv  --output filtered.processed.csv
    sed -i.orig s/Inc.//g filtered.csv
    sed -i.orig s/inc.//g filtered.csv
    sed -i.orig s/\\.//g filtered.csv
    sed -i.orig s/Ltd//g filtered.csv
    sed -i.orig s/\\\"//g filtered.csv
    sed -i.orig s/Technology//g filtered.csv
    sed -i.orig s/Technologie//g filtered.csv

all: fillempty replacestr concat convert 

gts: all globalstream  reduce fil
complete: clean init start all

clean:
    set -xe
    rm -rf *.csv
    rm -rf *.json
    rm -rf *.orig
    rm -rf *.xlsx


upload: @interactive
    rsync --progress -v *.db *.yml python3.ninja:apps/canada/
deploy: upload @interactive
    ssh zodman@python3.ninja sudo supervisorctl restart canada

ee:
    set -e
    rm -f express_entry_draws.*
    rows convert https://www.canada.ca/en/immigration-refugees-citizenship/corporate/mandate/policies-operational-instructions-agreements/ministerial-instructions/express-entry-rounds.html  express_entry_draws.csv
    rows  csv-to-sqlite express_entry_draws.csv  express_entry_draws.db

atlantic:
    set -e
    rows convert https://www.gov.nl.ca/immigration/immigrating-to-newfoundland-and-labrador/atlantic-immigration-pilot-program/designated-employers/  \
        atlantic-n-and-l.processed.csv
    #rows pdf-to-text https://novascotiaimmigration.com/wp-content/uploads/Designated_AIP_employers.pdf novascotia.pre.csv
    tail -n 259 novascotia.pre.csv > novascotia.pre1.csv
    awk 'NF' novascotia.pre1.csv > novascotia.pre2.csv
    sed -i s/,//g novascotia.pre2.csv
    sed -i '1s/^/company_name\n/' novascotia.pre2.csv
    rows convert novascotia.pre2.csv novascotia.processed.csv
    rows schema novascotia.processed.csv
    rows csv-to-sqlite novascotia.processed.csv novascotia-designed-employers.db
    rows csv-to-sqlite atlantic-n-and-l.processed.csv atlantic-n-and-l-designed-employers.db
    #$ curl https://www.princeedwardisland.ca/en/information/office-of-immigration/atlantic-immigration-program-designated-employers