<?php
/**
 * Data access for the services 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 ServiceRepository {

    private const SORTABLE_COLUMNS = [
        'title'         => 's.title',
        'base_price'    => 's.base_price',
        'base_duration' => 's.base_duration',
        'sort_order'    => 's.sort_order',
        'created_at'    => 's.created_at',
        'status'        => 's.status',
    ];

    private const EDITABLE_COLUMNS = [
        'category_id',
        'title',
        'description',
        'icon',
        'badge',
        'mode',
        'pricing_model',
        'base_price',
        'base_duration',
        'enable_beds',
        'bed_price',
        'enable_baths',
        'bath_price',
        'enable_sqft',
        'sqft_price',
        'enable_windows',
        'window_price',
        'enable_hours',
        'hourly_rate',
        'addons',
        'status',
        'sort_order',
    ];

    public function find( int $service_id ): ?array {
        global $wpdb;

        $row = $wpdb->get_row( $wpdb->prepare( $this->select_sql() . ' WHERE s.id = %d', $this->select_values( [ $service_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_title( string $title ): ?array {
        global $wpdb;

        $row = $wpdb->get_row( $wpdb->prepare( $this->select_sql() . ' WHERE s.title = %s LIMIT 1', $this->select_values( [ $title ] ) ), 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 $service_data ): int {
        global $wpdb;

        $now                     = current_time( 'mysql' );
        $sanitized               = $this->sanitize_payload( $service_data );
        $sanitized['created_at'] = $now;
        $sanitized['updated_at'] = $now;

        return $wpdb->insert( Schema::services_table(), $sanitized ) ? (int) $wpdb->insert_id : 0;
    }

    public function update( int $service_id, array $service_data ): bool {
        global $wpdb;

        $sanitized               = $this->sanitize_payload( $service_data, true );
        $sanitized['updated_at'] = current_time( 'mysql' );

        return false !== $wpdb->update( Schema::services_table(), $sanitized, [ 'id' => $service_id ] );
    }

    public function delete( int $service_id ): bool {
        global $wpdb;

        return (bool) $wpdb->delete( Schema::services_table(), [ 'id' => $service_id ], [ '%d' ] );
    }

    /**
     * @param array $args search, status, category_id, mode, 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'      => '',
                'category_id' => 0,
                'mode'        => '',
                'orderby'     => 'sort_order',
                'order'       => 'ASC',
                'per_page'    => 100,
                'page'        => 1,
            ]
        );

        $where_clauses = [ '1=1' ];
        $where_values  = [];

        if ( ! empty( $args['status'] ) ) {
            $where_clauses[] = 's.status = %s';
            $where_values[]  = $args['status'];
        }

        if ( ! empty( $args['category_id'] ) ) {
            $where_clauses[] = 's.category_id = %d';
            $where_values[]  = (int) $args['category_id'];
        }

        if ( ! empty( $args['mode'] ) ) {
            $where_clauses[] = 's.mode = %s';
            $where_values[]  = $args['mode'];
        }

        if ( ! empty( $args['search'] ) ) {
            $like            = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $where_clauses[] = '(s.title LIKE %s OR s.description LIKE %s OR s.badge LIKE %s OR cat.name LIKE %s)';
            array_push( $where_values, $like, $like, $like, $like );
        }

        $orderby   = self::SORTABLE_COLUMNS[ $args['orderby'] ] ?? 's.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 s LEFT JOIN %i cat ON cat.id = s.category_id WHERE {$where_sql}";
        $count_values = array_merge( [ Schema::services_table(), Schema::categories_table() ], $where_values );
        $items_sql    = $this->select_sql() . " WHERE {$where_sql} ORDER BY {$orderby} {$order}, s.title 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 get_active_services(): array {
        $result = $this->query(
            [
                'status'   => 'active',
                'per_page' => 250,
                'orderby'  => 'sort_order',
                'order'    => 'ASC',
            ]
        );

        return $result['items'];
    }

    public function count_by_status(): array {
        global $wpdb;

        $rows   = $wpdb->get_results( $wpdb->prepare( 'SELECT status, COUNT(*) AS total FROM %i GROUP BY status', Schema::services_table() ), ARRAY_A ) ?: [];
        $counts = [
            'active'   => 0,
            'inactive' => 0,
        ];

        foreach ( $rows as $row ) {
            $counts[ $row['status'] ] = (int) $row['total'];
        }

        return $counts;
    }

    public function count( string $status = '' ): int {
        global $wpdb;
        $table = Schema::services_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 s.*, cat.name AS category_name, cat.color AS category_color, cat.icon AS category_icon
            FROM %i s
            LEFT JOIN %i cat ON cat.id = s.category_id';
    }

    /**
     * Prepends the table names used inside select_sql().
     */
    private function select_values( array $values ): array {
        return array_merge( [ Schema::services_table(), Schema::categories_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['category_id'] ) || ! $is_update ) {
            $clean['category_id'] = absint( $filtered['category_id'] ?? 0 );
        }

        if ( isset( $filtered['title'] ) || ! $is_update ) {
            $clean['title'] = html_entity_decode( sanitize_text_field( (string) ( $filtered['title'] ?? '' ) ), ENT_QUOTES | ENT_HTML5, 'UTF-8' );
        }

        if ( array_key_exists( 'description', $filtered ) || ! $is_update ) {
            $clean['description'] = html_entity_decode( sanitize_textarea_field( (string) ( $filtered['description'] ?? '' ) ), ENT_QUOTES | ENT_HTML5, 'UTF-8' );
        }

        if ( isset( $filtered['icon'] ) || ! $is_update ) {
            $clean['icon'] = sanitize_text_field( (string) ( $filtered['icon'] ?? 'sparkles' ) ) ?: 'sparkles';
        }

        if ( array_key_exists( 'badge', $filtered ) || ! $is_update ) {
            $clean['badge'] = html_entity_decode( sanitize_text_field( (string) ( $filtered['badge'] ?? '' ) ), ENT_QUOTES | ENT_HTML5, 'UTF-8' );
        }

        if ( isset( $filtered['mode'] ) || ! $is_update ) {
            $mode          = sanitize_key( (string) ( $filtered['mode'] ?? 'fixed' ) );
            $clean['mode'] = in_array( $mode, [ 'fixed', 'quote' ], true ) ? $mode : 'fixed';
        }

        if ( isset( $filtered['pricing_model'] ) || ! $is_update ) {
            $pricing_model          = sanitize_key( (string) ( $filtered['pricing_model'] ?? 'unit' ) );
            $clean['pricing_model'] = in_array( $pricing_model, [ 'unit', 'hourly', 'flat' ], true ) ? $pricing_model : 'unit';
        }

        if ( isset( $filtered['base_price'] ) || ! $is_update ) {
            $clean['base_price'] = max( 0, (float) ( $filtered['base_price'] ?? 0 ) );
        }

        if ( isset( $filtered['base_duration'] ) || ! $is_update ) {
            $clean['base_duration'] = max( 15, absint( $filtered['base_duration'] ?? 60 ) );
        }

        $unit_flags = [
            'enable_beds'    => 'bed_price',
            'enable_baths'   => 'bath_price',
            'enable_sqft'    => 'sqft_price',
            'enable_windows' => 'window_price',
            'enable_hours'   => 'hourly_rate',
        ];

        foreach ( $unit_flags as $flag_key => $price_key ) {
            if ( array_key_exists( $flag_key, $filtered ) || ! $is_update ) {
                $clean[ $flag_key ] = ! empty( $filtered[ $flag_key ] ) ? 1 : 0;
            }
            if ( array_key_exists( $price_key, $filtered ) || ! $is_update ) {
                $clean[ $price_key ] = max( 0, (float) ( $filtered[ $price_key ] ?? 0 ) );
            }
        }

        if ( isset( $clean['pricing_model'] ) ) {
            if ( 'hourly' === $clean['pricing_model'] ) {
                $clean['enable_hours']   = 1;
                $clean['enable_beds']    = 0;
                $clean['enable_baths']   = 0;
                $clean['enable_sqft']    = 0;
                $clean['enable_windows'] = 0;
            } elseif ( 'flat' === $clean['pricing_model'] ) {
                $clean['enable_hours']   = 0;
                $clean['enable_beds']    = 0;
                $clean['enable_baths']   = 0;
                $clean['enable_sqft']    = 0;
                $clean['enable_windows'] = 0;
            } elseif ( 'unit' === $clean['pricing_model'] ) {
                $clean['enable_hours'] = 0;
            }
        }

        if ( array_key_exists( 'addons', $filtered ) || ! $is_update ) {
            $raw_addons       = is_array( $filtered['addons'] ?? null ) ? $filtered['addons'] : [];
            $sanitized_addons = [];

            foreach ( $raw_addons as $index => $addon ) {
                if ( ! is_array( $addon ) ) {
                    continue;
                }
                $name = html_entity_decode( sanitize_text_field( (string) ( $addon['name'] ?? '' ) ), ENT_QUOTES | ENT_HTML5, 'UTF-8' );
                if ( '' === $name ) {
                    continue;
                }
                $addon_id           = ! empty( $addon['id'] ) ? sanitize_key( (string) $addon['id'] ) : sanitize_title( $name ) . '-' . $index;
                $sanitized_addons[] = [
                    'id'       => $addon_id,
                    'name'     => $name,
                    'price'    => max( 0, (float) ( $addon['price'] ?? 0 ) ),
                    'duration' => max( 0, absint( $addon['duration'] ?? 15 ) ),
                    'icon'     => sanitize_text_field( (string) ( $addon['icon'] ?? 'sparkles' ) ) ?: 'sparkles',
                ];
            }

            $clean['addons'] = wp_json_encode( $sanitized_addons );
        }

        if ( isset( $filtered['status'] ) || ! $is_update ) {
            $status          = sanitize_key( (string) ( $filtered['status'] ?? 'active' ) );
            $clean['status'] = in_array( $status, [ 'active', 'inactive' ], true ) ? $status : 'active';
        }

        if ( isset( $filtered['sort_order'] ) || ! $is_update ) {
            $clean['sort_order'] = (int) ( $filtered['sort_order'] ?? 0 );
        }

        return $clean;
    }

    private function hydrate( array $row ): array {
        $addons = json_decode( (string) ( $row['addons'] ?? '[]' ), true );
        if ( ! is_array( $addons ) ) {
            $addons = [];
        }

        $category_name = html_entity_decode( (string) ( $row['category_name'] ?? '' ), ENT_QUOTES | ENT_HTML5, 'UTF-8' );

        $enable_beds    = (bool) ( $row['enable_beds'] ?? false );
        $enable_baths   = (bool) ( $row['enable_baths'] ?? false );
        $enable_sqft    = (bool) ( $row['enable_sqft'] ?? false );
        $enable_windows = (bool) ( $row['enable_windows'] ?? false );
        $enable_hours   = (bool) ( $row['enable_hours'] ?? false );

        $pricing_model = (string) ( $row['pricing_model'] ?: 'unit' );
        if ( ! in_array( $pricing_model, [ 'unit', 'hourly', 'flat' ], true ) ) {
            $pricing_model = 'unit';
        }

        if ( 'hourly' === $pricing_model ) {
            $enable_hours   = true;
            $enable_beds    = false;
            $enable_baths   = false;
            $enable_sqft    = false;
            $enable_windows = false;
        } elseif ( 'flat' === $pricing_model ) {
            $enable_hours   = false;
            $enable_beds    = false;
            $enable_baths   = false;
            $enable_sqft    = false;
            $enable_windows = false;
        } else {
            $enable_hours = false;
        }

        return [
            'id'             => (int) $row['id'],
            'category_id'    => (int) ( $row['category_id'] ?? 0 ),
            'category_name'  => $category_name,
            'category_color' => (string) ( $row['category_color'] ?? '#0D9488' ),
            'category_icon'  => (string) ( $row['category_icon'] ?? 'sparkles' ),
            'categories'     => '' !== $category_name ? [ $category_name ] : [],
            'title'          => html_entity_decode( (string) ( $row['title'] ?? '' ), ENT_QUOTES | ENT_HTML5, 'UTF-8' ),
            'description'    => html_entity_decode( (string) ( $row['description'] ?? '' ), ENT_QUOTES | ENT_HTML5, 'UTF-8' ),
            'icon'           => (string) ( $row['icon'] ?: 'sparkles' ),
            'badge'          => html_entity_decode( (string) ( $row['badge'] ?? '' ), ENT_QUOTES | ENT_HTML5, 'UTF-8' ),
            'mode'           => (string) ( $row['mode'] ?: 'fixed' ),
            'pricing_model'  => $pricing_model,
            'base_price'     => (float) ( $row['base_price'] ?? 0 ),
            'base_duration'  => (int) ( $row['base_duration'] ?: 60 ),
            'enable_beds'    => $enable_beds,
            'bed_price'      => (float) ( $row['bed_price'] ?? 0 ),
            'enable_baths'   => $enable_baths,
            'bath_price'     => (float) ( $row['bath_price'] ?? 0 ),
            'enable_sqft'    => $enable_sqft,
            'sqft_price'     => (float) ( $row['sqft_price'] ?? 0 ),
            'enable_windows' => $enable_windows,
            'window_price'   => (float) ( $row['window_price'] ?? 0 ),
            'enable_hours'   => $enable_hours,
            'hourly_rate'    => (float) ( $row['hourly_rate'] ?? 0 ),
            'addons'         => $addons,
            'status'         => (string) ( $row['status'] ?? 'active' ),
            'sort_order'     => (int) ( $row['sort_order'] ?? 0 ),
            'created_at'     => (string) ( $row['created_at'] ?? '' ),
            'updated_at'     => (string) ( $row['updated_at'] ?? '' ),
        ];
    }
}