<?php
/**
 * Supplier data access.
 *
 * @package RestaurantCosting
 */

declare(strict_types=1);

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

final class RC_Supplier_Repository {

	public static function get_all(): array {
		global $wpdb;
		$table = RC_Database::table( 'suppliers' );
		return $wpdb->get_results( "SELECT id, business_name FROM {$table} ORDER BY business_name ASC" ) ?: array();
	}

	public static function find_or_create( string $business_name ): int {
		global $wpdb;
		$table = RC_Database::table( 'suppliers' );
		$name  = trim( $business_name );
		if ( '' === $name ) {
			return 0;
		}

		$id = $wpdb->get_var( $wpdb->prepare( "SELECT id FROM {$table} WHERE business_name = %s", $name ) );
		if ( $id ) {
			return (int) $id;
		}

		$wpdb->insert( $table, array( 'business_name' => $name ), array( '%s' ) );
		RC_Exception::throw_on_db_error( 'insert', $table );
		return (int) $wpdb->insert_id;
	}

	public static function get_by_name( string $business_name ): ?object {
		global $wpdb;
		$table = RC_Database::table( 'suppliers' );
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE business_name = %s", $business_name ) ) ?: null;
	}

	public static function get( int $id ): ?object {
		global $wpdb;
		$table = RC_Database::table( 'suppliers' );
		return $wpdb->get_row( $wpdb->prepare( "SELECT * FROM {$table} WHERE id = %d", $id ) ) ?: null;
	}

	public static function count( array $filters = array() ): int {
		global $wpdb;
		$table   = RC_Database::table( 'suppliers' );
		$where   = array();
		$params  = array();

		if ( ! empty( $filters['s'] ) ) {
			$like     = '%' . $wpdb->esc_like( $filters['s'] ) . '%';
			$where[]  = "(business_name LIKE %s OR contact_name LIKE %s OR email LIKE %s OR phone LIKE %s)";
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

		$where_clause = '';
		if ( ! empty( $where ) ) {
			$where_clause = ' WHERE ' . implode( ' AND ', $where );
		}

		$query = "SELECT COUNT(*) FROM {$table}{$where_clause}";
		if ( ! empty( $params ) ) {
			$query = $wpdb->prepare( $query, $params );
		}

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

	public static function list( int $limit = 50, int $offset = 0, array $filters = array(), string $orderby = 'business_name', string $order = 'ASC' ): array {
		global $wpdb;
		$table = RC_Database::table( 'suppliers' );

		$allowed_cols = array( 'business_name', 'contact_name', 'email', 'phone', 'created_at' );
		$orderby = in_array( $orderby, $allowed_cols, true ) ? $orderby : 'business_name';
		$order   = ( strtoupper( $order ) === 'DESC' ) ? 'DESC' : 'ASC';

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

		if ( ! empty( $filters['s'] ) ) {
			$like     = '%' . $wpdb->esc_like( $filters['s'] ) . '%';
			$where[]  = "(business_name LIKE %s OR contact_name LIKE %s OR email LIKE %s OR phone LIKE %s)";
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
			$params[] = $like;
		}

		$where_clause = '';
		if ( ! empty( $where ) ) {
			$where_clause = ' WHERE ' . implode( ' AND ', $where );
		}

		$query = "SELECT * FROM {$table} {$where_clause} ORDER BY {$orderby} {$order} LIMIT %d OFFSET %d";
		$final_params = array_merge( $params, array( $limit, $offset ) );

		return $wpdb->get_results( $wpdb->prepare( $query, $final_params ) ) ?: array();
	}

	public static function upsert( array $data, ?int $id = null ): int {
		global $wpdb;
		$table = RC_Database::table( 'suppliers' );

		$row = array(
			'business_name' => sanitize_text_field( (string) ( $data['business_name'] ?? '' ) ),
			'address'       => isset( $data['address'] ) ? sanitize_textarea_field( (string) $data['address'] ) : null,
			'phone'         => sanitize_text_field( (string) ( $data['phone'] ?? '' ) ),
			'email'         => sanitize_email( (string) ( $data['email'] ?? '' ) ),
			'contact_name'  => sanitize_text_field( (string) ( $data['contact_name'] ?? '' ) ),
			'email_order'   => ! empty( $data['email_order'] ) ? 1 : 0,
		);

		$formats = array( '%s', '%s', '%s', '%s', '%s', '%d' );

		if ( $id && $id > 0 ) {
			$wpdb->update( $table, $row, array( 'id' => $id ), $formats, array( '%d' ) );
			RC_Exception::throw_on_db_error( 'update', $table );
			return $id;
		}

		$wpdb->insert( $table, $row, $formats );
		RC_Exception::throw_on_db_error( 'insert', $table );
		return (int) $wpdb->insert_id;
	}

	public static function delete( int $id ): bool {
		global $wpdb;
		$table = RC_Database::table( 'suppliers' );
		$result = $wpdb->delete( $table, array( 'id' => $id ), array( '%d' ) );
		RC_Exception::throw_on_db_error( 'delete', $table );
		return (bool) $result;
	}

	public static function export_csv(): void {
		global $wpdb;
		$table = RC_Database::table( 'suppliers' );
		$rows  = $wpdb->get_results( "SELECT * FROM {$table} ORDER BY business_name ASC", ARRAY_A );

		$filename = 'suppliers-export-' . gmdate( 'Y-m-d' ) . '.csv';
		header( 'Content-Type: text/csv; charset=utf-8' );
		header( 'Content-Disposition: attachment; filename=' . $filename );

		$out = fopen( 'php://output', 'wb' );
		fputcsv( $out, array( 'Business Name', 'Address', 'Phone', 'Email', 'Contact Name' ) );

		foreach ( $rows ?: array() as $row ) {
			fputcsv( $out, array( $row['business_name'], $row['address'], $row['phone'], $row['email'], $row['contact_name'] ) );
		}

		fclose( $out );
		exit;
	}
}
