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

export interface Portal {
    id?: number;
    name: string;
    slug: string;
    scraper_class: string;
    active?: number;
    created_at?: string;
    property_count?: number;
}

export const PortalModel = {
    async findAll(): Promise<Portal[]> {
        const [rows] = await pool.query<RowDataPacket[]>(
            `SELECT p.*, COUNT(pr.id) AS property_count
             FROM portals p
             LEFT JOIN properties pr ON pr.source = p.name
             GROUP BY p.id
             ORDER BY p.active DESC, p.name ASC`
        );
        return rows as Portal[];
    },

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

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

    async create(portal: Portal): Promise<number> {
        const slug = portal.slug || portal.name.toLowerCase()
            .normalize("NFD").replace(/[\u0300-\u036f]/g, "")
            .replace(/\s+/g, "-").replace(/[^a-z0-9-]/g, "");
        const [result] = await pool.query<ResultSetHeader>(
            "INSERT INTO portals (name, slug, scraper_class, active) VALUES (?, ?, ?, ?)",
            [portal.name, slug, portal.scraper_class, portal.active ?? 1]
        );
        return result.insertId;
    },

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

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

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