<?php
namespace ABeCe\Booking\Repository;

use ABeCe\Booking\Domain\CatalogException;

/** Bounded query count: identities, resources and grouped ledger totals are joined once. */
final class OperationsRepository {
 public function __construct(private readonly \wpdb $wpdb) {}
 private function joins(): string {
  $wpdb = $this->wpdb;
  return $wpdb->prepare(' LEFT JOIN %i r ON r.id=b.resource_id LEFT JOIN %i bm ON bm.id=b.customer_member_id LEFT JOIN %i um ON um.wp_user_id=b.customer_user_id LEFT JOIN %i u ON u.ID=b.customer_user_id LEFT JOIN %i ec ON ec.id=b.external_contact_id', \ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'resources' ), \ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'members' ), \ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'members' ), $wpdb->users, \ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'external_contacts' ));
 }
 private function projection(): string {
  return "b.*, r.name resource_name, r.sort_order resource_sort_order, COALESCE(um.id,bm.id) identity_member_id, COALESCE(b.customer_user_id,bm.wp_user_id) identity_user_id, COALESCE(NULLIF(TRIM(CONCAT(bm.first_name,' ',bm.last_name)),''),NULLIF(TRIM(CONCAT(um.first_name,' ',um.last_name)),''),u.display_name,b.external_customer_label,b.booking_reference) customer_name, COALESCE(bm.email,u.user_email,ec.email,'') customer_email";
 }
 public function daily(string $from,string $to): array {
  $wpdb = $this->wpdb;
  $sql=$wpdb->prepare('SELECT '.$this->projection().', h.metadata cancellation_metadata, h.performed_by_user_id history_actor_id, h.created_at history_cancelled_at, h.description history_reason, actor.display_name actor_current_name FROM %i b', \ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'bookings' )).$this->joins();
  $sql.=$wpdb->prepare(" LEFT JOIN %i h ON h.id=(SELECT MAX(h2.id) FROM %i h2 WHERE h2.booking_id=b.id AND h2.event_type='cancelled') LEFT JOIN %i actor ON actor.ID=COALESCE(h.performed_by_user_id,b.cancelled_by_user_id) WHERE b.starts_at >= %s AND b.starts_at < %s AND b.status <> 'voided' ORDER BY b.starts_at, r.sort_order, r.id, b.id",\ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'booking_history' ),\ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'booking_history' ),$wpdb->users,$from,$to);
  return $this->rows($sql);
 }
 /** SQL totals only; SettlementService owns the financial formula and validation. */
 public function due(string $until,?array $identity=null): array {
  $wpdb = $this->wpdb;
  $sql=$wpdb->prepare('SELECT '.$this->projection().', COALESCE(p.paid,0) ledger_paid, COALESCE(p.refunded,0) ledger_refunded, COALESCE(p.invalid,0) ledger_invalid FROM %i b',\ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'bookings' )).$this->joins();
  $sql.=$wpdb->prepare(" LEFT JOIN (SELECT booking_id, SUM(CASE WHEN transaction_type='payment' THEN amount ELSE 0 END) paid, SUM(CASE WHEN transaction_type='refund' THEN amount ELSE 0 END) refunded, MAX(amount<0) invalid, MIN(BINARY currency) min_currency, MAX(BINARY currency) max_currency FROM %i WHERE status='completed' GROUP BY booking_id) p ON p.booking_id=b.id WHERE b.ends_at <= %s",\ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'payment_transactions' ),$until);
  // A currency mismatch must fail closed, just as individual settlement does.
  $sql=str_replace('COALESCE(p.invalid,0) ledger_invalid',"(COALESCE(p.invalid,0) OR COALESCE(BINARY p.min_currency<>BINARY b.currency,0) OR COALESCE(BINARY p.max_currency<>BINARY b.currency,0)) ledger_invalid",$sql);
  if($identity){
   $clauses=[];$values=[];
   foreach(['customer_user_id','customer_member_id','external_contact_id','id'] as $field)if(!empty($identity[$field])){$clauses[]='b.'.$field.'=%d';$values[]=$identity[$field];}
   if(!$clauses)return [];
   $sql.=$wpdb->prepare(' AND ('.implode(' OR ',$clauses).')',...$values);
  }
  return $this->rows($sql.' ORDER BY b.ends_at, b.id');
 }
 /** Linkage is independent of active membership and the memberships feature switch. */
 public function identity(array $customer): array {
  $wpdb = $this->wpdb;
  $user=(int)($customer['customer_user_id']??0);$member=(int)($customer['customer_member_id']??0);
  if($user)$member=(int)$wpdb->get_var($wpdb->prepare('SELECT id FROM %i WHERE wp_user_id=%d',\ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'members' ),$user))?:$member;
  elseif($member)$user=(int)$wpdb->get_var($wpdb->prepare('SELECT wp_user_id FROM %i WHERE id=%d',\ABeCe\Booking\Infrastructure\Database\Tables::name( $wpdb, 'members' ),$member));
  $this->check();return ['customer_user_id'=>$user?:null,'customer_member_id'=>$member?:null,'external_contact_id'=>$customer['external_contact_id']??null,'id'=>$customer['id']??null];
 }
 public function booking(int $id): ?array {return (new BookingsRepository($this->wpdb))->find($id);}
 private function rows(string $sql): array {
  $wpdb = $this->wpdb;$rows=$wpdb->get_results($sql,ARRAY_A);$this->check();foreach($rows as &$row){foreach(['id','resource_id','customer_user_id','customer_member_id','external_contact_id','identity_member_id','identity_user_id','final_amount','cancellation_fee','no_show_fee','ledger_paid','ledger_refunded','ledger_invalid','history_actor_id','cancelled_by_user_id'] as $field)if(array_key_exists($field,$row))$row[$field]=$row[$field]===null?null:(int)$row[$field];$row['reference']=$row['booking_reference'];unset($row['booking_reference']);}unset($row);return $rows;}
 private function check(): void {if($this->wpdb->last_error)throw new CatalogException('abece_booking_operations_database_error',__('The operations overview could not be loaded. Please try again.','abece-booking'),503);}
}