#!/usr/bin/env node
/**
 * Seed Demo Data Script
 * Creates realistic bills across common categories for demo purposes.
 * Idempotent: can be run multiple times safely.
 */

const path = require('path');

// Use DB_PATH from env or default to db/bills.db
const DB_PATH = process.env.DB_PATH || path.join(__dirname, '..', 'db', 'bills.db');

// Import database helper
const { getDb, ensureUserDefaultCategories } = require('../db/database');
// Money columns (expected_amount, current_balance, minimum_payment) are stored as
// integer cents since migration v1.03 — convert the demo dollars before insert.
const { toCents } = require('../utils/money.mts');

const CATEGORIES = [
  'Utilities',
  'Housing',
  'Insurance',
  'Subscriptions',
  'Transportation',
  'Healthcare',
  'Credit Cards',
  'Loans',
  'Entertainment',
];

// billing_cycle CHECK: 'monthly' | 'quarterly' | 'annually' | 'irregular'
// cycle_type   CHECK: 'monthly' | 'weekly' | 'biweekly' | 'quarterly' | 'annual'
const BILLS = [
  // ── Utilities ─────────────────────────────────────────────────────────────
  {
    name: 'Electric Company',
    category: 'Utilities',
    amount: 85,
    dueDay: 15,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 0,
  },
  {
    name: 'Gas Utility',
    category: 'Utilities',
    amount: 35,
    dueDay: 12,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 0,
  },
  {
    name: 'City Water Dept',
    category: 'Utilities',
    amount: 45,
    dueDay: 20,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 0,
  },
  {
    name: 'Internet Provider',
    category: 'Utilities',
    amount: 70,
    dueDay: 18,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 0,
  },
  {
    name: 'Cell Phone',
    category: 'Utilities',
    amount: 65,
    dueDay: 25,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 0,
    currentBalance: 648,   // remaining on 24-month 0% carrier installment plan
    minPayment: 27,
    snowballOrder: 0,      // smallest balance — first in snowball
    snowballInclude: 1,
  },
  {
    name: 'Trash Service',
    category: 'Utilities',
    amount: 25,
    dueDay: 28,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 0,
  },

  // ── Housing ───────────────────────────────────────────────────────────────
  {
    name: 'Mortgage',
    category: 'Housing',
    amount: 1450,          // paying $250/mo above minimum → extra principal paydown
    dueDay: 1,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 3.25,
    currentBalance: 185000,
    minPayment: 1200,
    snowballOrder: null,   // not included in snowball — too large, handled separately
    snowballInclude: 0,
    snowballExempt: 1,
  },

  // ── Insurance ─────────────────────────────────────────────────────────────
  {
    name: 'Car Insurance',
    category: 'Insurance',
    amount: 120,
    dueDay: 5,
    cycle: 'quarterly',
    cycleType: 'quarterly',
    autopay: true,
    interestRate: 0,
  },
  {
    name: 'Homeowners Insurance',
    category: 'Insurance',
    amount: 300,
    dueDay: 10,
    cycle: 'annually',
    cycleType: 'annual',
    autopay: false,
    interestRate: 0,
  },
  {
    name: 'Health Insurance',
    category: 'Healthcare',
    amount: 200,
    dueDay: 1,
    cycle: 'quarterly',
    cycleType: 'quarterly',
    autopay: true,
    interestRate: 0,
  },
  {
    name: 'Dental Insurance',
    category: 'Healthcare',
    amount: 40,
    dueDay: 15,
    cycle: 'quarterly',
    cycleType: 'quarterly',
    autopay: true,
    interestRate: 0,
  },

  // ── Credit Cards (Snowball — ordered smallest→largest balance) ─────────────
  {
    name: 'Discover It Card',
    category: 'Credit Cards',
    amount: 65,
    dueDay: 26,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 22.99,
    currentBalance: 920,
    minPayment: 35,
    snowballOrder: 1,
    snowballInclude: 1,
    website: 'https://discover.com',
    has2fa: true,
  },
  {
    name: 'Capital One Quicksilver',
    category: 'Credit Cards',
    amount: 95,
    dueDay: 28,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 24.49,
    currentBalance: 1850,
    minPayment: 55,
    snowballOrder: 2,
    snowballInclude: 1,
    website: 'https://capitalone.com',
    has2fa: true,
  },
  {
    name: 'Chase Freedom',
    category: 'Credit Cards',
    amount: 140,
    dueDay: 12,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 21.49,
    currentBalance: 3200,
    minPayment: 90,
    snowballOrder: 3,
    snowballInclude: 1,
    website: 'https://chase.com',
    has2fa: true,
  },

  // ── Loans (Snowball — ordered by balance after credit cards) ──────────────
  {
    name: 'Car Payment',
    category: 'Loans',
    amount: 425,           // paying $75/mo above contractual minimum to shorten payoff
    dueDay: 22,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 4.5,
    currentBalance: 8400,
    minPayment: 350,
    snowballOrder: 4,
    snowballInclude: 1,
  },
  {
    name: 'Student Loan',
    category: 'Loans',
    amount: 250,
    dueDay: 15,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 5.5,
    currentBalance: 12500,
    minPayment: 150,
    snowballOrder: 5,
    snowballInclude: 1,
  },

  // ── Subscriptions ─────────────────────────────────────────────────────────
  {
    name: 'Netflix',
    category: 'Subscriptions',
    amount: 15.99,
    dueDay: 22,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 0,
    isSubscription: true,
    subscriptionType: 'streaming',
    website: 'https://netflix.com',
  },
  {
    name: 'Spotify',
    category: 'Subscriptions',
    amount: 9.99,
    dueDay: 14,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 0,
    isSubscription: true,
    subscriptionType: 'music',
    website: 'https://spotify.com',
  },
  {
    name: 'Adobe Creative Cloud',
    category: 'Subscriptions',
    amount: 54.99,
    dueDay: 8,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 0,
    isSubscription: true,
    subscriptionType: 'software',
    website: 'https://adobe.com',
    has2fa: true,
  },
  {
    name: 'Amazon Prime',
    category: 'Subscriptions',
    amount: 139,
    dueDay: 1,
    cycle: 'annually',
    cycleType: 'annual',
    autopay: true,
    interestRate: 0,
    isSubscription: true,
    subscriptionType: 'shopping',
    website: 'https://amazon.com',
  },
  {
    name: 'Apple iCloud+',
    category: 'Subscriptions',
    amount: 2.99,
    dueDay: 18,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 0,
    isSubscription: true,
    subscriptionType: 'cloud',
    website: 'https://icloud.com',
  },
  {
    name: 'Gym Membership',
    category: 'Subscriptions',
    amount: 45,
    dueDay: 10,
    cycle: 'monthly',
    cycleType: 'monthly',
    autopay: true,
    interestRate: 0,
    isSubscription: true,
    subscriptionType: 'fitness',
  },

  // ── Entertainment ─────────────────────────────────────────────────────────
  {
    name: 'Grocery Delivery',
    category: 'Entertainment',
    amount: 30,
    dueDay: 3,
    cycle: 'irregular',
    cycleType: 'monthly',
    autopay: false,
    interestRate: 0,
  },
];

/**
 * Get or create a category by name for a user
 */
function getCategoryByName(db, userId, name) {
  let category = db.prepare('SELECT id FROM categories WHERE user_id = ? AND LOWER(name) = LOWER(?)').get(userId, name);
  if (!category) {
    const result = db.prepare('INSERT INTO categories (user_id, name) VALUES (?, ?)').run(userId, name);
    category = { id: result.lastInsertRowid };
  }
  return category;
}

/**
 * Main seed function
 * @param {number} [userId] - User ID to seed data for. If not provided, uses the first admin user.
 */
function seedDemoData(userId = null) {
  const db = getDb();

  // Check if data already exists for this user (if userId provided) or globally
  let existingCheck;
  if (userId !== null) {
    existingCheck = db.prepare('SELECT COUNT(*) AS count FROM bills WHERE user_id = ?').get(userId);
  } else {
    existingCheck = db.prepare('SELECT COUNT(*) AS count FROM bills').get();
  }

  if (existingCheck.count > 0) {
    console.log(`⚠️  Found ${existingCheck.count} existing bills. Skipping seed to prevent duplicates.`);
    console.log('   Run again with --force to overwrite.');
    return { billsCreated: 0, categoriesCreated: 0, message: 'Data already exists' };
  }

  // Get user (or admin if userId not provided)
  let targetUser;
  if (userId !== null) {
    targetUser = db.prepare('SELECT id FROM users WHERE id = ?').get(userId);
  } else {
    targetUser = db.prepare('SELECT id FROM users WHERE role = ? ORDER BY id LIMIT 1', 'admin').get();
  }

  if (!targetUser) {
    throw new Error('User not found. Please create a user first.');
  }

  const targetUserId = targetUser.id;
  console.log(`📝 Seeding demo data for user: ${targetUserId}`);

  // Ensure default categories exist for this user
  ensureUserDefaultCategories(targetUserId);

  // Create our demo categories if they don't exist
  const categoriesMap = {};
  let categoriesCreated = 0;

  for (const categoryName of CATEGORIES) {
    const before = db.prepare('SELECT id FROM categories WHERE user_id = ? AND LOWER(name) = LOWER(?)').get(targetUserId, categoryName);
    const category = getCategoryByName(db, targetUserId, categoryName);
    categoriesMap[categoryName] = category.id;
    db.prepare('UPDATE categories SET is_seeded = 1 WHERE id = ?').run(category.id);
    if (!before) categoriesCreated++;
  }

  // Create bills
  let billsCreated = 0;
  const insertBill = db.prepare(`
    INSERT INTO bills (
      user_id, name, category_id, due_day,
      billing_cycle, cycle_type,
      expected_amount, autopay_enabled, interest_rate,
      current_balance, minimum_payment,
      snowball_order, snowball_include, snowball_exempt,
      is_subscription, subscription_type,
      website, has_2fa,
      active, is_seeded
    )
    VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, 1, 1)
  `);

  for (const billData of BILLS) {
    const category = categoriesMap[billData.category];

    try {
      insertBill.run(
        targetUserId,
        billData.name,
        category,
        billData.dueDay || Math.floor(Math.random() * 28) + 1,
        billData.cycle    || 'monthly',
        billData.cycleType || 'monthly',
        toCents(billData.amount),
        billData.autopay !== undefined ? (billData.autopay ? 1 : 0) : 0,
        billData.interestRate ?? 0,
        billData.currentBalance != null ? toCents(billData.currentBalance) : null,
        billData.minPayment      != null ? toCents(billData.minPayment)      : null,
        billData.snowballOrder   ?? null,
        billData.snowballInclude ?? 0,
        billData.snowballExempt  ?? 0,
        billData.isSubscription  ? 1 : 0,
        billData.subscriptionType ?? null,
        billData.website         ?? null,
        billData.has2fa          ? 1 : 0,
      );
      billsCreated++;
    } catch (err) {
      console.error(`Failed to create bill "${billData.name}":`, err.message);
    }
  }

  console.log(`✅ Created ${billsCreated} demo bills`);
  console.log(`✅ Created ${categoriesCreated} demo categories`);

  return { billsCreated, categoriesCreated };
}

// Run seed if called directly
if (require.main === module) {
  try {
    const result = seedDemoData();
    console.log('\nSeed complete:', result);
    process.exit(0);
  } catch (err) {
    console.error('Seed failed:', err.message);
    process.exit(1);
  }
}

module.exports = { seedDemoData };
