import pool from "../config/db";
import { RowDataPacket, ResultSetHeader } from "mysql2";

export interface PriceRecord {
    id?: number;
    property_id: number;
    price: number;
    recorded_at?: Date;
}

export class PriceHistoryModel {
    /** Registra el precio actual si es diferente al último registrado */
    static async recordIfChanged(propertyId: number, price: number): Promise<boolean> {
        const [rows] = await pool.query<RowDataPacket[]>(
            `SELECT price FROM price_history WHERE property_id = ? ORDER BY recorded_at DESC LIMIT 1`,
            [propertyId]
        );
        const lastPrice = rows[0]?.price;
        if (lastPrice === undefined || Math.abs(Number(lastPrice) - price) > 1) {
            await pool.query<ResultSetHeader>(
                `INSERT INTO price_history (property_id, price) VALUES (?, ?)`,
                [propertyId, price]
            );
            return true;
        }
        return false;
    }

    /** Obtiene el historial de precios de una propiedad */
    static async getByProperty(propertyId: number): Promise<PriceRecord[]> {
        const [rows] = await pool.query<RowDataPacket[]>(
            `SELECT id, property_id, price, recorded_at FROM price_history
       WHERE property_id = ? ORDER BY recorded_at ASC`,
            [propertyId]
        );
        return rows as PriceRecord[];
    }

    /** Estadísticas de tendencia de precios por municipio */
    static async getPriceTrendsByZone(): Promise<any[]> {
        const [rows] = await pool.query<RowDataPacket[]>(`
      SELECT
        p.municipality,
        AVG(ph.price) AS avg_price,
        MIN(ph.recorded_at) AS first_record,
        MAX(ph.recorded_at) AS last_record,
        COUNT(DISTINCT ph.property_id) AS properties_count,
        COUNT(ph.id) AS total_records
      FROM price_history ph
      JOIN properties p ON p.id = ph.property_id
      GROUP BY p.municipality
      ORDER BY avg_price DESC
    `);
        return rows;
    }

    /** Tendencia de precio medio a lo largo del tiempo */
    static async getOverallTrend(days = 90): Promise<any[]> {
        const [rows] = await pool.query<RowDataPacket[]>(`
      SELECT
        DATE(recorded_at) AS day,
        AVG(price) AS avg_price,
        MIN(price) AS min_price,
        MAX(price) AS max_price,
        COUNT(*) AS count
      FROM price_history
      WHERE recorded_at >= DATE_SUB(NOW(), INTERVAL ? DAY)
      GROUP BY DATE(recorded_at)
      ORDER BY day ASC
    `, [days]);
        return rows;
    }

    /** Suma propiedades con bajada de precio */
    static async getPriceDropStats(): Promise<{ drops: number; rises: number }> {
        const [rows] = await pool.query<RowDataPacket[]>(`
      SELECT
        SUM(CASE WHEN ph2.price < ph1.price THEN 1 ELSE 0 END) AS drops,
        SUM(CASE WHEN ph2.price > ph1.price THEN 1 ELSE 0 END) AS rises
      FROM price_history ph1
      JOIN price_history ph2 ON ph1.property_id = ph2.property_id
        AND ph2.id = (
          SELECT MAX(id) FROM price_history WHERE property_id = ph1.property_id
        )
      WHERE ph1.id = (
        SELECT MIN(id) FROM price_history WHERE property_id = ph1.property_id
      )
    `);
        return { drops: Number(rows[0]?.drops) || 0, rises: Number(rows[0]?.rises) || 0 };
    }
}