// 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, PoolClient } from 'pg';
import fs from 'node:fs/promises';
import path from 'node:path';
import bcrypt from 'bcrypt';
import { logger } from '../services/logger.server';

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() {
  let client: PoolClient | undefined;

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

    // 1. Clean the database by dropping and recreating the schema
    logger.info('--- Wiping and rebuilding schema... ---');
    const dropScriptPath = path.resolve(process.cwd(), 'sql/drop_tables.sql');
    const dropSql = await fs.readFile(dropScriptPath, 'utf-8');
    await client.query(dropSql);
    logger.info('All tables dropped successfully.');

    const schemaScriptPath = path.resolve(process.cwd(), 'sql/master_schema_rollup.sql');
    const schemaSql = await fs.readFile(schemaScriptPath, 'utf-8');
    await client.query(schemaSql);
    logger.info(
      'Schema rebuilt and static data seeded successfully from master_schema_rollup.sql.',
    );

    // 2. Seed Additional Stores (if any beyond what's in the rollup)
    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(', ')} ON CONFLICT (name) DO NOTHING RETURNING store_id, name`;
    await client.query<{ store_id: number; name: string }>(storeQuery, stores);
    const allStores = (
      await client.query<{ store_id: number; name: string }>(
        'SELECT store_id, name FROM public.stores',
      )
    ).rows;
    const storeMap = new Map(
      allStores.map((s: { name: string; store_id: number }) => [s.name, s.store_id]),
    );
    logger.info(`Seeded/verified ${allStores.length} total stores.`);

    // Fetch maps for items seeded by the master rollup script
    const masterItemMap = new Map(
      (
        await client.query<{ master_grocery_item_id: number; name: string }>(
          'SELECT master_grocery_item_id, name FROM public.master_grocery_items',
        )
      ).rows.map((item: { name: string; master_grocery_item_id: number }) => [
        item.name,
        item.master_grocery_item_id,
      ]),
    );

    // 3. 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)');

    // 4. 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, icon_url, checksum, store_id, valid_from, valid_to)
        VALUES ('safeway-flyer.jpg', 'https://example.com/flyer-images/safeway-flyer.jpg', 'https://example.com/flyer-images/icons/safeway-flyer.jpg', 'a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0a0', ${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}).`);

    // 5. Seed Flyer Items
    logger.info('--- Seeding Flyer Items... ---');
    const flyerItems = [
      {
        name: 'chicken breast',
        price_display: '$3.99 /lb',
        price_in_cents: 399,
        quantity: 'per lb',
        master_item_id: masterItemMap.get('chicken breast'),
      },
      {
        name: 'avocados',
        price_display: '2 for $5.00',
        price_in_cents: 250,
        quantity: 'each',
        master_item_id: masterItemMap.get('avocados'),
      },
      {
        name: 'soda',
        price_display: '$6.99',
        price_in_cents: 699,
        quantity: '12x355ml',
        master_item_id: masterItemMap.get('soda'),
      },
      {
        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.`);

    // 6. Seed Watched Items for the user
    logger.info('--- Seeding Watched Items... ---');
    const watchedItemIds = [
      masterItemMap.get('chicken breast'),
      masterItemMap.get('avocados'),
      masterItemMap.get('ground beef'),
    ];
    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.`);

    // 7. 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'), quantity: 1 },
      { master_item_id: masterItemMap.get('eggs'), 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.`,
    );

    // --- 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(
        {
          code: dbError.code,
          message: dbError.message,
          detail: dbError.detail,
          table: dbError.table,
        },
        '🔴 A database error occurred during seeding.',
      );
    } else {
      // Log a generic error if it's not a standard DB error (e.g., connection failed).
      logger.error({ error }, '🔴 An unexpected error occurred during seeding.');
    }

    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();
