import os
import pandas
import psycopg2


def connect():
    if os.getenv('NEW_OJO_HOST') == '':
        print("No configuration for the OJO database was found. Please create one now using `ojo_auth()`.")
        return
    else:
        conn = psycopg2.connect(
            host = os.getenv('NEW_OJO_HOST'),
            database = "ojodb",
            user = os.getenv('NEW_OJO_DEFAULT_USER'),
            password = os.getenv('NEW_OJO_DEFAULT_PASS'),
            port = os.getenv('NEW_OJO_PORT'),
            sslmode = os.getenv('NEW_OJO_SSL_MODE'),
            sslrootcert = os.getenv('NEW_OJO_SSL_ROOT_CERT'),
            sslcert = os.getenv('NEW_OJO_SSL_CERT'),
            sslkey = os.getenv('NEW_OJO_SSL_KEY')
        )
        return conn

# A function to get the list of plaintiffs; Takes a parameter n which is the number of plaintiffs to return;
# If n is None, all plaintiffs are returned
def plaintiffs(n=None):
    conn = connect()
    with conn:
        if n is None:
            sql = """select distinct(filed_by) from eviction_addresses.case c left join public.issue i on c.id = i.case_id;"""
        else:
            sql = """select distinct(filed_by) from eviction_addresses.case c left join public.issue i on c.id = i.case_id limit {};""".format(n)
        
        data = pandas.read_sql_query(sql, conn)
    conn.close()
    return data

data = plaintiffs().dropna()
data.to_csv('data/plaintiffs.csv', index=False, header=True)

def minutes(n=None):
    conn = connect()
    with conn:
        if n is None:
            sql = """select distinct(description) from eviction_addresses.case c left join public.minute m on c.id = m.case_id;"""
        else:
            sql = """select distinct(description) from eviction_addresses.case c left join public.minute m on c.id = m.case_id limit {};""".format(n)
        
        data = pandas.read_sql_query(sql, conn)
    conn.close()
    return data

data = minutes().dropna()
data.to_csv('data/minutes.csv', index=False, header=True)