#!/bin/python
# -*- mode: python; -*-
# 
# Copyright:
#    2023-Present, Pierre Fortin, <pierre@NCDataTeam.org>
# License:
#    GPLv3 or any later version: https://www.gnu.org/licenses/gpl-3.0.en.html
# Created:
#    2023-09-23 Initial script
# Updated: 

# Call:
#    dbcopy ff <input_file> <table_name>

# Examples:
#    dbcopy ff /tmp/ncvoter53.txt test ncvoter53
#    Total processing time: 1.681s
#
#    dbcopy ff /tmp/ncvoter_Statewide.txt test ncvoter_Statewide
#

# Defaults
INPUT = '/tmp/ncvoter_Statewide.txt'
USER = 'postgres'
PW = ''
DB = 'test'
TABLE = 'table'

FIELDS = [  # [un]comment the appropriate lines
    #'rid int GENERATED ALWAYS AS IDENTITY PRIMARY KEY',
  
    'county_id int',
    'county_desc text',
    'voter_reg_num bigint',
    'ncid text PRIMARY KEY',
    #'ncid text',
    'last_name text',
    'first_name text',
    'middle_name text',
    'name_suffix_lbl text',
    'status_cd text',
    'voter_status_desc text',
    'reason_cd text',
    'voter_status_reason_desc text',
    'res_street_address text',
    'res_city_desc text',
    'state_cd text',
    'zip_code text',
    'mail_addr1 text',
    'mail_addr2 text',
    'mail_addr3 text',
    'mail_addr4 text',
    'mail_city text',
    'mail_state text',
    'mail_zipcode text',
    'full_phone_number text',
    'confidential_ind text',
    'registr_dt date',
    'race_code text',
    'ethnic_code text',
    'party_cd text',
    'gender_code text',
    'birth_year int',
    'age_at_year_end int',
    'birth_state text',
    'drivers_lic text',
    'precinct_abbrv text',
    'precinct_desc text',
    'municipality_abbrv text',
    'municipality_desc text',
    'ward_abbrv text',
    'ward_desc text',
    'cong_dist_abbrv text',
    'super_court_abbrv text',
    'judic_dist_abbrv text',
    'nc_senate_abbrv text',
    'nc_house_abbrv text',
    'county_commiss_abbrv text',
    'county_commiss_desc text',
    'township_abbrv text',
    'township_desc text',
    'school_dist_abbrv text',
    'school_dist_desc text',
    'fire_dist_abbrv text',
    'fire_dist_desc text',
    'water_dist_abbrv text',
    'water_dist_desc text',
    'sewer_dist_abbrv text',
    'sewer_dist_desc text',
    'sanit_dist_abbrv text',
    'sanit_dist_desc text',
    'rescue_dist_abbrv text',
    'rescue_dist_desc text',
    'munic_dist_abbrv text',
    'munic_dist_desc text',
    'dist_1_abbrv text',
    'dist_1_desc text',
    'vtd_abbrv text',
    'vtd_desc text',
]
fields = [ x.split()[0] for x in FIELDS ]


import os
import sys
import psycopg2
import time

try:  INPUT = sys.argv[1]
except:  pass

try:  DB = sys.argv[2]
except:  pass

try:  TABLE = sys.argv[3]
except:  pass

try:  PW = sys.argv[4]
except:  pass

ANSIEraseLine = '\033[2K\033[1G'
COMMA = ','
NL = '\n'
COMMA_NL = f"{COMMA}{NL}"
TAB = '\t'

def pe( *s ):
    for each in s:
        print( each, file = sys.stderr )

def sql( s ):
    # if s.startswith( 'CREATE INDEX' ):  print( s )
    # else:  print( s, NL )
    cur.execute( s )
    
conn = psycopg2.connect(
    host = 'localhost',
    port = '5432',
    database = DB,
    user = USER,
    password = PW
)
cur = conn.cursor()

sql( f"DROP TABLE IF EXISTS {TABLE};" )
sql( f"CREATE TABLE IF NOT EXISTS {TABLE} ({NL}{COMMA_NL.join(FIELDS)}{NL});" )
sql( f"COPY {TABLE} ({NL}{COMMA_NL.join(fields)}{NL}) FROM '{INPUT}' WITH (FORMAT CSV, HEADER, DELIMITER '\t', ENCODING 'ISO-8859-1');" )

for index in fields:
    sql( f"CREATE INDEX IF NOT EXISTS idx_{TABLE}_{index} on {TABLE} ({index});" )

sql( 'COMMIT;' )

sql( f"GRANT SELECT, UPDATE, INSERT, DELETE, REFERENCES, TRIGGER ON {table} TO postgres;" )
#sql( f"GRANT USAGE, SELECT, UPDATE ON {table}_rid_seq TO postgres;" )