"""
SIM Analytics API - Comprehensive REST API
Provides access to all app features with Admin/User authentication levels
"""

from flask import Flask, request, jsonify, render_template_string, g
import os
import time
import psycopg2
import psycopg2.extras
from functools import wraps
from datetime import datetime, date
import json
from api_manager import validate_api_key, log_api_usage, check_rate_limit, APIKeyType

app = Flask(__name__)

# Production domain configuration
PRODUCTION_DOMAIN = "https://radius.telkom.click"
API_VERSION = "v1.1.3"

def get_db_connection():
    """Get PostgreSQL database connection"""
    return psycopg2.connect(os.environ['DATABASE_URL'])

# Authentication decorator
def require_api_key(required_level="user"):
    def decorator(f):
        @wraps(f)
        def decorated_function(*args, **kwargs):
            start_time = time.time()
            
            # Get API key from header
            api_key = request.headers.get('X-API-Key') or request.headers.get('Authorization')
            if api_key and api_key.startswith('Bearer '):
                api_key = api_key[7:]
            
            if not api_key:
                return jsonify({
                    'error': 'API key required',
                    'message': 'Please provide API key in X-API-Key header or Authorization header'
                }), 401
            
            # Validate API key
            key_info = validate_api_key(api_key)
            if not key_info:
                return jsonify({
                    'error': 'Invalid API key',
                    'message': 'The provided API key is invalid or inactive'
                }), 401
            
            # Check rate limit
            if check_rate_limit(key_info['id'], key_info['rate_limit_per_hour']):
                return jsonify({
                    'error': 'Rate limit exceeded',
                    'message': f'Rate limit of {key_info["rate_limit_per_hour"]} requests per hour exceeded'
                }), 429
            
            # Check access level
            if required_level == "admin" and key_info['key_type'] != 'admin':
                return jsonify({
                    'error': 'Insufficient permissions',
                    'message': 'Admin API key required for this endpoint'
                }), 403
            
            # Store key info for use in endpoint
            g.api_key_info = key_info
            
            # Execute the endpoint
            try:
                response = f(*args, **kwargs)
                status_code = 200
                if isinstance(response, tuple):
                    status_code = response[1]
                
                # Log API usage
                response_time = int((time.time() - start_time) * 1000)
                log_api_usage(
                    key_info['id'],
                    request.endpoint or request.path,
                    request.method,
                    request.remote_addr,
                    request.user_agent.string if request.user_agent else None,
                    json.dumps(dict(request.get_json() or {})) if request.get_json() else None,
                    status_code,
                    response_time
                )
                
                return response
                
            except Exception as e:
                # Log error
                response_time = int((time.time() - start_time) * 1000)
                log_api_usage(
                    key_info['id'],
                    request.endpoint or request.path,
                    request.method,
                    request.remote_addr,
                    request.user_agent.string if request.user_agent else None,
                    json.dumps(dict(request.get_json() or {})) if request.get_json() else None,
                    500,
                    response_time
                )
                
                return jsonify({
                    'error': 'Internal server error',
                    'message': str(e)
                }), 500
        
        return decorated_function
    return decorator

# Helper function for date serialization
def json_serial(obj):
    """JSON serializer for objects not serializable by default json code"""
    if isinstance(obj, (datetime, date)):
        return obj.isoformat()
    raise TypeError(f"Type {type(obj)} not serializable")

# API Documentation endpoint
@app.route('/api/v1/docs')
def api_docs():
    """Get API documentation"""
    docs = {
        "title": "SIM Analytics API v1",
        "description": "Comprehensive API for SIM card usage analytics and management",
        "version": "1.1.3",
        "authentication": {
            "type": "API Key",
            "header": "X-API-Key or Authorization (Bearer token)",
            "key_types": {
                "admin": "Full access to all endpoints and admin functions",
                "user": "Read-only access to analytics and reports"
            }
        },
        "base_url": PRODUCTION_DOMAIN + "/api/v1",
        "endpoints": {
            "authentication": {
                "/docs": {"method": "GET", "description": "API documentation", "auth": "none"},
                "/health": {"method": "GET", "description": "Health check", "auth": "none"}
            },
            "clients": {
                "/clients": {"method": "GET", "description": "List all clients", "auth": "user"},
                "/clients": {"method": "POST", "description": "Create new client", "auth": "admin"},
                "/clients/{client_id}": {"method": "GET", "description": "Get client details", "auth": "user"},
                "/clients/{client_id}": {"method": "PUT", "description": "Update client", "auth": "admin"},
                "/clients/{client_id}": {"method": "DELETE", "description": "Delete client", "auth": "admin"}
            },
            "sim_cards": {
                "/sims": {"method": "GET", "description": "List all SIM cards", "auth": "user"},
                "/sims": {"method": "POST", "description": "Create SIM card(s)", "auth": "admin"},
                "/sims/{sim_id}": {"method": "GET", "description": "Get SIM details", "auth": "user"},
                "/sims/{sim_id}": {"method": "PUT", "description": "Update SIM", "auth": "admin"},
                "/sims/{sim_id}": {"method": "DELETE", "description": "Delete SIM", "auth": "admin"}
            },
            "usage": {
                "/usage": {"method": "GET", "description": "Get usage records", "auth": "user"},
                "/usage": {"method": "POST", "description": "Create usage record", "auth": "admin"},
                "/usage/{record_id}": {"method": "GET", "description": "Get usage record", "auth": "user"},
                "/usage/{record_id}/daily": {"method": "GET", "description": "Get daily breakdown", "auth": "user"}
            },
            "analytics": {
                "/analytics/summary": {"method": "GET", "description": "Usage summary", "auth": "user"},
                "/analytics/protocols": {"method": "GET", "description": "SIM-based protocol analysis with exact usage matching", "auth": "user"},
                "/analytics/protocols/{sim_number}": {"method": "GET", "description": "Get protocol analysis for specific SIM", "auth": "user"},
                "/analytics/protocols/{sim_number}/{usage_record_id}": {"method": "GET", "description": "Get protocol analysis for specific usage period", "auth": "user"},
                "/analytics/reports/{client_id}": {"method": "GET", "description": "Generate client report", "auth": "user"}
            }
        }
    }
    return jsonify(docs)

# Health check
@app.route('/api/v1/health')
def health():
    """Health check endpoint"""
    return jsonify({
        'status': 'healthy',
        'timestamp': datetime.utcnow().isoformat(),
        'version': '1.1.3'
    })

# CLIENT ENDPOINTS
@app.route('/api/v1/clients', methods=['GET'])
@require_api_key("user")
def get_clients():
    """Get all clients"""
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    cursor.execute("SELECT * FROM clients ORDER BY client_name")
    clients = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return jsonify({
        'clients': [dict(client) for client in clients],
        'count': len(clients)
    })

@app.route('/api/v1/clients', methods=['POST'])
@require_api_key("admin")
def create_client():
    """Create a new client"""
    data = request.get_json()
    
    if not data or 'client_name' not in data:
        return jsonify({
            'error': 'Bad request',
            'message': 'client_name is required'
        }), 400
    
    client_name = data['client_name']
    
    # Generate client ID
    import re
    import secrets
    clean_name = re.sub(r'[^a-zA-Z0-9\s]', '', client_name)
    name_parts = clean_name.strip().split()
    
    if len(name_parts) >= 2:
        client_id = f"{name_parts[0][0].upper()}{name_parts[-1].capitalize()}{secrets.randbelow(9999):04d}"
    else:
        client_id = f"{clean_name.replace(' ', '')}{secrets.randbelow(9999):04d}"
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        cursor.execute("INSERT INTO clients (client_id, client_name) VALUES (%s, %s)", 
                      (client_id, client_name))
        conn.commit()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'message': 'Client created successfully',
            'client': {
                'client_id': client_id,
                'client_name': client_name
            }
        }), 201
        
    except psycopg2.IntegrityError:
        cursor.close()
        conn.close()
        return jsonify({
            'error': 'Conflict',
            'message': 'Client with this ID already exists'
        }), 409

@app.route('/api/v1/clients/<client_id>', methods=['GET'])
@require_api_key("user")
def get_client(client_id):
    """Get client details"""
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    cursor.execute("SELECT * FROM clients WHERE client_id = %s", (client_id,))
    client = cursor.fetchone()
    
    if not client:
        cursor.close()
        conn.close()
        return jsonify({
            'error': 'Not found',
            'message': 'Client not found'
        }), 404
    
    # Get SIM cards for this client
    cursor.execute("SELECT * FROM sim_numbers WHERE client_id = %s", (client_id,))
    sims = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return jsonify({
        'client': dict(client),
        'sim_cards': [dict(sim) for sim in sims],
        'sim_count': len(sims)
    })

@app.route('/api/v1/clients/<client_id>', methods=['DELETE'])
@require_api_key("admin")
def delete_client(client_id):
    """Delete a client"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    cursor.execute("DELETE FROM clients WHERE client_id = %s", (client_id,))
    
    if cursor.rowcount == 0:
        cursor.close()
        conn.close()
        return jsonify({
            'error': 'Not found',
            'message': 'Client not found'
        }), 404
    
    conn.commit()
    cursor.close()
    conn.close()
    
    return jsonify({'message': 'Client deleted successfully'})

# SIM CARD ENDPOINTS
@app.route('/api/v1/sims', methods=['GET'])
@require_api_key("user")
def get_sims():
    """Get all SIM cards"""
    client_id = request.args.get('client_id')
    
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    if client_id:
        cursor.execute("""
            SELECT s.*, c.client_name 
            FROM sim_numbers s 
            JOIN clients c ON s.client_id = c.client_id 
            WHERE s.client_id = %s
            ORDER BY s.sim_number
        """, (client_id,))
    else:
        cursor.execute("""
            SELECT s.*, c.client_name 
            FROM sim_numbers s 
            JOIN clients c ON s.client_id = c.client_id 
            ORDER BY c.client_name, s.sim_number
        """)
    
    sims = cursor.fetchall()
    cursor.close()
    conn.close()
    
    return jsonify({
        'sim_cards': [dict(sim) for sim in sims],
        'count': len(sims)
    })

@app.route('/api/v1/sims', methods=['POST'])
@require_api_key("admin")
def create_sims():
    """Create SIM card(s) - supports single SIM or bulk creation"""
    data = request.get_json()
    
    if not data:
        return jsonify({
            'error': 'Bad request',
            'message': 'JSON data required'
        }), 400
    
    # Support both single SIM and bulk creation
    sim_cards = []
    if 'sim_cards' in data:
        # Bulk creation
        sim_cards = data['sim_cards']
    else:
        # Single SIM creation
        sim_cards = [data]
    
    created_sims = []
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        for sim_data in sim_cards:
            if not all(key in sim_data for key in ['client_id', 'sim_number', 'msisdn']):
                return jsonify({
                    'error': 'Bad request',
                    'message': 'client_id, sim_number, and msisdn are required for each SIM'
                }), 400
            
            fup_gb = sim_data.get('fup_gb', 50)  # Default 50GB
            
            cursor.execute("""
                INSERT INTO sim_numbers (client_id, sim_number, msisdn, fup_gb) 
                VALUES (%s, %s, %s, %s)
                RETURNING id
            """, (sim_data['client_id'], sim_data['sim_number'], sim_data['msisdn'], fup_gb))
            
            result = cursor.fetchone()
            sim_id = result[0] if result else None
            created_sims.append({
                'id': sim_id,
                'client_id': sim_data['client_id'],
                'sim_number': sim_data['sim_number'],
                'msisdn': sim_data['msisdn'],
                'fup_gb': fup_gb
            })
        
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({
            'message': f'{len(created_sims)} SIM card(s) created successfully',
            'sim_cards': created_sims
        }), 201
        
    except psycopg2.IntegrityError as e:
        cursor.close()
        conn.close()
        return jsonify({
            'error': 'Conflict',
            'message': 'SIM number or MSISDN already exists'
        }), 409

@app.route('/api/v1/sims/<int:sim_id>', methods=['GET'])
@require_api_key("user")
def get_sim(sim_id):
    """Get SIM card details"""
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    cursor.execute("""
        SELECT s.*, c.client_name 
        FROM sim_numbers s 
        JOIN clients c ON s.client_id = c.client_id 
        WHERE s.id = %s
    """, (sim_id,))
    
    sim = cursor.fetchone()
    
    if not sim:
        cursor.close()
        conn.close()
        return jsonify({
            'error': 'Not found',
            'message': 'SIM card not found'
        }), 404
    
    # Get usage records for this SIM
    cursor.execute("""
        SELECT * FROM usage_records 
        WHERE msisdn = %s 
        ORDER BY date_from DESC
    """, (sim['msisdn'],))
    
    usage_records = cursor.fetchall()
    cursor.close()
    conn.close()
    
    return jsonify({
        'sim_card': dict(sim),
        'usage_records': [dict(record) for record in usage_records],
        'usage_count': len(usage_records)
    })

# USAGE ENDPOINTS
@app.route('/api/v1/usage', methods=['GET'])
@require_api_key("user")
def get_usage_records():
    """Get usage records with optional filters"""
    client_id = request.args.get('client_id')
    sim_number = request.args.get('sim_number')
    msisdn = request.args.get('msisdn')
    limit = request.args.get('limit', 100, type=int)
    offset = request.args.get('offset', 0, type=int)
    
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    base_query = """
        SELECT ur.*, c.client_name, s.fup_gb
        FROM usage_records ur
        JOIN clients c ON ur.client_id = c.client_id
        LEFT JOIN sim_numbers s ON ur.msisdn = s.msisdn
        WHERE 1=1
    """
    
    params = []
    if client_id:
        base_query += " AND ur.client_id = %s"
        params.append(client_id)
    if sim_number:
        base_query += " AND ur.sim_number = %s"
        params.append(sim_number)
    if msisdn:
        base_query += " AND ur.msisdn = %s"
        params.append(msisdn)
    
    base_query += " ORDER BY ur.date_from DESC LIMIT %s OFFSET %s"
    params.extend([limit, offset])
    
    cursor.execute(base_query, params)
    records = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return jsonify({
        'usage_records': [dict(record) for record in records],
        'count': len(records),
        'limit': limit,
        'offset': offset
    })

@app.route('/api/v1/usage', methods=['POST'])
@require_api_key("admin")
def create_usage_record():
    """Create a new usage record"""
    data = request.get_json()
    
    required_fields = ['client_id', 'sim_number', 'msisdn', 'total_usage_gb', 'date_from', 'date_to']
    if not data or not all(field in data for field in required_fields):
        return jsonify({
            'error': 'Bad request',
            'message': f'Required fields: {", ".join(required_fields)}'
        }), 400
    
    conn = get_db_connection()
    cursor = conn.cursor()
    
    try:
        cursor.execute("""
            INSERT INTO usage_records 
            (client_id, sim_number, msisdn, total_usage_gb, date_from, date_to, fup_reached)
            VALUES (%s, %s, %s, %s, %s, %s, %s)
            RETURNING id
        """, (
            data['client_id'], data['sim_number'], data['msisdn'],
            data['total_usage_gb'], data['date_from'], data['date_to'],
            data.get('fup_reached', False)
        ))
        
        result = cursor.fetchone()
        record_id = result[0] if result else None
        conn.commit()
        cursor.close()
        conn.close()
        
        return jsonify({
            'message': 'Usage record created successfully',
            'record_id': record_id
        }), 201
        
    except psycopg2.Error as e:
        cursor.close()
        conn.close()
        return jsonify({
            'error': 'Database error',
            'message': str(e)
        }), 500

# ANALYTICS ENDPOINTS
@app.route('/api/v1/analytics/summary', methods=['GET'])
@require_api_key("user")
def get_analytics_summary():
    """Get usage analytics summary"""
    client_id = request.args.get('client_id')
    
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    # Basic stats
    base_query = "SELECT COUNT(*) as total_records, SUM(total_usage_gb) as total_usage FROM usage_records"
    params = []
    
    if client_id:
        base_query += " WHERE client_id = %s"
        params.append(client_id)
    
    cursor.execute(base_query, params)
    stats = cursor.fetchone()
    
    # FUP exceeded count
    fup_query = "SELECT COUNT(*) as fup_exceeded FROM usage_records WHERE fup_reached = TRUE"
    if client_id:
        fup_query += " AND client_id = %s"
        cursor.execute(fup_query, [client_id])
    else:
        cursor.execute(fup_query)
    
    fup_stats = cursor.fetchone()
    
    # Top usage by client
    top_clients_query = """
        SELECT c.client_name, c.client_id, COUNT(ur.id) as records, SUM(ur.total_usage_gb) as total_usage
        FROM usage_records ur
        JOIN clients c ON ur.client_id = c.client_id
    """
    
    if client_id:
        top_clients_query += " WHERE ur.client_id = %s"
        top_clients_query += " GROUP BY c.client_name, c.client_id"
        cursor.execute(top_clients_query, [client_id])
    else:
        top_clients_query += " GROUP BY c.client_name, c.client_id ORDER BY total_usage DESC LIMIT 10"
        cursor.execute(top_clients_query)
    
    top_clients = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return jsonify({
        'summary': {
            'total_records': (stats['total_records'] or 0) if stats else 0,
            'total_usage_gb': float(stats['total_usage'] or 0) if stats else 0.0,
            'fup_exceeded_count': (fup_stats['fup_exceeded'] or 0) if fup_stats else 0
        },
        'top_clients': [dict(client) for client in top_clients]
    })

@app.route('/api/v1/analytics/protocols', methods=['GET'])
@require_api_key("user")
def get_protocol_analysis():
    """Get protocol usage analysis"""
    client_id = request.args.get('client_id')
    
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    base_query = """
        SELECT pu.protocol_name, pu.category, SUM(pu.usage_gb) as total_usage_gb, 
               AVG(pu.percentage) as avg_percentage, COUNT(*) as usage_count
        FROM protocol_usage pu
        JOIN usage_records ur ON pu.usage_record_id = ur.id
    """
    
    params = []
    if client_id:
        base_query += " WHERE ur.client_id = %s"
        params.append(client_id)
    
    base_query += " GROUP BY pu.protocol_name, pu.category ORDER BY total_usage_gb DESC"
    
    cursor.execute(base_query, params)
    protocols = cursor.fetchall()
    
    # Category summary
    category_query = """
        SELECT pu.category, SUM(pu.usage_gb) as total_usage_gb, COUNT(DISTINCT pu.protocol_name) as protocol_count
        FROM protocol_usage pu
        JOIN usage_records ur ON pu.usage_record_id = ur.id
    """
    
    if client_id:
        category_query += " WHERE ur.client_id = %s"
        category_query += " GROUP BY pu.category ORDER BY total_usage_gb DESC"
        cursor.execute(category_query, [client_id])
    else:
        category_query += " GROUP BY pu.category ORDER BY total_usage_gb DESC"
        cursor.execute(category_query)
    
    categories = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    return jsonify({
        'protocols': [dict(protocol) for protocol in protocols],
        'categories': [dict(category) for category in categories],
        'total_protocols': len(protocols)
    })

# SIM-based Protocol Analysis endpoint
@app.route('/api/v1/analytics/protocols/<sim_number>')
@require_api_key("user")
def get_sim_protocol_analysis(sim_number):
    """Get protocol analysis for a specific SIM card"""
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    try:
        # Get all usage records for this SIM
        cursor.execute("""
            SELECT ur.id, ur.total_usage_gb, ur.date_from, ur.date_to,
                   s.client_id, c.client_name
            FROM usage_records ur
            JOIN sim_numbers s ON ur.sim_number = s.sim_number
            JOIN clients c ON s.client_id = c.client_id
            WHERE ur.sim_number = %s
            ORDER BY ur.date_from DESC
        """, (sim_number,))
        usage_records = cursor.fetchall()
        
        if not usage_records:
            return jsonify({'error': 'No usage records found for this SIM'}), 404
        
        # Get protocol data for all usage records of this SIM
        cursor.execute("""
            SELECT pu.protocol_name, pu.category, pu.usage_gb, pu.percentage,
                   ur.id as usage_record_id, ur.date_from, ur.date_to
            FROM protocol_usage pu
            JOIN usage_records ur ON pu.usage_record_id = ur.id
            WHERE ur.sim_number = %s
            ORDER BY ur.date_from DESC, pu.usage_gb DESC
        """, (sim_number,))
        protocol_data = cursor.fetchall()
        
        # Data validation check
        cursor.execute("""
            SELECT ur.id, ur.total_usage_gb as recorded_usage,
                   COALESCE(SUM(pu.usage_gb), 0) as protocol_total,
                   ABS(ur.total_usage_gb - COALESCE(SUM(pu.usage_gb), 0)) as discrepancy
            FROM usage_records ur
            LEFT JOIN protocol_usage pu ON ur.id = pu.usage_record_id
            WHERE ur.sim_number = %s
            GROUP BY ur.id, ur.total_usage_gb
        """, (sim_number,))
        validation_results = cursor.fetchall()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'sim_number': sim_number,
            'client_name': usage_records[0]['client_name'] if usage_records else None,
            'usage_records': [dict(record) for record in usage_records],
            'protocol_data': [dict(protocol) for protocol in protocol_data],
            'data_validation': [dict(validation) for validation in validation_results],
            'total_records': len(usage_records)
        })
        
    except Exception as e:
        cursor.close()
        conn.close()
        return jsonify({'error': str(e)}), 500

# Specific usage period protocol analysis
@app.route('/api/v1/analytics/protocols/<sim_number>/<int:usage_record_id>')
@require_api_key("user")
def get_usage_period_protocol_analysis(sim_number, usage_record_id):
    """Get protocol analysis for specific SIM usage period"""
    conn = get_db_connection()
    cursor = conn.cursor(cursor_factory=psycopg2.extras.RealDictCursor)
    
    try:
        # Verify the usage record belongs to the SIM
        cursor.execute("""
            SELECT ur.id, ur.total_usage_gb, ur.date_from, ur.date_to,
                   s.client_id, c.client_name, s.msisdn
            FROM usage_records ur
            JOIN sim_numbers s ON ur.sim_number = s.sim_number
            JOIN clients c ON s.client_id = c.client_id
            WHERE ur.sim_number = %s AND ur.id = %s
        """, (sim_number, usage_record_id))
        usage_record = cursor.fetchone()
        
        if not usage_record:
            return jsonify({'error': 'Usage record not found for this SIM'}), 404
        
        # Get protocol data for this specific usage record
        cursor.execute("""
            SELECT pu.protocol_name, pu.category, pu.usage_gb, pu.percentage
            FROM protocol_usage pu
            WHERE pu.usage_record_id = %s
            ORDER BY pu.usage_gb DESC
        """, (usage_record_id,))
        protocol_data = cursor.fetchall()
        
        # Category breakdown
        cursor.execute("""
            SELECT pu.category, SUM(pu.usage_gb) as category_total,
                   COUNT(*) as protocol_count
            FROM protocol_usage pu
            WHERE pu.usage_record_id = %s
            GROUP BY pu.category
            ORDER BY category_total DESC
        """, (usage_record_id,))
        category_data = cursor.fetchall()
        
        # Data validation for this specific record
        cursor.execute("""
            SELECT ur.total_usage_gb as recorded_usage,
                   COALESCE(SUM(pu.usage_gb), 0) as protocol_total,
                   ABS(ur.total_usage_gb - COALESCE(SUM(pu.usage_gb), 0)) as discrepancy
            FROM usage_records ur
            LEFT JOIN protocol_usage pu ON ur.id = pu.usage_record_id
            WHERE ur.id = %s
            GROUP BY ur.id, ur.total_usage_gb
        """, (usage_record_id,))
        validation_result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        return jsonify({
            'sim_number': sim_number,
            'usage_record': dict(usage_record),
            'protocol_data': [dict(protocol) for protocol in protocol_data],
            'category_data': [dict(category) for category in category_data],
            'data_validation': dict(validation_result) if validation_result else None,
            'exact_match': validation_result['discrepancy'] <= 0.001 if validation_result else False
        })
        
    except Exception as e:
        cursor.close()
        conn.close()
        return jsonify({'error': str(e)}), 500

# Public API Documentation route
@app.route('/api-docs')
def public_api_docs():
    """Public API documentation page"""
    html_template = """
    <!DOCTYPE html>
    <html lang="en">
    <head>
        <meta charset="UTF-8">
        <meta name="viewport" content="width=device-width, initial-scale=1.0">
        <title>SIM Analytics API Documentation</title>
        <style>
            body { font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', system-ui, sans-serif; line-height: 1.6; margin: 0; padding: 0; background: #f8fafc; }
            .container { max-width: 1200px; margin: 0 auto; padding: 20px; }
            .header { background: linear-gradient(135deg, #667eea 0%, #764ba2 100%); color: white; padding: 2rem; border-radius: 12px; margin-bottom: 2rem; }
            .section { background: white; padding: 1.5rem; margin-bottom: 1.5rem; border-radius: 8px; box-shadow: 0 2px 4px rgba(0,0,0,0.1); }
            .endpoint { border-left: 4px solid #3b82f6; padding: 1rem; margin: 1rem 0; background: #f8fafc; border-radius: 4px; }
            .method { display: inline-block; padding: 0.25rem 0.5rem; border-radius: 4px; font-weight: bold; margin-right: 0.5rem; }
            .get { background: #10b981; color: white; }
            .post { background: #3b82f6; color: white; }
            .put { background: #f59e0b; color: white; }
            .delete { background: #ef4444; color: white; }
            .auth-admin { color: #dc2626; font-weight: bold; }
            .auth-user { color: #059669; font-weight: bold; }
            .auth-none { color: #6b7280; }
            code { background: #f1f5f9; padding: 0.25rem 0.5rem; border-radius: 4px; font-family: 'Monaco', 'Menlo', monospace; }
            pre { background: #1e293b; color: #f1f5f9; padding: 1rem; border-radius: 6px; overflow-x: auto; }
            .highlight { background: #fef3c7; padding: 0.5rem; border-radius: 4px; margin: 1rem 0; }
            .feature-grid { display: grid; grid-template-columns: repeat(auto-fit, minmax(300px, 1fr)); gap: 1rem; margin: 1rem 0; }
            .feature-card { background: #f8fafc; padding: 1rem; border-radius: 6px; border-left: 4px solid #8b5cf6; }
        </style>
    </head>
    <body>
        <div class="container">
            <div class="header">
                <h1>🚀 SIM Analytics API Documentation</h1>
                <p>Comprehensive REST API for 4G/5G SIM card usage analytics, protocol analysis, and client management</p>
                <p><strong>Version:</strong> {{ API_VERSION }} | <strong>Base URL:</strong> {{ PRODUCTION_DOMAIN }}/api/v1</p>
            </div>

            <div class="section">
                <h2>🔐 Authentication</h2>
                <div class="feature-grid">
                    <div class="feature-card">
                        <h3>Admin API Key</h3>
                        <p>Full access to all endpoints including create, update, delete operations</p>
                        <span class="auth-admin">Required for: Client/SIM management, Data modification</span>
                    </div>
                    <div class="feature-card">
                        <h3>User API Key</h3>
                        <p>Read-only access to analytics, reports, and usage data</p>
                        <span class="auth-user">Required for: Analytics, Protocol analysis, Reports</span>
                    </div>
                </div>
                
                <div class="highlight">
                    <strong>Authentication Header:</strong> Include your API key in the <code>X-API-Key</code> header or as a <code>Bearer</code> token in the <code>Authorization</code> header.
                </div>

                <pre>
# Example Request with API Key
curl -H "X-API-Key: your_api_key_here" \\
     -H "Content-Type: application/json" \\
     {{ PRODUCTION_DOMAIN }}/api/v1/clients
                </pre>
            </div>

            <div class="section">
                <h2>🆕 New Features - SIM-Based Protocol Analysis</h2>
                <div class="highlight">
                    <strong>Major Update:</strong> Protocol analysis is now performed on individual SIM cards with exact usage matching and byte-level precision validation.
                </div>
                
                <div class="endpoint">
                    <span class="method get">GET</span>
                    <code>/api/v1/analytics/protocols/{sim_number}</code>
                    <span class="auth-user">USER</span>
                    <p>Get comprehensive protocol analysis for a specific SIM card across all usage periods.</p>
                    <ul>
                        <li>Returns all usage records for the SIM</li>
                        <li>Protocol breakdown for each usage period</li>
                        <li>Data validation with discrepancy detection</li>
                        <li>Exact usage matching verification</li>
                    </ul>
                </div>

                <div class="endpoint">
                    <span class="method get">GET</span>
                    <code>/api/v1/analytics/protocols/{sim_number}/{usage_record_id}</code>
                    <span class="auth-user">USER</span>
                    <p>Get detailed protocol analysis for a specific SIM usage period with byte-level precision.</p>
                    <ul>
                        <li>Protocol breakdown for specific usage period</li>
                        <li>Category analysis with protocol counts</li>
                        <li>Data integrity validation (exact matching)</li>
                        <li>Discrepancy alerts when data doesn't match</li>
                    </ul>
                </div>
            </div>

            <div class="section">
                <h2>📚 Core API Endpoints</h2>
                
                <h3>System Health</h3>
                <div class="endpoint">
                    <span class="method get">GET</span>
                    <code>/api/v1/health</code>
                    <span class="auth-none">NO AUTH</span>
                    <p>Check API health and version information</p>
                </div>

                <h3>Client Management</h3>
                <div class="endpoint">
                    <span class="method get">GET</span>
                    <code>/api/v1/clients</code>
                    <span class="auth-user">USER</span>
                    <p>List all clients with pagination support</p>
                </div>
                <div class="endpoint">
                    <span class="method post">POST</span>
                    <code>/api/v1/clients</code>
                    <span class="auth-admin">ADMIN</span>
                    <p>Create new client with contact information and service details</p>
                </div>
                <div class="endpoint">
                    <span class="method get">GET</span>
                    <code>/api/v1/clients/{client_id}</code>
                    <span class="auth-user">USER</span>
                    <p>Get detailed client information including assigned SIM cards</p>
                </div>

                <h3>SIM Card Management</h3>
                <div class="endpoint">
                    <span class="method get">GET</span>
                    <code>/api/v1/sims</code>
                    <span class="auth-user">USER</span>
                    <p>List all SIM cards with filtering options by client</p>
                </div>
                <div class="endpoint">
                    <span class="method post">POST</span>
                    <code>/api/v1/sims</code>
                    <span class="auth-admin">ADMIN</span>
                    <p>Create new SIM card assignments (single or bulk)</p>
                </div>

                <h3>Usage Analytics</h3>
                <div class="endpoint">
                    <span class="method get">GET</span>
                    <code>/api/v1/usage</code>
                    <span class="auth-user">USER</span>
                    <p>Get usage records with filtering by client, SIM, or date range</p>
                </div>
                <div class="endpoint">
                    <span class="method get">GET</span>
                    <code>/api/v1/analytics/summary</code>
                    <span class="auth-user">USER</span>
                    <p>Get comprehensive usage analytics and statistics</p>
                </div>
            </div>

            <div class="section">
                <h2>💻 Example Usage</h2>
                
                <h3>Get SIM Protocol Analysis (Python)</h3>
                <pre>
import requests

headers = {
    'X-API-Key': 'your_api_key_here',
    'Content-Type': 'application/json'
}

# Get protocol analysis for specific SIM
response = requests.get(
    '{{ PRODUCTION_DOMAIN }}/api/v1/analytics/protocols/89270277240011345783',
    headers=headers
)

sim_analysis = response.json()
print(f"SIM: {sim_analysis['sim_number']}")
print(f"Client: {sim_analysis['client_name']}")
print(f"Usage Records: {sim_analysis['total_records']}")

# Check data validation
for validation in sim_analysis['data_validation']:
    if validation['discrepancy'] > 0.001:
        print(f"⚠️ Data mismatch detected: {validation['discrepancy']:.3f} GB")
    else:
        print("✅ Protocol data matches usage records exactly")
                </pre>

                <h3>Get Usage Period Analysis (JavaScript)</h3>
                <pre>
const response = await fetch('{{ PRODUCTION_DOMAIN }}/api/v1/analytics/protocols/89270277240011345783/179', {
    headers: {
        'X-API-Key': 'your_api_key_here',
        'Content-Type': 'application/json'
    }
});

const analysis = await response.json();

console.log(`Usage Period: ${analysis.usage_record.date_from} to ${analysis.usage_record.date_to}`);
console.log(`Total Usage: ${analysis.usage_record.total_usage_gb} GB`);
console.log(`Exact Match: ${analysis.exact_match ? '✅' : '⚠️'}`);
console.log(`Categories: ${analysis.category_data.length}`);
console.log(`Protocols: ${analysis.protocol_data.length}`);
                </pre>
            </div>

            <div class="section">
                <h2>🔍 Data Integrity Features</h2>
                <div class="feature-grid">
                    <div class="feature-card">
                        <h3>Exact Usage Matching</h3>
                        <p>Protocol usage data is validated to match recorded usage to the last byte</p>
                    </div>
                    <div class="feature-card">
                        <h3>Discrepancy Detection</h3>
                        <p>Automatic detection and reporting of data mismatches</p>
                    </div>
                    <div class="feature-card">
                        <h3>Per-SIM Analysis</h3>
                        <p>Individual SIM card analysis instead of client aggregation</p>
                    </div>
                    <div class="feature-card">
                        <h3>Usage Period Tracking</h3>
                        <p>Separate analysis for each usage period with date ranges</p>
                    </div>
                </div>
            </div>

            <div class="section">
                <h2>📞 Support & Resources</h2>
                <p><strong>Documentation:</strong> Complete API reference at <code>{{ PRODUCTION_DOMAIN }}/api/v1/docs</code></p>
                <p><strong>Health Check:</strong> <code>{{ PRODUCTION_DOMAIN }}/api/v1/health</code></p>
                <p><strong>Rate Limits:</strong> Configurable per API key (default: 1000 requests/hour)</p>
                <p><strong>Response Format:</strong> JSON with consistent error handling and status codes</p>
            </div>
        </div>
    </body>
    </html>
    """
    
    return render_template_string(
        html_template,
        API_VERSION=API_VERSION,
        PRODUCTION_DOMAIN=PRODUCTION_DOMAIN
    )

if __name__ == '__main__':
    app.run(host='0.0.0.0', port=5001, debug=True)