database.py

import sqlite3
import json
from datetime import datetime, timedelta
import hashlib
import secrets

DB_PATH = "api_management.db"

def init_database():
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # Users table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT UNIQUE NOT NULL,
            telegram TEXT UNIQUE NOT NULL,
            full_name TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            is_active BOOLEAN DEFAULT 1,
            notes TEXT
        )
    ''')
    
    # API Keys table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS api_keys (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER,
            api_key TEXT UNIQUE NOT NULL,
            secret_key TEXT UNIQUE NOT NULL,
            name TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            expires_at TIMESTAMP,
            is_active BOOLEAN DEFAULT 1,
            rate_limit_per_minute INTEGER DEFAULT 30,
            daily_limit INTEGER DEFAULT 1000,
            FOREIGN KEY (user_id) REFERENCES users (id)
        )
    ''')
    
    # Check if daily_limit column exists
    cursor.execute("PRAGMA table_info(api_keys)")
    columns = [column[1] for column in cursor.fetchall()]
    if 'daily_limit' not in columns:
        cursor.execute('ALTER TABLE api_keys ADD COLUMN daily_limit INTEGER DEFAULT 1000')
    
    # Settings table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS settings (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            key TEXT UNIQUE NOT NULL,
            value TEXT,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # Insert default settings if not exists
    default_settings = {
        'developer': '@Xalpha90',
        'website': 'https://xalpha90.com',
        'telegram': 'https://t.me/Xalpha90',
        'company': 'Xalpha Tech'
    }
    
    for key, value in default_settings.items():
        cursor.execute('SELECT id FROM settings WHERE key = ?', (key,))
        if not cursor.fetchone():
            cursor.execute('INSERT INTO settings (key, value) VALUES (?, ?)', (key, value))
    
    # Request logs table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS request_logs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            api_key_id INTEGER,
            endpoint TEXT,
            query TEXT,
            ip_address TEXT,
            timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            response_time INTEGER,
            status_code INTEGER,
            FOREIGN KEY (api_key_id) REFERENCES api_keys (id)
        )
    ''')
    
    # Rate limiting table
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS rate_limits (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            api_key_id INTEGER,
            minute_timestamp INTEGER,
            request_count INTEGER DEFAULT 0,
            FOREIGN KEY (api_key_id) REFERENCES api_keys (id)
        )
    ''')
    
    conn.commit()
    conn.close()

def get_db_connection():
    conn = sqlite3.connect(DB_PATH)
    conn.row_factory = sqlite3.Row
    return conn

def get_setting(key):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT value FROM settings WHERE key = ?', (key,))
    result = cursor.fetchone()
    conn.close()
    return result['value'] if result else None

def get_all_settings():
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute('SELECT key, value FROM settings')
    results = cursor.fetchall()
    conn.close()
    return {row['key']: row['value'] for row in results}

def get_daily_usage(key_id: int):
    """Get today's usage count for a key"""
    conn = get_db_connection()
    cursor = conn.cursor()
    
    # Get today's date in the same format as stored in DB
    today = datetime.now().strftime('%Y-%m-%d')
    
    cursor.execute('''
        SELECT COUNT(*) as count FROM request_logs 
        WHERE api_key_id = ? AND date(timestamp) = ?
    ''', (key_id, today))
    
    result = cursor.fetchone()
    conn.close()
    
    return result['count'] if result else 0

# Initialize database
init_database()