<?php
/**
 * Client CRUD operations.
 */

if ( ! defined( 'ABSPATH' ) ) {
    exit;
}

// phpcs:disable WordPress.DB.DirectDatabaseQuery, WordPress.DB.PreparedSQL, PluginCheck.Security.DirectDB

class ZINV_Client {

    private ZINV_Database $db;

    private const ALLOWED_STATUSES     = [ 'active', 'inactive', 'archived' ];
    private const ALLOWED_ACCOUNT_TYPES = [ 'standard', 'enterprise', 'at_risk' ];
    private const ALLOWED_SORT_FIELDS  = [ 'id', 'name', 'email', 'total_ltv', 'status', 'created_at' ];

    public function __construct() {
        $this->db = new ZINV_Database( 'zinv_clients' );
    }

    public function create( array $data ): int {
        $now = current_time( 'mysql' );

        $insert = [
            'name'               => sanitize_text_field( $data['name'] ),
            'email'              => sanitize_email( $data['email'] ?? '' ),
            'phone'              => isset( $data['phone'] ) ? sanitize_text_field( $data['phone'] ) : null,
            'company'            => isset( $data['company'] ) ? sanitize_text_field( $data['company'] ) : null,
            'website'            => isset( $data['website'] ) ? esc_url_raw( $data['website'] ) : null,
            'address_line1'      => isset( $data['address_line1'] ) ? sanitize_text_field( $data['address_line1'] ) : null,
            'address_line2'      => isset( $data['address_line2'] ) ? sanitize_text_field( $data['address_line2'] ) : null,
            'city'               => isset( $data['city'] ) ? sanitize_text_field( $data['city'] ) : null,
            'state'              => isset( $data['state'] ) ? sanitize_text_field( $data['state'] ) : null,
            'postal_code'        => isset( $data['postal_code'] ) ? sanitize_text_field( $data['postal_code'] ) : null,
            'country'            => isset( $data['country'] ) ? $this->sanitize_country( $data['country'] ) : null,
            'account_type'       => $this->validate_account_type( $data['account_type'] ?? 'standard' ),
            'tax_id'             => isset( $data['tax_id'] ) ? sanitize_text_field( $data['tax_id'] ) : null,
            'notes'              => isset( $data['notes'] ) ? sanitize_textarea_field( $data['notes'] ) : null,
            'currency'           => isset( $data['currency'] ) ? sanitize_text_field( $data['currency'] ) : 'USD',
            'status'             => 'active',
            'wp_user_id'         => isset( $data['wp_user_id'] ) ? absint( $data['wp_user_id'] ) : null,
            'tax_exempt'         => isset( $data['tax_exempt'] ) ? (int) (bool) $data['tax_exempt'] : 0,
            'total_ltv'          => 0,
            'stripe_customer_id' => null,
            'version'            => 1,
            'created_at'         => $now,
            'updated_at'         => $now,
        ];

        $client_id = $this->db->insert( $insert );

        if ( $client_id ) {
            $name = sanitize_text_field( $data['name'] ?? '' );
            ZINV_Activity::log(
                'client.created',
                'client',
                $client_id,
                /* translators: %s: client display name */
                sprintf( __( 'Client "%s" was added to your directory.', 'zoventic-invoices' ), $name )
            );
        }

        return $client_id;
    }

    public function get( int $id ): ?array {
        return $this->db->get_row(
            "SELECT * FROM {$this->db->get_table_name()} WHERE id = %d",
            $id
        );
    }

    public function get_by_email( string $email, int $exclude_id = 0 ): ?array {
        $table = $this->db->get_table_name();
        if ( $exclude_id > 0 ) {
            return $this->db->get_row(
                "SELECT * FROM {$table} WHERE email = %s AND id != %d",
                sanitize_email( $email ),
                $exclude_id
            );
        }
        return $this->db->get_row(
            "SELECT * FROM {$table} WHERE email = %s",
            sanitize_email( $email )
        );
    }

    public function update( int $id, array $data, int $version ): bool {
        $client = $this->get( $id );
        if ( ! $client ) {
            return false;
        }

        $fields = [];
        $now    = current_time( 'mysql' );

        $updatable = [
            'name'          => 'sanitize_text_field',
            'email'         => 'sanitize_email',
            'phone'         => 'sanitize_text_field',
            'company'       => 'sanitize_text_field',
            'website'       => 'esc_url_raw',
            'address_line1' => 'sanitize_text_field',
            'address_line2' => 'sanitize_text_field',
            'city'          => 'sanitize_text_field',
            'state'         => 'sanitize_text_field',
            'postal_code'   => 'sanitize_text_field',
            'country'       => 'sanitize_text_field',
            'tax_id'        => 'sanitize_text_field',
            'notes'         => 'sanitize_textarea_field',
            'currency'      => 'sanitize_text_field',
        ];

        foreach ( $updatable as $field => $sanitizer ) {
            if ( array_key_exists( $field, $data ) ) {
                $fields[ $field ] = $sanitizer( $data[ $field ] );
            }
        }

        if ( array_key_exists( 'wp_user_id', $data ) ) {
            $fields['wp_user_id'] = null === $data['wp_user_id'] || '' === $data['wp_user_id'] ? null : absint( $data['wp_user_id'] );
        }

        if ( isset( $data['account_type'] ) ) {
            $fields['account_type'] = $this->validate_account_type( $data['account_type'] );
        }

        if ( isset( $data['status'] ) && in_array( $data['status'], self::ALLOWED_STATUSES, true ) ) {
            $fields['status'] = sanitize_text_field( $data['status'] );
        }

        if ( isset( $data['tax_exempt'] ) ) {
            $fields['tax_exempt'] = (int) (bool) $data['tax_exempt'];
        }

        $fields['version']    = $version + 1;
        $fields['updated_at'] = $now;

        global $wpdb;
        // phpcs:disable WordPress.DB.DirectDatabaseQuery.DirectQuery, WordPress.DB.DirectDatabaseQuery.NoCaching, WordPress.DB.PreparedSQL.InterpolatedNotPrepared, WordPress.DB.PreparedSQL.NotPrepared, PluginCheck.Security.DirectDB.UnescapedDBParameter
        $result = $wpdb->update(
            $this->db->get_table_name(),
            $fields,
            [ 'id' => $id, 'version' => $version ],
            $this->get_formats( $fields ),
            [ '%d', '%d' ]
        );

        // Distinguish "no row matched (= version conflict)" from "row matched but data identical".
        // After UPDATE with a version match, the row exists with the new version, so re-fetching
        // and checking the version is reliable.
        if ( false === $result ) {
            return false;
        }

        $new_version = (int) $wpdb->get_var( $wpdb->prepare(
            "SELECT version FROM {$this->db->get_table_name()} WHERE id = %d",
            $id
        ) );

        return $new_version === ( $version + 1 );
    }

    public function archive( int $id ): bool {
        $now = current_time( 'mysql' );

        global $wpdb;
        $result = $wpdb->update(
            $this->db->get_table_name(),
            [ 'status' => 'archived', 'updated_at' => $now ],
            [ 'id' => $id ],
            [ '%s', '%s' ],
            [ '%d' ]
        );

        return false !== $result;
    }

    public function list( array $args = [] ): array {
        global $wpdb;
        $defaults = [
            'page'        => 1,
            'per_page'    => 20,
            'search'      => '',
            'status'      => '',
            'account_type'=> '',
            'orderby'     => 'created_at',
            'order'       => 'DESC',
        ];

        $args     = wp_parse_args( $args, $defaults );
        $table    = $this->db->get_table_name();
        $where    = [];
        $where_args = [];

        if ( ! empty( $args['search'] ) ) {
            $like        = '%' . $wpdb->esc_like( $args['search'] ) . '%';
            $where[]     = "(name LIKE %s OR email LIKE %s OR company LIKE %s)";
            $where_args[] = $like;
            $where_args[] = $like;
            $where_args[] = $like;
        }

        if ( ! empty( $args['status'] ) && in_array( $args['status'], self::ALLOWED_STATUSES, true ) ) {
            $where[]     = "status = %s";
            $where_args[] = sanitize_text_field( $args['status'] );
        }

        if ( ! empty( $args['account_type'] ) && in_array( $args['account_type'], self::ALLOWED_ACCOUNT_TYPES, true ) ) {
            $where[]     = "account_type = %s";
            $where_args[] = sanitize_text_field( $args['account_type'] );
        }

        $where_clause = ! empty( $where ) ? implode( ' AND ', $where ) : '';
        $orderby      = in_array( $args['orderby'], self::ALLOWED_SORT_FIELDS, true ) ? $args['orderby'] : 'created_at';

        $max = isset( $args['_max_per_page'] ) ? (int) $args['_max_per_page'] : 100;

        return $this->db->get_paginated(
            '*',
            $table,
            (int) $args['page'],
            (int) $args['per_page'],
            $where_clause,
            $orderby,
            $args['order'],
            $where_args,
            $max
        );
    }

    public function update_ltv( int $client_id ): void {
        global $wpdb;

        $payments_table = $wpdb->prefix . 'zinv_payments';

        $ltv = (string) $wpdb->get_var( $wpdb->prepare(
            "SELECT COALESCE(SUM(amount), 0) FROM {$payments_table} WHERE client_id = %d AND status = 'successful'",
            $client_id
        ) );

        $wpdb->update(
            $this->db->get_table_name(),
            [ 'total_ltv' => $ltv, 'updated_at' => current_time( 'mysql' ) ],
            [ 'id' => $client_id ],
            [ '%s', '%s' ],
            [ '%d' ]
        );
    }

    private function validate_account_type( string $type ): string {
        return in_array( $type, self::ALLOWED_ACCOUNT_TYPES, true ) ? $type : 'standard';
    }

    private function get_formats( array $data ): array {
        $formats = [];
        foreach ( $data as $value ) {
            if ( is_int( $value ) ) {
                $formats[] = '%d';
            } elseif ( is_float( $value ) ) {
                $formats[] = '%f';
            } elseif ( null === $value ) {
                $formats[] = '%s';
            } else {
                $formats[] = '%s';
            }
        }
        return $formats;
    }

    private function sanitize_country( ?string $country ): ?string {
        if ( null === $country || '' === $country ) {
            return null;
        }
        $clean = strtoupper( trim( sanitize_text_field( $country ) ) );
        return '' !== $clean ? substr( $clean, 0, 2 ) : null;
    }
}

