import redis, os, ConnectionPool, json, datetime, logging
from mysql.connector import Error
from dotenv import load_dotenv

load_dotenv("/opt/.env")

REDIS_PASS      = os.environ.get("REDIS_PASS")
REDIS_HOST      = '127.0.0.1'
REDIS_CHANNELS  = ('test.transactions.*', 'test.orders.*')
READY_FILE      = '/run/razgar/bee-data-handler.ready'

DB_CONFIG = {
    'host': os.environ.get("RESULTS_DB_HOST"),
    'database': os.environ.get("RESULTS_DB_NAME"),
    'user': os.environ.get("RESULTS_DB_USERNAME"),
    'password': os.environ.get("RESULTS_DB_PASS"),
    'table': "bee_transaction_log"
}

ORDER_TYPES = {
    'limit': 1,
    'market': 2,
    'stop_limit': 3,
    'stop_market': 4,
    'take_limit': 5,
    'take_market': 6,
    'take_maket': 6,
}

# Set up logging to a file
logging.basicConfig(
    filename='/var/log/bee_data_handler.log',
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s'
)

def add_transaction_to_db(pool, transaction):
    """
    Function to add the transaction to the database.
    """
    try:
        # Get a connection from the pool
        conn = pool.get_connection()

        cursor = conn.cursor()
        # Do some database operations
        query = f"""INSERT INTO {DB_CONFIG['table']} (`transaction_id`, `test_id`, `timestamp`, `instrument_id`, `order_name`, `side`, `amount`, `position`, `price`, `pnl`, `equity`, `average_price`, `type`, `fee`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"""
        params = (transaction['id'], transaction['test_id'], transaction['timestamp'], transaction['instrument_id'], transaction['order_name'], transaction['side'], transaction['amount'], transaction['position'], transaction['price'], transaction['pnl'], transaction['equity'], transaction['average_price'], transaction['type'], transaction['fee'])
        cursor.execute(query, params)
        result = conn.commit()

        logging.info(f"{datetime.datetime.now()} | Transaction logged in DB: {result}")

    except Error as e:
        logging.error(f"{datetime.datetime.now()} | Error while connecting to MariaDB: {e}")
    finally:
        # Release the connection back to the pool
        pool.release_connection(conn)


def upsert_order_to_db(pool, order):
    """Persist one Redis order event under its stable BEE-generated ID."""
    conn = None
    try:
        conn = pool.get_connection()
        cursor = conn.cursor()
        order_type = order['type']
        if isinstance(order_type, str):
            order_type = ORDER_TYPES.get(order_type, 0)
        query = """INSERT INTO `bee_orders`
            (`source_order_id`, `label`, `side`, `test_id`, `instrument_id`,
             `amount`, `creation_timestamp`, `price`, `status`, `type`)
            VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)
            ON DUPLICATE KEY UPDATE
                `label` = VALUES(`label`),
                `side` = VALUES(`side`),
                `test_id` = VALUES(`test_id`),
                `instrument_id` = VALUES(`instrument_id`),
                `amount` = VALUES(`amount`),
                `creation_timestamp` = VALUES(`creation_timestamp`),
                `price` = VALUES(`price`),
                `status` = VALUES(`status`),
                `type` = VALUES(`type`)"""
        params = (
            order['id'], order['label'], order['side'], order['test_id'],
            order['instrument_id'], order['amount'], order['creation_timestamp'],
            order['price'], order['status'], order_type,
        )
        cursor.execute(query, params)
        conn.commit()
        logging.info(f"{datetime.datetime.now()} | Order persisted: {order['id']}")
    except Error as e:
        logging.error(f"{datetime.datetime.now()} | Error while persisting order: {e}")
    finally:
        if conn is not None:
            pool.release_connection(conn)


def listen_to_redis():
    """
    Function to listen to a Redis Pub/Sub channel and write messages to MariaDB.
    """

    # Initialize the connection pool
    pool = ConnectionPool.ConnectionPool(
        pool_name="transactions_pool",
        pool_size=5,  # Max 10 connections
        host=DB_CONFIG['host'],
        user=DB_CONFIG['user'],
        password=DB_CONFIG['password'],
        database=DB_CONFIG['database']
    )

    try:
        # Use 'with' to ensure that the Redis connection is properly closed
        with redis.Redis(
            host=REDIS_HOST,
            password=REDIS_PASS
        ) as redis_client:
            # Subscribe to a Redis channel
            pubsub = redis_client.pubsub()
            pubsub.psubscribe(*REDIS_CHANNELS)
            os.makedirs(os.path.dirname(READY_FILE), exist_ok=True)
            with open(READY_FILE, 'w', encoding='utf-8') as marker:
                marker.write('ready\n')

            logging.info(f"{datetime.datetime.now()} | Listening for messages on Redis channels: {REDIS_CHANNELS}")

            # Infinite loop to listen to messages
            for message in pubsub.listen():
                # Redis sends a 'subscribe' message when you first subscribe. Ignore it.
                if message['type'] == 'pmessage':
                    # Get the actual message data
                    message_data = json.loads(message['data'].decode("utf-8"))
                    logging.info(f"{datetime.datetime.now()} | Received message: {message_data}")

                    channel = message['channel'].decode("utf-8")
                    if channel.startswith('test.orders.'):
                        upsert_order_to_db(pool, message_data)
                    else:
                        add_transaction_to_db(pool, message_data)

    except redis.ConnectionError as e:
        logging.error(f"{datetime.datetime.now()} | Redis connection error: {e}")

    except KeyboardInterrupt:
        logging.error(f"{datetime.datetime.now()} | Script interrupted.")

    finally:
        try:
            os.unlink(READY_FILE)
        except FileNotFoundError:
            pass
        logging.info(f"{datetime.datetime.now()} | Shutting down listener.")
        del pool

if __name__ == "__main__":
    listen_to_redis()