import os
import openpyxl
import sys
from shutil import copy2

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],
}

# 10 Female and 10 Male verified high-quality portrait IDs
FEMALE_PORTRAITS = [
    'photo-1494790108377-be9c29b29330',
    'photo-1534528741775-53994a69daeb',
    'photo-1517841905240-472988babdf9',
    'photo-1524504388940-b1c1722653e1',
    'photo-1580489944761-15a19d654956',
    'photo-1546961329-78bef0414d7c',
    'photo-1554151228-14d9def656e4',
    'photo-1544723795-3fb6469f5b39',
    'photo-1438761681033-6461ffad8d80',
    'photo-1488716820095-cbe80883c496'
]

MALE_PORTRAITS = [
    'photo-1507003211169-0a1dd7228f2d',
    'photo-1506794778202-cad84cf45f1d',
    'photo-1472099645785-5658abf4ff4e',
    'photo-1519085360753-af0119f7cbe7',
    'photo-1552374196-c4e7ffc6e126',
    'photo-1568602471122-7832951cc4c5',
    'photo-1552058544-f2b08422138a',
    'photo-1542909168-82c3e7fdca5c',
    'photo-1500048993953-d23a436266cf',
    'photo-1522075469751-3a6694fb2f61'
]

# Standard landscape/interior Unsplash IDs for banner/logo/thumbnail
INTERIOR_PHOTOS = [
    'photo-1554118811-1e0d58224f24', # Cafe interior
    'photo-1501339847302-ac426a4a7cbb', # Cafe storefront
    'photo-1495474472287-4d71bcdd2085', # Espresso bar
    'photo-1555396273-367ea4eb4db5', # Restaurant kitchen
    'photo-1517248135467-4c7edcad34c4', # Dining room
    'photo-1546069901-ba9599a7e63c', # Salad bar
    'photo-1512621776951-a57141f2eefd', # Vegan food corner
    'photo-1511920170033-f8396924c348', # Bakery display
    'photo-1514933651103-005eec06c04b', # Fancy dining
    'photo-1552566626-52f8b828add9'  # Modern bistro
]

def get_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 get_portrait_url(photo_id):
    return f'https://images.unsplash.com/{photo_id}?auto=format&fit=crop&w=1440&h=1080&q=90&crop=faces'

def get_interior_url(photo_id, w=1920, h=1080):
    return f'https://images.unsplash.com/{photo_id}?auto=format&fit=crop&w={w}&h={h}&q=85'

def get_best_keyword(name):
    name_lower = name.lower()
    if 'laksa' in name_lower or 'kway teow' in name_lower or 'mee' in name_lower or 'noodle' in name_lower:
        return 'noodle'
    if 'nasi lemak' in name_lower or 'rice' in name_lower or 'nasi' in name_lower:
        return 'rice'
    if 'curry' in name_lower:
        return 'curry'
    if 'satay' in name_lower or 'skewers' in name_lower or 'kebab' in name_lower or 'otah' in name_lower:
        return 'kebab'
    if 'prata' in name_lower or 'puff' in name_lower or 'croissant' in name_lower or 'pastry' in name_lower:
        return 'pastry'
    if 'toast' in name_lower or 'bread' in name_lower or 'sandwich' in name_lower or 'bagel' in name_lower:
        return 'bakery'
    if 'pancake' in name_lower or 'waffle' in name_lower:
        return 'pastry'
    if 'burger' in name_lower:
        return 'burger'
    if 'pasta' in name_lower or 'spaghetti' in name_lower:
        return 'pasta'
    if 'steak' in name_lower or 'chop' in name_lower or 'grill' in name_lower or 'ribeye' in name_lower:
        return 'grill'
    if 'chicken' in name_lower or 'wings' in name_lower:
        return 'chicken'
    if 'salad' in name_lower or 'acai' in name_lower or 'bowl' in name_lower or 'greens' in name_lower:
        return 'salad'
    if 'cake' in name_lower or 'tart' in name_lower or 'brownie' in name_lower or 'dessert' in name_lower or 'kacang' in name_lower or 'chendol' in name_lower:
        return 'dessert'
    if 'tea' in name_lower or 'tarik' in name_lower or 'bandung' in name_lower or 'lemonade' in name_lower or 'juice' in name_lower or 'drink' in name_lower or 'beverage' in name_lower:
        return 'tea'
    if 'coffee' in name_lower or 'latte' in name_lower or 'cappuccino' in name_lower or 'espresso' in name_lower or 'flat white' in name_lower or 'black' in name_lower:
        return 'coffee'
    if 'soup' in name_lower or 'broth' in name_lower:
        return 'soup'
    if 'dumpling' in name_lower or 'mai' in name_lower or 'gow' in name_lower:
        return 'dumpling'
    if 'fries' in name_lower or 'fried' in name_lower or 'rolls' in name_lower:
        return 'fried'
    return 'cafe'

def get_person_photo(name, index):
    # Detect gender from name
    name_lower = name.lower()
    female_first_names = [
        'amanda', 'sarah', 'rachel', 'priya', 'chloe', 'mei ling', 'xiu hui', 
        'hui min', 'qi xuan', 'amirah', 'nurul', 'deepa', 'jamie', 'sophie', 
        'clara', 'cheryl', 'fiona', 'natalie', 'michelle', 'jessica', 'kimberly', 
        'valerie', 'stephanie', 'patricia', 'evelyn', 'siti', 'diana', 'hidayah', 
        'fazira', 'nadhirah', 'aishah', 'kamariah', 'shalini', 'preethi', 'meera', 'divya'
    ]
    is_female = False
    for fn in female_first_names:
        if name_lower.startswith(fn):
            is_female = True
            break
            
    pool = FEMALE_PORTRAITS if is_female else MALE_PORTRAITS
    photo_id = pool[index % len(pool)]
    return get_portrait_url(photo_id)

def update_excel_images(path):
    wb = openpyxl.load_workbook(path)
    
    # Process all sheets starting with M followed by 3 digits
    import re
    target_sheets = [s for s in wb.sheetnames if re.match(r'^M\d{3}\b', s)]
    print(f"Updating images in workbook: {path}")
    print(f"Target sheets: {target_sheets}")
    
    for sheet_name in target_sheets:
        ws = wb[sheet_name]
        mid_str = sheet_name.split(' ', 1)[0]
        mid_num = int(mid_str[1:])
        
        # 1. Update Logo URL
        logo_row = None
        for r in range(1, ws.max_row+1):
            if ws.cell(row=r, column=1).value == 'Logo URL':
                logo_row = r
                break
        if logo_row:
            logo_photo = INTERIOR_PHOTOS[mid_num % len(INTERIOR_PHOTOS)]
            ws.cell(row=logo_row, column=2).value = get_interior_url(logo_photo, 1024, 1024)
            print(f"  [{sheet_name}] Logo URL updated.")
            
        # 2. Update Merchant Thumbnail URL
        thumb_row = None
        for r in range(1, ws.max_row+1):
            if ws.cell(row=r, column=1).value == 'Merchant Thumbnail URL':
                thumb_row = r
                break
        if thumb_row:
            # Let's use a nice interior or food photo for the merchant thumbnail
            thumb_photo = INTERIOR_PHOTOS[(mid_num + 3) % len(INTERIOR_PHOTOS)]
            ws.cell(row=thumb_row, column=2).value = get_interior_url(thumb_photo, 1920, 1440)
            print(f"  [{sheet_name}] Merchant Thumbnail URL updated.")
            
        # 3. Update Banner Media
        banner_start = None
        for r in range(1, ws.max_row+1):
            if ws.cell(row=r, column=1).value == 'BANNER MEDIA':
                banner_start = r + 2
                break
        if banner_start:
            r = banner_start
            b_idx = 0
            while True:
                val = ws.cell(row=r, column=1).value
                if not val or val == 'ITEM INFORMATION / PRODUCTS':
                    break
                ws.cell(row=r, column=3).value = get_interior_url(INTERIOR_PHOTOS[(mid_num + b_idx) % len(INTERIOR_PHOTOS)], 1920, 1080)
                r += 1
                b_idx += 1
            print(f"  [{sheet_name}] Banners updated ({b_idx} banners).")
            
        # 4. Update Products
        prod_start = None
        for r in range(1, ws.max_row+1):
            if ws.cell(row=r, column=1).value == 'ITEM INFORMATION / PRODUCTS':
                prod_start = r + 2
                break
        if prod_start:
            r = prod_start
            p_idx = 0
            while True:
                val = ws.cell(row=r, column=1).value
                if not val or val == 'COUPONS':
                    break
                p_name = ws.cell(row=r, column=3).value
                kw = get_best_keyword(p_name)
                ws.cell(row=r, column=2).value = get_pexels_url(kw, mid_num + p_idx)
                r += 1
                p_idx += 1
            print(f"  [{sheet_name}] Products updated ({p_idx} products).")
            
        # 5. Update Personnel
        pers_start = None
        for r in range(1, ws.max_row+1):
            if ws.cell(row=r, column=1).value == 'PERSONNEL':
                pers_start = r + 2
                break
        if pers_start:
            r = pers_start
            p_idx = 0
            while True:
                val = ws.cell(row=r, column=1).value
                if not val or val == 'TERMS_AND_CONDITIONS':
                    break
                p_name = ws.cell(row=r, column=3).value
                ws.cell(row=r, column=2).value = get_person_photo(p_name, mid_num + p_idx)
                r += 1
                p_idx += 1
            print(f"  [{sheet_name}] Personnel updated ({p_idx} personnel).")

    wb.save(path)
    print("Workbook successfully saved!")

if __name__ == '__main__':
    base_dir = os.path.dirname(os.path.abspath(__file__))
    file_path = os.path.join(base_dir, "excel_backups", "fnb_merchant_seed_dataset_FNB_50_M011_M020_images_refreshed_relevant_v2.xlsx")
    
    # Make a backup
    backup_path = file_path.replace('.xlsx', '_backup_refreshed.xlsx')
    if not os.path.exists(backup_path):
        copy2(file_path, backup_path)
        print(f"Backup created at: {backup_path}")
        
    update_excel_images(file_path)