o i|@sddlZddlmZmZddlmZmZddlmZddlm Z m Z ddl m Z e j e e jZeje je jfdZddd Zd d Zd d ZddZddZddZddZddZdS)N)datetime timedelta)requestjsonify)closing)connect_to_databaseConfig)app)authcCstd}d}|r||ks||krtd|d||r,||ks"||kr,td|d||r8|r8||kr8tddS)z$Validate timestamps for Razorpay APICm8V 8zstart_at must be between z and zexpire_by must be between z expire_by must be after start_atT) ValueError)start_at expire_byZmin_tsZmax_tsrX/Users/harjaapdhillon/Desktop/newidea/medvista_backend/Services/Billing/Subscriptions.pyvalidate_razorpay_timestamps src CsFt}ddg}|D]}||vrtd|dddfSq t}zzt|jdd}|d|df|}|r|d d kr^td dddfWd WW|r]|SS|d d kr|drwz t j |dWnY|d|df|d|df|d|df|}|stddddfWd WW|r|SSt |ddk}|ddvr|s|dstddddfWd WW|r|SSt }|tdd|tddd d|d} |rd nd } |d|d|d|| | |d| f|j} |rd nd!} |rd"nd#} d$|d%d&| }|d'| |||td(d|d|d|d)| | f |j}|rl|td*| d d+d,d-fWd WW|rk|SSz tt }d.}d/}t|d0|}|ddvr|dd1kr|d2}d3}n|d4}d5}t||}t||td6|td7|d8td9||d|d:||t|dt| t||d;d<d=}td>|t j |}|d?|d| f|d@|d|f|dtt |ddA|d;dB|dBdCi|dD}dE}nEt jtt |ddFdA|t|dt| t||d;d<dG}|d@|d|f|dtt |ddA|d;dB|dBdCidH}dI}|t|| ||d+dJd-fWWd WW|rx|SStjj y}z|!t|}tdK|dL|vsdM|vsdN|vsdO|vrtdPd|dQdRfWYd }~Wd WW|r|SSdS|vrtdTdddRfWYd }~Wd WW|r|SStdU|dddRfWYd }~Wd WW|r|SSd }~wtjj"yN}z-|!tdVt|tdWdddRfWYd }~Wd WW|rI|SSd }~wt#y}z0|!tdXt|tdYdt|dQdRfWYd }~Wd WW|r|SSd }~wt$y}z2|!tdZt|td[t|dddRfWYd }~Wd WW|r|SSd }~ww1swYWn:t$y }z-|r|!td\t|td]t|dddRfWYd }~W|r|SSd }~wwW|r|d Sd S|r"|ww)^zS Create a new subscription with proper validation and Razorpay integration user_id package_idMissing required field: Tmessageerror dictionaryz SELECT id, package_id, status, razorpay_subscription_id FROM subscriptions WHERE user_id = %s AND status IN ('active', 'pending') ORDER BY id DESC LIMIT 1 statusactivez^You already have an active subscription. Please cancel it before subscribing to a new package.Npendingrazorpay_subscription_idz UPDATE subscriptions SET status = 'canceled' WHERE id = %s idz UPDATE invoices SET status = 'canceled' WHERE subscription_id = %s AND status IN ('draft', 'pending', 'unpaid') z SELECT id, billing_cycle, razorpay_plan_id, price, currency_id, package_name, maximum_bookings, price_after_max FROM packages WHERE id = %s AND is_active = TRUE zActive package not foundpriceg billing_cycle)monthlyannualrazorpay_plan_idzWThis package is not properly configured for recurring payments. Please contact support.daysm)r$r%lifetimez INSERT INTO subscriptions ( user_id, package_id, start_date, end_date, status, billing_cycle, next_billing_date ) VALUES (%s, %s, %s, %s, %s, %s, %s) ZpaidZdraftfreerazorpayzINV-z %Y%m%d%H%M%S-a  INSERT INTO invoices ( subscription_id, invoice_number, issue_date, due_date, amount, total_amount, currency_id, status, gateway_type ) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)  currency_idz(Free subscription activated successfullyF)rsubscription_id payment_datarr r i,r$i$.izCurrent timestamp: zStart at timestamp: z (current + 5 min)zExpire by timestamp:  package_name)rr1 invoice_idr7)plan_id total_countZcustomer_notifyrrnotesz*Creating Razorpay subscription with data: z UPDATE subscriptions SET razorpay_subscription_id = %s WHERE id = %s z UPDATE invoices SET gateway_order_id = %s WHERE id = %s ZINRemail)r razorpay_keyamountcurrencynameprefillr:z&Subscription created, awaiting paymentd)r?r@Zreceiptr;)razorpay_order_idr>r?r@rArBz(One-time order created, awaiting payment)rr1r8r2rzRazorpay BadRequestError: end_time timestamprrz@Subscription timing configuration error. Please contact support.)rrdetailsr9z4Package configuration error. Please contact support.zPayment gateway error: zRazorpay ServerError: z=Payment gateway is temporarily unavailable. Please try again.zTimestamp validation error: z2Subscription timing error. Please contact support.zPayment creation error: zPayment creation failed: zSubscription creation error: zError creating subscription: )%rget_jsonrrrcursorexecutefetchonecloserazorpay_client subscriptioncancelfloatrnowrget lastrowidstrftimecommitintrFmaxminrprintstrcreateRAZORPAY_KEY_IDorderr-errorsBadRequestErrorrollback ServerErrorr Exception)datarequired_fieldsfield connectionrJZexisting_subscriptionpackageis_free start_dateend_dateZ sub_statusr1invoice_statusZ gateway_typeZinvoice_numberr8Zcurrent_timestampZ min_timestampZ max_timestamprrr:Zsubscription_dataZrazorpay_subscriptionr2rZrazorpay_ordereZ error_msgrrrcreate_subscriptions                            (        (   rnc Cst}tjd}zzt|jdd}d}|g}|r$|d7}|||d7}||t|| }i}d}|D]R} | d} |sN| d | d | d d }| |vru| d| d | d| d| d| d| dt | d| dgd || <| dr|| d| d| d| ddq:t t | |dddfWdWW|r|SS1swYWn1ty} z%tdt| t dt| ddd fWYd} ~ W|r|SSd} ~ wwW|r|dSdS|r|ww)!z4Get all subscriptions for a user with their invoicesrTrab SELECT s.id AS subscription_id, s.razorpay_subscription_id, s.status AS subscription_status, s.start_date, s.end_date, s.next_billing_date, p.package_name, p.price, p.billing_cycle, i.id AS invoice_id, i.status AS invoice_status, i.created_at AS invoice_date, u.email, u.firstname, u.lastname FROM subscriptions s JOIN packages p ON s.package_id = p.id LEFT JOIN invoices i ON s.id = i.subscription_id JOIN users u ON s.user_id = u.id WHERE s.user_id = %s  AND s.status = %sz. ORDER BY s.start_date DESC, i.created_at DESCNr1r< firstnamelastname)r<rprqrsubscription_statusrjrknext_billing_dater7r"r#) r1rrrrjrkrsr7r"r#invoicesr8rtrl invoice_date)r8rlruF)rduserrError fetching subscriptions: rrH)rrargsrSrrJappendrKtuplefetchallrQrlistvaluesrMrcrZr[) rrgZ status_filterrJZsqlparamsrecords subscriptionsZ user_inforowZsub_idrmrrrget_user_subscriptionsEs      P L( P  rc Cs t}zzt|jdd}|d|f|}|s5tddddfWdWW|r4|SS|d}|d }|rztj |t d |Wn7t j j yk}zt d t|WYd}~nd}~wty}zt d t|WYd}~nd}~ww|d tt|f|dt|f|tddddfWdWW|r|SS1swYWn7ty}z+|r|t dt|tdt|dddfWYd}~W|r|SSd}~wwW|r|dSdS|r|ww)z3Cancel a subscription in both Razorpay and databaseTrz SELECT razorpay_subscription_id, status, user_id FROM subscriptions WHERE id = %s AND status IN ('active', 'pending') z2No active or pending subscription found to cancel.rr!Nrrz!Cancelled Razorpay subscription: zRazorpay cancellation warning: zRazorpay cancellation error: z UPDATE subscriptions SET status = 'canceled', end_date = %s, updated_at = %s WHERE id = %s z UPDATE invoices SET status = 'canceled', updated_at = %s WHERE subscription_id = %s AND status IN ('draft', 'unpaid', 'pending') z#Subscription canceled successfully.FrwzError canceling subscription: rH)rrrJrKrLrrMrNrOrPrZr-r_r`r[rcrrRrVra)r1rgrJrOZrazorpay_sub_idZprevious_statusrmrrrcancel_subscriptions9   9 3( 9  rc Cst}zzt|jdd}|d||f|}|s5tddddfWdWW|r4|SS|d|f|}|D]}|d |d f|}||d <|d rat|d |d <qB|d |d |d|d|d|d| d|d|drt|dndt|d|d|d }t|dddfWdWW|r|SS1swYWn1t y} z%t dt | tdt | dddfWYd} ~ W|r|SSd} ~ wwW|r|dSdS|r|ww)zBGet detailed subscription information including usage and invoicesTra6 SELECT s.*, p.package_name, p.maximum_bookings, p.price_after_max, p.price, p.currency_id FROM subscriptions s JOIN packages p ON s.package_id = p.id WHERE s.id = %s AND s.user_id = %s Subscription not foundrr!Na  SELECT id, invoice_number, total_amount, status, due_date, issue_date, gateway_order_id, gateway_type FROM invoices WHERE subscription_id = %s ORDER BY issue_date DESC a SELECT id, amount, payment_method, transaction_id, payment_date, status FROM payments WHERE invoice_id = %s ORDER BY payment_date DESC LIMIT 1 r Zlatest_payment total_amountr7r#rrjrkrsmaximum_bookingsprice_after_maxr"r) r1r7r#rrjrkrsrrr"rrtFrdrrwz%Error fetching subscription details: rH) rrrJrKrLrrMr|rQrSrcrZr[) rr1rgrJrOrtinvoicepayment response_datarmrrrget_user_subscription_detailss B  B >( B  rc Cstjd}tjjddtd}tjjddtd}t}zzt|jddy}d }g}|r5|d 7}||d |d }|||| d }|d7}| ||d|g|||| } | D]} |d| df| } | | d<| drt | d| d<qbt | |||dddfWdWW|r|SS1swYWn1ty} z%tdt| t dt| dddfWYd} ~ W|r|SSd} ~ wwW|r|dSdS|r|ww)z&Get all subscriptions (admin endpoint)rpager6)defaulttypelimit Tra SELECT s.id, s.user_id, s.package_id, s.status, s.start_date, s.end_date, s.billing_cycle, s.razorpay_subscription_id, s.created_at, p.package_name, p.price, u.email, u.firstname, u.lastname FROM subscriptions s JOIN packages p ON s.package_id = p.id JOIN users u ON s.user_id = u.id WHERE 1=1 rozSELECT COUNT(*) as total FROM (z ) AS subquerytotalz. ORDER BY s.start_date DESC LIMIT %s OFFSET %sz SELECT id, invoice_number, total_amount, status, issue_date FROM invoices WHERE subscription_id = %s ORDER BY issue_date DESC LIMIT 1 r latest_invoicer"F)rdrrper_pagerrwNrxrrH)rryrSrWrrrJrzrKrLextendr|rQrrMrcrZr[) rrrrgrJZ base_queryr count_queryrrsubrrmrrrget_all_subscriptions,sn       : 6( :  rc Csjt}ddg}|D]}||vrtd|dddfSq t}zz@t|jdd,}|d|df|}|sXtd ddd fWd WW|rW|SSzt j |d}|d d krt }d }|ddkr{|tdd}n |ddkr|tdd}|d|||t |df|dt |df|d|dt t||df|tdd dddfWWd WW|r|SStd|d |d dddfWWd WW|r|SStjjy)} z%tdt| dddfWYd } ~ Wd WW|r$|SSd } ~ wtyZ} z%tdt| dddfWYd } ~ Wd WW|rU|SSd } ~ ww1s_wYWn:ty} z-|ru|tdt| td t| dddfWYd } ~ W|r|SSd } ~ wwW|r|d Sd S|r|ww)!z1Verify Razorpay payment and activate subscription payment_idr1rTrrrz SELECT s.*, p.package_name, p.billing_cycle FROM subscriptions s JOIN packages p ON s.package_id = p.id WHERE s.id = %s rr!NrZcapturedr#r$r'r(r%r*z UPDATE subscriptions SET status = 'active', start_date = %s, end_date = %s, next_billing_date = %s, updated_at = %s WHERE id = %s z UPDATE invoices SET status = 'paid', updated_at = %s WHERE subscription_id = %s AND status IN ('draft', 'pending', 'unpaid') a INSERT INTO payments ( invoice_id, amount, payment_method, transaction_id, payment_date, status, gateway_response ) SELECT i.id, i.total_amount, 'razorpay', %s, %s, 'completed', %s FROM invoices i WHERE i.subscription_id = %s AND i.status = 'paid' ORDER BY i.id DESC LIMIT 1 z8Payment verified and subscription activated successfullyF)rZpayment_statusrrwzPayment not captured. Status: zInvalid payment ID: zPayment verification failed: rHzPayment verification error: zError verifying payment: )rrIrrrrJrKrLrMrNrfetchrrRrr[rVr-r_r`rcrarZ) rdrerfrgrJrOrrjrkrmrrrverify_paymentssW      W  W  W  W Q( W  rc Cs:t}zzTt|jddB}|d|f|}|D]}|dr)t|d|d<|dr5t|d|d<qt|dddfWd WW|rN|SS1sRwYWn1ty}z%t d t |td t |dd d fWYd }~W|r|SSd }~wwW|r|d Sd S|r|ww) z,Get all invoices for a specific subscriptionTraU SELECT i.*, p.payment_method, p.transaction_id, p.payment_date, p.status as payment_status FROM invoices i LEFT JOIN payments p ON i.id = p.invoice_id WHERE i.subscription_id = %s ORDER BY i.issue_date DESC r?rFrrwNzError fetching invoices: rrH) rrrJrKr|rQrrMrcrZr[)r1rgrJrtrrmrrrget_subscription_invoicessL  (   r)NN)r-rrflaskrr contextlibrconfrrr config from_objectr]ClientRAZORPAY_KEY_SECRETrNrrnrrrrrrrrrrs&    *ZBKG g