<?php
/**
 * Database operations.
 *
 * @package TonItaliaRegistration
 */

defined( 'ABSPATH' ) || exit;

/**
 * Class TON_Reg_Database
 */
class TON_Reg_Database {

	/**
	 * @return string
	 */
	public static function table_name() {
		global $wpdb;
		return $wpdb->prefix . TON_REG_TABLE;
	}

	/**
	 * Create registrations table.
	 */
	public static function create_table() {
		global $wpdb;

		$table   = self::table_name();
		$charset = $wpdb->get_charset_collate();

		$sql = "CREATE TABLE {$table} (
			id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
			user_id bigint(20) unsigned DEFAULT NULL,
			cognome varchar(100) NOT NULL DEFAULT '',
			nome varchar(100) NOT NULL DEFAULT '',
			luogo_nascita varchar(120) NOT NULL DEFAULT '',
			provincia_nascita char(2) NOT NULL DEFAULT '',
			data_nascita date DEFAULT NULL,
			codice_fiscale char(16) NOT NULL DEFAULT '',
			comune_residenza varchar(120) NOT NULL DEFAULT '',
			provincia_residenza char(2) NOT NULL DEFAULT '',
			indirizzo varchar(200) NOT NULL DEFAULT '',
			numero_civico varchar(20) NOT NULL DEFAULT '',
			cap char(10) NOT NULL DEFAULT '',
			telefono varchar(40) NOT NULL DEFAULT '',
			email varchar(190) NOT NULL DEFAULT '',
			luogo_dichiarazione varchar(120) NOT NULL DEFAULT '',
			data_dichiarazione date DEFAULT NULL,
			bonifico_effettuato tinyint(1) NOT NULL DEFAULT 0,
			consenso_statuto tinyint(1) NOT NULL DEFAULT 0,
			consenso_privacy tinyint(1) NOT NULL DEFAULT 0,
			consenso_newsletter tinyint(1) NOT NULL DEFAULT 0,
			registration_ip varchar(45) NOT NULL DEFAULT '',
			user_agent varchar(255) NOT NULL DEFAULT '',
			privacy_text_version varchar(32) NOT NULL DEFAULT '',
			consent_statuto_at datetime DEFAULT NULL,
			consent_privacy_at datetime DEFAULT NULL,
			status varchar(20) NOT NULL DEFAULT 'pending',
			admin_notes text NULL,
			libro_associati_date date DEFAULT NULL,
			doc_identita_id bigint(20) unsigned DEFAULT NULL,
			doc_bonifico_id bigint(20) unsigned DEFAULT NULL,
			doc_pagamento_id bigint(20) unsigned DEFAULT NULL,
			gdpr_log longtext NULL,
			mailchimp_status varchar(20) DEFAULT NULL,
			mailchimp_synced_at datetime DEFAULT NULL,
			mailchimp_error varchar(255) DEFAULT NULL,
			created_at datetime NOT NULL,
			updated_at datetime NOT NULL,
			anonymized_at datetime DEFAULT NULL,
			PRIMARY KEY  (id),
			KEY email (email),
			KEY codice_fiscale (codice_fiscale),
			KEY status (status),
			KEY created_at (created_at),
			KEY user_id (user_id)
		) {$charset};";

		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		dbDelta( $sql );
	}

	/**
	 * @param array<string, mixed> $data Row data.
	 * @return int|false Insert ID or false.
	 */
	public static function insert( $data ) {
		global $wpdb;

		$now = current_time( 'mysql', true );
		$data['created_at'] = $now;
		$data['updated_at'] = $now;

		$result = $wpdb->insert( self::table_name(), $data );
		return false === $result ? false : (int) $wpdb->insert_id;
	}

	/**
	 * @param int                  $id   Registration ID.
	 * @param array<string,mixed> $data Data.
	 * @return bool
	 */
	public static function update( $id, $data ) {
		global $wpdb;

		$data['updated_at'] = current_time( 'mysql', true );

		return false !== $wpdb->update(
			self::table_name(),
			$data,
			array( 'id' => $id ),
			null,
			array( '%d' )
		);
	}

	/**
	 * @param int $id Registration ID.
	 * @return object|null
	 */
	public static function get( $id ) {
		global $wpdb;

		return $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM ' . self::table_name() . ' WHERE id = %d',
				$id
			)
		);
	}

	/**
	 * @param int $id Registration ID.
	 * @return bool
	 */
	public static function delete( $id ) {
		global $wpdb;

		return false !== $wpdb->delete(
			self::table_name(),
			array( 'id' => $id ),
			array( '%d' )
		);
	}

	/**
	 * @param string $email Email.
	 * @param string $cf    Codice fiscale.
	 * @return object|null
	 */
	public static function find_active_by_email_or_cf( $email, $cf ) {
		global $wpdb;

		return $wpdb->get_row(
			$wpdb->prepare(
				'SELECT * FROM ' . self::table_name() . " WHERE ( email = %s OR codice_fiscale = %s ) AND status IN ('pending','admitted') AND anonymized_at IS NULL LIMIT 1",
				$email,
				$cf
			)
		);
	}

	/**
	 * @param array<string,mixed> $args Query args.
	 * @return array<int,object>
	 */
	public static function query( $args = array() ) {
		global $wpdb;

		$defaults = array(
			'status'  => '',
			'search'  => '',
			'limit'   => 20,
			'offset'  => 0,
			'orderby' => 'created_at',
			'order'   => 'DESC',
		);
		$args = wp_parse_args( $args, $defaults );

		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $args['status'] ) ) {
			$where[]  = 'status = %s';
			$params[] = $args['status'];
		}

		if ( ! empty( $args['search'] ) ) {
			$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where[]  = '( cognome LIKE %s OR nome LIKE %s OR email LIKE %s OR codice_fiscale LIKE %s )';
			$params   = array_merge( $params, array( $like, $like, $like, $like ) );
		}

		$allowed_orderby = array( 'created_at', 'cognome', 'nome', 'email', 'status' );
		$orderby         = in_array( $args['orderby'], $allowed_orderby, true ) ? $args['orderby'] : 'created_at';
		$order           = 'ASC' === strtoupper( $args['order'] ) ? 'ASC' : 'DESC';

		$sql = 'SELECT * FROM ' . self::table_name() . ' WHERE ' . implode( ' AND ', $where )
			. " ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";

		$params[] = (int) $args['limit'];
		$params[] = (int) $args['offset'];

		if ( ! empty( $params ) ) {
			$sql = $wpdb->prepare( $sql, $params );
		}

		return $wpdb->get_results( $sql );
	}

	/**
	 * @param array<string,mixed> $args Args.
	 * @return int
	 */
	public static function count( $args = array() ) {
		global $wpdb;

		$where  = array( '1=1' );
		$params = array();

		if ( ! empty( $args['status'] ) ) {
			$where[]  = 'status = %s';
			$params[] = $args['status'];
		}

		if ( ! empty( $args['search'] ) ) {
			$like     = '%' . $wpdb->esc_like( $args['search'] ) . '%';
			$where[]  = '( cognome LIKE %s OR nome LIKE %s OR email LIKE %s OR codice_fiscale LIKE %s )';
			$params   = array_merge( $params, array( $like, $like, $like, $like ) );
		}

		$sql = 'SELECT COUNT(*) FROM ' . self::table_name() . ' WHERE ' . implode( ' AND ', $where );

		if ( ! empty( $params ) ) {
			$sql = $wpdb->prepare( $sql, $params );
		}

		return (int) $wpdb->get_var( $sql );
	}
}