import logging
from datetime import datetime, timedelta, timezone
from sqlalchemy import text
from app.models.connection import get_async_db
from collections import defaultdict

RATE_PLAN_ID = 120
HOTEL_ID = 122
SUB_HOTELS = (123, 124, 125)

log = logging.getLogger(__name__)

# CREATE TABLE daily_room_type_features (
#     id BIGINT AUTO_INCREMENT PRIMARY KEY,

#     date DATE NOT NULL,
#     sub_hotel_id INT NOT NULL,
#     room_type_id INT NOT NULL,
#     rate_plan_id INT NOT NULL,

#     room_type_price DECIMAL(10,2),

#     total_inventory INT,
#     booked_inventory INT,
#     Occupancy_Rate DECIMAL(6,4),

#     Day_of_Week TINYINT,
#     Month TINYINT,
#     Week_of_Year TINYINT,
#     Is_Weekend TINYINT,

#     Bookings_Last_1_Day INT,
#     Bookings_Last_3_Days INT,
#     Bookings_Last_7_Days INT,
#     Bookings_Last_14_Days INT,

#     Occupancy_Lag_1 DECIMAL(6,4),
#     Occupancy_Lag_7 DECIMAL(6,4),
#     Occupancy_Lag_14 DECIMAL(6,4),
#     Occupancy_Lag_30 DECIMAL(6,4),

#     created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

#     -- Prevent duplicate daily inserts
#     UNIQUE KEY uniq_daily_feature (
#         date,
#         sub_hotel_id,
#         room_type_id,
#         rate_plan_id
#     ),

#     -- Helpful indexes for ML queries
#     INDEX idx_date (date),
#     INDEX idx_room_type (room_type_id),
#     INDEX idx_sub_hotel (sub_hotel_id)
# );


def parse_ids(id_string):
    if not id_string:
        return []
    return [int(x.strip()) for x in id_string.split(",") if x.strip()]

def safe_float(val):
    if val is None:
        return None
    return round(val, 4)

def compute_time_features(date_obj):
    return {
        "Day_of_Week": date_obj.weekday(),
        "Month": date_obj.month,
        "Week_of_Year": date_obj.isocalendar()[1],
        "Is_Weekend": 1 if date_obj.weekday() >= 5 else 0
    }

async def fetch_30_days_data(db, start_date, end_date):
    query = text("""
        SELECT 
            date,
            sub_hotel_id,
            total_active_rooms,
            total_rented
        FROM hotel_future_occupancies
        WHERE date BETWEEN :start_date AND :end_date
        AND hotel_id = :hotel_id
        AND sub_hotel_id IN :sub_hotels
    """)

    result = await db.execute(query, {
        "start_date": start_date,
        "end_date": end_date,
        "hotel_id": HOTEL_ID,
        "sub_hotels": SUB_HOTELS
    })

    return result.fetchall()

async def fetch_products(db):
    query = text("""
        SELECT product_id, sub_hotel_id, type_id AS room_type_id
        FROM products
        WHERE hotel_id = :hotel_id
        AND sub_hotel_id IN :sub_hotels
    """)

    result = await db.execute(query, {
        "hotel_id": HOTEL_ID,
        "sub_hotels": SUB_HOTELS
    })

    return result.fetchall()

async def fetch_prices(db, target_date):
    query = text("""
        SELECT date, sub_hotel_id, room_type_id, room_type_price
        FROM hotel_price_122
        WHERE date = :target_date
        AND rate_plan_id = :rate_plan_id
        AND channel_id = 1
        AND sub_hotel_id IN :sub_hotels
    """)

    result = await db.execute(query, {
        "target_date": target_date,
        "rate_plan_id": RATE_PLAN_ID,
        "sub_hotels": SUB_HOTELS
    })

    return result.fetchall()

async def insert_daily_features(db, records):
    if not records:
        return

    query = text("""
        INSERT INTO daily_room_type_features (
            date,
            sub_hotel_id,
            room_type_id,
            rate_plan_id,
            room_type_price,
            total_inventory,
            booked_inventory,
            Occupancy_Rate,
            Day_of_Week,
            Month,
            Week_of_Year,
            Is_Weekend,
            Bookings_Last_1_Day,
            Bookings_Last_3_Days,
            Bookings_Last_7_Days,
            Bookings_Last_14_Days,
            Occupancy_Lag_1,
            Occupancy_Lag_7,
            Occupancy_Lag_14,
            Occupancy_Lag_30
        )
        VALUES (
            :date,
            :sub_hotel_id,
            :room_type_id,
            :rate_plan_id,
            :room_type_price,
            :total_inventory,
            :booked_inventory,
            :Occupancy_Rate,
            :Day_of_Week,
            :Month,
            :Week_of_Year,
            :Is_Weekend,
            :Bookings_Last_1_Day,
            :Bookings_Last_3_Days,
            :Bookings_Last_7_Days,
            :Bookings_Last_14_Days,
            :Occupancy_Lag_1,
            :Occupancy_Lag_7,
            :Occupancy_Lag_14,
            :Occupancy_Lag_30
        )
        ON DUPLICATE KEY UPDATE
            room_type_price = VALUES(room_type_price),
            total_inventory = VALUES(total_inventory),
            booked_inventory = VALUES(booked_inventory),
            Occupancy_Rate = VALUES(Occupancy_Rate),
            Day_of_Week = VALUES(Day_of_Week),
            Month = VALUES(Month),
            Week_of_Year = VALUES(Week_of_Year),
            Is_Weekend = VALUES(Is_Weekend),
            Bookings_Last_1_Day = VALUES(Bookings_Last_1_Day),
            Bookings_Last_3_Days = VALUES(Bookings_Last_3_Days),
            Bookings_Last_7_Days = VALUES(Bookings_Last_7_Days),
            Bookings_Last_14_Days = VALUES(Bookings_Last_14_Days),
            Occupancy_Lag_1 = VALUES(Occupancy_Lag_1),
            Occupancy_Lag_7 = VALUES(Occupancy_Lag_7),
            Occupancy_Lag_14 = VALUES(Occupancy_Lag_14),
            Occupancy_Lag_30 = VALUES(Occupancy_Lag_30)
    """)

    await db.execute(query, records)
    await db.commit()

async def save_daily_data():
    try:
        target_date = (datetime.now(tz=timezone.utc) - timedelta(days=1)).date()
        start_date = target_date - timedelta(days=30)

        async with get_async_db() as db:
            occ_rows = await fetch_30_days_data(db, start_date, target_date)
            product_rows = await fetch_products(db)
            price_rows = await fetch_prices(db, target_date)

            # product_id → room_type
            product_map = {p.product_id: p.room_type_id for p in product_rows}

            # 📊 store daily metrics per (date, sub_hotel, room_type)
            daily_metrics = defaultdict(lambda: {
                "total": 0,
                "booked": 0
            })

            for row in occ_rows:
                active = set(parse_ids(row.total_active_rooms))
                rented = set(parse_ids(row.total_rented))

                for pid in active:
                    room_type = product_map.get(pid)
                    if not room_type:
                        continue

                    key = (row.date, row.sub_hotel_id, room_type)

                    daily_metrics[key]["total"] += 1
                    if pid in rented:
                        daily_metrics[key]["booked"] += 1

            # 📌 build index for easy lookup
            def get_metrics(date, sub_hotel, room_type):
                data = daily_metrics.get((date, sub_hotel, room_type))
                if not data or data["total"] == 0:
                    return 0, 0, 0
                total = data["total"]
                booked = data["booked"]
                occ = booked / total if total else 0
                return total, booked, occ

            # price map
            price_map = {
                (p.sub_hotel_id, p.room_type_id): p.room_type_price
                for p in price_rows
            }

            output = []

            for (date, sub_hotel_id, room_type_id), data in daily_metrics.items():
                if date != target_date:
                    continue

                total = data["total"]
                booked = data["booked"]
                occ_rate = booked / total if total else 0

                # 📊 Booking aggregates
                def sum_bookings(days):
                    total_bookings = 0
                    for d in range(1, days + 1):
                        _, b, _ = get_metrics(
                            target_date - timedelta(days=d),
                            sub_hotel_id,
                            room_type_id
                        )
                        total_bookings += b
                    return total_bookings

                bookings_1 = sum_bookings(1)
                bookings_3 = sum_bookings(3)
                bookings_7 = sum_bookings(7)
                bookings_14 = sum_bookings(14)

                # 📉 Lag features
                _, _, lag_1 = get_metrics(target_date - timedelta(days=1), sub_hotel_id, room_type_id)
                _, _, lag_7 = get_metrics(target_date - timedelta(days=7), sub_hotel_id, room_type_id)
                _, _, lag_14 = get_metrics(target_date - timedelta(days=14), sub_hotel_id, room_type_id)
                _, _, lag_30 = get_metrics(target_date - timedelta(days=30), sub_hotel_id, room_type_id)

                record = {
                    "date": target_date,
                    "sub_hotel_id": sub_hotel_id,
                    "room_type_id": room_type_id,
                    "rate_plan_id": RATE_PLAN_ID,
                    "room_type_price": price_map.get((sub_hotel_id, room_type_id)),

                    "total_inventory": total,
                    "booked_inventory": booked,
                    "Occupancy_Rate": safe_float(occ_rate),

                    **compute_time_features(target_date),

                    "Bookings_Last_1_Day": bookings_1,
                    "Bookings_Last_3_Days": bookings_3,
                    "Bookings_Last_7_Days": bookings_7,
                    "Bookings_Last_14_Days": bookings_14,

                    "Occupancy_Lag_1": safe_float(lag_1),
                    "Occupancy_Lag_7": safe_float(lag_7),
                    "Occupancy_Lag_14": safe_float(lag_14),
                    "Occupancy_Lag_30": safe_float(lag_30),
                }

                output.append(record)

            # print(len(output))
            # for row in output:
            #     if row['room_type_id'] == 305:
            #         print(row)

            await insert_daily_features(db, output)

            log.warning(f"Data saved successfully for date: {target_date}")

            return None
    except Exception as e:
        log.error(f"Error: {e} for date: {target_date}")
        return None