import { db } from '@server/db/drizzle';
import { sql } from 'drizzle-orm';
import { initDatabase } from '@server/db/init';

interface SeedModGroup { name: string; type: 'radio' | 'checkbox'; isRequired: boolean; options: { name: string; priceDelta: number }[] }
interface SeedItem { name: string; description: string; price: number; dietaryTags: string[]; isVeg: boolean; spiceLevel: number; prepTime: number; calories: number; imageUrl: string; modGroups?: SeedModGroup[] }
interface SeedCategory { name: string; displayOrder: number; items: SeedItem[] }

const IMAGES = {
  samosa: 'https://images.unsplash.com/photo-1601050690597-df0568f70950?auto=format&fit=crop&w=300&q=80',
  paneerTikka: 'https://images.unsplash.com/photo-1567188040759-fb8a883dc6d8?auto=format&fit=crop&w=300&q=80',
  aalooTikki: 'https://images.unsplash.com/photo-1606491956689-2ea866880c84?auto=format&fit=crop&w=300&q=80',
  dalMakhani: 'https://images.unsplash.com/photo-1546833999-b9f581a1996d?auto=format&fit=crop&w=300&q=80',
  shahiPaneer: 'https://images.unsplash.com/photo-1631452180519-c014fe946bc7?auto=format&fit=crop&w=300&q=80',
  palakPaneer: 'https://images.unsplash.com/photo-1601050690597-df0568f70950?auto=format&fit=crop&w=300&q=80',
  chickenBiryani: 'https://images.unsplash.com/photo-1563379091339-03b21ab4a4f8?auto=format&fit=crop&w=300&q=80',
  muttonBiryani: 'https://images.unsplash.com/photo-1563379091339-03b21ab4a4f8?auto=format&fit=crop&w=300&q=80',
  tandooriChicken: 'https://images.unsplash.com/photo-1599487488170-d11ec9c172f0?auto=format&fit=crop&w=300&q=80',
  chickenTikka: 'https://images.unsplash.com/photo-1603894584373-5ac82b2ae398?auto=format&fit=crop&w=300&q=80',
  naan: 'https://images.unsplash.com/photo-1586444248902-2f64eddc13df?auto=format&fit=crop&w=300&q=80',
  gulabJamun: 'https://images.unsplash.com/photo-1601050690117-94f5f6fa8bd7?auto=format&fit=crop&w=300&q=80',
  mangoLassi: 'https://images.unsplash.com/photo-1553361371-9b22f78e8b1d?auto=format&fit=crop&w=300&q=80',
  chai: 'https://images.unsplash.com/photo-1563911302283-d2bc129e7570?auto=format&fit=crop&w=300&q=80',
  noodles: 'https://images.unsplash.com/photo-1612929633738-8fe44f7ec841?auto=format&fit=crop&w=300&q=80',
  friedRice: 'https://images.unsplash.com/photo-1603133872878-684f208fb84b?auto=format&fit=crop&w=300&q=80',
  generic: 'https://images.unsplash.com/photo-1546069901-ba9599a7e63c?auto=format&fit=crop&w=300&q=80',
};

const PORTION_GROUP: SeedModGroup = {
  name: 'Choose Portion', type: 'radio', isRequired: true,
  options: [{ name: 'Half', priceDelta: -50 }, { name: 'Full', priceDelta: 0 }],
};
const SPICE_GROUP: SeedModGroup = {
  name: 'Spice Level', type: 'radio', isRequired: false,
  options: [{ name: 'Mild', priceDelta: 0 }, { name: 'Medium', priceDelta: 0 }, { name: 'Extra Hot', priceDelta: 0 }],
};
const BREAD_GROUP: SeedModGroup = {
  name: 'Add Bread', type: 'checkbox', isRequired: false,
  options: [{ name: 'Butter Naan (+₹60)', priceDelta: 60 }, { name: 'Tandoori Roti (+₹40)', priceDelta: 40 }, { name: 'Garlic Naan (+₹70)', priceDelta: 70 }],
};
const RICE_GROUP: SeedModGroup = {
  name: 'Add Rice', type: 'checkbox', isRequired: false,
  options: [{ name: 'Steamed Rice (+₹80)', priceDelta: 80 }, { name: 'Jeera Rice (+₹100)', priceDelta: 100 }],
};
const BIRYANI_EXTRAS: SeedModGroup = {
  name: 'Extras', type: 'checkbox', isRequired: false,
  options: [{ name: 'Extra Raita (+₹60)', priceDelta: 60 }, { name: 'Extra Salan (+₹40)', priceDelta: 40 }, { name: 'Double Meat (+₹120)', priceDelta: 120 }],
};
const TIKKA_ADDON: SeedModGroup = {
  name: 'Add Dips & Sauces', type: 'checkbox', isRequired: false,
  options: [{ name: 'Mint Chutney', priceDelta: 0 }, { name: 'Tamarind Chutney', priceDelta: 0 }, { name: 'Garlic Dip (+₹30)', priceDelta: 30 }],
};
const NAAN_ADDON: SeedModGroup = {
  name: 'Flavour', type: 'radio', isRequired: true,
  options: [{ name: 'Plain', priceDelta: -10 }, { name: 'Butter', priceDelta: 0 }, { name: 'Garlic', priceDelta: 10 }, { name: 'Cheese', priceDelta: 20 }],
};
const LASSI_SIZE: SeedModGroup = {
  name: 'Size', type: 'radio', isRequired: true,
  options: [{ name: 'Regular', priceDelta: 0 }, { name: 'Large (+₹40)', priceDelta: 40 }],
};

const MENU: SeedCategory[] = [
  {
    name: 'Indian Starters', displayOrder: 1, items: [
      { name: 'Samosa (2 pcs)', description: 'Crispy fried pastry filled with spiced potatoes and peas, served with mint chutney', price: 120, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 10, calories: 180, imageUrl: IMAGES.samosa, modGroups: [{ name: 'Add Chutney', type: 'checkbox', isRequired: false, options: [{ name: 'Mint Chutney', priceDelta: 0 }, { name: 'Tamarind Chutney', priceDelta: 0 }] }] },
      { name: 'Paneer Tikka', description: 'Marinated cottage cheese cubes grilled in tandoor with bell peppers and onions', price: 280, dietaryTags: ['V'], isVeg: true, spiceLevel: 2, prepTime: 20, calories: 350, imageUrl: IMAGES.paneerTikka, modGroups: [TIKKA_ADDON, SPICE_GROUP] },
      { name: 'Aloo Tikki', description: 'Golden fried spiced potato patties served with tamarind chutney and yoghurt', price: 130, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 12, calories: 210, imageUrl: IMAGES.aalooTikki },
      { name: 'Hara Bhara Kebab', description: 'Soft patties made from spinach, green peas and paneer, shallow fried to perfection', price: 220, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 18, calories: 240, imageUrl: IMAGES.generic },
      { name: 'Dahi Puri', description: 'Crisp puris filled with spiced yoghurt, potato, chickpeas and sweet-tangy chutneys', price: 150, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 8, calories: 190, imageUrl: IMAGES.generic },
      { name: 'Pav Bhaji', description: 'Spiced mashed vegetable curry served with butter-toasted buns and raw onions', price: 180, dietaryTags: ['V'], isVeg: true, spiceLevel: 2, prepTime: 15, calories: 380, imageUrl: IMAGES.generic, modGroups: [{ name: 'Extra Pav', type: 'checkbox', isRequired: false, options: [{ name: '+2 Pav (+₹30)', priceDelta: 30 }] }] },
      { name: 'Chaat', description: 'Crunchy papdi layered with chickpeas, yoghurt, sev, and sweet-spicy chutneys', price: 160, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 8, calories: 220, imageUrl: IMAGES.generic },
      { name: 'Veg Cutlet', description: 'Crispy mixed vegetable and bread cutlets seasoned with Indian spices', price: 140, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 12, calories: 200, imageUrl: IMAGES.generic },
    ],
  },
  {
    name: 'Main Course', displayOrder: 2, items: [
      { name: 'Dal Makhani', description: 'Slow-cooked black lentils simmered overnight with cream, butter and aromatic spices', price: 280, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 15, calories: 320, imageUrl: IMAGES.dalMakhani, modGroups: [BREAD_GROUP, RICE_GROUP] },
      { name: 'Shahi Paneer', description: 'Cottage cheese cubes in a rich, royal cashew-tomato gravy with a touch of cream', price: 320, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 20, calories: 420, imageUrl: IMAGES.shahiPaneer, modGroups: [SPICE_GROUP, BREAD_GROUP, RICE_GROUP] },
      { name: 'Palak Paneer', description: 'Fresh cottage cheese in a smooth, spiced spinach gravy — a classic north Indian favourite', price: 300, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 20, calories: 380, imageUrl: IMAGES.palakPaneer, modGroups: [SPICE_GROUP, BREAD_GROUP] },
      { name: 'Chole Masala', description: 'Hearty chickpeas cooked in a robust tomato-onion masala with whole spices', price: 260, dietaryTags: ['V', 'VG'], isVeg: true, spiceLevel: 2, prepTime: 15, calories: 340, imageUrl: IMAGES.generic, modGroups: [BREAD_GROUP] },
      { name: 'Aloo Gobi', description: 'Potato and cauliflower stir-fried with turmeric, cumin and fresh coriander', price: 240, dietaryTags: ['V', 'VG'], isVeg: true, spiceLevel: 1, prepTime: 18, calories: 290, imageUrl: IMAGES.generic },
      { name: 'Rajma', description: 'Red kidney beans in a thick spiced gravy, served with steamed basmati rice', price: 260, dietaryTags: ['V', 'VG'], isVeg: true, spiceLevel: 1, prepTime: 15, calories: 360, imageUrl: IMAGES.generic, modGroups: [RICE_GROUP] },
      { name: 'Kadhi Pakora', description: 'Gram flour fritters in a tangy yoghurt-based curry, tempered with mustard and curry leaves', price: 240, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 20, calories: 310, imageUrl: IMAGES.generic },
      { name: 'Mix Veg Curry', description: 'Seasonal vegetables cooked in a mildly spiced tomato-cashew gravy', price: 260, dietaryTags: ['V', 'VG'], isVeg: true, spiceLevel: 1, prepTime: 18, calories: 280, imageUrl: IMAGES.generic, modGroups: [SPICE_GROUP, BREAD_GROUP, RICE_GROUP] },
    ],
  },
  {
    name: 'Tandoor', displayOrder: 3, items: [
      { name: 'Tandoori Chicken (Half)', description: 'Whole chicken marinated in yoghurt and spices, roasted in the clay oven — smoky and succulent', price: 380, dietaryTags: [], isVeg: false, spiceLevel: 2, prepTime: 25, calories: 450, imageUrl: IMAGES.tandooriChicken, modGroups: [SPICE_GROUP, PORTION_GROUP, TIKKA_ADDON] },
      { name: 'Chicken Tikka', description: 'Boneless chicken pieces marinated in yoghurt and tandoori masala, grilled to perfection', price: 360, dietaryTags: [], isVeg: false, spiceLevel: 2, prepTime: 22, calories: 380, imageUrl: IMAGES.chickenTikka, modGroups: [SPICE_GROUP, TIKKA_ADDON] },
      { name: 'Seekh Kebab', description: 'Minced mutton blended with herbs and spices, skewered and chargrilled in the tandoor', price: 380, dietaryTags: [], isVeg: false, spiceLevel: 2, prepTime: 22, calories: 420, imageUrl: IMAGES.generic, modGroups: [SPICE_GROUP, TIKKA_ADDON] },
      { name: 'Paneer Tikka Tandoori', description: 'Large cottage cheese cubes marinated in yoghurt and spices, cooked in the clay oven', price: 320, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 20, calories: 360, imageUrl: IMAGES.paneerTikka, modGroups: [SPICE_GROUP, TIKKA_ADDON] },
      { name: 'Malai Tikka', description: 'Tender chicken marinated in cream, cheese and mild spices — melt-in-the-mouth texture', price: 400, dietaryTags: [], isVeg: false, spiceLevel: 0, prepTime: 25, calories: 440, imageUrl: IMAGES.generic },
      { name: 'Fish Tikka', description: 'Fresh fish fillets marinated in ajwain-spiced yoghurt and grilled in the tandoor', price: 420, dietaryTags: [], isVeg: false, spiceLevel: 2, prepTime: 22, calories: 310, imageUrl: IMAGES.generic, modGroups: [TIKKA_ADDON] },
      { name: 'Reshmi Kebab', description: 'Silky smooth chicken kebab marinated in cream, cashew paste and saffron', price: 420, dietaryTags: [], isVeg: false, spiceLevel: 0, prepTime: 25, calories: 460, imageUrl: IMAGES.generic },
    ],
  },
  {
    name: 'Mughlai', displayOrder: 4, items: [
      { name: 'Chicken Biryani', description: 'Fragrant long-grain basmati rice layered with spiced chicken, saffron and fried onions — dum cooked', price: 420, dietaryTags: [], isVeg: false, spiceLevel: 2, prepTime: 30, calories: 620, imageUrl: IMAGES.chickenBiryani, modGroups: [BIRYANI_EXTRAS] },
      { name: 'Mutton Biryani', description: 'Succulent mutton pieces with saffron-infused basmati rice, slow-cooked in the dum style', price: 520, dietaryTags: [], isVeg: false, spiceLevel: 2, prepTime: 35, calories: 720, imageUrl: IMAGES.muttonBiryani, modGroups: [BIRYANI_EXTRAS] },
      { name: 'Veg Biryani', description: 'Aromatic basmati rice cooked with seasonal vegetables, nuts and saffron in dum style', price: 320, dietaryTags: ['V'], isVeg: true, spiceLevel: 1, prepTime: 28, calories: 520, imageUrl: IMAGES.chickenBiryani, modGroups: [{ name: 'Extras', type: 'checkbox', isRequired: false, options: [{ name: 'Extra Raita (+₹60)', priceDelta: 60 }, { name: 'Extra Salan (+₹40)', priceDelta: 40 }] }] },
      { name: 'Chicken Korma', description: 'Tender chicken braised in a creamy cashew-based Mughlai sauce with saffron and rose water', price: 380, dietaryTags: [], isVeg: false, spiceLevel: 1, prepTime: 25, calories: 480, imageUrl: IMAGES.generic, modGroups: [BREAD_GROUP, RICE_GROUP] },
      { name: 'Mutton Rogan Josh', description: 'Classic Kashmiri preparation — slow-cooked mutton in a deep red, aromatic gravy of whole spices', price: 480, dietaryTags: [], isVeg: false, spiceLevel: 3, prepTime: 35, calories: 560, imageUrl: IMAGES.generic, modGroups: [BREAD_GROUP, RICE_GROUP] },
      { name: 'Nihari', description: 'Traditional slow-cooked beef shank stew with marrow, spices and wheat flour — a Mughal delicacy', price: 520, dietaryTags: [], isVeg: false, spiceLevel: 2, prepTime: 40, calories: 640, imageUrl: IMAGES.generic },
      { name: 'Haleem', description: 'Slow-cooked wheat, lentils and meat pounded to a thick, wholesome stew with caramelised onions', price: 380, dietaryTags: [], isVeg: false, spiceLevel: 2, prepTime: 35, calories: 520, imageUrl: IMAGES.generic },
    ],
  },
  {
    name: 'Chinese', displayOrder: 5, items: [
      { name: 'Veg Hakka Noodles', description: 'Stir-fried noodles with fresh vegetables in a light soy and vinegar sauce', price: 220, dietaryTags: ['V', 'VG'], isVeg: true, spiceLevel: 1, prepTime: 15, calories: 360, imageUrl: IMAGES.noodles, modGroups: [{ name: 'Choose Spice', type: 'radio', isRequired: false, options: [{ name: 'Regular', priceDelta: 0 }, { name: 'Schezwan', priceDelta: 30 }] }] },
      { name: 'Chicken Fried Rice', description: 'Wok-tossed rice with tender chicken strips, egg and seasonal vegetables', price: 260, dietaryTags: [], isVeg: false, spiceLevel: 1, prepTime: 15, calories: 440, imageUrl: IMAGES.friedRice },
      { name: 'Manchurian', description: 'Crispy vegetable dumplings tossed in a tangy, spicy manchurian sauce', price: 220, dietaryTags: ['V', 'VG'], isVeg: true, spiceLevel: 2, prepTime: 15, calories: 310, imageUrl: IMAGES.generic, modGroups: [{ name: 'Style', type: 'radio', isRequired: true, options: [{ name: 'Dry', priceDelta: 0 }, { name: 'Gravy', priceDelta: 0 }] }] },
      { name: 'Chilli Paneer', description: 'Crispy fried paneer tossed with capsicum and onions in a spicy Indo-Chinese sauce', price: 300, dietaryTags: ['V'], isVeg: true, spiceLevel: 2, prepTime: 18, calories: 380, imageUrl: IMAGES.generic, modGroups: [{ name: 'Style', type: 'radio', isRequired: true, options: [{ name: 'Dry', priceDelta: 0 }, { name: 'Gravy', priceDelta: 0 }] }] },
      { name: 'Spring Rolls (4 pcs)', description: 'Crispy rolls stuffed with stir-fried vegetables and glass noodles, served with sweet chilli dip', price: 200, dietaryTags: ['V', 'VG'], isVeg: true, spiceLevel: 1, prepTime: 15, calories: 280, imageUrl: IMAGES.generic },
      { name: 'Hot & Sour Soup', description: 'Classic tangy and spicy broth with tofu, mushrooms and bamboo shoots', price: 160, dietaryTags: ['V', 'VG'], isVeg: true, spiceLevel: 2, prepTime: 10, calories: 120, imageUrl: IMAGES.generic },
      { name: 'Dim Sum (4 pcs)', description: 'Delicate steamed dumplings filled with seasoned vegetables, served with chilli oil', price: 220, dietaryTags: ['V', 'VG'], isVeg: true, spiceLevel: 0, prepTime: 18, calories: 220, imageUrl: IMAGES.generic, modGroups: [{ name: 'Filling', type: 'radio', isRequired: true, options: [{ name: 'Veg', priceDelta: 0 }, { name: 'Chicken (+₹30)', priceDelta: 30 }, { name: 'Prawn (+₹60)', priceDelta: 60 }] }] },
    ],
  },
  {
    name: 'Breads', displayOrder: 6, items: [
      { name: 'Butter Naan', description: 'Soft leavened flatbread baked in the tandoor, brushed generously with fresh butter', price: 60, dietaryTags: ['V'], isVeg: true, spiceLevel: 0, prepTime: 8, calories: 160, imageUrl: IMAGES.naan, modGroups: [NAAN_ADDON] },
      { name: 'Garlic Naan', description: 'Tandoor-baked naan topped with minced garlic and fresh coriander', price: 70, dietaryTags: ['V'], isVeg: true, spiceLevel: 0, prepTime: 8, calories: 170, imageUrl: IMAGES.naan },
      { name: 'Tandoori Roti', description: 'Whole wheat flatbread baked directly on the walls of the clay oven', price: 40, dietaryTags: ['V', 'VG'], isVeg: true, spiceLevel: 0, prepTime: 6, calories: 110, imageUrl: IMAGES.naan },
      { name: 'Paratha', description: 'Multi-layered flaky whole wheat paratha with a crisp golden exterior', price: 70, dietaryTags: ['V'], isVeg: true, spiceLevel: 0, prepTime: 10, calories: 200, imageUrl: IMAGES.naan, modGroups: [{ name: 'Stuffing', type: 'radio', isRequired: false, options: [{ name: 'Plain', priceDelta: 0 }, { name: 'Aloo (+₹20)', priceDelta: 20 }, { name: 'Paneer (+₹30)', priceDelta: 30 }, { name: 'Mix Veg (+₹25)', priceDelta: 25 }] }] },
      { name: 'Puri (2 pcs)', description: 'Deep-fried, puffed whole wheat bread — perfect with curries or chaat', price: 50, dietaryTags: ['V', 'VG'], isVeg: true, spiceLevel: 0, prepTime: 8, calories: 180, imageUrl: IMAGES.naan },
      { name: 'Kulcha', description: 'Tandoor-baked bread stuffed with spiced potatoes or paneer, served with chole', price: 90, dietaryTags: ['V'], isVeg: true, spiceLevel: 0, prepTime: 10, calories: 230, imageUrl: IMAGES.naan, modGroups: [{ name: 'Stuffing', type: 'radio', isRequired: true, options: [{ name: 'Aloo', priceDelta: 0 }, { name: 'Paneer (+₹20)', priceDelta: 20 }] }] },
    ],
  },
  {
    name: 'Desserts & Beverages', displayOrder: 7, items: [
      { name: 'Gulab Jamun (2 pcs)', description: 'Soft milk-solid dumplings soaked in rose-cardamom sugar syrup, served warm', price: 100, dietaryTags: ['V'], isVeg: true, spiceLevel: 0, prepTime: 5, calories: 280, imageUrl: IMAGES.gulabJamun },
      { name: 'Rasgulla (2 pcs)', description: 'Spongy cottage cheese balls soaked in light, fragrant sugar syrup', price: 100, dietaryTags: ['V'], isVeg: true, spiceLevel: 0, prepTime: 5, calories: 240, imageUrl: IMAGES.generic },
      { name: 'Kheer', description: 'Creamy rice pudding slow-cooked with milk, sugar, cardamom, saffron and dry fruits', price: 120, dietaryTags: ['V'], isVeg: true, spiceLevel: 0, prepTime: 8, calories: 310, imageUrl: IMAGES.generic },
      { name: 'Mango Lassi', description: 'Thick, chilled yoghurt drink blended with ripe Alphonso mango — refreshing and sweet', price: 130, dietaryTags: ['V'], isVeg: true, spiceLevel: 0, prepTime: 5, calories: 220, imageUrl: IMAGES.mangoLassi, modGroups: [LASSI_SIZE] },
      { name: 'Masala Chai', description: 'Aromatic Indian spiced tea brewed with ginger, cardamom, cinnamon and cloves', price: 80, dietaryTags: ['V'], isVeg: true, spiceLevel: 0, prepTime: 5, calories: 80, imageUrl: IMAGES.chai, modGroups: [{ name: 'Sweetness', type: 'radio', isRequired: false, options: [{ name: 'Less Sweet', priceDelta: 0 }, { name: 'Normal', priceDelta: 0 }, { name: 'Extra Sweet', priceDelta: 0 }] }] },
      { name: 'Shahi Tukda', description: 'Fried bread soaked in sweet milk, topped with thickened rabri and silvered pistachios', price: 160, dietaryTags: ['V'], isVeg: true, spiceLevel: 0, prepTime: 8, calories: 420, imageUrl: IMAGES.generic },
    ],
  },
];

export async function seedNoidaMenu(
  restaurantId: string,
  branchId: string,
  force = false,
): Promise<{ categoriesSeeded: number; itemsSeeded: number }> {
  await initDatabase();

  /* raw: SELECT name FROM menu_categories WHERE restaurant_id = $1 AND (branch_id IS NULL OR branch_id = $2) */
  const { rows: existingRows } = await db.execute(sql`
    SELECT name FROM menu_categories WHERE restaurant_id = ${restaurantId} AND (branch_id IS NULL OR branch_id = ${branchId})
  `);
  const existingNames = new Set((existingRows as { name: string }[]).map(r => r.name));
  const toSeed = force ? MENU : MENU.filter(cat => !existingNames.has(cat.name));
  if (toSeed.length === 0) return { categoriesSeeded: 0, itemsSeeded: 0 };

  let categoriesSeeded = 0;
  let itemsSeeded = 0;

  await db.transaction(async (tx) => {
    for (const cat of toSeed) {
      if (force && existingNames.has(cat.name)) {
        /* raw: DELETE FROM menu_categories WHERE restaurant_id = $1 AND name = $2 */
        await tx.execute(sql`DELETE FROM menu_categories WHERE restaurant_id = ${restaurantId} AND name = ${cat.name}`);
      }
      /* raw: INSERT INTO menu_categories (...) VALUES (...) RETURNING id */
      const { rows: catRows } = await tx.execute(sql`
        INSERT INTO menu_categories (restaurant_id, branch_id, name, display_order, is_active)
        VALUES (${restaurantId}, ${null}, ${cat.name}, ${cat.displayOrder}, true)
        RETURNING id
      `);
      const catId = (catRows[0] as { id: string } | undefined)?.id;
      if (!catId) continue;
      categoriesSeeded++;

      for (const item of cat.items) {
        /* raw: INSERT INTO menu_items (...) VALUES (...) RETURNING id */
        const { rows: itemRows } = await tx.execute(sql`
          INSERT INTO menu_items
            (restaurant_id, branch_id, category_id, name, description, price, dietary_tags, is_available,
             is_veg, spice_level, prep_time_minutes, calories, image_url)
          VALUES (${restaurantId}, ${null}, ${catId}, ${item.name}, ${item.description}, ${item.price},
                  ${JSON.stringify(item.dietaryTags)}::jsonb, true, ${item.isVeg}, ${item.spiceLevel},
                  ${item.prepTime}, ${item.calories}, ${item.imageUrl || null})
          RETURNING id
        `);
        const itemId = (itemRows[0] as { id: string } | undefined)?.id;
        itemsSeeded++;

        if (itemId && item.modGroups) {
          for (let gi = 0; gi < item.modGroups.length; gi++) {
            const grp = item.modGroups[gi];
            /* raw: INSERT INTO menu_modifier_groups (...) VALUES (...) RETURNING id */
            const { rows: grpRows } = await tx.execute(sql`
              INSERT INTO menu_modifier_groups
                (restaurant_id, branch_id, menu_item_id, name, type, is_required, min_selections, max_selections, display_order)
              VALUES (${restaurantId}, ${null}, ${itemId}, ${grp.name}, ${grp.type}, ${grp.isRequired},
                      ${grp.isRequired ? 1 : 0}, ${grp.type === 'radio' ? 1 : null}, ${gi})
              RETURNING id
            `);
            const grpId = (grpRows[0] as { id: string } | undefined)?.id;
            if (!grpId) continue;
            for (let oi = 0; oi < grp.options.length; oi++) {
              const opt = grp.options[oi];
              /* raw: INSERT INTO menu_modifier_options (...) VALUES (...) */
              await tx.execute(sql`
                INSERT INTO menu_modifier_options (modifier_group_id, name, price_delta, is_default, is_available, display_order)
                VALUES (${grpId}, ${opt.name}, ${opt.priceDelta}, false, true, ${oi})
              `);
            }
          }
        }
      }
    }
  });

  return { categoriesSeeded, itemsSeeded };
}

export async function findOrCreateNoidaBranch(restaurantId: string): Promise<string> {
  await initDatabase();
  /* raw: SELECT id FROM branches WHERE restaurant_id = $1 AND name = $2 */
  const { rows: existing } = await db.execute(sql`SELECT id FROM branches WHERE restaurant_id = ${restaurantId} AND name = ${'Noida'}`);
  if (existing[0]) return (existing[0] as { id: string }).id;
  /* raw: INSERT INTO branches (...) VALUES (...) RETURNING id */
  const { rows: created } = await db.execute(sql`INSERT INTO branches (restaurant_id, name, is_active) VALUES (${restaurantId}, ${'Noida'}, true) RETURNING id`);
  if (!created[0]) throw new Error('Failed to create Noida branch');
  return (created[0] as { id: string }).id;
}