import os
import sys
from copy import copy
from shutil import copy2

from openpyxl import load_workbook


PEXELS_IDS = {
    'cafe': [302899, 312418, 2615323, 1850595, 260922, 1813504, 374885, 376464, 1639557, 1438672],
    'coffee': [302899, 312418, 2615323, 374885, 1640777, 4109743],
    'espresso': [302899, 312418, 991207],
    'latte': [312418, 302899, 2615323],
    'pastry': [376464, 1639557, 291528, 374885],
    'burger': [1639557, 1639562, 3616956],
    'pasta': [1438672, 1437587, 2664216],
    'grill': [1639557, 2664216, 3616956],
    'cake': [291528, 376464, 1854652],
    'dessert': [291528, 376464, 1854652, 374885],
    'bakery': [376464, 291528, 1854652, 374885],
    'sushi': [858501, 1148086, 2323398],
    'ramen': [1907244, 3607876, 2664216],
    'curry': [2673353, 2664216, 3184183],
    'salad': [1640777, 1279330, 1640777],
    'acai': [1640777, 1279330, 1640777],
    'vegan': [1640777, 1279330, 1279330],
    'hotpot': [3184183, 2673353, 2664216],
    'kebab': [3184183, 2673353, 1639562],
    'rice': [2673353, 2664216, 1639557],
    'noodle': [1907244, 3607876, 2664216],
    'dumpling': [3184183, 2673353, 2664216],
    'tea': [302899, 312418, 374885],
    'fried': [1639557, 3616956, 2664216],
    'chicken': [1639557, 3616956, 1639562],
    'smoothie': [1640777, 4109743, 302899],
    'wrap': [1640777, 1279330, 1639557],
    'soup': [3184183, 2673353, 2664216],
}


def pexels_url(keyword, seed, w=1600, h=1200):
    key = 'cafe'
    lower = (keyword or '').lower()
    for candidate in PEXELS_IDS:
        if candidate in lower:
            key = candidate
            break
    pool = PEXELS_IDS[key]
    photo_id = pool[seed % len(pool)]
    return f'https://images.pexels.com/photos/{photo_id}/pexels-photo-{photo_id}.jpeg?auto=compress&cs=tinysrgb&w={w}&h={h}&fit=crop'


def infer_theme(merchant_name, category):
    name = (merchant_name or '').lower()
    if any(k in name for k in ['coffee', 'cafe', 'espresso', 'latte', 'toast', 'barista', 'bean', 'brew', 'kopi', 'bakery']):
        return 'Cafes & Coffee'
    if any(k in name for k in ['sushi', 'ramen', 'izakaya', 'udon', 'yakitori', 'donburi', 'omakase']):
        return 'Japanese Food'
    if any(k in name for k in ['korean', 'kimchi', 'bbq', 'bibimbap']):
        return 'Korean'
    if any(k in name for k in ['thai', 'tom yum', 'basil', 'mango sticky']):
        return 'Thai'
    if any(k in name for k in ['indian', 'biryani', 'tandoori', 'masala', 'naan']):
        return 'Indian'
    if any(k in name for k in ['halal', 'nasi', 'satay', 'mee rebus', 'penyet']):
        return 'Malay / Halal'
    if any(k in name for k in ['hotpot', 'steamboat', 'mala']):
        return 'Hotpot'
    if any(k in name for k in ['acai', 'salad', 'vegan', 'grain', 'smoothie']):
        return 'Health (Vegan / Acai / Clean eating)'
    if any(k in name for k in ['dessert', 'bakery', 'cake', 'patisserie', 'pastry']):
        return 'Desserts / Bakery'
    return category or 'Others (F&B)'


def extra_products(theme, merchant_name):
    products = {
        'Cafes & Coffee': [
            {'name': f'{merchant_name} Signature Toast', 'desc': f'House toast at {merchant_name} layered with premium toppings for a warm and satisfying cafe bite.', 'price': 15, 'cat': 'Brunch', 'keyword': 'pastry'},
            {'name': 'Smoked Salmon Bagel', 'desc': 'A toasted bagel layered with smoked salmon, cream cheese, and crisp greens for a premium all-day cafe bite.', 'price': 17, 'cat': 'Brunch', 'keyword': 'pastry'},
            {'name': 'Berry Yogurt Parfait', 'desc': 'Layers of yogurt, house granola, and mixed berries come together for a light and refreshing breakfast option.', 'price': 11, 'cat': 'Dessert', 'keyword': 'dessert'},
            {'name': 'Cold Brew Coffee', 'desc': 'Slow-steeped cold brew served chilled with a smooth body and low acidity for a bold but refreshing finish.', 'price': 8, 'cat': 'Beverage', 'keyword': 'coffee'},
            {'name': 'Chocolate Muffin', 'desc': 'A moist chocolate muffin with a rich cocoa profile, baked fresh for a comforting sweet treat.', 'price': 6, 'cat': 'Pastry', 'keyword': 'pastry'},
        ],
        'Western / Fusion': [
            {'name': f'{merchant_name} Signature Pasta', 'desc': f'A house-style pasta at {merchant_name} finished with a rich sauce and balanced garnish for a hearty western main.', 'price': 24, 'cat': 'Pasta', 'keyword': 'pasta'},
            {'name': 'Grilled Chicken Chop', 'desc': 'Juicy chicken chop grilled to order and served with sides for a familiar western comfort plate.', 'price': 21, 'cat': 'Main', 'keyword': 'grill'},
            {'name': 'Classic Beef Burger', 'desc': 'A stacked beef burger with crisp greens, sauce, and fries for a satisfying all-day favourite.', 'price': 20, 'cat': 'Main', 'keyword': 'burger'},
            {'name': 'Truffle Fries Basket', 'desc': 'Crisp fries tossed with truffle seasoning and served hot as a savoury sharing snack for the table.', 'price': 12, 'cat': 'Starter', 'keyword': 'fried'},
            {'name': 'Buttermilk Waffles', 'desc': 'Golden waffles served warm with syrup and cream, created as a sweet western-style dessert plate.', 'price': 14, 'cat': 'Dessert', 'keyword': 'dessert'},
        ],
        'Chinese Cuisine': [
            {'name': f'{merchant_name} Signature Fried Rice', 'desc': f'Wok-fried rice at {merchant_name} tossed with aromatics, egg, and savoury toppings for a comforting staple.', 'price': 18, 'cat': 'Rice', 'keyword': 'rice'},
            {'name': 'Chicken Dumpling Basket', 'desc': 'A basket of juicy dumplings served warm with dipping sauce as a classic sharing starter.', 'price': 14, 'cat': 'Starter', 'keyword': 'dumpling'},
            {'name': 'Braised Beef Noodles', 'desc': 'Springy noodles in a savoury braised broth with tender beef for a deeply satisfying bowl.', 'price': 19, 'cat': 'Noodles', 'keyword': 'noodle'},
            {'name': 'Mapo Tofu Bowl', 'desc': 'Silken tofu simmered in a bold savoury sauce and served with rice for a comforting meal option.', 'price': 17, 'cat': 'Main', 'keyword': 'curry'},
            {'name': 'Chrysanthemum Tea', 'desc': 'A lightly floral tea served chilled to refresh the palate alongside richer dishes.', 'price': 5, 'cat': 'Beverage', 'keyword': 'tea'},
        ],
        'Japanese Food': [
            {'name': f'{merchant_name} Signature Donburi', 'desc': f'A generous donburi bowl from {merchant_name} topped with premium ingredients over warm rice.', 'price': 22, 'cat': 'Rice Bowl', 'keyword': 'rice'},
            {'name': 'Salmon Sushi Set', 'desc': 'Fresh salmon sushi arranged as a balanced set for guests who enjoy a clean Japanese flavour profile.', 'price': 24, 'cat': 'Sushi', 'keyword': 'sushi'},
            {'name': 'Tonkotsu Ramen', 'desc': 'A rich ramen broth with noodles and toppings, served hot for a deeply comforting meal.', 'price': 20, 'cat': 'Noodles', 'keyword': 'ramen'},
            {'name': 'Crispy Tempura Platter', 'desc': 'Lightly battered tempura served crisp and golden with dipping sauce for sharing.', 'price': 18, 'cat': 'Starter', 'keyword': 'fried'},
            {'name': 'Matcha Mochi Dessert', 'desc': 'A sweet matcha-forward dessert with chewy mochi texture for a gentle Japanese finish.', 'price': 11, 'cat': 'Dessert', 'keyword': 'dessert'},
        ],
        'Korean': [
            {'name': f'{merchant_name} Signature Bibimbap', 'desc': f'A colourful bibimbap bowl from {merchant_name} with rice, vegetables, protein, and house sauce.', 'price': 20, 'cat': 'Rice Bowl', 'keyword': 'rice'},
            {'name': 'Kimchi Stew', 'desc': 'A warming stew with kimchi and savoury depth, served bubbling hot for a comforting Korean meal.', 'price': 18, 'cat': 'Soup', 'keyword': 'soup'},
            {'name': 'Soy Garlic Chicken', 'desc': 'Crisp chicken glazed in soy garlic sauce for a sweet-savoury Korean crowd favourite.', 'price': 19, 'cat': 'Main', 'keyword': 'chicken'},
            {'name': 'Tteokbokki Bites', 'desc': 'Chewy rice cakes coated in a bold Korean sauce and served as a popular snack to share.', 'price': 14, 'cat': 'Starter', 'keyword': 'fried'},
            {'name': 'Korean Barley Tea', 'desc': 'A roasted barley tea served chilled for a mellow and refreshing beverage pairing.', 'price': 5, 'cat': 'Beverage', 'keyword': 'tea'},
        ],
        'Thai': [
            {'name': f'{merchant_name} Signature Pad Thai', 'desc': f'A wok-tossed pad thai from {merchant_name} with balanced sweet, savoury, and tangy notes.', 'price': 19, 'cat': 'Noodles', 'keyword': 'noodle'},
            {'name': 'Tom Yum Seafood Soup', 'desc': 'A bright, spicy soup with seafood and herbs, created as a bold Thai signature bowl.', 'price': 18, 'cat': 'Soup', 'keyword': 'soup'},
            {'name': 'Basil Chicken Rice', 'desc': 'Fragrant basil chicken served over rice for a punchy Thai comfort plate.', 'price': 17, 'cat': 'Rice', 'keyword': 'chicken'},
            {'name': 'Mango Sticky Rice', 'desc': 'A classic Thai dessert with sweet mango and coconut sticky rice for a tropical finish.', 'price': 11, 'cat': 'Dessert', 'keyword': 'dessert'},
            {'name': 'Thai Milk Tea', 'desc': 'Creamy Thai milk tea served iced as a rich and refreshing beverage choice.', 'price': 6, 'cat': 'Beverage', 'keyword': 'tea'},
        ],
        'Indian': [
            {'name': f'{merchant_name} Signature Curry', 'desc': f'A rich house curry from {merchant_name} simmered with spices and served with hearty sides.', 'price': 21, 'cat': 'Main', 'keyword': 'curry'},
            {'name': 'Lamb Biryani', 'desc': 'Fragrant biryani rice layered with tender lamb and spices for a complete Indian main.', 'price': 22, 'cat': 'Rice', 'keyword': 'rice'},
            {'name': 'Masala Dosa', 'desc': 'A crisp dosa wrapped around spiced potato filling, served with chutneys and sambar.', 'price': 14, 'cat': 'Snack', 'keyword': 'fried'},
            {'name': 'Tandoori Chicken Platter', 'desc': 'A smoky chicken platter marinated in spices and roasted for a bold Indian flavour.', 'price': 20, 'cat': 'Grill', 'keyword': 'chicken'},
            {'name': 'Teh Tarik', 'desc': 'Pulled milk tea served hot or iced as a familiar South Asian beverage companion.', 'price': 5, 'cat': 'Beverage', 'keyword': 'tea'},
        ],
        'Malay / Halal': [
            {'name': f'{merchant_name} Signature Nasi Lemak', 'desc': f'A house nasi lemak at {merchant_name} with fragrant rice, sambal, and classic accompaniments.', 'price': 16, 'cat': 'Rice', 'keyword': 'rice'},
            {'name': 'Satay Platter', 'desc': 'Skewered satay grilled and served with sauce for a popular halal sharing appetiser.', 'price': 17, 'cat': 'Starter', 'keyword': 'kebab'},
            {'name': 'Mee Rebus Bowl', 'desc': 'A hearty noodle bowl in thick savoury gravy, topped for a comforting local meal.', 'price': 14, 'cat': 'Noodles', 'keyword': 'noodle'},
            {'name': 'Ayam Penyet Set', 'desc': 'Crispy smashed chicken with rice and sambal, plated as a satisfying halal staple.', 'price': 18, 'cat': 'Main', 'keyword': 'chicken'},
            {'name': 'Bandung Soda', 'desc': 'A chilled rose syrup beverage with a creamy finish for a sweet local refreshment.', 'price': 5, 'cat': 'Beverage', 'keyword': 'tea'},
        ],
        'Desserts / Bakery': [
            {'name': f'{merchant_name} Signature Croissant', 'desc': f'A buttery croissant at {merchant_name}, baked to a flaky finish and served fresh.', 'price': 7, 'cat': 'Pastry', 'keyword': 'pastry'},
            {'name': 'Strawberry Tart', 'desc': 'A fruit tart with smooth filling and fresh strawberries for a bright bakery dessert.', 'price': 9, 'cat': 'Dessert', 'keyword': 'cake'},
            {'name': 'Cinnamon Roll', 'desc': 'A soft roll layered with cinnamon sugar and glaze, baked for a warm comforting treat.', 'price': 8, 'cat': 'Pastry', 'keyword': 'bakery'},
            {'name': 'Chocolate Layer Cake', 'desc': 'A rich chocolate cake slice with soft layers and a smooth finish, ideal for sharing or solo indulgence.', 'price': 10, 'cat': 'Dessert', 'keyword': 'cake'},
            {'name': 'Vanilla Eclair', 'desc': 'A delicate pastry filled with vanilla cream for a light but satisfying sweet option.', 'price': 8, 'cat': 'Pastry', 'keyword': 'dessert'},
        ],
        'Hotpot': [
            {'name': f'{merchant_name} Signature Broth', 'desc': f'A signature broth from {merchant_name} prepared to anchor a warming hotpot meal.', 'price': 26, 'cat': 'Broth Base', 'keyword': 'hotpot'},
            {'name': 'Wagyu Hotpot Platter', 'desc': 'Thinly sliced wagyu arranged for quick cooking in a rich bubbling hotpot broth.', 'price': 36, 'cat': 'Premium Meat', 'keyword': 'hotpot'},
            {'name': 'Fish Paste Assortment', 'desc': 'A selection of handmade fish paste pieces created to add texture and flavour to hotpot.', 'price': 18, 'cat': 'Side', 'keyword': 'hotpot'},
            {'name': 'Vegetable Basket', 'desc': 'A fresh basket of leafy greens, mushrooms, and vegetables prepared for the pot.', 'price': 15, 'cat': 'Vegetable', 'keyword': 'salad'},
            {'name': 'Handmade Noodles', 'desc': 'Fresh noodles designed to soak up broth and round out a complete hotpot meal.', 'price': 8, 'cat': 'Noodles', 'keyword': 'noodle'},
        ],
        'Health (Vegan / Acai / Clean eating)': [
            {'name': f'{merchant_name} Signature Acai Bowl', 'desc': f'A colourful acai bowl from {merchant_name} topped with fruits, seeds, and house granola.', 'price': 15, 'cat': 'Bowl', 'keyword': 'acai'},
            {'name': 'Quinoa Salad Plate', 'desc': 'A balanced salad with quinoa, greens, and vegetables designed for a clean and filling meal.', 'price': 16, 'cat': 'Salad', 'keyword': 'salad'},
            {'name': 'Grilled Tofu Protein Bowl', 'desc': 'A protein-focused bowl with tofu, grains, and vegetables for a wholesome plant-forward option.', 'price': 17, 'cat': 'Bowl', 'keyword': 'vegan'},
            {'name': 'Green Detox Smoothie', 'desc': 'A fresh smoothie blended with greens and fruit for a bright and energising drink.', 'price': 9, 'cat': 'Beverage', 'keyword': 'smoothie'},
            {'name': 'Avocado Wrap', 'desc': 'A soft wrap filled with avocado and crunchy vegetables for an easy healthy lunch choice.', 'price': 14, 'cat': 'Wrap', 'keyword': 'wrap'},
        ],
        'Others (F&B)': [
            {'name': f'{merchant_name} House Platter', 'desc': f'A mixed house platter from {merchant_name} combining popular bites into one satisfying selection.', 'price': 24, 'cat': 'Platter', 'keyword': 'grill'},
            {'name': 'Signature Noodles', 'desc': 'A savoury noodle dish built as an easy all-day choice with balanced flavour and texture.', 'price': 16, 'cat': 'Noodles', 'keyword': 'noodle'},
            {'name': 'Grilled Chicken Bowl', 'desc': 'A hearty bowl of grilled chicken, rice, and vegetables for a satisfying daily main.', 'price': 18, 'cat': 'Bowl', 'keyword': 'chicken'},
            {'name': 'Seasonal Dessert Cup', 'desc': 'A compact dessert cup made to finish the meal with a sweet and refreshing note.', 'price': 8, 'cat': 'Dessert', 'keyword': 'dessert'},
            {'name': 'Iced Lemon Tea', 'desc': 'A chilled tea beverage with citrus brightness to refresh the palate throughout the meal.', 'price': 5, 'cat': 'Beverage', 'keyword': 'tea'},
        ],
    }
    return products.get(theme, products['Others (F&B)'])


def load_index_meta(index_ws):
    result = {}
    for row in index_ws.iter_rows(min_row=6, values_only=True):
        merchant_id, sheet_name, merchant_name, category = row[:4]
        if not merchant_id or not sheet_name:
            continue
        result[sheet_name] = {
            'merchant_name': merchant_name or str(sheet_name).split(' ', 1)[-1],
            'category': category or 'Others (F&B)',
        }
    return result


def find_product_section(ws):
    for i in range(1, ws.max_row + 1):
        if ws.cell(i, 1).value == 'ITEM INFORMATION / PRODUCTS':
            return i
    return None


def update_workbook(path):
    backup_path = path.replace('.xlsx', '_before_20_products.xlsx')
    if not os.path.exists(backup_path):
        copy2(path, backup_path)

    wb = load_workbook(path)
    meta = load_index_meta(wb['Index'])
    processed = 0

    for ws in wb.worksheets:
        if ws.title == 'Index':
            continue

        section_row = find_product_section(ws)
        if not section_row:
            continue

        first_product_row = section_row + 2
        blank_row = first_product_row
        while ws.cell(blank_row, 1).value is not None:
            blank_row += 1
        existing_count = blank_row - first_product_row

        info = meta.get(ws.title, {})
        merchant_name = info.get('merchant_name', ws.title.split(' ', 1)[-1])
        category = info.get('category', 'Others (F&B)')
        theme = infer_theme(merchant_name, category)
        products = extra_products(theme, merchant_name)

        if existing_count < 20:
            ws.insert_rows(blank_row, amount=20 - existing_count)
        elif existing_count > 20:
            ws.delete_rows(first_product_row + 20, existing_count - 20)

        mid = ws.title.split(' ', 1)[0]
        style_row = first_product_row
        for index, product in enumerate(products, start=16):
            row_num = first_product_row + index - 1
            for col in range(1, 8):
                ws.cell(row_num, col)._style = copy(ws.cell(style_row, col)._style)
            ws.row_dimensions[row_num].height = ws.row_dimensions[style_row].height
            ws.cell(row_num, 1).value = f'I{mid[1:]}-{index}'
            ws.cell(row_num, 2).value = pexels_url(product['keyword'], int(mid[1:]) + index)
            ws.cell(row_num, 3).value = product['name']
            ws.cell(row_num, 4).value = product['desc']
            ws.cell(row_num, 5).value = product['price']
            ws.cell(row_num, 6).value = product['cat']
            ws.cell(row_num, 7).value = 'Regular'

        processed += 1

    wb.save(path)
    return processed, backup_path


if __name__ == '__main__':
    if len(sys.argv) != 2:
        print('Usage: python3 update_excel_products.py /absolute/path/to/file.xlsx')
        raise SystemExit(1)

    workbook_path = sys.argv[1]
    processed_count, backup_path = update_workbook(workbook_path)
    print(f'Updated {processed_count} merchant sheets.')
    print(f'Backup copy: {backup_path}')
    print(f'Workbook updated: {workbook_path}')