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': None,
           'sandbox': None}


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=True):
    with get_rds_connection(connection_name) as conn:
        with conn.cursor() as cur:
            cur.execute(query, vars=vars)
            if fetch:
                return cur.fetchall()


bucket_name = S3_BUCKET_DEVEL
ignore_schemas = ['c9a36f2bafdef7670a976949255c06dbb9b805380b34990353924fea9',
                  'ce2267b1912ae089c276e9e02d0fafd69450eface4e1e7eba8e448117',
                  'c43e12e843c97d73ce49d2d0632c31c032874407a4375255b26fe7638',
                  'ce3a3c25769899dc04411a543802bcdf04254250de8b0965320ea2829',
                  'c664d3dfd18c69e7fac0425d7e9da738b3ca7d783aadbc792c09691c8',
                  'cb613adf7a887a9cde017ca0c1fa2d49a2170f690f3100c0e69d80774',
                  'caf5c7d751d062c3c21b7dfc88c7be6f375988092834ba0bbc03ab78f',
                  'c5b800e9d1cf2cd89f8b3a2d0512218bd3baaf30140114e82a3eadf91',
                  'c5a4243c944a47ca5864baaffedaf20ac045c6ccbaf1006941aa68261']

all_sources = []
connection = 'fansifter-rds-live'
other_buckets = set()
all_chemas = rds_query(connection,
                       "select table_schema, table_name from information_schema.tables where table_name ='fan'")
print(f"Found {len(all_chemas)} schemas in database {connection}")
total_fans = 0
companies = 0
for schema, table in [sch.values() for sch in all_chemas]:
    if schema.startswith('c') and schema not in ignore_schemas:
        fans = rds_query(connection, f"select count(*) from {schema}.{table}")
        print(schema, ": ", fans[0]['count'])
        total_fans += fans[0]['count']
        companies += 1
print(f"Found {total_fans} fans in {companies} companies")