import json from collections.abc import Iterable from typing import Dict from internal.queries import rds_query import logging from internal.commons import DP_URL, USER_POOL_NAME from internal.helpers import generate_schema_id, make_iso_date_now, get_user_id from internal.queries import http_query from management.commons import MANAGEMENT_ROLES, MANAGEMENT_ROLE_SET, PENDING_MEMBER, PENDING_GUEST log = logging.getLogger().getChild("management.helpers") def get_default_workspace(event: Dict) -> str: try: user_sub = get_user_id(event) # log.info(f"get_default_workspace: looking for '{user_sub}'") return rds_query(f"SELECT default_schema FROM commons.user_company " f"WHERE user_id = %(user_sub)s " f"LIMIT 1", {'user_sub': user_sub})[0]['default_schema'] except Exception as e: log.error("Could not get default workspace", exc_info=e) raise RuntimeError('Could not identify user.') def get_raw_alliances(event: Dict): default_workspace = get_default_workspace(event) return rds_query(f"SELECT id, num, management_company_id FROM {default_workspace}.alliance a " f"JOIN commons.company_alliance ca ON ca.alliance_id = a.id") def get_id_and_management_schema_for_alliance(default_schema, alliance_id): try: for alliance in rds_query(f"SELECT alliance_id, management_company_id FROM commons.company_alliance " f"WHERE alliance_id = (SELECT id FROM {default_schema}.alliance " f"WHERE id = %(alliance_id)s) " f"LIMIT 1", {'alliance_id': alliance_id}): return alliance['alliance_id'], alliance['management_company_id'] else: log.error("Could not find alliance id in management schema") raise RuntimeError('Access denied.') except Exception as e: log.error("Could not get id and management schema for alliance", exc_info=e) raise RuntimeError('Access denied.') def get_roles(user_id, caw_schema, management_schema): try: for line in rds_query(f"SELECT roles FROM {management_schema}.user_roles " f"WHERE id = %(caw_schema)s AND user_id = %(user_id)s", {'caw_schema': caw_schema, 'user_id': user_id}): return line['roles'] else: return [] except Exception as e: log.exception("ROLE NOT FOUND", exc_info=e) return [] def has_some_role(user_id, caw_schema, management_schema, *role_names): if MANAGEMENT_ROLE_SET.issuperset(role_names): return bool(set(get_roles(user_id, caw_schema, management_schema)).intersection(role_names)) else: return False def add_role(user_id, caw_schema, management_schema, role_name, schema_type): if role_name in MANAGEMENT_ROLES: roles = get_roles(user_id, caw_schema, management_schema) if role_name not in roles: roles.append(role_name) set_roles(user_id, caw_schema, management_schema, roles, schema_type) else: RuntimeError('Invalid role') def set_roles(user_id, caw_schema, management_schema, role_names, schema_type): """role_names - list of role names. Empty list removes previous roles record.""" if MANAGEMENT_ROLE_SET.issuperset(role_names): if role_names: rds_query(f"INSERT INTO {management_schema}.user_roles (id,company_alliance_workspace, user_id, roles) " f"VALUES (%(caw_schema)s, %(schema_type)s, %(user_id)s, %(roles)s) " f"ON CONFLICT (id, user_id) DO UPDATE SET roles = EXCLUDED.roles;", {'user_id': user_id, 'caw_schema': caw_schema, 'schema_type': schema_type, 'roles': json.dumps(role_names)}) else: rds_query(f"DELETE FROM {management_schema}.user_roles " f"WHERE id = %(caw_schema)s AND company_alliance_workspace = %(schema_type)s AND " f"user_id = %(user_id)s;", {'user_id': user_id, 'caw_schema': caw_schema, 'schema_type': schema_type}) else: raise RuntimeError('Invalid role') def add_alliance_management(management_schema, alliance_schema, name, description): rds_query(f"INSERT INTO commons.company_alliance (alliance_id, management_company_id) " f"VALUES (%(alliance_schema)s, %(management_schema)s )", {'alliance_schema': alliance_schema, 'management_schema': management_schema}) update_caw_meta(alliance_schema, {'name': name, 'description': description}) res_num = add_alliance_for_invitee(management_schema, alliance_schema) return res_num def add_alliance_for_invitee(invi_default_schema, alliance_schema): for a_rec in rds_query(f"INSERT INTO {invi_default_schema}.alliance (id) " f"VALUES (%(alliance_schema)s) " f"ON CONFLICT (id) DO UPDATE SET id = EXCLUDED.id " f"RETURNING num;", {'alliance_schema': alliance_schema, }): return a_rec['num'] else: raise RuntimeError('Failed to register new alliance for invitee.') def remove_alliance_for_invitee(invi_default_schema, alliance_schema): rds_query(f"DELETE FROM {invi_default_schema}.alliance " f"WHERE id = %(alliance_schema)s;", {'alliance_schema': alliance_schema, }) return None def user_and_company_for_email(email): try: for u_a_c in rds_query(f"SELECT user_id, default_schema FROM commons.user_company " f"WHERE email = %(email)s", {'email': email}): return u_a_c['user_id'], u_a_c['default_schema'] else: log.error(f"USER NOT FOUND: {email}") except Exception as e: log.exception("USER NOT FOUND", exc_info=e) return '', '' def email_and_company_for_user(user_id): try: u_a_c = rds_query(f"SELECT email, default_schema FROM commons.user_company " f"WHERE user_id = %(user_id)s", {'user_id': user_id}) # log.info(f"email_and_company_for_user: looking for '{user_id}': {u_a_c}") return u_a_c[0]['email'], u_a_c[0]['default_schema'] except Exception as e: log.exception("USER NOT FOUND", exc_info=e) return None, None def update_caw_meta(caw_schema, req): sql_params = {} for p in ['name', 'description', 'updated']: if p in req and req[p] is not None: sql_params[p] = req[p] if sql_params: if 'updated' not in sql_params: sql_params['updated'] = make_iso_date_now() rds_query(f"""INSERT INTO {caw_schema}.meta_data (key, val) VALUES """ f"""{','.join([f"('{q}', %({q})s)" for q in sql_params])} """ f"""ON CONFLICT (key) DO UPDATE SET val = EXCLUDED.val;""", sql_params) def update_alliance_updated(alliance_schema): update_caw_meta(alliance_schema, {'updated': make_iso_date_now()}) def add_invitation(user_id, invi_email, role, invi_user_id=None, into_alliance=None, into_company=None, message=None): rds_query( f"INSERT INTO commons.user_invitation (email, user_id, into_company, into_alliance, invited_by, message, role) " f"VALUES (%(invi_email)s,%(invi_user_id)s,%(into_company)s,%(into_alliance)s,%(user_id)s,%(message)s, %(role)s) ", {'invi_email': invi_email, 'invi_user_id': invi_user_id, 'into_company': into_company, 'into_alliance': into_alliance, 'user_id': user_id, 'message': message, 'role': role }) def remove_invitation(email, into_alliance=None, into_company=None): if into_company or into_alliance: cond = {'email': email} if into_company: cond['into_company'] = into_company if into_alliance: cond['into_alliance'] = into_alliance rds_query(f"DELETE FROM commons.user_invitation WHERE {' and '.join(f'{k} = %({k})s' for k in cond)}", cond) else: raise RuntimeError("Remove invitation to alliance or company?") def gen_pass(event): return generate_schema_id(event)[-8:] def cognito_create_user(new_email, tmp_passwd, invi_default_schema): req = { 'new_schema': invi_default_schema, 'email': new_email, 'userPoolName': USER_POOL_NAME, 'tmpPasswd': tmp_passwd, 'caw': 'company' # 'userId': new_user_id } endpoint = {"url": DP_URL + "create_new_user", "method": "POST", "query": http_query } res = http_query(endpoint, req, {'endpoint': endpoint}) return res['user_id'] def create_new_user(new_email, temp_passwd): """ returns invi_user_id, invi_default_schema """ invi_default_schema = generate_schema_id() new_user_id = cognito_create_user(new_email, temp_passwd, invi_default_schema) req = {'Item': {'UID': new_user_id, 'IID': invi_default_schema, 'email': new_email, 'createdDateTime': make_iso_date_now() } } return new_user_id, invi_default_schema def get_alliance_member(endpoint, req, event): if 'memberId' in req: current_user_id = get_user_id(event) if 'member_roles' in req: roles = req['member_roles'] if roles is None: roles = [] else: if 'alliance_schema' in req: roles = get_roles(req['memberId'], req['alliance_schema'], req['alliance_management_schema']) else: roles = [] if 'user_name' not in req or 'company_name' not in req or 'member_email' not in req: for member in rds_query('SELECT user_name, company_name, email FROM commons.user_company ' 'WHERE user_id = %(user_id)s', {'user_id': req['memberId']}): user_name = member['user_name'] company_name = member['company_name'] member_email = member['email'] break else: user_name = req['user_name'] if 'user_name' in req else 'unknown' company_name = req['company_name'] if 'company_name' in req else f"{user_name} company" member_email = req['member_email'] if 'member_email' in req else 'unknown' else: user_name = req['user_name'] company_name = req['company_name'] member_email = req['member_email'] alliance_owner_id = req['alliance_owner_id'] if 'alliance_owner_id' in req else \ get_caw_owner_id(req['alliance_schema'], req['alliance_management_schema']) \ if 'alliance_schema' in req and 'management_schema' in req else None schema = req['alliance_schema'] if 'alliance_schema' in req else req['workspace_schema'] return { '__typename': 'MemberRole', 'email': member_email if alliance_owner_id == req['memberId'] or alliance_owner_id == current_user_id else '', 'id': f"{schema}-{req['memberId'][:13]}", 'role': ', '.join(roles), 'name': user_name, 'company': company_name, 'me': req['memberId'] == current_user_id, 'pending': PENDING_MEMBER in roles or PENDING_GUEST in roles if isinstance(roles, Iterable) else False } else: return { '__typename': 'MemberRole', 'id': 'NotFound', 'role': '', 'name': 'Not Found', 'me': False } def get_caw_owner_id(caw_schema, management_schema): for meta_line in rds_query(f"SELECT key,val FROM {caw_schema}.meta_data " f"WHERE key IN ('creator')"): return meta_line['val'] # TODO: query actual user rights, in case ownership has been forwarded. raise RuntimeError('Alliance owner not found') def add_event_log(schema: str, collection_id: int, user_id: str, log_type: str, description: str): rds_query(f"""INSERT INTO {schema}.event_log (collection_id, user_id, type, description) VALUES (%(collection_id)s, %(user_id)s, %(log_type)s,%(description)s);""", {'collection_id': collection_id, 'user_id': user_id, 'log_type': log_type, 'description': description}) def add_workspace_access(management_schema, workspace_schema): for a_rec in rds_query(f"INSERT INTO {management_schema}.workspace (id) " f"VALUES (%(workspace_schema)s) " f"ON CONFLICT (id) DO UPDATE SET id = EXCLUDED.id " f"RETURNING num;", {'workspace_schema': workspace_schema, }): return a_rec['num'] else: raise RuntimeError('Failed to register new workspace.') def add_workspace_management(management_schema, workspace_schema, name, description): rds_query(f"INSERT INTO commons.company_alliance (alliance_id, management_company_id) " f"VALUES (%(workspace_schema)s, %(management_schema)s )", {'workspace_schema': workspace_schema, 'management_schema': management_schema}) update_caw_meta(workspace_schema, {'name': name, 'description': description}) res_num = add_workspace_access(management_schema, workspace_schema) return res_num def get_raw_workspaces(event: Dict): default_workspace = get_default_workspace(event) # TODO: rights management inside the company return rds_query(f"SELECT id, num FROM {default_workspace}.workspace ORDER BY num") STRIPE_VAT_TYPE = {'AT':'eu_vat', 'BE': 'eu_vat', 'BG': 'eu_vat', 'HR': 'eu_vat', 'CY': 'eu_vat', 'CZ': 'eu_vat', 'DK': 'eu_vat', 'EE': 'eu_vat', 'FI': 'eu_vat', 'FR': 'eu_vat', 'DE': 'eu_vat', 'EL': 'eu_vat', 'HU': 'eu_vat', 'ID': 'id_npwp', 'IE': 'eu_vat', 'IT': 'eu_vat', 'LV': 'eu_vat', 'LT': 'eu_vat', 'LU': 'eu_vat', 'MT': 'eu_vat', 'NL': 'eu_vat', 'NO': 'no_vat', 'PL': 'eu_vat', 'PT': 'eu_vat', 'RO': 'eu_vat', 'SA': 'sa_vat', 'SK': 'eu_vat', 'SI': 'eu_vat', 'ES': 'eu_vat', 'SE': 'eu_vat', 'CH': 'ch_vat', 'TW': 'tw_vat', 'TH': 'th_vat', 'US': 'us_ein'} def tax_type_for_country(country): if country in STRIPE_VAT_TYPE: return STRIPE_VAT_TYPE[country] return None def send_subscription_email(template_type, recipient, package_name, billing_cycle=None, active_date=None, next_payment_date=None): endpoint = { 'url': DP_URL + "async_notify" } email_req = { 'async_source': 'appsync.sendSubscriptionEmail', 'template_type': template_type, 'recipient': recipient, 'package_name': package_name, 'billing_cycle': billing_cycle, 'active_date': {'day': f'{active_date.day}', 'month': active_date.strftime("%B"), 'year': f'{active_date.year}' } if active_date else None, 'next_payment_date': {'day': f'{next_payment_date.day}', 'month': next_payment_date.strftime("%B"), 'year': f'{next_payment_date.year}' } if next_payment_date else None } return http_query(endpoint, email_req, None) def send_subscription_activated_email(recipient, package_name, billing_cycle, active_date, next_payment_date): return send_subscription_email('subscription_activated', recipient, package_name, billing_cycle, active_date, next_payment_date) def send_subscription_canceled_email(recipient, package_name, next_payment_date): return send_subscription_email('subscription_canceled', recipient, package_name, None, None, next_payment_date) def send_subscription_renewed_email(recipient, package_name, billing_cycle, active_date, next_payment_date): return send_subscription_email('subscription_renewed', recipient, package_name, billing_cycle, active_date, next_payment_date)