import pool from "../config/db";
import { Filter } from "../types/filter";

export const FilterModel = {
  async findAll(userId: number): Promise<Filter[]> {
    const [rows] = await pool.query("SELECT * FROM filters WHERE user_id = ? ORDER BY id DESC", [userId]);
    return rows as Filter[];
  },

  async findById(id: number, userId: number): Promise<Filter | null> {
    const [rows] = await pool.query("SELECT * FROM filters WHERE id = ? AND user_id = ?", [id, userId]);
    const arr = rows as Filter[];
    return arr.length > 0 ? arr[0] : null;
  },

  async create(filter: Filter, userId: number): Promise<Filter> {
    const q = `INSERT INTO filters (user_id, name, min_price, max_price, property_type, operation_type, municipality, min_rooms, min_size_m2, portal, email_alerts)
               VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;
    const [result] = await pool.query(q, [
      userId,
      filter.name || null, filter.min_price || null, filter.max_price || null,
      filter.property_type || null, filter.operation_type || null,
      filter.municipality || null,
      filter.min_rooms || null, filter.min_size_m2 || null,
      filter.portal || null,
      filter.email_alerts ? 1 : 0
    ]);
    const r = result as any;
    return { ...filter, id: r.insertId, user_id: userId };
  },

  async update(id: number, userId: number, filter: Partial<Filter>): Promise<void> {
    const q = `UPDATE filters SET name=?, min_price=?, max_price=?, property_type=?, operation_type=?,
               municipality=?, min_rooms=?, min_size_m2=?, portal=?, email_alerts=? WHERE id=? AND user_id=?`;
    await pool.query(q, [
      filter.name || null, filter.min_price || null, filter.max_price || null,
      filter.property_type || null, filter.operation_type || null, filter.municipality || null,
      filter.min_rooms || null, filter.min_size_m2 || null,
      filter.portal || null,
      filter.email_alerts ? 1 : 0, id, userId
    ]);
  },

  async deleteById(id: number, userId: number): Promise<void> {
    await pool.query("DELETE FROM filters WHERE id = ? AND user_id = ?", [id, userId]);
  },

  async findEmailAlerts(): Promise<Filter[]> {
    const [rows] = await pool.query(
      `SELECT f.*, u.email AS recipient_email
         FROM filters f LEFT JOIN users u ON u.id = f.user_id
        WHERE f.email_alerts = 1`
    );
    return rows as Filter[];
  },

  async excludeDelivered<T extends { id?: number }>(filterId: number, properties: T[]): Promise<T[]> {
    const ids = properties.map(property => property.id).filter((id): id is number => Number.isInteger(id));
    if (ids.length === 0) return [];
    const placeholders = ids.map(() => "?").join(",");
    const [rows] = await pool.query<any[]>(
      `SELECT property_id FROM filter_alert_deliveries WHERE filter_id = ? AND property_id IN (${placeholders})`,
      [filterId, ...ids]
    );
    const delivered = new Set(rows.map(row => Number(row.property_id)));
    return properties.filter(property => property.id && !delivered.has(property.id));
  },

  async markDelivered(filterId: number, propertyIds: number[]): Promise<void> {
    if (propertyIds.length === 0) return;
    const placeholders = propertyIds.map(() => "(?, ?)").join(",");
    const params = propertyIds.flatMap(propertyId => [filterId, propertyId]);
    await pool.query(
      `INSERT IGNORE INTO filter_alert_deliveries (filter_id, property_id) VALUES ${placeholders}`,
      params
    );
  }
};