import { PriceRecordModel } from '../../schemas/price-record.schema.js';
import type { PriceHistoryQueryInput } from '@meshitrack/shared';

export interface CreatePriceRecordData {
  householdId: string;
  productId: string;
  productName: string;
  storeId: string;
  storeName: string;
  price: number;
  currency: string;
  quantity: number;
  unit: string;
  pricePerUnit: number;
  date: Date;
  receiptImageUrl?: string;
  notes?: string;
  createdBy: string;
}

export class PricesRepository {
  public async create(data: CreatePriceRecordData) {
    const record = new PriceRecordModel(data);
    const saved = await record.save();
    return saved.toObject();
  }

  public async createMany(data: CreatePriceRecordData[]) {
    const records = await PriceRecordModel.insertMany(data);
    return records.map((r) => r.toObject());
  }

  public async findByProduct(
    householdId: string,
    productId: string,
    query: PriceHistoryQueryInput,
  ) {
    const filter: Record<string, unknown> = { householdId, productId };

    if (query.storeId) filter['storeId'] = query.storeId;

    if (query.startDate || query.endDate) {
      const dateFilter: Record<string, Date> = {};
      if (query.startDate) dateFilter['$gte'] = new Date(query.startDate);
      if (query.endDate) dateFilter['$lte'] = new Date(query.endDate);
      filter['date'] = dateFilter;
    }

    if (query.cursor) {
      const id = Buffer.from(query.cursor, 'base64').toString();
      filter['_id'] = { $lt: id };
    }

    const limit = query.limit || 20;
    const items = await PriceRecordModel.find(filter)
      .sort({ _id: -1 })
      .limit(limit + 1)
      .lean()
      .exec();

    const hasMore = items.length > limit;
    const data = hasMore ? items.slice(0, limit) : items;
    const cursor =
      data.length > 0 ? Buffer.from(data[data.length - 1]._id.toString()).toString('base64') : null;

    return { data, pagination: { cursor: hasMore ? cursor : null, hasMore } };
  }

  public async compareStores(householdId: string, productId: string) {
    const results = await PriceRecordModel.aggregate([
      { $match: { householdId, productId } },
      { $sort: { storeId: 1, date: -1 } },
      {
        $group: {
          _id: '$storeId',
          storeName: { $first: '$storeName' },
          latestPrice: { $first: '$price' },
          latestPricePerUnit: { $first: '$pricePerUnit' },
          currency: { $first: '$currency' },
          date: { $first: '$date' },
        },
      },
      { $sort: { latestPricePerUnit: 1 } },
    ]).exec();

    return results.map((r) => ({
      storeId: r._id as string,
      storeName: r.storeName as string,
      latestPrice: r.latestPrice as number,
      latestPricePerUnit: r.latestPricePerUnit as number,
      currency: r.currency as string,
      date: r.date as Date,
    }));
  }

  public async getLatestForProduct(householdId: string, productId: string, storeId?: string) {
    const filter: Record<string, unknown> = { householdId, productId };
    if (storeId) filter['storeId'] = storeId;
    return PriceRecordModel.findOne(filter).sort({ date: -1 }).lean().exec();
  }

  public async getAnalytics(householdId: string) {
    const [spendingOverTime, averageBasketByStore, spendingByCategory] = await Promise.all([
      // 1. Total Spending Over Time (by month)
      PriceRecordModel.aggregate([
        { $match: { householdId } },
        {
          $group: {
            _id: { $dateToString: { format: '%Y-%m', date: '$date' } },
            total: { $sum: '$price' },
          },
        },
        { $sort: { _id: 1 } },
        { $project: { _id: 0, period: '$_id', total: 1 } },
      ]).exec(),

      // 2. Average basket by store (grouping by Day + StoreId to simulate distinct trips)
      PriceRecordModel.aggregate([
        { $match: { householdId } },
        {
          $group: {
            _id: {
              storeId: '$storeId',
              day: { $dateToString: { format: '%Y-%m-%d', date: '$date' } },
            },
            storeName: { $first: '$storeName' },
            tripTotal: { $sum: '$price' },
          },
        },
        {
          $group: {
            _id: '$_id.storeId',
            storeName: { $first: '$storeName' },
            avgTotal: { $avg: '$tripTotal' },
            tripCount: { $sum: 1 },
          },
        },
        { $sort: { avgTotal: -1 } },
        { $project: { _id: 0, storeId: '$_id', storeName: 1, avgTotal: 1, tripCount: 1 } },
      ]).exec(),

      // 3. Spending by Category (Requires lookup join with Products)
      PriceRecordModel.aggregate([
        { $match: { householdId } },
        // Safeguard converting the dynamic string field to a valid ObjectId for proper joins
        {
          $addFields: {
            prodObjId: { $toObjectId: '$productId' },
          },
        },
        {
          $lookup: {
            from: 'products',
            localField: 'prodObjId',
            foreignField: '_id',
            as: 'matchedProduct',
          },
        },
        { $unwind: { path: '$matchedProduct', preserveNullAndEmptyArrays: true } },
        {
          $group: {
            _id: { $ifNull: ['$matchedProduct.category', 'other'] },
            total: { $sum: '$price' },
            avgPerItem: { $avg: '$price' },
          },
        },
        { $sort: { total: -1 } },
        { $project: { _id: 0, category: '$_id', total: 1, avgPerItem: 1 } },
      ]).exec(),
    ]);

    // 4. Detect product inflation alerts (>10% price rise on last purchase compared to prior one)
    const priceAlerts = await PriceRecordModel.aggregate([
      { $match: { householdId } },
      { $sort: { productId: 1, storeId: 1, date: -1 } },
      {
        $group: {
          _id: { productId: '$productId', storeId: '$storeId' },
          productName: { $first: '$productName' },
          storeName: { $first: '$storeName' },
          prices: { $push: '$pricePerUnit' },
          dates: { $push: '$date' },
        },
      },
      { $match: { 'prices.1': { $exists: true } } },
      {
        $addFields: {
          currentPrice: { $arrayElemAt: ['$prices', 0] },
          previousPrice: { $arrayElemAt: ['$prices', 1] },
          alertDate: { $arrayElemAt: ['$dates', 0] },
        },
      },
      {
        $addFields: {
          changePercent: {
            $multiply: [
              { $divide: [{ $subtract: ['$currentPrice', '$previousPrice'] }, '$previousPrice'] },
              100,
            ],
          },
        },
      },
      { $match: { changePercent: { $gt: 10 } } },
      {
        $project: {
          _id: 0,
          productId: '$_id.productId',
          storeId: '$_id.storeId',
          productName: 1,
          storeName: 1,
          previousPrice: 1,
          currentPrice: 1,
          changePercent: 1,
          date: '$alertDate',
        },
      },
    ]).exec();

    return {
      spendingOverTime: spendingOverTime as { period: string; total: number }[],
      averageBasketByStore: averageBasketByStore as {
        storeId: string;
        storeName: string;
        avgTotal: number;
        tripCount: number;
      }[],
      spendingByCategory: spendingByCategory as {
        category: string;
        total: number;
        avgPerItem: number;
      }[],
      priceAlerts: priceAlerts as {
        productId: string;
        storeId: string;
        productName: string;
        storeName: string;
        previousPrice: number;
        currentPrice: number;
        changePercent: number;
        date: Date;
      }[],
    };
  }
}
