// src/db/seed.ts
/**
 * @file This script seeds the database with sample data for development and testing.
 * It is designed to be run from the command line.
 * WARNING: This script will completely WIPE all data in the public schema before seeding.
 * DO NOT run this on a production database.
 */

import { Pool } from 'pg';
import bcrypt from 'bcrypt';
import { logger } from '../services/logger.server';
import { CATEGORIES } from '../types';

const pool = new Pool({
  user: process.env.DB_USER,
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  password: process.env.DB_PASSWORD,
  port: parseInt(process.env.DB_PORT || '5432', 10),
});

async function main() {
  // Declare client outside the try block so it's accessible in the finally block.
  let client;

  try {
    client = await pool.connect();
    logger.info('Connected to the database for seeding.');
    await client.query('BEGIN');

    // 1. Clean the database
    logger.info('--- Wiping existing data... ---');
    // Using TRUNCATE ... RESTART IDENTITY CASCADE is a powerful way to clean all tables
    // and reset auto-incrementing keys, while respecting foreign key relationships.
    const tablesRes = await client.query(`
        SELECT tablename 
        FROM pg_tables 
        WHERE schemaname = 'public'
        -- Exclude PostGIS system tables from truncation to avoid permission errors.
        AND tablename NOT IN ('spatial_ref_sys', 'geometry_columns')
    `);
    const tables = tablesRes.rows.map(row => `"${row.tablename}"`).join(', ');
    if (tables) {
        await client.query(`TRUNCATE ${tables} RESTART IDENTITY CASCADE`);
        logger.info('All tables in public schema have been truncated.');
    }

    // 2. Seed Categories
    logger.info('--- Seeding Categories... ---');
    const categoryQuery = `INSERT INTO public.categories (name) VALUES ${CATEGORIES.map((_, i) => `($${i + 1})`).join(', ')} RETURNING category_id, name`;
    const seededCategories = (await client.query<{category_id: number, name: string}>(categoryQuery, CATEGORIES)).rows;
    const categoryMap = new Map(seededCategories.map(c => [c.name, c.category_id]));
    logger.info(`Seeded ${seededCategories.length} categories.`);

    // 3. Seed Stores
    logger.info('--- Seeding Stores... ---');
    const stores = ['Safeway', 'No Frills', 'Costco', 'Superstore'];
    const storeQuery = `INSERT INTO public.stores (name) VALUES ${stores.map((_, i) => `($${i + 1})`).join(', ')} RETURNING store_id, name`;
    const seededStores = (await client.query<{store_id: number, name: string}>(storeQuery, stores)).rows;
    const storeMap = new Map(seededStores.map(s => [s.name, s.store_id]));
    logger.info(`Seeded ${seededStores.length} stores.`);

    // 4. Seed Master Grocery Items
    logger.info('--- Seeding Master Grocery Items... ---');
    const masterItems = [
        { name: 'Chicken Breast, Boneless Skinless', category: 'Meat & Seafood' },
        { name: 'Ground Beef, Lean', category: 'Meat & Seafood' },
        { name: 'Avocado', category: 'Fruits & Vegetables' },
        { name: 'Bananas', category: 'Fruits & Vegetables' },
        { name: 'Broccoli', category: 'Fruits & Vegetables' },
        { name: 'Cheddar Cheese, Block', category: 'Dairy & Eggs' },
        { name: 'Milk, 2%', category: 'Dairy & Eggs' },
        { name: 'Eggs, Large', category: 'Dairy & Eggs' },
        { name: 'Whole Wheat Bread', category: 'Bakery & Bread' },
        { name: 'Pasta, Spaghetti', category: 'Pantry & Dry Goods' },
        { name: 'Canned Tomatoes, Diced', category: 'Canned Goods' },
        { name: 'Coca-Cola, 12-pack', category: 'Beverages' },
        { name: 'Frozen Pizza', category: 'Frozen Foods' },
        { name: 'Paper Towels', category: 'Household & Cleaning' },
    ];
    const masterItemValues = masterItems.map(item => `('${item.name.replace(/'/g, "''")}', ${categoryMap.get(item.category)})`).join(', ');
    const masterItemQuery = `INSERT INTO public.master_grocery_items (name, category_id) VALUES ${masterItemValues} RETURNING master_grocery_item_id, name`;
    const seededMasterItems = (await client.query<{master_grocery_item_id: number, name: string}>(masterItemQuery)).rows;
    const masterItemMap = new Map(seededMasterItems.map(item => [item.name, item.master_grocery_item_id]));
    logger.info(`Seeded ${seededMasterItems.length} master grocery items.`);

    // 5. Seed Users & Profiles
    logger.info('--- Seeding Users & Profiles... ---');
    const saltRounds = 10;
    const adminPassHash = await bcrypt.hash('adminpass', saltRounds);
    const userPassHash = await bcrypt.hash('userpass', saltRounds);

    // Admin User
    await client.query("SELECT set_config('my_app.user_metadata', $1, true)", [JSON.stringify({ full_name: 'Admin User', role: 'admin' })]);
    // The trigger will create a profile with the 'user' role. We capture the ID to update it.
    const adminRes = await client.query('INSERT INTO public.users (email, password_hash) VALUES ($1, $2) RETURNING user_id', ['admin@example.com', adminPassHash]);
    const adminId = adminRes.rows[0].user_id;
    // Explicitly update the role to 'admin' for the newly created user.
    await client.query("UPDATE public.profiles SET role = 'admin' WHERE user_id = $1", [adminId]);
    logger.info('Seeded admin user (admin@example.com / adminpass)');
    logger.info(`> Role for ${adminId} set to 'admin'.`);

    // Regular User
    await client.query("SELECT set_config('my_app.user_metadata', $1, true)", [JSON.stringify({ full_name: 'Test User' })]);
    const userRes = await client.query('INSERT INTO public.users (email, password_hash) VALUES ($1, $2) RETURNING user_id', ['user@example.com', userPassHash]);
    const userId = userRes.rows[0].user_id;
    logger.info('Seeded regular user (user@example.com / userpass)');

    // 6. Seed a Flyer
    logger.info('--- Seeding a Sample Flyer... ---');
    const today = new Date();
    const validFrom = new Date(today);
    validFrom.setDate(today.getDate() - 2);
    const validTo = new Date(today);
    validTo.setDate(today.getDate() + 5);

    const flyerQuery = `
        INSERT INTO public.flyers (file_name, image_url, checksum, store_id, valid_from, valid_to)
        VALUES ('safeway-flyer.jpg', '/sample-assets/safeway-flyer.jpg', 'sample-checksum-123', ${storeMap.get('Safeway')}, $1, $2)
        RETURNING flyer_id;
    `;
    const flyerRes = await client.query<{flyer_id: number}>(flyerQuery, [validFrom.toISOString().split('T')[0], validTo.toISOString().split('T')[0]]);
    const flyerId = flyerRes.rows[0].flyer_id;
    logger.info(`Seeded flyer for Safeway (ID: ${flyerId}).`);

    // 7. Seed Flyer Items
    logger.info('--- Seeding Flyer Items... ---');
    const flyerItems = [
        { name: 'Chicken Breast, Boneless Skinless', price_display: '$3.99 /lb', price_in_cents: 399, quantity: 'per lb', master_item_id: masterItemMap.get('Chicken Breast, Boneless Skinless') },
        { name: 'Avocado', price_display: '2 for $5.00', price_in_cents: 250, quantity: 'each', master_item_id: masterItemMap.get('Avocado') },
        { name: 'Coca-Cola 12-pack', price_display: '$6.99', price_in_cents: 699, quantity: '12x355ml', master_item_id: masterItemMap.get('Coca-Cola, 12-pack') },
        { name: 'Unmatched Sample Item', price_display: '$1.23', price_in_cents: 123, quantity: 'each', master_item_id: null },
    ];

    for (const item of flyerItems) {
        await client.query(
            `INSERT INTO public.flyer_items (flyer_id, item, price_display, price_in_cents, quantity, master_item_id) VALUES ($1, $2, $3, $4, $5, $6)`,
            [flyerId, item.name, item.price_display, item.price_in_cents, item.quantity, item.master_item_id]
        );
    }
    logger.info(`Seeded ${flyerItems.length} items for the Safeway flyer.`);

    // 8. Seed Watched Items for the user
    logger.info('--- Seeding Watched Items... ---');
    const watchedItemIds = [
        masterItemMap.get('Chicken Breast, Boneless Skinless'),
        masterItemMap.get('Avocado'),
        masterItemMap.get('Ground Beef, Lean'),
    ];
    for (const itemId of watchedItemIds) {
        if (itemId) {
            await client.query('INSERT INTO public.user_watched_items (user_id, master_item_id) VALUES ($1, $2)', [userId, itemId]);
        }
    }
    logger.info(`Seeded ${watchedItemIds.length} watched items for Test User.`);

    // 9. Seed a Shopping List
    logger.info('--- Seeding a Shopping List... ---');
    const listRes = await client.query<{shopping_list_id: number}>('INSERT INTO public.shopping_lists (user_id, name) VALUES ($1, $2) RETURNING shopping_list_id', [userId, 'Weekly Groceries']);
    const listId = listRes.rows[0].shopping_list_id;

    const shoppingListItems = [
        { master_item_id: masterItemMap.get('Milk, 2%'), quantity: 1 },
        { master_item_id: masterItemMap.get('Eggs, Large'), quantity: 1 },
        { custom_item_name: 'Specialty Hot Sauce', quantity: 1 },
    ];

    for (const item of shoppingListItems) {
        await client.query(
            'INSERT INTO public.shopping_list_items (shopping_list_id, master_item_id, custom_item_name, quantity) VALUES ($1, $2, $3, $4)',
            [listId, item.master_item_id, item.custom_item_name, item.quantity]
        );
    }
    logger.info(`Seeded shopping list "Weekly Groceries" with ${shoppingListItems.length} items for Test User.`);


    // 10. Seed Brands
    logger.info('--- Seeding Brands... ---');
    const brands = ['Coca-Cola', 'Kraft', 'Maple Leaf', 'Dempster\'s', 'No Name', 'President\'s Choice'];
    const brandQuery = `INSERT INTO public.brands (name) VALUES ${brands.map((_, i) => `($${i + 1})`).join(', ')} ON CONFLICT (name) DO NOTHING`;
    await client.query(brandQuery, brands);
    logger.info(`Seeded ${brands.length} brands.`);

    // Link store-specific brands
    const loblawsId = storeMap.get('Loblaws');
    if (loblawsId) {
        await client.query('UPDATE public.brands SET store_id = $1 WHERE name = $2 OR name = $3', [loblawsId, 'No Name', 'President\'s Choice']);
        logger.info('Linked store brands to Loblaws.');
    }

    // 11. Seed Recipes
    logger.info('--- Seeding Recipes... ---');
    const recipes = [
        { name: 'Simple Chicken and Rice', description: 'A quick and healthy weeknight meal.', instructions: '1. Cook rice. 2. Cook chicken. 3. Combine.', prep: 10, cook: 20, servings: 4 },
        { name: 'Classic Spaghetti Bolognese', description: 'A rich and hearty meat sauce.', instructions: '1. Brown beef. 2. Add sauce. 3. Simmer.', prep: 15, cook: 45, servings: 6 },
        { name: 'Vegetable Stir-fry', description: 'A fast and flavorful vegetarian meal.', instructions: '1. Chop veggies. 2. Stir-fry. 3. Add sauce.', prep: 10, cook: 10, servings: 3 },
    ];
    for (const recipe of recipes) {
        await client.query(
            `INSERT INTO public.recipes (name, description, instructions, prep_time_minutes, cook_time_minutes, servings, status) 
             VALUES ($1, $2, $3, $4, $5, $6, 'public') ON CONFLICT (name) WHERE user_id IS NULL DO NOTHING`,
            [recipe.name, recipe.description, recipe.instructions, recipe.prep, recipe.cook, recipe.servings]
        );
    }
    logger.info(`Seeded ${recipes.length} recipes.`);


    // --- SEED SCRIPT DEBUG LOGGING ---
    // Corrected the query to be unambiguous by specifying the table alias for each column.
    // `id` and `email` come from the `users` table (u), and `role` comes from the `profiles` table (p).
    const allUsersInDb = await client.query('SELECT u.user_id, u.email, p.role FROM public.users u JOIN public.profiles p ON u.user_id = p.user_id');
    logger.debug('[SEED SCRIPT] Final state of users table after seeding:');
    console.table(allUsersInDb.rows);
    // --- END DEBUG LOGGING ---

    await client.query('COMMIT');
    logger.info('✅ Database seeding completed successfully!');

  } catch (error) {
    // Check if the error is a detailed PostgreSQL error object.
    if (error && typeof error === 'object' && 'code' in error && 'message' in error) {
        const dbError = error as { code: string; message: string; detail?: string; table?: string };
        logger.error('🔴 A database error occurred during seeding.', {
            code: dbError.code,
            message: dbError.message,
            detail: dbError.detail,
            table: dbError.table,
        });
    } else {
        // Log a generic error if it's not a standard DB error (e.g., connection failed).
        logger.error('🔴 An unexpected error occurred during seeding.', { error });
    }

    if (client) {
        await client.query('ROLLBACK');
        logger.warn('Database transaction rolled back.');
    }

    process.exit(1); // Exit with an error code
  } finally {
    if (client) client.release();
    await pool.end();
    logger.info('Database connection pool closed.');
  }
}

main();
