import razorpay
from datetime import datetime, timedelta
from flask import request, jsonify
from contextlib import closing
from conf import connect_to_database, Config
from app import app

# Initialize Razorpay client
app.config.from_object(Config)
RAZORPAY_KEY_ID = Config.RAZORPAY_KEY_ID
razorpay_client = razorpay.Client(auth=(Config.RAZORPAY_KEY_ID, Config.RAZORPAY_KEY_SECRET))

def validate_razorpay_timestamps(start_at=None, expire_by=None):
    """Validate timestamps for Razorpay API"""
    min_ts = 946684800  # Jan 1, 2000
    max_ts = 4765046400  # Jan 1, 2120
    
    if start_at and (start_at < min_ts or start_at > max_ts):
        raise ValueError(f"start_at must be between {min_ts} and {max_ts}")
    
    if expire_by and (expire_by < min_ts or expire_by > max_ts):
        raise ValueError(f"expire_by must be between {min_ts} and {max_ts}")
    
    if start_at and expire_by and expire_by <= start_at:
        raise ValueError("expire_by must be after start_at")
    
    return True

def create_subscription():
    """
    Create a new subscription with proper validation and Razorpay integration
    """
    data = request.get_json()
    required_fields = ['user_id', 'package_id']

    for field in required_fields:
        if field not in data:
            return jsonify({'message': f'Missing required field: {field}', 'error': True}), 400

    connection = connect_to_database()
    
    try:
        with closing(connection.cursor(dictionary=True)) as cursor:
            # Check for existing active or pending subscriptions
            cursor.execute("""
                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
            """, (data['user_id'],))
            existing_subscription = cursor.fetchone()

            if existing_subscription:
                if existing_subscription['status'] == 'active':
                    return jsonify({
                        'message': 'You already have an active subscription. Please cancel it before subscribing to a new package.',
                        'error': True
                    }), 400
                elif existing_subscription['status'] == 'pending':
                    # Cancel the pending subscription before creating new one
                    if existing_subscription['razorpay_subscription_id']:
                        try:
                            razorpay_client.subscription.cancel(existing_subscription['razorpay_subscription_id'])
                        except:
                            pass  # Continue even if Razorpay cancel fails
                    
                    cursor.execute("""
                        UPDATE subscriptions SET status = 'canceled' 
                        WHERE id = %s
                    """, (existing_subscription['id'],))
                    
                    cursor.execute("""
                        UPDATE invoices SET status = 'canceled'
                        WHERE subscription_id = %s AND status IN ('draft', 'pending', 'unpaid')
                    """, (existing_subscription['id'],))

            # Fetch package details
            cursor.execute("""
                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
            """, (data['package_id'],))
            package = cursor.fetchone()

            if not package:
                return jsonify({'message': 'Active package not found', 'error': True}), 404

            is_free = float(package['price']) == 0.0

            # Validate Razorpay plan for recurring paid plans
            if package['billing_cycle'] in ['monthly', 'annual'] and not is_free and not package['razorpay_plan_id']:
                return jsonify({
                    'message': 'This package is not properly configured for recurring payments. Please contact support.',
                    'error': True
                }), 400

            # Create new subscription
            start_date = datetime.now()
            end_date = {
                'monthly': start_date + timedelta(days=30),
                'annual': start_date + timedelta(days=365),
                'lifetime': None
            }.get(package['billing_cycle'])

            sub_status = 'active' if is_free else 'pending'

            cursor.execute("""
                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)
            """, (
                data['user_id'], data['package_id'], start_date, end_date,
                sub_status, package['billing_cycle'], end_date
            ))
            subscription_id = cursor.lastrowid

            # Create invoice
            invoice_status = 'paid' if is_free else 'draft'
            gateway_type = 'free' if is_free else 'razorpay'
            invoice_number = f"INV-{start_date.strftime('%Y%m%d%H%M%S')}-{subscription_id}"

            cursor.execute("""
                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)
            """, (
                subscription_id, invoice_number, start_date,
                start_date + timedelta(days=7), package['price'],
                package['price'], package['currency_id'], invoice_status, gateway_type
            ))
            invoice_id = cursor.lastrowid

            # For free packages, commit and return
            if is_free:
                connection.commit()
                return jsonify({
                    'message': 'Free subscription activated successfully',
                    'subscription_id': subscription_id,
                    'payment_data': None,
                    'error': False
                }), 201

            # Handle Razorpay subscription or one-time order for paid packages
            try:
                # Calculate valid timestamps for Razorpay - use future timestamps
                current_timestamp = int(datetime.now().timestamp())
                min_timestamp = 946684800
                max_timestamp = 4765046400
                
                # For start_at, use current time + 5 minutes to ensure it's in the future
                start_at = max(current_timestamp + 300, min_timestamp)  # +5 minutes
                
                if package['billing_cycle'] in ['monthly', 'annual']:
                    # Calculate expire_by timestamp based on billing cycle
                    if package['billing_cycle'] == 'monthly':
                        # Start at current + 5 min, expire in 35 days from start
                        expire_by = start_at + (35 * 24 * 60 * 60)
                        total_count = 24  # 2 years maximum
                    else:  # annual
                        # Start at current + 5 min, expire in 370 days from start
                        expire_by = start_at + (370 * 24 * 60 * 60)
                        total_count = 5   # 5 years maximum
                    
                    # Ensure expire_by doesn't exceed maximum
                    expire_by = min(expire_by, max_timestamp)
                    
                    # Validate timestamps
                    validate_razorpay_timestamps(start_at, expire_by)
                    
                    print(f"Current timestamp: {current_timestamp}")
                    print(f"Start at timestamp: {start_at} (current + 5 min)")
                    print(f"Expire by timestamp: {expire_by}")
                    
                    # Create Razorpay subscription with proper timestamps
                    subscription_data = {
                        "plan_id": package['razorpay_plan_id'],
                        "total_count": total_count,
                        "customer_notify": 1,
                        "start_at": start_at,
                        "expire_by": expire_by,
                        "notes": {
                            "user_id": str(data['user_id']),
                            "subscription_id": str(subscription_id),
                            "invoice_id": str(invoice_id),
                            "package_name": package['package_name']
                        }
                    }
                    
                    print(f"Creating Razorpay subscription with data: {subscription_data}")
                    
                    razorpay_subscription = razorpay_client.subscription.create(subscription_data)

                    # Store Razorpay subscription ID
                    cursor.execute("""
                        UPDATE subscriptions 
                        SET razorpay_subscription_id = %s 
                        WHERE id = %s
                    """, (razorpay_subscription['id'], subscription_id))

                    # Store in invoice too
                    cursor.execute("""
                        UPDATE invoices
                        SET gateway_order_id = %s
                        WHERE id = %s
                    """, (razorpay_subscription['id'], invoice_id))

                    payment_data = {
                        'razorpay_subscription_id': razorpay_subscription['id'],
                        'razorpay_key': RAZORPAY_KEY_ID,
                        'amount': float(package['price']),
                        'currency': 'INR',  # Default currency, adjust as needed
                        'name': package['package_name'],
                        'prefill': {
                            'email': data.get('email', '')
                        },
                        'total_count': total_count
                    }

                    message = 'Subscription created, awaiting payment'

                else:  # Lifetime package
                    # Create one-time Razorpay order
                    razorpay_order = razorpay_client.order.create({
                        'amount': int(float(package['price']) * 100),  # Convert to paise
                        'currency': 'INR',  # Default currency, adjust as needed
                        'receipt': invoice_number,
                        'notes': {
                            "user_id": str(data['user_id']),
                            "subscription_id": str(subscription_id),
                            "invoice_id": str(invoice_id),
                            "package_name": package['package_name']
                        }
                    })

                    # Store order ID
                    cursor.execute("""
                        UPDATE invoices
                        SET gateway_order_id = %s
                        WHERE id = %s
                    """, (razorpay_order['id'], invoice_id))

                    payment_data = {
                        'razorpay_order_id': razorpay_order['id'],
                        'razorpay_key': RAZORPAY_KEY_ID,
                        'amount': float(package['price']),
                        'currency': 'INR',  # Default currency, adjust as needed
                        'name': package['package_name'],
                        'prefill': {
                            'email': data.get('email', '')
                        }
                    }

                    message = 'One-time order created, awaiting payment'

                connection.commit()

                return jsonify({
                    'message': message,
                    'subscription_id': subscription_id,
                    'invoice_id': invoice_id,
                    'payment_data': payment_data,
                    'error': False
                }), 201

            except razorpay.errors.BadRequestError as e:
                connection.rollback()
                error_msg = str(e)
                print(f"Razorpay BadRequestError: {error_msg}")
                
                # Provide more specific error messages
                if 'end_time' in error_msg or 'timestamp' in error_msg or 'expire_by' in error_msg or 'start_at' in error_msg:
                    return jsonify({
                        'message': 'Subscription timing configuration error. Please contact support.',
                        'error': True,
                        'details': error_msg
                    }), 500
                elif 'plan_id' in error_msg:
                    return jsonify({
                        'message': 'Package configuration error. Please contact support.',
                        'error': True
                    }), 500
                else:
                    return jsonify({
                        'message': f"Payment gateway error: {error_msg}",
                        'error': True
                    }), 500
                    
            except razorpay.errors.ServerError as e:
                connection.rollback()
                print(f"Razorpay ServerError: {str(e)}")
                return jsonify({
                    'message': 'Payment gateway is temporarily unavailable. Please try again.',
                    'error': True
                }), 500
                
            except ValueError as e:
                connection.rollback()
                print(f"Timestamp validation error: {str(e)}")
                return jsonify({
                    'message': 'Subscription timing error. Please contact support.',
                    'error': True,
                    'details': str(e)
                }), 500
                
            except Exception as e:
                connection.rollback()
                print(f"Payment creation error: {str(e)}")
                return jsonify({
                    'message': f"Payment creation failed: {str(e)}",
                    'error': True
                }), 500

    except Exception as e:
        if connection:
            connection.rollback()
        print(f"Subscription creation error: {str(e)}")
        return jsonify({'message': f"Error creating subscription: {str(e)}", 'error': True}), 500
    finally:
        if connection:
            connection.close()


def get_user_subscriptions(user_id):
    """Get all subscriptions for a user with their invoices"""
    connection = connect_to_database()
    status_filter = request.args.get('status')

    try:
        with closing(connection.cursor(dictionary=True)) as cursor:
            sql = """
                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
            """

            params = [user_id]

            if status_filter:
                sql += " AND s.status = %s"
                params.append(status_filter)

            sql += " ORDER BY s.start_date DESC, i.created_at DESC"

            cursor.execute(sql, tuple(params))
            records = cursor.fetchall()

            subscriptions = {}
            user_info = None

            for row in records:
                sub_id = row['subscription_id']
                
                if not user_info:
                    user_info = {
                        'email': row['email'],
                        'firstname': row['firstname'],
                        'lastname': row['lastname']
                    }

                if sub_id not in subscriptions:
                    subscriptions[sub_id] = {
                        'subscription_id': row['subscription_id'],
                        'razorpay_subscription_id': row['razorpay_subscription_id'],
                        'subscription_status': row['subscription_status'],
                        'start_date': row['start_date'],
                        'end_date': row['end_date'],
                        'next_billing_date': row['next_billing_date'],
                        'package_name': row['package_name'],
                        'price': float(row['price']),
                        'billing_cycle': row['billing_cycle'],
                        'invoices': []
                    }

                if row['invoice_id']:
                    subscriptions[sub_id]['invoices'].append({
                        'invoice_id': row['invoice_id'],
                        'invoice_status': row['invoice_status'],
                        'invoice_date': row['invoice_date']
                    })

            return jsonify({
                'data': list(subscriptions.values()),
                'user': user_info,
                'error': False
            }), 200

    except Exception as e:
        print(f"Error fetching subscriptions: {str(e)}")
        return jsonify({'message': f"Error fetching subscriptions: {str(e)}", 'error': True}), 500
    finally:
        if connection:
            connection.close()


def cancel_subscription(subscription_id):
    """Cancel a subscription in both Razorpay and database"""
    connection = connect_to_database()

    try:
        with closing(connection.cursor(dictionary=True)) as cursor:
            # Get the subscription details
            cursor.execute("""
                SELECT razorpay_subscription_id, status, user_id
                FROM subscriptions
                WHERE id = %s AND status IN ('active', 'pending')
            """, (subscription_id,))
            subscription = cursor.fetchone()

            if not subscription:
                return jsonify({
                    'message': 'No active or pending subscription found to cancel.',
                    'error': True
                }), 404

            razorpay_sub_id = subscription['razorpay_subscription_id']
            previous_status = subscription['status']

            # Cancel the Razorpay subscription if it exists
            if razorpay_sub_id:
                try:
                    razorpay_client.subscription.cancel(razorpay_sub_id)
                    print(f"Cancelled Razorpay subscription: {razorpay_sub_id}")
                except razorpay.errors.BadRequestError as e:
                    # Subscription might already be cancelled in Razorpay
                    print(f"Razorpay cancellation warning: {str(e)}")
                except Exception as e:
                    print(f"Razorpay cancellation error: {str(e)}")
                    # Continue with local cancellation even if Razorpay fails

            # Cancel in the local database
            cursor.execute("""
                UPDATE subscriptions 
                SET status = 'canceled', end_date = %s, updated_at = %s
                WHERE id = %s
            """, (datetime.now(), datetime.now(), subscription_id))

            # Cancel any unpaid or draft invoices
            cursor.execute("""
                UPDATE invoices 
                SET status = 'canceled', updated_at = %s
                WHERE subscription_id = %s AND status IN ('draft', 'unpaid', 'pending')
            """, (datetime.now(), subscription_id,))

            connection.commit()

            return jsonify({
                'message': f'Subscription canceled successfully.',
                'error': False
            }), 200

    except Exception as e:
        if connection:
            connection.rollback()
        print(f"Error canceling subscription: {str(e)}")
        return jsonify({'message': f"Error canceling subscription: {str(e)}", 'error': True}), 500
    finally:
        if connection:
            connection.close()


def get_user_subscription_details(user_id, subscription_id):
    """Get detailed subscription information including usage and invoices"""
    connection = connect_to_database()
    
    try:
        with closing(connection.cursor(dictionary=True)) as cursor:
            # Fetch subscription and package details
            cursor.execute("""
                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_id, user_id))
            
            subscription = cursor.fetchone()
            if not subscription:
                return jsonify({'message': 'Subscription not found', 'error': True}), 404

            # Get all invoices for this subscription
            cursor.execute("""
                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
            """, (subscription_id,))
            invoices = cursor.fetchall()

            # For each invoice, get the latest payment info
            for invoice in invoices:
                cursor.execute("""
                    SELECT id, amount, payment_method, transaction_id, payment_date, status
                    FROM payments
                    WHERE invoice_id = %s
                    ORDER BY payment_date DESC
                    LIMIT 1
                """, (invoice['id'],))
                payment = cursor.fetchone()
                invoice['latest_payment'] = payment
                
                # Convert amount to float for consistency
                if invoice['total_amount']:
                    invoice['total_amount'] = float(invoice['total_amount'])

            # Format the response
            response_data = {
                'subscription_id': subscription['id'],
                'package_name': subscription['package_name'],
                'billing_cycle': subscription['billing_cycle'],
                'status': subscription['status'],
                'start_date': subscription['start_date'],
                'end_date': subscription['end_date'],
                'next_billing_date': subscription.get('next_billing_date'),
                'maximum_bookings': subscription['maximum_bookings'],
                'price_after_max': float(subscription['price_after_max']) if subscription['price_after_max'] else None,
                'price': float(subscription['price']),
                'razorpay_subscription_id': subscription['razorpay_subscription_id'],
                'invoices': invoices
            }

            return jsonify({
                'data': response_data,
                'error': False
            }), 200

    except Exception as e:
        print(f"Error fetching subscription details: {str(e)}")
        return jsonify({'message': f"Error fetching subscription details: {str(e)}", 'error': True}), 500
    finally:
        if connection:
            connection.close()


def get_all_subscriptions():
    """Get all subscriptions (admin endpoint)"""
    status = request.args.get('status')
    page = request.args.get('page', default=1, type=int)
    limit = request.args.get('limit', default=10, type=int)

    connection = connect_to_database()

    try:
        with closing(connection.cursor(dictionary=True)) as cursor:
            base_query = """
                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
            """
            params = []

            if status:
                base_query += " AND s.status = %s"
                params.append(status)

            # Get total count
            count_query = f"SELECT COUNT(*) as total FROM ({base_query}) AS subquery"
            cursor.execute(count_query, params)
            total = cursor.fetchone()['total']

            # Add pagination
            base_query += " ORDER BY s.start_date DESC LIMIT %s OFFSET %s"
            params.extend([limit, (page - 1) * limit])

            cursor.execute(base_query, params)
            subscriptions = cursor.fetchall()

            # Get latest invoice for each subscription
            for sub in subscriptions:
                cursor.execute("""
                    SELECT id, invoice_number, total_amount, status, issue_date
                    FROM invoices
                    WHERE subscription_id = %s
                    ORDER BY issue_date DESC
                    LIMIT 1
                """, (sub['id'],))
                latest_invoice = cursor.fetchone()
                sub['latest_invoice'] = latest_invoice
                
                # Convert price to float
                if sub['price']:
                    sub['price'] = float(sub['price'])

            return jsonify({
                'data': subscriptions,
                'total': total,
                'page': page,
                'per_page': limit,
                'error': False
            }), 200

    except Exception as e:
        print(f"Error fetching subscriptions: {str(e)}")
        return jsonify({'message': f"Error fetching subscriptions: {str(e)}", 'error': True}), 500
    finally:
        if connection:
            connection.close()


def verify_payment():
    """Verify Razorpay payment and activate subscription"""
    data = request.get_json()
    required_fields = ['payment_id', 'subscription_id']
    
    for field in required_fields:
        if field not in data:
            return jsonify({'message': f'Missing required field: {field}', 'error': True}), 400

    connection = connect_to_database()
    
    try:
        with closing(connection.cursor(dictionary=True)) as cursor:
            # Get subscription details
            cursor.execute("""
                SELECT s.*, p.package_name, p.billing_cycle
                FROM subscriptions s
                JOIN packages p ON s.package_id = p.id
                WHERE s.id = %s
            """, (data['subscription_id'],))
            subscription = cursor.fetchone()

            if not subscription:
                return jsonify({'message': 'Subscription not found', 'error': True}), 404

            # Verify payment with Razorpay
            try:
                payment = razorpay_client.payment.fetch(data['payment_id'])
                
                if payment['status'] == 'captured':
                    # Payment successful - activate subscription
                    start_date = datetime.now()
                    end_date = None
                    
                    if subscription['billing_cycle'] == 'monthly':
                        end_date = start_date + timedelta(days=30)
                    elif subscription['billing_cycle'] == 'annual':
                        end_date = start_date + timedelta(days=365)
                    # Lifetime packages remain with end_date = None
                    
                    # Update subscription status
                    cursor.execute("""
                        UPDATE subscriptions 
                        SET status = 'active', start_date = %s, end_date = %s, 
                            next_billing_date = %s, updated_at = %s
                        WHERE id = %s
                    """, (start_date, end_date, end_date, datetime.now(), data['subscription_id']))

                    # Update invoice status
                    cursor.execute("""
                        UPDATE invoices 
                        SET status = 'paid', updated_at = %s
                        WHERE subscription_id = %s AND status IN ('draft', 'pending', 'unpaid')
                    """, (datetime.now(), data['subscription_id']))

                    # Create payment record
                    cursor.execute("""
                        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
                    """, (data['payment_id'], datetime.now(), str(payment), data['subscription_id']))

                    connection.commit()

                    return jsonify({
                        'message': 'Payment verified and subscription activated successfully',
                        'payment_status': 'captured',
                        'error': False
                    }), 200
                else:
                    return jsonify({
                        'message': f'Payment not captured. Status: {payment["status"]}',
                        'payment_status': payment['status'],
                        'error': True
                    }), 400

            except razorpay.errors.BadRequestError as e:
                return jsonify({
                    'message': f'Invalid payment ID: {str(e)}',
                    'error': True
                }), 400
            except Exception as e:
                return jsonify({
                    'message': f'Payment verification failed: {str(e)}',
                    'error': True
                }), 500

    except Exception as e:
        if connection:
            connection.rollback()
        print(f"Payment verification error: {str(e)}")
        return jsonify({'message': f"Error verifying payment: {str(e)}", 'error': True}), 500
    finally:
        if connection:
            connection.close()


def get_subscription_invoices(subscription_id):
    """Get all invoices for a specific subscription"""
    connection = connect_to_database()
    
    try:
        with closing(connection.cursor(dictionary=True)) as cursor:
            cursor.execute("""
                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
            """, (subscription_id,))
            
            invoices = cursor.fetchall()
            
            # Format amounts
            for invoice in invoices:
                if invoice['amount']:
                    invoice['amount'] = float(invoice['amount'])
                if invoice['total_amount']:
                    invoice['total_amount'] = float(invoice['total_amount'])

            return jsonify({
                'data': invoices,
                'error': False
            }), 200

    except Exception as e:
        print(f"Error fetching invoices: {str(e)}")
        return jsonify({'message': f"Error fetching invoices: {str(e)}", 'error': True}), 500
    finally:
        if connection:
            connection.close()