import os
import json
import boto3
import psycopg2
from psycopg2.extras import RealDictCursor
import logging

log = logging.getLogger()
log.setLevel(logging.INFO)
REGION = os.environ["AWS_REGION"] if "AWS_REGION" in os.environ else "eu-west-1"
S3_BUCKET_DEVEL = "frontend-api-devel-filestore"


def get_secret(secret_name):
    # Create a Secrets Manager client
    session = boto3.session.Session()
    client = session.client(
        service_name='secretsmanager',
        region_name=REGION
    )
    get_secret_value_response = json.loads(client.get_secret_value(SecretId=secret_name)['SecretString'])
    return get_secret_value_response


DB_CONN = {'fansifter-rds': None,
           'fansifter-rds-test': None,
           'fansifter-rds-live': None,
           'sandbox': None}

DB_PORT = {'fansifter-rds': 5432,
           'fansifter-rds-test': 5433,
           'fansifter-rds-live': 5434,
           'sandbox': None}


def get_rds_connection(conn_name):
    global DB_CONN
    if DB_CONN[conn_name] is None:
        try:
            secret = get_secret(conn_name)
            DB_CONN[conn_name] = psycopg2.connect(host=secret['host'], #'localhost',
                                                    port=DB_PORT[conn_name],
                                                    database=secret['dbname'],
                                                    user=secret['username'],
                                                    password=secret['password'],
                                                    cursor_factory=RealDictCursor)
        except Exception as e:
            log.exception(e)
    return DB_CONN[conn_name]


def rds_query(connection_name, query, vars=None, fetch=False):
    with get_rds_connection(connection_name) as conn:
        with conn.cursor() as cur:
            cur.execute(query, vars=vars)
            if fetch:
                return cur.fetchall()


databases = [
    'fansifter-rds',
    'fansifter-rds-test',
    'fansifter-rds-live',
]

other_buckets = set()
for connection in databases:
    all_chemas = rds_query(connection,
                           "select table_schema, table_name from information_schema.tables where table_name LIKE 'temp_gender%%'", fetch=True)
    print(f"Found {len(all_chemas)} temp tables in database {connection}")
    for schema, table in [sch.values() for sch in all_chemas]:
        print(f'DELETING: {schema}.{table}')
        statements = []
        vars = {}
        try:
            statements.extend([
                f"""DROP TABLE {schema}.{table};"""
            ])
        except Exception as e:
            print(f'Oops, crapshoot: {schema}.{table}')
            # statements.extend([
            #     f"""ALTER TABLE {schema}.company ADD billing_cycle varchar(100) default 'monthly';""",
            #     #f"""ALTER TABLE {schema}.company ADD stripe_customer_id varchar(100);""",
            # ])

        for statement in statements:
            try:
                rds_query(connection, statement, vars)
            except Exception as e:
                log.exception("oops", exc_info=e)