import asyncio
import sys
import aiopg
import time
import json
from configparser import ConfigParser
from datetime import timezone, timedelta


class TargetPriceFetcher:
    """
    Класс для извлечения данных о ценах из базы данных.

    Логика работы:
    1. По имени биржи находит exchange_id в таблице exchanges
    2. По паре (base_quote) и exchange_id находит symbol_id в таблице symbols
    3. По symbol_id находит group_id в таблице id_group_relation
    4. Извлекает target_price данные по group_id из таблицы target_price
    5. Извлекает book данные по symbol_id из таблицы book_<symbol_id>
    """

    def __init__(self, path_to_config, max_connections=3):
        parser = ConfigParser()
        parser.read(path_to_config)

        self.db_params = {}
        if parser.has_section('postgresql'):
            params = parser.items('postgresql')
            for param in params:
                self.db_params[param[0]] = param[1]

        self.pool = None
        self.max_connections = max_connections

    async def init_pool(self):
        if self.pool is None:
            self.pool = await aiopg.create_pool(**self.db_params, minsize=1, maxsize=self.max_connections)

    async def get_target_prices_by_group(self, group_id):
        """
        Получает target_price записи для заданного group_id и возвращает список
        в формате [{"target_price": value, "timestamp": timestamp}, ...]
        отсортированный по возрастанию timestamp
        """
        query = f"""
            SELECT target_price, EXTRACT(epoch FROM time) as timestamp
            FROM target_price
            WHERE group_id = {group_id}
            ORDER BY time ASC
        """

        result = await self.execute_select(query)

        if not result:
            return []

        target_prices = []
        for row in result:
            row_str = row[0]
            cleaned_str = row_str.strip('()')
            parts = cleaned_str.split(',')

            if len(parts) >= 4:
                try:
                    target_price = float(parts[1])
                    timestamp = float(row[1])

                    target_prices.append({
                        "target_price": target_price,
                        "timestamp": timestamp
                    })
                except (ValueError, IndexError) as e:
                    print(f"Error parsing row {row}: {e}")
                    continue

        return target_prices

    async def get_exchange_id(self, exchange_name):
        """Получает exchange_id по имени биржи"""
        query = f"""
            SELECT exchange_id
            FROM exchanges
            WHERE exchange_name = '{exchange_name}'
        """
        result = await self.execute_select(query)
        return result[0][0] if result else None

    async def get_symbol_id(self, base, quote, exchange_id):
        """Получает symbol_id по base, quote и exchange_id"""
        query = f"""
            SELECT symbol_id
            FROM symbols
            WHERE base = '{base}' AND quote = '{quote}' AND exchange_id = {exchange_id}
        """
        result = await self.execute_select(query)
        return result[0][0] if result else None

    async def get_group_id(self, symbol_id):
        """Получает group_id по symbol_id из таблицы id_group_relation"""
        query = f"""
            SELECT group_id
            FROM id_group_relation
            WHERE symbol_id = {symbol_id}
        """
        result = await self.execute_select(query)
        return result[0][0] if result else None

    async def get_book_by_symbol(self, symbol_id):
        """
        Получает book записи для заданного symbol_id и возвращает список
        в формате [{"bid_price": value, "ask_price": value, "timestamp": timestamp}, ...]
        отсортированный по timestamp
        """
        query = f"""
            SELECT bid_price, ask_price, EXTRACT(epoch FROM time) as timestamp
            FROM book_{symbol_id}
            ORDER BY time ASC
        """

        result = await self.execute_select(query)

        if not result:
            return []

        book_data = []
        for row in result:
            bid_price = float(row[0])
            ask_price = float(row[1])
            timestamp = float(row[2])

            book_data.append({
                "bid_price": bid_price,
                "ask_price": ask_price,
                "timestamp": timestamp
            })

        return book_data

    async def execute_select(self, query):
        try:
            async with self.pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    await cursor.execute(query)
                    return await cursor.fetchall()
        except Exception as e:
            print(f'[EXCEPTION] execute_select: {e}')
            return []

    async def close_connection(self):
        if self.pool:
            self.pool.close()
            await self.pool.wait_closed()
            self.pool = None


async def main():
    if len(sys.argv) != 3:
        print("Usage: python target_price_fetcher.py <symbol_pair> <exchange_name>")
        print("Example: python target_price_fetcher.py CHEX_USDT binance")
        sys.exit(1)

    symbol_pair = sys.argv[1]
    exchange_name = sys.argv[2]

    if '_' not in symbol_pair:
        print("Error: Symbol pair must be in format BASE_QUOTE (e.g., CHEX_USDT)")
        sys.exit(1)

    base, quote = symbol_pair.split('_', 1)

    fetcher = TargetPriceFetcher('/home/ubuntu/app/database.ini')
    await fetcher.init_pool()

    try:
        exchange_id = await fetcher.get_exchange_id(exchange_name)
        if exchange_id is None:
            print(f"Error: Exchange '{exchange_name}' not found")
            return

        print(f"Found exchange_id: {exchange_id} for exchange '{exchange_name}'")

        symbol_id = await fetcher.get_symbol_id(base, quote, exchange_id)
        if symbol_id is None:
            print(f"Error: Symbol '{base}/{quote}' not found for exchange '{exchange_name}'")
            return

        print(f"Found symbol_id: {symbol_id} for symbol '{base}/{quote}'")

        group_id = await fetcher.get_group_id(symbol_id)
        if group_id is None:
            print(f"Error: Group not found for symbol_id {symbol_id}")
            return

        print(f"Found group_id: {group_id} for symbol_id {symbol_id}")

        target_prices = await fetcher.get_target_prices_by_group(group_id)

        tp_filename = f"tp_{symbol_pair}_{exchange_name}.json"
        with open(tp_filename, 'w', encoding='utf-8') as f:
            json.dump(target_prices, f, indent=2, ensure_ascii=False)

        print(f"Target price данные сохранены в файл: {tp_filename}")
        print(f"Всего target price записей: {len(target_prices)}")

        book_data = await fetcher.get_book_by_symbol(symbol_id)

        book_filename = f"book_{symbol_pair}_{exchange_name}.json"
        with open(book_filename, 'w', encoding='utf-8') as f:
            json.dump(book_data, f, indent=2, ensure_ascii=False)

        print(f"Book данные сохранены в файл: {book_filename}")
        print(f"Всего book записей: {len(book_data)}")

    finally:
        await fetcher.close_connection()


if __name__ == "__main__":
    asyncio.run(main())