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

export interface Zone {
    id?: number;
    name: string;
    slug: string;
    lat: number;
    lng: number;
    radius_km?: number;
    distance_from_valencia_km?: number | null;
    coverage_segment?: string | null;
    coverage_scope?: string | null;
    active?: number;
    pisos_slug?: string;
    thinkspain_slug?: string | null;
    globaliza_slug?: string | null;
    created_at?: string;
}

export const ZoneModel = {
    async findAll(): Promise<Zone[]> {
        const [rows] = await pool.query<RowDataPacket[]>(
            `SELECT * FROM zones ORDER BY active DESC, name ASC`
        );
        return rows as Zone[];
    },

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

    async findById(id: number): Promise<Zone | null> {
        const [rows] = await pool.query<RowDataPacket[]>(
            "SELECT * FROM zones WHERE id = ?",
            [id]
        );
        return rows.length > 0 ? (rows[0] as Zone) : null;
    },

    async findBySlug(slug: string): Promise<Zone | null> {
        const [rows] = await pool.query<RowDataPacket[]>(
            "SELECT * FROM zones WHERE slug = ? LIMIT 1",
            [slug]
        );
        return rows.length > 0 ? (rows[0] as Zone) : null;
    },

    async create(zone: Zone): Promise<number> {
        const slug = zone.slug || zone.name.toLowerCase()
            .normalize("NFD").replace(/[̀-ͯ]/g, "")
            .replace(/\s+/g, "-").replace(/[^a-z0-9-]/g, "");
        const pisos_slug = zone.pisos_slug || `pisos-${slug}`;
        const thinkspain_slug = zone.thinkspain_slug ?? null;
        const globaliza_slug = zone.globaliza_slug ?? null;
        const [result] = await pool.query<ResultSetHeader>(
            `INSERT INTO zones (name, slug, lat, lng, radius_km, active, pisos_slug, thinkspain_slug, globaliza_slug)
       VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`,
            [zone.name, slug, zone.lat, zone.lng,
            zone.radius_km ?? 10, zone.active ?? 1, pisos_slug, thinkspain_slug, globaliza_slug]
        );
        return result.insertId;
    },

    async update(id: number, zone: Partial<Zone>): Promise<void> {
        const allowed: (keyof Zone)[] = ["name", "slug", "lat", "lng", "radius_km", "active", "pisos_slug", "thinkspain_slug", "globaliza_slug", "distance_from_valencia_km", "coverage_segment", "coverage_scope"];
        const updates = allowed.filter(k => zone[k] !== undefined);
        if (!updates.length) return;
        const fields = updates.map(k => `${k} = ?`).join(", ");
        const values = [...updates.map(k => (zone as any)[k]), id];
        await pool.query(`UPDATE zones SET ${fields} WHERE id = ?`, values);
    },

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

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