<?php
namespace WebshoptigerCatalogSync;

defined( 'ABSPATH' ) || exit;

class SiteManager {

    private static function table(): string {
        global $wpdb;
        return $wpdb->prefix . 'webshoptigercatalogsync_sites';
    }

    public static function get_all(): array {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $rows = $wpdb->get_results( "SELECT * FROM `{$wpdb->prefix}webshoptigercatalogsync_sites` ORDER BY id ASC", ARRAY_A ); // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
        if ( ! $rows ) return [];
        foreach ( $rows as &$row ) {
            $row = self::normalize( $row );
        }
        return $rows;
    }

    public static function get( int $id ): ?array {
        global $wpdb;
        // phpcs:ignore WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $row = $wpdb->get_row(
            $wpdb->prepare( "SELECT * FROM `{$wpdb->prefix}webshoptigercatalogsync_sites` WHERE id = %d", $id ), // phpcs:ignore WordPress.DB.PreparedSQL.InterpolatedNotPrepared
            ARRAY_A
        );
        if ( ! $row ) return null;
        return self::normalize( $row );
    }

    private static function normalize( array $row ): array {
        $row['categories']       = $row['categories']   ? json_decode( $row['categories'],   true ) : [];
        $row['sync_fields']      = $row['sync_fields']  ? json_decode( $row['sync_fields'],   true ) : null;
        $row['sync_description'] = isset( $row['sync_description'] ) ? (int) $row['sync_description'] : 1;
        $row['sync_interval']    = $row['sync_interval']    ?? 'global';
        $row['price_adjustment'] = $row['price_adjustment'] ?? 'none';
        $row['price_value']      = isset( $row['price_value'] ) ? (float) $row['price_value'] : 0.00;
        $row['report_email']     = $row['report_email']     ?? '';
        return $row;
    }

    public static function save( array $data ): mixed {
        global $wpdb;

        $is_new = empty( $data['id'] );

        // Lite limit: max destination sites
        if ( $is_new ) {
            $site_limit = Settings::site_limit();
            if ( $site_limit > 0 && count( self::get_all() ) >= $site_limit ) {
                return new \WP_Error(
                    'webshoptigercatalogsync_site_limit',
                    'Limit of ' . $site_limit . ' destination site(s) reached. Upgrade to Pro for unlimited destination sites.'
                );
            }
        }

        $fields = [
            'label'           => sanitize_text_field( $data['label'] ?? '' ),
            'site_url'        => esc_url_raw( $data['site_url'] ?? '', [ 'https', 'http' ] ),
            'api_key'         => sanitize_text_field( $data['api_key'] ?? '' ),
            'api_secret'      => sanitize_text_field( $data['api_secret'] ?? '' ),
            'categories'      => json_encode( array_map( 'absint', (array) ( $data['categories'] ?? [] ) ) ),
            'enabled'         => (int) ( $data['enabled'] ?? 0 ),
            'sync_description'=> (int) ( $data['sync_description'] ?? 1 ),

        ];

        if ( empty( $fields['label'] ) || empty( $fields['site_url'] ) ) {
            return new \WP_Error( 'validation', 'Label and URL are required.' );
        }

        // Reject non-http(s) URLs to prevent SSRF (file://, ftp://, etc.)
        if ( ! preg_match( '#^https?://#i', $fields['site_url'] ) ) {
            return new \WP_Error( 'validation', 'Site URL must start with http:// or https://.' );
        }

        if ( ! $is_new ) {
            // phpcs:ignore // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->update( self::table(), $fields, [ 'id' => absint( $data['id'] ) ] );
            // Reset next_sync_at so the new interval is picked up immediately
            // phpcs:ignore // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
            $wpdb->update( self::table(), [ 'next_sync_at' => null ], [ 'id' => absint( $data['id'] ) ] );
            return absint( $data['id'] );
        }

        // phpcs:ignore // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $wpdb->insert( self::table(), $fields );
        return $wpdb->insert_id;
    }

    public static function delete( int $id ): bool {
        global $wpdb;
        // phpcs:ignore // phpcs:ignore WordPress.DB.PreparedSQL.NotPrepared, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQLPlaceholders.ReplacementsWrongNumber, WordPress.DB.PreparedSQLPlaceholders.UnfinishedPrepare, PluginCheck.Security.DirectDB.UnescapedDBParameter, WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching
        $result = (bool) $wpdb->delete( self::table(), [ 'id' => $id ] );
        if ( $result ) {
            if ( class_exists( 'WebshoptigerCatalogSync\\CategorySync' ) ) CategorySync::clear_cache( $id );
            if ( class_exists( 'WebshoptigerCatalogSync\\BrandSync' ) )    BrandSync::clear_cache( $id );
        }
        return $result;
    }

    public static function get_enabled(): array {
        return array_filter( self::get_all(), fn( $s ) => (int) $s['enabled'] === 1 );
    }

    






}
