<?php
/**
 * Data access for the service categories table.
 *
 * @package Cleannetic
 */

namespace Cleannetic\Database;

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

// phpcs:disable WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.DirectDatabaseQuery -- Internal table names; all values go through prepare().

class CategoryRepository {

    private const SORTABLE_COLUMNS = [
        'name'           => 'cat.name',
        'slug'           => 'cat.slug',
        'sort_order'     => 'cat.sort_order',
        'created_at'     => 'cat.created_at',
        'services_count' => 'services_count',
    ];

    private const EDITABLE_COLUMNS = [
        'name',
        'slug',
        'description',
        'icon',
        'color',
        'sort_order',
        'status',
    ];

    public function find( int $category_id ): ?array {
        global $wpdb;

        $row = $wpdb->get_row( $wpdb->prepare( $this->select_sql() . ' WHERE cat.id = %d GROUP BY cat.id', $this->select_values( [ $category_id ] ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- select_sql() is a literal with %i table placeholders.

        return $row ? $this->hydrate( $row ) : null;
    }

    public function find_by_name( string $name ): ?array {
        global $wpdb;

        $row = $wpdb->get_row( $wpdb->prepare( $this->select_sql() . ' WHERE cat.name = %s GROUP BY cat.id LIMIT 1', $this->select_values( [ $name ] ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- select_sql() is a literal with %i table placeholders.

        return $row ? $this->hydrate( $row ) : null;
    }

    public function find_by_slug( string $slug ): ?array {
        global $wpdb;

        $row = $wpdb->get_row( $wpdb->prepare( $this->select_sql() . ' WHERE cat.slug = %s GROUP BY cat.id LIMIT 1', $this->select_values( [ $slug ] ) ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter -- select_sql() is a literal with %i table placeholders.

        return $row ? $this->hydrate( $row ) : null;
    }

    public function insert( array $category_data ): int {
        global $wpdb;

        $now                         = current_time( 'mysql' );
        $sanitized                   = $this->sanitize_payload( $category_data );
        $sanitized['created_at']     = $now;
        $sanitized['updated_at']     = $now;

        return $wpdb->insert( Schema::categories_table(), $sanitized ) ? (int) $wpdb->insert_id : 0;
    }

    public function update( int $category_id, array $category_data ): bool {
        global $wpdb;

        $sanitized               = $this->sanitize_payload( $category_data, true );
        $sanitized['updated_at'] = current_time( 'mysql' );

        return false !== $wpdb->update( Schema::categories_table(), $sanitized, [ 'id' => $category_id ] );
    }

    /**
     * Deletes a category and unassigns any services linked to it.
     */
    public function delete( int $category_id ): bool {
        global $wpdb;

        $services_table = Schema::services_table();
        $wpdb->update( $services_table, [ 'category_id' => 0 ], [ 'category_id' => $category_id ], [ '%d' ], [ '%d' ] );

        return (bool) $wpdb->delete( Schema::categories_table(), [ 'id' => $category_id ], [ '%d' ] );
    }

    /**
     * @param array $args search, status, orderby, order, per_page, page.
     * @return array{items: array[], total: int}
     */
    public function query( array $args = [] ): array {
        global $wpdb;

        $args = wp_parse_args(
            $args,
            [
                'search'   => '',
                'status'   => '',
                'orderby'  => 'sort_order',
                'order'    => 'ASC',
                'per_page' => 100,
                'page'     => 1,
            ]
        );

        $where_clauses = [ '1=1' ];
        $where_values  = [];

        if ( ! empty( $args['status'] ) ) {
            $where_clauses[] = 'cat.status = %s';
            $where_values[]  = $args['status'];
        }

        if ( ! empty( $args['search'] ) ) {
            $like            = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $where_clauses[] = '(cat.name LIKE %s OR cat.slug LIKE %s OR cat.description LIKE %s)';
            array_push( $where_values, $like, $like, $like );
        }

        $orderby   = self::SORTABLE_COLUMNS[ $args['orderby'] ] ?? 'cat.sort_order';
        $order     = 'DESC' === strtoupper( $args['order'] ) ? 'DESC' : 'ASC';
        $per_page  = max( 1, (int) $args['per_page'] );
        $offset    = max( 0, ( (int) $args['page'] - 1 ) * $per_page );
        $where_sql = implode( ' AND ', $where_clauses );

        $count_sql    = "SELECT COUNT(*) FROM %i cat WHERE {$where_sql}";
        $count_values = array_merge( [ Schema::categories_table() ], $where_values );
        $items_sql    = $this->select_sql() . " WHERE {$where_sql} GROUP BY cat.id ORDER BY {$orderby} {$order}, cat.name ASC LIMIT %d OFFSET %d";
        $items_values = $this->select_values( array_merge( $where_values, [ $per_page, $offset ] ) );

        // WHERE is built from literal placeholder fragments and ORDER BY from whitelisted columns.
        $total_count = (int) $wpdb->get_var( $wpdb->prepare( $count_sql, $count_values ) ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $rows        = $wpdb->get_results( $wpdb->prepare( $items_sql, $items_values ), ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter

        return [
            'items' => array_map( [ $this, 'hydrate' ], $rows ?: [] ),
            'total' => $total_count,
        ];
    }

    public function all_active(): array {
        $result = $this->query(
            [
                'status'   => 'active',
                'per_page' => 250,
                'orderby'  => 'sort_order',
                'order'    => 'ASC',
            ]
        );
        return $result['items'];
    }

    public function count( string $status = '' ): int {
        global $wpdb;
        $table = Schema::categories_table();

        if ( $status ) {
            return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i WHERE status = %s', $table, $status ) );
        }

        return (int) $wpdb->get_var( $wpdb->prepare( 'SELECT COUNT(*) FROM %i', $table ) );
    }

    /**
     * Shared SELECT with table placeholders; pair it with select_values().
     */
    private function select_sql(): string {
        return 'SELECT cat.*, COUNT(s.id) AS services_count
            FROM %i cat
            LEFT JOIN %i s ON s.category_id = cat.id';
    }

    /**
     * Prepends the table names used inside select_sql().
     */
    private function select_values( array $values ): array {
        return array_merge( [ Schema::categories_table(), Schema::services_table() ], $values );
    }

    private function sanitize_payload( array $data, bool $is_update = false ): array {
        $filtered = array_intersect_key( $data, array_flip( self::EDITABLE_COLUMNS ) );
        $clean    = [];

        if ( isset( $filtered['name'] ) || ! $is_update ) {
            $clean['name'] = sanitize_text_field( (string) ( $filtered['name'] ?? '' ) );
        }

        if ( isset( $filtered['slug'] ) || isset( $clean['name'] ) ) {
            $raw_slug      = ! empty( $filtered['slug'] ) ? $filtered['slug'] : ( $clean['name'] ?? '' );
            $clean['slug'] = sanitize_title( (string) $raw_slug );
        }

        if ( array_key_exists( 'description', $filtered ) || ! $is_update ) {
            $clean['description'] = sanitize_textarea_field( (string) ( $filtered['description'] ?? '' ) );
        }

        if ( isset( $filtered['icon'] ) || ! $is_update ) {
            $clean['icon'] = sanitize_text_field( (string) ( $filtered['icon'] ?? 'sparkles' ) ) ?: 'sparkles';
        }

        if ( isset( $filtered['color'] ) || ! $is_update ) {
            $clean['color'] = sanitize_hex_color( (string) ( $filtered['color'] ?? '#0D9488' ) ) ?: '#0D9488';
        }

        if ( isset( $filtered['sort_order'] ) || ! $is_update ) {
            $clean['sort_order'] = (int) ( $filtered['sort_order'] ?? 0 );
        }

        if ( isset( $filtered['status'] ) || ! $is_update ) {
            $status          = sanitize_key( (string) ( $filtered['status'] ?? 'active' ) );
            $clean['status'] = in_array( $status, [ 'active', 'inactive' ], true ) ? $status : 'active';
        }

        return $clean;
    }

    private function hydrate( array $row ): array {
        $row['id']             = (int) $row['id'];
        $row['name']           = html_entity_decode( (string) ( $row['name'] ?? '' ), ENT_QUOTES | ENT_HTML5, 'UTF-8' );
        $row['slug']           = (string) ( $row['slug'] ?? '' );
        $row['description']    = html_entity_decode( (string) ( $row['description'] ?? '' ), ENT_QUOTES | ENT_HTML5, 'UTF-8' );
        $row['icon']           = (string) ( $row['icon'] ?? 'sparkles' );
        $row['color']          = (string) ( $row['color'] ?? '#0D9488' );
        $row['sort_order']     = (int) ( $row['sort_order'] ?? 0 );
        $row['status']         = (string) ( $row['status'] ?? 'active' );
        $row['services_count'] = (int) ( $row['services_count'] ?? 0 );

        return $row;
    }
}