import stripe
import json
import mysql.connector
from datetime import datetime

stripe.api_key = ""


def cents_to_dollars(cents):
    if cents is not None:
        return cents / 100


def unix_to_readable(ts):
    if ts is not None:
        return datetime.utcfromtimestamp(ts).strftime('%m/%d/%Y')


# Get a list of successful payouts from Stripe
payouts = stripe.Payout.list(limit=20)
payout_count = 0
for p in payouts:
    payout_count += 1
    charge_count = 0
    payout_id = p['id']
    payout_amount = cents_to_dollars(p['amount'])
    expected_by = unix_to_readable(p['arrival_date'])
    # print("({})".format(payout_count))
    print('Date: {}\nPayout amount: {:.2f}'.format(
        expected_by, payout_amount))
    print('Transactions in payout:')
    charges = stripe.BalanceTransaction.list(
        limit=30, payout=payout_id)
    for charge in charges:
        if charge['amount'] > 0:
            charge_id = charge['source']
            charge_count += 1
            amount_billed = cents_to_dollars(charge['amount'])
            fee_meta = charge['fee_details']
            for fm in fee_meta:
                fee = cents_to_dollars(fm['amount'])
            # Reconstruct client data based on charge id in database
            # Prabaly should use JOINs
            cnx = mysql.connector.connect(
                user='',
                password='',
                host='localhost',
                port=3306,
                database='')
            cursor = cnx.cursor()
            client_id_query = (
                "SELECT client_id FROM invoices WHERE charge_id = %s")
            cursor.execute(client_id_query, (charge_id,))
            invoice_data = cursor.fetchone()
            client_id = invoice_data[0]
            # note = invoice_data[1]
            client_address_query = (
                "SELECT country_code, administrative_area, "
                "locality, postal_code, thoroughfare, premise "
                "FROM client_addresses WHERE client_id = %s")
            client_query = (
                "SELECT first_name, last_name, email "
                "FROM clients WHERE id = %s")

            # Get client name, email
            cursor.execute(client_query, (client_id))
            print(client_id)
            client = cursor.fetchone()
            first_name = client[0]
            last_name = client[1]
            email = client[2]
            if not email:
                email = ''
            if not first_name:
                first_name = ''
            if not last_name:
                last_name = ''
            # Get client address
            cursor.execute(client_address_query, (client_id))
            client_address = cursor.fetchone()
            country_code = client_address[0]
            administrative_area = client_address[1]
            locality = client_address[2]
            postal_code = client_address[3]
            premise = client_address[5]
            if not premise:
                premise = ''
            thoroughfare = client_address[4] + premise

            # print(" "*4 + "({})".format(charge_count))
            print(" "*4 + "Amount billed: {:.2f}".format(amount_billed))
            print(" "*4 + "Fee: {:.2f}".format(fee))
            print(" "*4 + "Type of Service: ")
            print(" "*4 + "Name: {} {}".format(first_name, last_name))
            print(" "*4 + "Address:")
            print(" "*4 + "{} {}".format(thoroughfare, premise))
            print(" "*4 + "{} {}".format(locality, administrative_area))
            print(" "*4 + "{}".format(postal_code))
            print("\n")
    # Clear database connection
    close_cursor = cursor.close()