<?php
/**
 * Storage: members (with mandate), collection runs, run items.
 *
 * A custom table rather than a CPT because the member list is the Verein's
 * legal record for direct debits: it needs typed columns (amounts, mandate
 * dates), it must never leak through REST/post queries, and IBANs live here
 * encrypted (see BSTEW_Crypto).
 */

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

class BSTEW_Store {

	public static function members_table() {
		global $wpdb;
		return $wpdb->prefix . 'bstew_members';
	}

	public static function runs_table() {
		global $wpdb;
		return $wpdb->prefix . 'bstew_runs';
	}

	public static function items_table() {
		global $wpdb;
		return $wpdb->prefix . 'bstew_run_items';
	}

	public static function install() {
		global $wpdb;
		require_once ABSPATH . 'wp-admin/includes/upgrade.php';
		$charset = $wpdb->get_charset_collate();

		$members = self::members_table();
		dbDelta( "CREATE TABLE {$members} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			member_no VARCHAR(32) NOT NULL DEFAULT '',
			name VARCHAR(191) NOT NULL,
			email VARCHAR(191) NOT NULL DEFAULT '',
			street VARCHAR(191) NOT NULL DEFAULT '',
			postcode VARCHAR(16) NOT NULL DEFAULT '',
			city VARCHAR(96) NOT NULL DEFAULT '',
			country VARCHAR(2) NOT NULL DEFAULT 'DE',
			iban_enc TEXT NULL,
			iban_last4 VARCHAR(4) NOT NULL DEFAULT '',
			mandate_ref VARCHAR(35) NOT NULL DEFAULT '',
			mandate_date DATE NULL,
			sequence_type VARCHAR(4) NOT NULL DEFAULT 'FRST',
			amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
			active TINYINT(1) NOT NULL DEFAULT 1,
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY active (active),
			KEY mandate_ref (mandate_ref)
		) {$charset};" );

		$runs = self::runs_table();
		dbDelta( "CREATE TABLE {$runs} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			msg_id VARCHAR(35) NOT NULL,
			collection_date DATE NOT NULL,
			purpose VARCHAR(140) NOT NULL DEFAULT '',
			tx_count INT UNSIGNED NOT NULL DEFAULT 0,
			total DECIMAL(18,2) NOT NULL DEFAULT 0.00,
			xml_file VARCHAR(191) NOT NULL DEFAULT '',
			created_at DATETIME NOT NULL,
			PRIMARY KEY  (id),
			KEY collection_date (collection_date)
		) {$charset};" );

		$items = self::items_table();
		dbDelta( "CREATE TABLE {$items} (
			id BIGINT(20) UNSIGNED NOT NULL AUTO_INCREMENT,
			run_id BIGINT(20) UNSIGNED NOT NULL,
			member_id BIGINT(20) UNSIGNED NOT NULL,
			amount DECIMAL(18,2) NOT NULL DEFAULT 0.00,
			end_to_end VARCHAR(35) NOT NULL DEFAULT '',
			PRIMARY KEY  (id),
			KEY run_id (run_id),
			KEY member_id (member_id)
		) {$charset};" );
	}

	/* ---- Members ---- */

	public static function save_member( array $m, $id = 0 ) {
		global $wpdb;
		$iban = strtoupper( preg_replace( '/\s+/', '', $m['iban'] ?? '' ) );
		$row  = array(
			'member_no'     => sanitize_text_field( $m['member_no'] ?? '' ),
			'name'          => sanitize_text_field( $m['name'] ?? '' ),
			'email'         => sanitize_email( $m['email'] ?? '' ),
			'street'        => sanitize_text_field( $m['street'] ?? '' ),
			'postcode'      => sanitize_text_field( $m['postcode'] ?? '' ),
			'city'          => sanitize_text_field( $m['city'] ?? '' ),
			'country'       => strtoupper( substr( sanitize_text_field( $m['country'] ?? 'DE' ), 0, 2 ) ),
			'mandate_ref'   => sanitize_text_field( $m['mandate_ref'] ?? '' ),
			'mandate_date'  => preg_match( '/^\d{4}-\d{2}-\d{2}$/', $m['mandate_date'] ?? '' ) ? $m['mandate_date'] : null,
			'sequence_type' => in_array( $m['sequence_type'] ?? 'FRST', array( 'FRST', 'RCUR', 'OOFF', 'FNAL' ), true ) ? $m['sequence_type'] : 'FRST',
			'amount'        => number_format( (float) str_replace( ',', '.', $m['amount'] ?? '0' ), 2, '.', '' ),
			'active'        => empty( $m['active'] ) ? 0 : 1,
		);
		if ( '' !== $iban ) {
			$row['iban_enc']   = BSTEW_Crypto::encrypt( $iban );
			$row['iban_last4'] = substr( $iban, -4 );
		}
		if ( $id > 0 ) {
			$wpdb->update( self::members_table(), $row, array( 'id' => (int) $id ) );
			return (int) $id;
		}
		$row['created_at'] = current_time( 'mysql' );
		$wpdb->insert( self::members_table(), $row );
		return (int) $wpdb->insert_id;
	}

	public static function get_member( $id ) {
		global $wpdb;
		$t = self::members_table();
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$t} WHERE id = %d", $id ) ); // phpcs:ignore
	}

	public static function member_iban( $member ) {
		return $member && ! empty( $member->iban_enc ) ? BSTEW_Crypto::decrypt( $member->iban_enc ) : '';
	}

	public static function active_members() {
		global $wpdb;
		$t = self::members_table();
		return $wpdb->get_results( "SELECT * FROM {$t} WHERE active = 1 ORDER BY name ASC" ); // phpcs:ignore
	}

	public static function all_members() {
		global $wpdb;
		$t = self::members_table();
		return $wpdb->get_results( "SELECT * FROM {$t} ORDER BY name ASC" ); // phpcs:ignore
	}

	public static function delete_member( $id ) {
		global $wpdb;
		$wpdb->delete( self::members_table(), array( 'id' => (int) $id ) );
	}

	/* ---- Runs ---- */

	public static function record_run( array $run, array $items ) {
		global $wpdb;
		$run['created_at'] = current_time( 'mysql' );
		if ( false === $wpdb->insert( self::runs_table(), $run ) ) {
			return 0; // Caller must not report success without a run record.
		}
		$run_id = (int) $wpdb->insert_id;
		foreach ( $items as $item ) {
			$item['run_id'] = $run_id;
			$wpdb->insert( self::items_table(), $item );
		}
		// The first successful collection flips a FRST mandate to RCUR for
		// every included member — forgetting this is one of the classic ways a
		// second Beitragslauf gets rejected.
		$member_ids = array_map( function ( $i ) {
			return (int) $i['member_id'];
		}, $items );
		if ( $member_ids ) {
			$in = implode( ',', array_map( 'intval', $member_ids ) );
			$m  = self::members_table();
			$wpdb->query( "UPDATE {$m} SET sequence_type = 'RCUR' WHERE id IN ({$in}) AND sequence_type = 'FRST'" ); // phpcs:ignore
		}
		return $run_id;
	}

	public static function runs() {
		global $wpdb;
		$t = self::runs_table();
		return $wpdb->get_results( "SELECT * FROM {$t} ORDER BY created_at DESC LIMIT 50" ); // phpcs:ignore
	}

	public static function get_run( $id ) {
		global $wpdb;
		$t = self::runs_table();
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$t} WHERE id = %d", $id ) ); // phpcs:ignore
	}
}
