from internal.helpers import rds_query, get_user_id from management.helpers import send_subscription_activated_email, send_subscription_renewed_email from management.subscriptions import stripe_customer_id, stripe_customer, stripe_invoice, stripe_subscription, \ stripe_schedule, stripe_update_subscription from product_packages import RIGHT_CHECKS import datetime from dateutil.relativedelta import relativedelta import logging log = logging.getLogger().getChild("management.packages") def get_package_by_id(package_id, billing_cycle): for package in rds_query(f"SELECT id, package_name \"name\", description, prices, campaign_name, reset_period, resets_to_package FROM commons.packages " f"WHERE id = %(package_id)s", {'package_id': package_id}): for price in package['prices']: if price['billingCycle'] == billing_cycle: return package, price raise RuntimeError('Invalid package') def get_package_price(product_name, billing_cycle): for package in rds_query(f"SELECT id, package_name \"name\", description, prices, campaign_name, reset_period, resets_to_package FROM commons.packages " f"WHERE package_name = %(product_name)s", {'product_name': product_name}): for price in package['prices']: if price['billingCycle'] == billing_cycle: return package, price raise RuntimeError('Invalid product') def get_package_price_campaign(product_name, billing_cycle, campaign): for package in rds_query(f"SELECT id, package_name \"name\", description, prices, campaign_name, reset_period, resets_to_package FROM commons.packages " f"WHERE package_name LIKE %(product_name)s AND campaign_name = %(campaign)s", {'product_name': f"{product_name}%", 'campaign': campaign}): for price in package['prices']: if price['billingCycle'] == billing_cycle: return package, price raise RuntimeError('Invalid product') def find_price_by_id(price_id): for package in rds_query(f"SELECT id, package_name \"name\", description, prices, campaign_name, reset_period, resets_to_package FROM commons.packages "): if 'prices' in package and package['prices']: for price in package['prices']: if 'stripe_price_id' in price and price['stripe_price_id'] == price_id: return package, price raise RuntimeError('Invalid product') def get_product_packages(endpoint=None, req=None, event=None): """ type ProductPrice { price: Float billingCycle: String } type ProductPackage { name: String description: String prices: [ProductPrice] } """ management_schema = req['management_schema'] for company in rds_query(f"SELECT unused_campaign_code from {management_schema}.company"): campaign_code = company['unused_campaign_code'] break else: campaign_code = None packages = [] if campaign_code: dbpackages = rds_query(f"SELECT cp.id, ccp.package_name \"name\", cp.description, cp.prices, " f"ccp.description \"campaignDescription\", ccp.campaign_name \"campaignCode\", ccp.prices \"campaign_prices\" FROM commons.packages cp " f"JOIN commons.packages ccp ON cp.package_name = ccp.resets_to_package " f"WHERE ccp.campaign_name = %(camp_code)s " f"ORDER BY id", {'camp_code': campaign_code}) else: dbpackages = rds_query(f"SELECT id, package_name \"name\", description, prices FROM commons.packages " f"WHERE public = TRUE " f"ORDER BY id") for package in dbpackages: quotas = [] for quota in rds_query(f"SELECT r.id, r.name, r.description, pr.quota \"limit\" FROM commons.rights r " f"JOIN commons.package_rights pr ON pr.right_id = r.id " f"WHERE pr.package_id = %(current_package_id)s AND r.public = true", {'current_package_id': package['id']}): if quota['name'] in RIGHT_CHECKS: if 'current' in RIGHT_CHECKS[quota['name']]: val = RIGHT_CHECKS[quota['name']]['current'](management_schema) # quota['current'] = f"{val}" quota['current'] = val # if quota['limit'] == -1: # quota['limit'] = 'unlimited' # else: # quota['limit'] = f"{quota['limit']}" quota['id'] = f"{management_schema}-{package['name']}-{quota['name']}" quotas.append(quota) if 'campaign_prices' in package: for price in package['prices']: for cprice in package['campaign_prices']: if price['billingCycle'] == cprice['billingCycle']: price['campaignPrice'] = cprice['price'] break package['quotas'] = quotas package['id'] = package['name'] packages.append(package) return packages def get_current_package(endpoint, req, event): """ type UserPackageQuoata { id: ID name: String limit: String current: String } type UserPackage { id: ID name: String trialEnd: AWSDateTime price: Float billingCycle: String nextPayment: AWSDateTime quotas: [UserPackageQuoata] } :param endpoint: :param req: :param event: :return: """ user_id = get_user_id(event) management_schema = req['management_schema'] customer_id = stripe_customer_id(user_id, management_schema) customer = stripe_customer(customer_id) cancel_at_period_end = True trial = False subscription_valid_until_date = None for s in customer['subscriptions']['data']: sub = s cancel_at_period_end = sub['cancel_at_period_end'] try: invoice = stripe_invoice(sub['latest_invoice']) payment_status = invoice['payment_intent']['status'] except Exception as e: payment_status = None try: payment_error_message = invoice['payment_intent']['last_payment_error']['message'] if invoice['payment_intent'][ 'last_payment_error'] else None except Exception as e: payment_error_message = None break else: sub = None payment_status = None payment_error_message = None # doing insta sync check if sub and sub['status']: price_id = sub['items']['data'][0]['price']['id'] package, price = find_price_by_id(price_id) if sub['status'] == 'active': subscription_valid_until_date = datetime.date.fromtimestamp(sub['current_period_end']) else: subscription_valid_until_date = datetime.date.fromtimestamp(sub['current_period_start']) else: for cpackage in rds_query(f"SELECT current_package_id, subscription_valid_until_date, trial, billing_cycle, " f"unused_campaign_code FROM {management_schema}.company"): trial = cpackage['trial'] subscription_valid_until_date = cpackage['subscription_valid_until_date'] try: package, price = get_package_by_id(cpackage['current_package_id'], cpackage['billing_cycle']) except Exception as e: log.exception('INVALID PACKAGE', exc_info=e) if cpackage['unused_campaign_code']: package, price = get_package_price_campaign('Lite', cpackage['billing_cycle'] if cpackage['billing_cycle'] else 'monthly', cpackage['unused_campaign_code']) else: package, price = get_package_price('Lite', cpackage['billing_cycle'] if cpackage['billing_cycle'] else 'monthly') break else: raise RuntimeError('No package info') if package['resets_to_package']: qpackage, _ = get_package_price(package['resets_to_package'], price['billingCycle']) else: qpackage = package quotas = [] for quota in rds_query(f"SELECT r.id, r.name, r.description, pr.quota \"limit\" FROM commons.rights r " f"JOIN commons.package_rights pr ON pr.right_id = r.id " f"WHERE pr.package_id = %(current_package_id)s AND r.public = true", {'current_package_id': qpackage['id']}): if quota['name'] in RIGHT_CHECKS: if 'current' in RIGHT_CHECKS[quota['name']]: val = RIGHT_CHECKS[quota['name']]['current'](management_schema) quota['current'] = val quota['id'] = f"{management_schema}-{quota['name']}" quotas.append(quota) active = { 'id': f"{management_schema}-{package['id']}", 'name': package['name'], 'description': package['description'], 'status': 'trial' if trial else sub['status'] if sub else 'inactive', 'paymentStatus': payment_status, 'paymentErrorMessage': payment_error_message, 'price': price['price'], 'billingCycle': price['billingCycle'] if price else 'unknown', 'cancelAtPeriodEnd': cancel_at_period_end, 'nextPayment': str(subscription_valid_until_date) if subscription_valid_until_date else None, 'quotas': quotas } currents = [active] if customer['subscriptions']['data']: sub = stripe_subscription(customer['subscriptions']['data'][0]['id']) if 'schedule' in sub and sub['schedule']: schedule = stripe_schedule(sub['schedule']) for phase in schedule['phases']: if phase['start_date'] >= sub['current_period_end']: package, stripe_price = find_price_by_id(phase['plans'][0]['price']) currents.append({ 'id': f"{management_schema}-{package['id']}-{len(currents)}", 'name': package['name'], 'description': package['description'], 'status': 'scheduled', 'paymentStatus': None, 'paymentErrorMessage': None, 'price': stripe_price['price'], 'billingCycle': stripe_price['billingCycle'], 'cancelAtPeriodEnd': cancel_at_period_end, 'nextPayment': str(datetime.date.fromtimestamp(phase['start_date'])), 'quotas': [] }) if 'pending_update' in sub and sub['pending_update']: package, stripe_price = find_price_by_id(sub['pending_update']['subscription_items'][0]['plan']['id']) currents.append({ 'id': f"{management_schema}-{package['id']}-{len(currents)}", 'name': package['name'], 'description': package['description'], 'status': 'pending update', 'paymentStatus': sub['latest_invoice']['payment_intent']['status'], 'paymentErrorMessage': f"{sub['latest_invoice']['payment_intent']['last_payment_error']['message']}" if sub['latest_invoice']['payment_intent']['last_payment_error'] else None , # None, 'price': stripe_price['price'], 'billingCycle': stripe_price['billingCycle'], 'cancelAtPeriodEnd': cancel_at_period_end, 'nextPayment': str(datetime.date.fromtimestamp(sub['latest_invoice']['created'])), 'quotas': [] }) return currents def sync_subscription(sub, send_email=False): """ Supposed to make sure the package and subscriptions are in sync in database. :param send_email: :param sub: :return: """ try: if sub and sub['status'] == 'active': price_id = sub['items']['data'][0]['price']['id'] customer_id = sub['customer'] for user in rds_query(f"SELECT default_schema, email FROM commons.user_company uc " f"JOIN commons.stripe_customer sc ON sc.company_id = uc.default_schema " f"WHERE sc.stripe_id = %(stripe_customer_id)s", {'stripe_customer_id': customer_id}): management_schema = user['default_schema'] package, price = find_price_by_id(price_id) if package['resets_to_package']: o_package = package package, price = get_package_price(package['resets_to_package'], price['billingCycle']) if not sub['schedule']: reset_period = o_package['reset_period'] start_date = datetime.datetime.fromtimestamp(sub['current_period_start']) + relativedelta( **reset_period[price['billingCycle']]) log.info(f"ADDING SCHEDULE: package: {reset_period} start: {start_date} price: {price}") sub = stripe_update_subscription(sub, price, start_date) if package and price: rds_query(f"UPDATE {management_schema}.company " f"SET current_package_id = %(package_id)s, " f"subscription_valid_until_date = %(sub_end_date)s, " f"billing_cycle = %(billing_cycle)s, " f"trial = false", {'package_id': package['id'], 'sub_end_date': str(datetime.date.fromtimestamp(sub['current_period_end'])), 'billing_cycle': price['billingCycle']}) if send_email: sub_created = datetime.datetime.fromtimestamp(sub['created']) sub_current_period_start = datetime.datetime.fromtimestamp(sub['current_period_start']) sub_current_period_end = datetime.datetime.fromtimestamp(sub['current_period_end']) if sub_created == sub_current_period_start: send_subscription_activated_email(user['email'], package['name'], price['billingCycle'], sub_current_period_start.date(), sub_current_period_end.date()) else: send_subscription_renewed_email(user['email'], package['name'], price['billingCycle'], sub_current_period_start.date(), sub_current_period_end.date()) return sub else: raise RuntimeError('Did not find package ') else: raise RuntimeError('Did not find stripe customer ') except Exception as e: log.exception("SYNC ERROR", exc_info=e) return sub