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

export interface ValenciaDistrict {
  id?: number;
  name: string;
  idealista_slug: string;
  fotocasa_slug: string;
  pisos_slug: string;
  active?: number;
  sort_order?: number;
}

export const ValenciaDistrictModel = {
  async findAll(): Promise<ValenciaDistrict[]> {
    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT * FROM valencia_district_slugs ORDER BY sort_order ASC, name ASC`
    );
    return rows as ValenciaDistrict[];
  },

  async findActive(): Promise<ValenciaDistrict[]> {
    const [rows] = await pool.query<RowDataPacket[]>(
      `SELECT * FROM valencia_district_slugs WHERE active = 1 ORDER BY sort_order ASC, name ASC`
    );
    return rows as ValenciaDistrict[];
  },

  async create(d: ValenciaDistrict): Promise<number> {
    const [result] = await pool.query<ResultSetHeader>(
      `INSERT INTO valencia_district_slugs
      (name, idealista_slug, fotocasa_slug, pisos_slug, active, sort_order)
      VALUES (?, ?, ?, ?, ?, ?)`,
      [d.name, d.idealista_slug, d.fotocasa_slug, d.pisos_slug, d.active ?? 1, d.sort_order ?? 999]
    );
    return result.insertId;
  },

  async update(id: number, d: Partial<ValenciaDistrict>): Promise<void> {
    const allowed: (keyof ValenciaDistrict)[] = ["name", "idealista_slug", "fotocasa_slug", "pisos_slug", "active", "sort_order"];
    const updates = allowed.filter(k => d[k] !== undefined);
    if (!updates.length) return;
    const fields = updates.map(k => `${k} = ?`).join(", ");
    const values = [...updates.map(k => (d as any)[k]), id];
    await pool.query(`UPDATE valencia_district_slugs SET ${fields} WHERE id = ?`, values);
  },

  async toggleActive(id: number): Promise<void> {
    await pool.query("UPDATE valencia_district_slugs SET active = IF(active=1,0,1) WHERE id = ?", [id]);
  },

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