cannaiq/backend/archive/add-price-history.ts

import { pool } from './src/db/migrate';

async function addPriceHistory() {
  console.log('💰 Adding price history tracking...\n');

  const client = await pool.connect();

  try {
    await client.query('BEGIN');

    // Step 1: Create price_history table
    console.log('1. Creating price_history table...');
    await client.query(`
      CREATE TABLE IF NOT EXISTS price_history (
        id SERIAL PRIMARY KEY,
        product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
        store_id INTEGER REFERENCES stores(id) ON DELETE CASCADE,
        brand_id INTEGER REFERENCES brands(id) ON DELETE SET NULL,
        category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
        product_name VARCHAR(500),
        price DECIMAL(10, 2),
        sale_price DECIMAL(10, 2),
        original_price DECIMAL(10, 2),
        discount_percentage DECIMAL(5, 2),
        discount_amount DECIMAL(10, 2),
        in_stock BOOLEAN DEFAULT true,
        is_special BOOLEAN DEFAULT false,
        recorded_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      )
    `);

    // Step 2: Add indexes for fast price queries
    console.log('2. Creating indexes for price queries...');
    await client.query(`
      CREATE INDEX IF NOT EXISTS idx_price_history_product ON price_history(product_id, recorded_at DESC);
      CREATE INDEX IF NOT EXISTS idx_price_history_store ON price_history(store_id, recorded_at DESC);
      CREATE INDEX IF NOT EXISTS idx_price_history_brand ON price_history(brand_id, recorded_at DESC);
      CREATE INDEX IF NOT EXISTS idx_price_history_date ON price_history(recorded_at DESC);
      CREATE INDEX IF NOT EXISTS idx_price_history_price_change ON price_history(product_id, price, recorded_at DESC);
    `);

    // Step 3: Create function to log price changes
    console.log('3. Creating price change trigger function...');
    await client.query(`
      CREATE OR REPLACE FUNCTION log_price_change()
      RETURNS TRIGGER AS $$
      BEGIN
        -- Only log if price actually changed or product just appeared
        IF (TG_OP = 'INSERT') OR 
           (OLD.price IS DISTINCT FROM NEW.price) OR 
           (OLD.sale_price IS DISTINCT FROM NEW.sale_price) OR
           (OLD.discount_percentage IS DISTINCT FROM NEW.discount_percentage) OR
           (OLD.in_stock IS DISTINCT FROM NEW.in_stock) THEN
          
          INSERT INTO price_history (
            product_id, store_id, brand_id, category_id, product_name,
            price, sale_price, original_price, discount_percentage, discount_amount,
            in_stock, is_special, recorded_at
          ) VALUES (
            NEW.id, NEW.store_id, NEW.brand_id, NEW.category_id, NEW.name,
            NEW.price, NEW.sale_price, NEW.original_price, 
            NEW.discount_percentage, NEW.discount_amount,
            NEW.in_stock, NEW.is_special, CURRENT_TIMESTAMP
          );
        END IF;
        
        RETURN NEW;
      END;
      $$ LANGUAGE plpgsql;
    `);

    // Step 4: Create trigger on products table
    console.log('4. Creating trigger on products table...');
    await client.query(`
      DROP TRIGGER IF EXISTS price_change_trigger ON products;
      CREATE TRIGGER price_change_trigger
      AFTER INSERT OR UPDATE ON products
      FOR EACH ROW
      EXECUTE FUNCTION log_price_change();
    `);

    // Step 5: Populate initial price history from existing products
    console.log('5. Populating initial price history...');
    await client.query(`
      INSERT INTO price_history (
        product_id, store_id, brand_id, category_id, product_name,
        price, sale_price, original_price, discount_percentage, discount_amount,
        in_stock, is_special, recorded_at
      )
      SELECT 
        id, store_id, brand_id, category_id, name,
        price, sale_price, original_price, discount_percentage, discount_amount,
        in_stock, is_special, COALESCE(first_seen_at, created_at)
      FROM products
      WHERE price IS NOT NULL
      ON CONFLICT DO NOTHING
    `);

    const countResult = await client.query('SELECT COUNT(*) FROM price_history');
    console.log(`   ✅ ${countResult.rows[0].count} price records created`);

    // Step 6: Create helpful views for price monitoring
    console.log('6. Creating price monitoring views...');
    
    // Price changes view
    await client.query(`
      CREATE OR REPLACE VIEW price_changes AS
      WITH price_with_previous AS (
        SELECT 
          ph.*,
          LAG(ph.price) OVER (PARTITION BY ph.product_id ORDER BY ph.recorded_at) as previous_price,
          LAG(ph.recorded_at) OVER (PARTITION BY ph.product_id ORDER BY ph.recorded_at) as previous_date
        FROM price_history ph
      )
      SELECT 
        pwp.product_id,
        pwp.product_name,
        s.name as store_name,
        b.name as brand_name,
        pwp.previous_price,
        pwp.price as current_price,
        pwp.price - pwp.previous_price as price_change,
        ROUND(((pwp.price - pwp.previous_price) / NULLIF(pwp.previous_price, 0) * 100)::numeric, 2) as price_change_percent,
        pwp.previous_date,
        pwp.recorded_at as current_date
      FROM price_with_previous pwp
      JOIN stores s ON pwp.store_id = s.id
      LEFT JOIN brands b ON pwp.brand_id = b.id
      WHERE pwp.previous_price IS NOT NULL
        AND pwp.price IS DISTINCT FROM pwp.previous_price
      ORDER BY pwp.recorded_at DESC
    `);

    // Current prices view
    await client.query(`
      CREATE OR REPLACE VIEW current_prices AS
      SELECT DISTINCT ON (product_id)
        ph.product_id,
        ph.product_name,
        s.name as store_name,
        b.name as brand_name,
        c.name as category_name,
        ph.price,
        ph.sale_price,
        ph.discount_percentage,
        ph.in_stock,
        ph.is_special,
        ph.recorded_at as last_updated
      FROM price_history ph
      JOIN stores s ON ph.store_id = s.id
      LEFT JOIN brands b ON ph.brand_id = b.id
      LEFT JOIN categories c ON ph.category_id = c.id
      ORDER BY ph.product_id, ph.recorded_at DESC
    `);

    // Price trends view (last 30 days)
    await client.query(`
      CREATE OR REPLACE VIEW price_trends_30d AS
      WITH price_data AS (
        SELECT
          ph.product_id,
          ph.product_name,
          s.name as store_name,
          ph.price,
          ph.recorded_at,
          ROW_NUMBER() OVER (PARTITION BY ph.product_id ORDER BY ph.recorded_at ASC) as first_row,
          ROW_NUMBER() OVER (PARTITION BY ph.product_id ORDER BY ph.recorded_at DESC) as last_row
        FROM price_history ph
        JOIN stores s ON ph.store_id = s.id
        WHERE ph.recorded_at >= CURRENT_DATE - INTERVAL '30 days'
      )
      SELECT
        product_id,
        product_name,
        store_name,
        COUNT(*) as price_points,
        MIN(price) as min_price,
        MAX(price) as max_price,
        ROUND(AVG(price)::numeric, 2) as avg_price,
        MAX(CASE WHEN first_row = 1 THEN price END) as starting_price,
        MAX(CASE WHEN last_row = 1 THEN price END) as current_price
      FROM price_data
      GROUP BY product_id, product_name, store_name
    `);

    await client.query('COMMIT');

    console.log('\n✅ Price history tracking enabled!');
    console.log('\n📊 Available price monitoring views:');
    console.log('  - price_changes: See all price increases/decreases');
    console.log('  - current_prices: Latest price for each product');
    console.log('  - price_trends_30d: Price trends over last 30 days');
    
    console.log('\n💡 Example queries:');
    console.log('  -- Recent price increases:');
    console.log('  SELECT * FROM price_changes WHERE price_change > 0 ORDER BY current_date DESC LIMIT 10;');
    console.log('  ');
    console.log('  -- Products on sale:');
    console.log('  SELECT * FROM current_prices WHERE sale_price IS NOT NULL;');
    console.log('  ');
    console.log('  -- Biggest price drops:');
    console.log('  SELECT * FROM price_changes WHERE price_change < 0 ORDER BY price_change ASC LIMIT 10;');

  } catch (error) {
    await client.query('ROLLBACK');
    console.error('❌ Error:', error);
    throw error;
  } finally {
    client.release();
    await pool.end();
  }
}

addPriceHistory();