U - 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}|r0||ks||kr0td|d||rX||ksD||krXtd|d||rp|rp||krptddS)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_tsr|t j |}|d?|d| f|d@|d|f|dtt |ddA|d;dB|dBdCi|dD}dE}nt jtt |ddFdA|t|dt| t||d;d<dG}|d@|d|f|dtt |ddA|d;dB|dBdCidH}dI}|t|| ||d+dJd-fWW5QRWWStjj k r}z|!t|}tdK|dL|ks dM|ks dN|ks dO|krPtdPd|dQdRfWYW5QRWW@SdS|krtdTdddRfWYNW5QRWWStdU|dddRfWYW5QRWWSW5d}~XYnFtjj"k r8}zH|!tdVt|tdWdddRfWYW5QRWWdSd}~XYnt#k r}zN|!tdXt|tdYdt|dQdRfWYW5QRWWSd}~XYnpt$k r}zP|!tdZt|td[t|dddRfWYW5QRWWSd}~XYnXW5QRXWnht$k r}zH|rB|!td\t|td]t|dddRfWYWSd}~XYnXW5|r|XdS)^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.pendingrazorpay_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.daysmN)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_jsonrrclosercursorexecutefetchonerazorpay_client subscriptioncancelfloatrnowrget lastrowidstrftimecommitintrFmaxminrprintstrcreateRAZORPAY_KEY_IDorderr-errorsBadRequestErrorrollback ServerErrorr Exception)datarequired_fieldsfield connectionrKZexisting_subscriptionpackageis_free start_dateend_dateZ sub_statusr1invoice_statusZ gateway_typeZinvoice_numberr8Zcurrent_timestampZ min_timestampZ max_timestamprrr:Zsubscription_dataZrazorpay_subscriptionr2rZrazorpay_ordereZ error_msgrrrcreate_subscriptions &                    (   2,, ::rnc Cst}tjd}zz>t|jdd"}d}|g}|rN|d7}|||d7}||t || }i}d}|D]} | d} |s| d | d | d d }| |kr| d| d | d| d| d| d| dt | d| dgd || <| drz|| d| d| d| ddqzt t ||dddfW5QRWWjSQRXWnZtk r} z:tdt| t dt| ddd fWYWSd} ~ XYnXW5|r|XdS)!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)rrargsrSrJrrKappendrLtuplefetchallrQrlistvaluesrcrZr[) rrgZ status_filterrKZsqlparamsrecords subscriptionsZ user_inforowZsub_idrmrrrget_user_subscriptionsEsf        :rc Cst}zʐzZt|jdd>}|d|f|}|sbtddddfW5QRWWtS|d}|d}|rztj |t d |Wnlt j j k r}zt d t|W5d }~XYn6tk r}zt d t|W5d }~XYnX|d tt|f|dt|f|tddddfW5QRWWxSQRXWnhtk r}zH|r|t dt|tdt|dddfWYWSd }~XYnXW5|r|Xd S)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!rrz!Cancelled Razorpay subscription: zRazorpay cancellation warning: NzRazorpay 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)rrJrrKrLrMrrNrOrPrZr-r_r`r[rcrrRrVra)r1rgrKrOZrazorpay_sub_idZprevious_statusrmrrrcancel_subscriptionsX "$  :rc Cst}zz@t|jdd$}|d||f|}|sdtddddfW5QRWWJS|d|f|}|D]>}|d|d f|}||d <|d r~t|d |d <q~|d |d |d |d|d|d| d|d|drt|dndt|d|d|d }t|dddfW5QRWWjSQRXWnZt k r} z:t dt | tdt | dddfWYWSd} ~ XYnXW5|r|XdS)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!a  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_maxNr"r) r1r7r#rrjrkrsrrr"rrtFrdrrwz%Error fetching subscription details: rH) rrJrrKrLrMrr|rQrSrcrZr[) rr1rgrKrOrtinvoicepayment response_datarmrrrget_user_subscription_detailssZ &  :rc Cstjd}tjjddtd}tjjddtd}t}z`zt|jdd}d }g}|rn|d 7}||d |d }| ||| d }|d7}| ||d|g| ||| } | D]>} | d| df| } | | d<| drt | d| d<qt| |||dddfW5QRWWjSQRXWnZtk r} z:tdt| tdt| dddfWYWSd} ~ XYnXW5|r|XdS)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_pagerrwrxrrHN)rryrSrWrrJrrKrzrLrMextendr|rQrrcrZr[) rrrrgrKZ base_queryr count_queryrrsubrrmrrrget_all_subscriptions,sP        :rc Cst}ddg}|D](}||krtd|dddfSqt}zzBt|jdd&}|d|df|}|std ddd fW5QRWWXSz*t j |d}|d d krt }d }|ddkr|tdd}n|ddkr |tdd}|d|||t |df|dt |df|d|dt t||df|tdd dddfWW5QRWWfStd|d |d dddfWW5QRWW.SWntjjk r*} z6tdt| dddfWYW5QRWWSd } ~ XYnVtk r~} z6tdt| dddfWYW5QRWWSd } ~ XYnXW5QRXWnhtk r} zH|r|tdt| td t| dddfWYWSd } ~ XYnXW5|r|Xd S)!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!rZcapturedNr#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: )rrIrrrJrrKrLrMrNrfetchrrRrr[rVr-r_r`rcrarZ) rdrerfrgrKrOrrjrkrmrrrverify_paymentss&     * ::rc Cs t}zzt|jddv}|d|f|}|D]4}|drRt|d|d<|dr6t|d|d<q6t|dddfW5QRWWhSQRXWnXtk r}z:t d t |td t |dd d fWYWSd }~XYnXW5|r|Xd S) 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?rFrrwzError fetching invoices: rrHN) rrJrrKrLr|rQrrcrZr[)r1rgrKrtrrmrrrget_subscription_invoicess.  :r)NN)r-rrflaskrr contextlibrconfrrr config from_objectr]ClientRAZORPAY_KEY_SECRETrNrrnrrrrrrrrrrs$    *ZBKGg