o 1dj1 @sddlZddlZddlmZmZddlmZddlZddlmZe e Z dde de de fd d Z e d e d e ed ee de de ede de de ede de de ede dddddZGdddZeZdS)N)datetime timedelta)Any) DB_CONFIGnamefallbackreturncCsttj|d}|p|S)Nr)strosenvirongetstrip)rrvaluer6/var/www/back/telegram_server/official_mail_service.py_env srOFFICIAL_MAIL_DB_HOSTAPP_MASTER_DB_HOSThostOFFICIAL_MAIL_DB_PORTAPP_MASTER_DB_PORTportOFFICIAL_MAIL_DB_USERAPP_MASTER_DB_USERuserOFFICIAL_MAIL_DB_PASSWORDAPP_MASTER_DB_PASSWORDpasswordOFFICIAL_MAIL_DB_NAME official_mailutf8mb4T)rrrrdbcharset autocommitc @s^eZdZdddZedefddZdddZdd d Zd e d e de e e e ffd dZdS)OfficialMailEventServicer NcCsd|_d|_dSNF)_pool_billing_history_replayedselfrrr__init__,s z!OfficialMailEventService.__init__cCs |jduS)N)r'r)rrris_ready0s z!OfficialMailEventService.is_readyc s|jrdStjdddddtIdH|_|j4IdH1}|4IdH}|dIdHWdIdHn 1IdHsAwYWdIdHn 1IdHsVwYtdtddS) N)minsizemaxsizeconnect_timeoutz6SELECT 1 FROM domain_setup_assistance_requests LIMIT 1zOfficial Mail DB connected: %sr"r) r'aiomysql create_poolOFFICIAL_MAIL_DB_CONFIGacquirecursorexecuteloggerinfo)r* connectionr7rrr initialize4s* *(z#OfficialMailEventService.initializecs6|jr|j|jIdHd|_d|_dSdSr&)r'close wait_closedr(r)rrrr=Is  zOfficialMailEventService.closestart_atend_atc s|jsgS|jst|tddn|}|j4IdH}|tj4IdHo}|d|fIdHt | IdH}|d|fIdHt | IdH}|d|fIdHt | IdH}|d|fIdHt | IdH} |d|fIdHt | IdH} WdIdHn 1IdHswYWdIdHn 1IdHswYd|_|D]} d | d <d | d <d | dd| d<q|D]} d| d <d| d <d| dd| d| d<q|D]} d| d <d| d <d| dd| d| d<q| D]} d| d <d| d <d| d<d| dd| d| d<q| D]} d | d <d| d <d!| d<d"| d#d$| d<q1t g|||| | fd%d&d'S)(N)daysa" SELECT r.id AS event_id, 'official-mail' AS app_key, r.domain AS title, r.provider AS detail, u.email AS actor, r.fee_amount AS amount, 'KRW' AS currency, 'domain-assistance' AS platform, r.status, r.notification_email, r.requested_at AS event_time FROM domain_setup_assistance_requests r INNER JOIN users u ON u.id = r.user_id WHERE r.requested_at > %s AND r.requested_at <= %s ORDER BY r.requested_at ASC, r.id ASC u SELECT p.id AS event_id, 'official-mail' AS app_key, '성장 플랜' AS title, p.decline_message AS detail, u.email AS actor, p.amount / 100.0 AS amount, UPPER(p.currency) AS currency, 'polar' AS platform, p.status, p.payment_method, p.payment_trigger, p.card_brand, p.card_last_four, p.decline_reason, p.polar_payment_id, p.polar_checkout_id, p.payment_created_at AS occurred_at, p.status_changed_at AS event_time FROM mailbox_polar_payments p INNER JOIN users u ON u.id = p.owner_user_id WHERE p.status_changed_at > %s AND p.status_changed_at <= %s AND p.status IN ('succeeded', 'failed') ORDER BY p.status_changed_at ASC, p.id ASC a SELECT c.id AS event_id, 'official-mail' AS app_key, c.product_desc AS title, c.error_message AS detail, u.email AS actor, c.amount AS amount, 'KRW' AS currency, 'toss-pay' AS platform, c.status, c.pay_method AS payment_method, c.charge_kind AS payment_trigger, COALESCE(c.card_company_name, p.card_company_name) AS card_brand, COALESCE(c.card_num4_print, p.card_num4_print) AS card_last_four, c.error_code AS decline_reason, c.order_no AS payment_id, c.transaction_id, c.requested_at AS occurred_at, c.updated_at AS event_time, CASE WHEN c.status = 'success' AND c.subscription_id IS NOT NULL AND c.id = ( SELECT MIN(first_charge.id) FROM mailbox_toss_pay_billing_charges first_charge WHERE first_charge.subscription_id = c.subscription_id AND first_charge.status = 'success' ) THEN 1 ELSE 0 END AS is_initial_subscription_charge FROM mailbox_toss_pay_billing_charges c INNER JOIN users u ON u.id = c.owner_user_id INNER JOIN mailbox_toss_pay_billing_profiles p ON p.id = c.billing_profile_id WHERE c.updated_at > %s AND c.updated_at <= %s AND c.status IN ('success', 'failed') ORDER BY c.updated_at ASC, c.id ASC u( SELECT p.id AS event_id, 'official-mail' AS app_key, '결제수단' AS title, u.email AS actor, 'toss-pay' AS platform, p.status, p.pay_method AS payment_method, p.card_company_name AS card_brand, p.card_num4_print AS card_last_four, p.account_bank_name, SHA2(p.billing_key, 256) AS registration_key, p.activated_at AS event_time FROM mailbox_toss_pay_billing_profiles p INNER JOIN users u ON u.id = p.owner_user_id WHERE p.activated_at > %s AND p.activated_at <= %s AND p.status = 'active' AND p.last_action = 'ACTIVATED' AND p.billing_key IS NOT NULL ORDER BY p.activated_at ASC, p.id ASC u SELECT s.id AS event_id, 'official-mail' AS app_key, '성장 플랜' AS title, u.email AS actor, s.amount / 100.0 AS amount, UPPER(s.currency) AS currency, 'polar' AS platform, s.status, 'card' AS payment_method, latest_payment.card_brand, latest_payment.card_last_four, s.polar_subscription_id, s.checkout_id, COALESCE(s.last_event_at, s.updated_at) AS event_time FROM mailbox_polar_subscriptions s INNER JOIN users u ON u.id = s.owner_user_id LEFT JOIN mailbox_polar_payments latest_payment ON latest_payment.id = ( SELECT payment.id FROM mailbox_polar_payments payment WHERE payment.owner_user_id = s.owner_user_id AND payment.status = 'succeeded' AND ( payment.polar_checkout_id = s.checkout_id OR s.checkout_id IS NULL ) ORDER BY payment.payment_created_at DESC, payment.id DESC LIMIT 1 ) WHERE COALESCE(s.last_event_at, s.updated_at) > %s AND COALESCE(s.last_event_at, s.updated_at) <= %s AND s.status = 'active' ORDER BY event_time ASC, s.id ASC T domain_setup_assistance_requestssourcedomain_assistance event_typez!domain_setup_assistance_requests:event_idz :requested event_keymailbox_polar_payments polar_paymentzmailbox_polar_payments:polar_payment_id:status mailbox_toss_pay_billing_charges toss_paymentz!mailbox_toss_pay_billing_charges:!mailbox_toss_pay_billing_profilespayment_method_registereddomesticregionz"mailbox_toss_pay_billing_profiles:registration_keymailbox_polar_subscriptionsoverseaszmailbox_polar_subscriptions:polar_subscription_idz :activatedcs|dp|dfS)N event_timerH)r )rowr@rrBsz7OfficialMailEventService.fetch_events..)key) r'r(minrr6r7r3 DictCursorr8listfetchallr sorted) r*r?r@billing_start_atr;r7 domain_rowspolar_payment_rowstoss_payment_rowstoss_method_rowspolar_method_rowsrYrrZr fetch_eventsPs  + . $ '*(0   z%OfficialMailEventService.fetch_events)r N)__name__ __module__ __qualname__r+propertyboolr,r<r=rr_dictr rrhrrrrr%+s   r%)r)loggingr rrtypingrr3 persistencer getLoggerrir9r rintr5r%official_mail_servicerrrrsD