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

export const PropertyModel = {
  async findAll(filters?: Partial<Property>): Promise<Property[]> {
    let query = "SELECT * FROM properties WHERE deleted_at IS NULL";
    const params: any[] = [];
    if (filters?.municipality) {
      query += " AND municipality LIKE ?";
      params.push(`%${filters.municipality}%`);
    }
    if (filters?.property_type) {
      query += " AND property_type = ?";
      params.push(filters.property_type);
    }
    if (filters?.operation_type) {
      query += " AND operation_type = ?";
      params.push(filters.operation_type);
    }
    query += " ORDER BY COALESCE(last_seen_at, updated_at) DESC";
    const [rows] = await pool.query(query, params);
    return rows as Property[];
  },

  /** Estadísticas globales del dashboard (sin cargar todas las filas) */
  async getStats(operationType?: "sale" | "rent"): Promise<{ total: number; avg_price: number; min_price: number; source_count: number }> {
    const whereOperation = operationType ? " AND operation_type = ?" : "";
    const opParams = operationType ? [operationType] : [];
    const [[s]] = (await pool.query<RowDataPacket[]>(
      `SELECT COUNT(*) AS total,
              COALESCE(AVG(price),0) AS avg_price,
              COALESCE(MIN(price),0) AS min_price,
              COUNT(DISTINCT source) AS source_count
       FROM properties WHERE deleted_at IS NULL${whereOperation}`,
      opParams
    )) as any;
    return {
      total: Number(s.total),
      avg_price: Number(s.avg_price),
      min_price: Number(s.min_price),
      source_count: Number(s.source_count),
    };
  },

  /** @deprecated usar getStats() + findPaginatedDT() en su lugar */
  async findForDashboard(limit = 400): Promise<{ rows: Property[]; total: number; avg_price: number; min_price: number }> {
    const stats = await PropertyModel.getStats();
    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT * FROM properties WHERE deleted_at IS NULL ORDER BY COALESCE(score,0) DESC, COALESCE(last_seen_at, updated_at) DESC LIMIT ?`,
      [limit]
    );
    return { rows: rows as Property[], total: stats.total, avg_price: stats.avg_price, min_price: stats.min_price };
  },

  /** Carga mínima para el mapa: sólo columnas necesarias para los markers */
  async findForMap(operationType?: "sale" | "rent"): Promise<Pick<Property, "id" | "title" | "price" | "lat" | "lng" | "source" | "municipality" | "property_type" | "image_url" | "operation_type">[]> {
    const whereOperation = operationType ? " AND operation_type = ?" : "";
    const opParams = operationType ? [operationType] : [];
    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT id, title, price, lat, lng, source, municipality, property_type, image_url, operation_type
       FROM properties WHERE lat IS NOT NULL AND lng IS NOT NULL AND deleted_at IS NULL
       ${whereOperation}
       ORDER BY COALESCE(last_seen_at, updated_at) DESC LIMIT 2000`,
      opParams
    );
    return rows as any;
  },

  async findById(id: number, userId = 0): Promise<Property | null> {
    const [rows] = await pool.query(
      `SELECT p.*, COALESCE(s.is_favorite, 0) AS is_favorite, s.notes AS notes
         FROM properties p
         LEFT JOIN user_property_state s ON s.property_id = p.id AND s.user_id = ?
        WHERE p.id = ?`,
      [userId, id]
    );
    const arr = rows as Property[];
    return arr.length > 0 ? arr[0] : null;
  },

  async upsertWithStatus(prop: Property & { price_per_m2?: number }): Promise<{ id: number; isNew: boolean }> {
    const q = `INSERT INTO properties
      (title, price, location, municipality, property_type, operation_type, rooms, bathrooms, size_m2, description, image_url, url, source, lat, lng, price_per_m2, updated_at, last_seen_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, NOW(), NOW())
      ON DUPLICATE KEY UPDATE
        title=VALUES(title), price=VALUES(price), location=VALUES(location),
        municipality=VALUES(municipality), property_type=VALUES(property_type), operation_type=VALUES(operation_type),
        rooms=VALUES(rooms), bathrooms=VALUES(bathrooms), size_m2=VALUES(size_m2),
        description=VALUES(description), image_url=VALUES(image_url),
        source=VALUES(source), lat=VALUES(lat), lng=VALUES(lng),
        price_per_m2=VALUES(price_per_m2), deleted_at=NULL, updated_at=NOW(), last_seen_at=NOW(),
        id=LAST_INSERT_ID(id)`;
    const [result] = await pool.query<ResultSetHeader>(q, [
      prop.title, prop.price, prop.location, prop.municipality || null,
      prop.property_type, prop.operation_type || "sale", prop.rooms || null, prop.bathrooms || null, prop.size_m2 || null,
      prop.description || null, prop.image_url || null, prop.url, prop.source,
      prop.lat || null, prop.lng || null, prop.price_per_m2 || null,
    ]);
    return { id: result.insertId, isNew: result.affectedRows === 1 };
  },

  async upsert(prop: Property & { price_per_m2?: number }): Promise<number> {
    const result = await PropertyModel.upsertWithStatus(prop);
    return result.id;
  },

  /** Soft-delete: marca como eliminado (queda en archivo 30 días) */
  async deleteById(id: number): Promise<void> {
    await pool.query("UPDATE properties SET deleted_at = NOW() WHERE id = ? AND deleted_at IS NULL", [id]);
  },

  /** Restaura una propiedad eliminada al listado activo */
  async restoreById(id: number): Promise<void> {
    await pool.query("UPDATE properties SET deleted_at = NULL WHERE id = ?", [id]);
  },

  /** Eliminación permanente e irrecuperable */
  async hardDeleteById(id: number): Promise<void> {
    const connection = await pool.getConnection();
    try {
      await connection.beginTransaction();
      await connection.query("DELETE FROM user_property_state WHERE property_id = ?", [id]);
      await connection.query("DELETE FROM filter_alert_deliveries WHERE property_id = ?", [id]);
      await connection.query("DELETE FROM price_history WHERE property_id = ?", [id]);
      await connection.query("DELETE FROM properties WHERE id = ?", [id]);
      await connection.commit();
    } catch (error) {
      await connection.rollback();
      throw error;
    } finally {
      connection.release();
    }
  },

  /** Devuelve propiedades en el archivo (soft-deleted), paginadas */
  async findDeleted(page = 1, perPage = 100): Promise<{ rows: Property[]; total: number }> {
    const offset = (page - 1) * perPage;
    const [[{ total }]] = (await pool.query<RowDataPacket[]>(
      "SELECT COUNT(*) AS total FROM properties WHERE deleted_at IS NOT NULL"
    )) as any;
    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT * FROM properties WHERE deleted_at IS NOT NULL
       ORDER BY deleted_at DESC LIMIT ? OFFSET ?`,
      [perPage, offset]
    );
    return { rows: rows as Property[], total: Number(total) };
  },

  /** Purga propiedades eliminadas hace más de 30 días */
  async purgeOldDeleted(): Promise<number> {
    const [result] = await pool.query<ResultSetHeader>(
      "DELETE FROM properties WHERE deleted_at IS NOT NULL AND deleted_at < DATE_SUB(NOW(), INTERVAL 30 DAY)"
    );
    return result.affectedRows;
  },

  /**
   * Archiva (soft-delete) propiedades de un portal y operación que NO fueron
   * detectadas en el último scraping (su `last_seen_at` es anterior al inicio).
   * @param source   Nombre del portal, p. ej. "Idealista"
   * @param operationType Operación del ciclo; venta nunca debe archivar alquiler
   * @param olderThan Fecha de inicio del scraping — todo lo no actualizado desde entonces se archiva
   * @returns Número de propiedades archivadas
   */
  async archiveStaleBySource(
    source: string,
    operationType: "sale" | "rent",
    olderThan: Date
  ): Promise<number> {
    // Exigimos que la propiedad lleve AL MENOS 2 días sin aparecer en ningún ciclo
    // de scraping antes de archivarla. Esto evita que un fallo puntual del scraper
    // (403, anti-bot, red) borre propiedades que siguen publicadas en el portal.
    const gracePeriodMs = 2 * 24 * 60 * 60 * 1000; // 2 días en ms
    const threshold = new Date(olderThan.getTime() - gracePeriodMs);
    const [result] = await pool.query<ResultSetHeader>(
      `UPDATE properties SET deleted_at = NOW()
       WHERE source = ? AND operation_type = ?
         AND COALESCE(last_seen_at, updated_at) < ? AND deleted_at IS NULL`,
      [source, operationType, threshold]
    );
    return result.affectedRows;
  },

  async filterAdvanced(min_price?: number, max_price?: number, property_type?: string, municipality?: string, min_rooms?: number, min_size_m2?: number, operation_type?: "sale" | "rent", valencia_zone?: string, source?: string): Promise<Property[]> {
    let query = "SELECT * FROM properties WHERE deleted_at IS NULL";
    const params: any[] = [];
    if (min_price != null) { query += " AND price >= ?"; params.push(min_price); }
    if (max_price != null) { query += " AND price <= ?"; params.push(max_price); }
    if (property_type) { query += " AND property_type = ?"; params.push(property_type); }
    if (municipality) { query += " AND municipality LIKE ?"; params.push(`%${municipality}%`); }
    if (operation_type) { query += " AND operation_type = ?"; params.push(operation_type); }
    if (source) { query += " AND source = ?"; params.push(source); }
    if (valencia_zone) {
      query += " AND municipality LIKE '%Valencia%' AND (location LIKE ? OR title LIKE ?)";
      params.push(`%${valencia_zone}%`, `%${valencia_zone}%`);
    }
    if (min_rooms != null) { query += " AND rooms >= ?"; params.push(min_rooms); }
    if (min_size_m2 != null) { query += " AND size_m2 >= ?"; params.push(min_size_m2); }
    query += " ORDER BY price ASC";
    const [rows] = await pool.query(query, params);
    return rows as Property[];
  },

  async toggleFavorite(id: number, userId: number): Promise<boolean> {
    await pool.query(
      `INSERT INTO user_property_state (user_id, property_id, is_favorite)
       VALUES (?, ?, 1)
       ON DUPLICATE KEY UPDATE is_favorite = IF(is_favorite = 1, 0, 1)`,
      [userId, id]
    );
    const [rows] = await pool.query<RowDataPacket[]>(
      "SELECT is_favorite FROM user_property_state WHERE user_id = ? AND property_id = ?",
      [userId, id]
    );
    return Boolean(rows[0]?.is_favorite);
  },

  async updateNotes(id: number, userId: number, notes: string): Promise<void> {
    await pool.query(
      `INSERT INTO user_property_state (user_id, property_id, notes)
       VALUES (?, ?, ?)
       ON DUPLICATE KEY UPDATE notes = VALUES(notes)`,
      [userId, id, notes || null]
    );
  },

  async findFavorites(userId: number): Promise<Property[]> {
    const [rows] = await pool.query(
      `SELECT p.*, s.is_favorite, s.notes
         FROM properties p
         JOIN user_property_state s ON s.property_id = p.id AND s.user_id = ? AND s.is_favorite = 1
        WHERE p.deleted_at IS NULL
        ORDER BY p.score DESC, COALESCE(p.last_seen_at, p.updated_at) DESC`,
      [userId]
    );
    return rows as Property[];
  },

  async findPaginated(
    page: number,
    perPage: number,
    search?: string
  ): Promise<{ rows: Property[]; total: number }> {
    const offset = (page - 1) * perPage;
    let where = "WHERE deleted_at IS NULL";
    const params: any[] = [];
    if (search) {
      where += " AND (title LIKE ? OR location LIKE ? OR municipality LIKE ?)";
      params.push(`%${search}%`, `%${search}%`, `%${search}%`);
    }
    const [[{ total }]] = (await pool.query<RowDataPacket[]>(
      `SELECT COUNT(*) AS total FROM properties ${where}`,
      params
    )) as any;
    const [rows] = await pool.query(
      `SELECT * FROM properties ${where} ORDER BY COALESCE(last_seen_at, updated_at) DESC LIMIT ? OFFSET ?`,
      [...params, perPage, offset]
    );
    return { rows: rows as Property[], total: Number(total) };
  },

  /**
   * Endpoint principal para DataTables server-side.
   * Soporta búsqueda global, filtros por columna, ordenación y paginación.
   * Cuando deleted=true devuelve el archivo de eliminados.
   */
  async findPaginatedDT(params: {
    draw: number;
    start: number;
    length: number;
    search?: string;
    orderCol?: number;
    orderDir?: "asc" | "desc";
    municipality?: string;
    property_type?: string;
    min_price?: number;
    max_price?: number;
    min_rooms?: number;
    source?: string;
    deleted?: boolean;
    operation_type?: "sale" | "rent";
    valencia_zone?: string;
    userId?: number;
  }): Promise<{ draw: number; recordsTotal: number; recordsFiltered: number; data: Property[] }> {
    const {
      draw, start, length, search,
      orderCol = 2, orderDir = "asc",
      municipality, property_type,
      min_price, max_price, min_rooms, source, operation_type, valencia_zone,
      deleted = false,
      userId = 0,
    } = params;

    // Columnas ordenables (índice DT → campo DB)
    const sortableFields: Record<number, string> = {
      1: "title", 2: "price", 3: "price_per_m2", 4: "score",
      5: "municipality", 6: "property_type", 7: "rooms", 8: "size_m2", 9: "source",
    };
    const orderField = sortableFields[orderCol] ?? "price";
    const safeDir = orderDir === "desc" ? "DESC" : "ASC";

    // Base WHERE
    const deletedCondition = deleted ? "deleted_at IS NOT NULL" : "deleted_at IS NULL";
    let where = `WHERE ${deletedCondition}`;
    const queryParams: any[] = [];

    // Filtros personalizados
    if (municipality) { where += " AND municipality LIKE ?"; queryParams.push(`%${municipality}%`); }
    if (property_type) { where += " AND property_type = ?"; queryParams.push(property_type); }
    if (min_price != null && min_price > 0) { where += " AND price >= ?"; queryParams.push(min_price); }
    if (max_price != null && max_price > 0) { where += " AND price <= ?"; queryParams.push(max_price); }
    if (min_rooms != null && min_rooms > 0) { where += " AND rooms >= ?"; queryParams.push(min_rooms); }
    if (source) { where += " AND source LIKE ?"; queryParams.push(`%${source}%`); }
    if (operation_type) { where += " AND operation_type = ?"; queryParams.push(operation_type); }
    if (valencia_zone) {
      where += " AND municipality LIKE '%Valencia%' AND (location LIKE ? OR title LIKE ?)";
      queryParams.push(`%${valencia_zone}%`, `%${valencia_zone}%`);
    }

    // Total sin filtro de búsqueda (para recordsTotal)
    const [[{ recordsTotal }]] = (await pool.query<RowDataPacket[]>(
      `SELECT COUNT(*) AS recordsTotal FROM properties ${where}`, queryParams
    )) as any;

    // Búsqueda global
    const searchParams = [...queryParams];
    if (search) {
      where += " AND (title LIKE ? OR location LIKE ? OR municipality LIKE ? OR source LIKE ?)";
      const s = `%${search}%`;
      searchParams.push(s, s, s, s);
    }

    // Total con filtro de búsqueda (para recordsFiltered)
    const [[{ recordsFiltered }]] = (await pool.query<RowDataPacket[]>(
      `SELECT COUNT(*) AS recordsFiltered FROM properties ${where}`, searchParams
    )) as any;

    // Datos de la página
    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT properties.*,
              COALESCE((SELECT s.is_favorite FROM user_property_state s WHERE s.user_id = ? AND s.property_id = properties.id), 0) AS is_favorite,
              (SELECT s.notes FROM user_property_state s WHERE s.user_id = ? AND s.property_id = properties.id) AS notes
         FROM properties ${where} ORDER BY ${orderField} ${safeDir} LIMIT ? OFFSET ?`,
      [userId, userId, ...searchParams, length, start]
    );

    return {
      draw,
      recordsTotal: Number(recordsTotal),
      recordsFiltered: Number(recordsFiltered),
      data: rows as Property[],
    };
  },
};