<?php

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

class AIEO_Option_Groups_Store {

	
	private static function t() {
		return AIEO_Option_Groups_Schema::tables();
	}

	
	
	

	
	public static function get_product_config( $product_id ) {
		global $wpdb;
		$product_id = (int) $product_id;
		$t          = self::t();
		$empty      = array( 'product_id' => $product_id, 'groups' => array(), 'rules' => array() );
		if ( $product_id <= 0 ) { return $empty; }

		
		
		
		
		$groups = $wpdb->get_results( $wpdb->prepare(
			"SELECT g.id, g.slug, g.name, g.select_type, g.min_select, g.max_select,
			        g.included_free, g.free_policy, g.description,
			        a.id AS assignment_id, a.sort_order AS sort_order
			   FROM {$t['assignments']} a
			   JOIN {$t['groups']} g ON g.id = a.group_id
			  WHERE a.product_id = %d AND a.enabled = 1 AND g.enabled = 1
			  ORDER BY a.sort_order ASC, g.name ASC",
			$product_id
		), ARRAY_A );
		if ( empty( $groups ) ) { return $empty; }

		$group_ids = array();
		$out       = array();
		foreach ( $groups as $g ) {
			$gid = (int) $g['id'];
			$group_ids[] = $gid;
			$out[ $gid ] = array(
				'id'             => $gid,
				'slug'           => (string) $g['slug'],
				'name'           => (string) $g['name'],
				'select_type'    => (string) $g['select_type'],
				'min_select'     => (int) $g['min_select'],
				'max_select'     => (int) $g['max_select'],
				'included_free'  => (int) $g['included_free'],
				'free_policy'    => (string) $g['free_policy'],
				'description'    => (string) $g['description'],
				'assignment_id'  => (int) $g['assignment_id'],
				'sort_order'     => (int) $g['sort_order'],
				'options'        => array(),
			);
		}

		$in = implode( ',', array_map( 'intval', $group_ids ) );

		
		
		$options = $wpdb->get_results(
			"SELECT id, group_id, slug, label, price_delta, default_selected,
			        exclude_from_allowance, linked_product_id, sku, availability, sort_order
			   FROM {$t['options']}
			  WHERE group_id IN ({$in}) AND enabled = 1
			  ORDER BY group_id ASC, sort_order ASC, label ASC",
			ARRAY_A
		);
		$option_ids = array();
		foreach ( (array) $options as $o ) {
			$gid = (int) $o['group_id'];
			if ( ! isset( $out[ $gid ] ) ) { continue; }
			$oid = (int) $o['id'];
			$option_ids[] = $oid;
			$out[ $gid ]['options'][ $oid ] = array(
				'id'                     => $oid,
				'group_id'               => $gid,
				'slug'                   => (string) $o['slug'],
				'label'                  => (string) $o['label'],
				'price_delta'            => (float) $o['price_delta'],
				'default_selected'       => (int) $o['default_selected'] === 1,
				'exclude_from_allowance' => (int) $o['exclude_from_allowance'] === 1,
				'linked_product_id'      => (int) $o['linked_product_id'],
				'sku'                    => (string) $o['sku'],
				'availability'           => (string) $o['availability'],
				'available'              => self::effective_available( $o ),
				'sort_order'             => (int) $o['sort_order'],
			);
		}

		
		$rules = array();
		if ( $option_ids ) {
			$oin = implode( ',', array_map( 'intval', $option_ids ) );
			
			$rows = $wpdb->get_results(
				"SELECT option_id, scope_assignment_id, when_group_id, when_option_id, price_delta
				   FROM {$t['prices']}
				  WHERE option_id IN ({$oin})",
				ARRAY_A
			);
			foreach ( (array) $rows as $r ) {
				$oid = (int) $r['option_id'];
				$rules[ $oid ][] = array(
					'scope_assignment_id' => (int) $r['scope_assignment_id'],
					'when_group_id'       => (int) $r['when_group_id'],
					'when_option_id'      => (int) $r['when_option_id'],
					'price_delta'         => (float) $r['price_delta'],
				);
			}
		}

		return array( 'product_id' => $product_id, 'groups' => $out, 'rules' => $rules );
	}

	
	public static function effective_available( array $option ) {
		$mode = isset( $option['availability'] ) ? (string) $option['availability'] : 'auto';
		if ( 'in_stock' === $mode )     { return true; }
		if ( 'out_of_stock' === $mode ) { return false; }

		
		$pid = isset( $option['linked_product_id'] ) ? (int) $option['linked_product_id'] : 0;
		if ( $pid <= 0 && ! empty( $option['sku'] ) && function_exists( 'wc_get_product_id_by_sku' ) ) {
			$pid = (int) wc_get_product_id_by_sku( (string) $option['sku'] );
		}
		if ( $pid <= 0 || ! function_exists( 'wc_get_product' ) ) {
			return true; 
		}
		$p = wc_get_product( $pid );
		return $p ? (bool) $p->is_in_stock() : true;
	}

	
	
	

	
	public static function upsert_group( array $data ) {
		global $wpdb;
		$t   = self::t();
		$row = array(
			'name'          => isset( $data['name'] ) ? (string) $data['name'] : '',
			'select_type'   => ( isset( $data['select_type'] ) && 'radio' === $data['select_type'] ) ? 'radio' : 'checkbox',
			'min_select'    => isset( $data['min_select'] ) ? max( 0, (int) $data['min_select'] ) : 0,
			'max_select'    => isset( $data['max_select'] ) ? max( 0, (int) $data['max_select'] ) : 0,
			'included_free' => isset( $data['included_free'] ) ? max( 0, (int) $data['included_free'] ) : 0,
			'free_policy'   => ( isset( $data['free_policy'] ) && 'cheapest_free' === $data['free_policy'] ) ? 'cheapest_free' : 'expensive_free',
			'description'   => isset( $data['description'] ) ? (string) $data['description'] : '',
			'enabled'       => isset( $data['enabled'] ) ? (int) (bool) $data['enabled'] : 1,
			'sort_order'    => isset( $data['sort_order'] ) ? (int) $data['sort_order'] : 0,
			'updated_at'    => current_time( 'mysql', true ),
		);
		$id = isset( $data['id'] ) ? (int) $data['id'] : 0;
		if ( $id > 0 ) {
			$wpdb->update( $t['groups'], $row, array( 'id' => $id ) );
			return $id;
		}
		$row['slug']       = self::unique_slug( $t['groups'], isset( $data['slug'] ) ? $data['slug'] : $row['name'] );
		$row['created_at'] = current_time( 'mysql', true );
		$wpdb->insert( $t['groups'], $row );
		return (int) $wpdb->insert_id;
	}

	
	public static function upsert_option( array $data ) {
		global $wpdb;
		$t   = self::t();
		$row = array(
			'label'                  => isset( $data['label'] ) ? (string) $data['label'] : '',
			'price_delta'            => isset( $data['price_delta'] ) ? round( (float) $data['price_delta'], 4 ) : 0,
			'default_selected'       => isset( $data['default_selected'] ) ? (int) (bool) $data['default_selected'] : 0,
			'exclude_from_allowance' => isset( $data['exclude_from_allowance'] ) ? (int) (bool) $data['exclude_from_allowance'] : 0,
			'linked_product_id'      => isset( $data['linked_product_id'] ) ? max( 0, (int) $data['linked_product_id'] ) : 0,
			'sku'                    => isset( $data['sku'] ) ? (string) $data['sku'] : null,
			'availability'           => self::sanitize_availability( isset( $data['availability'] ) ? $data['availability'] : 'auto' ),
			'enabled'                => isset( $data['enabled'] ) ? (int) (bool) $data['enabled'] : 1,
			'sort_order'             => isset( $data['sort_order'] ) ? (int) $data['sort_order'] : 0,
			'updated_at'             => current_time( 'mysql', true ),
		);
		$id = isset( $data['id'] ) ? (int) $data['id'] : 0;
		if ( $id > 0 ) {
			$wpdb->update( $t['options'], $row, array( 'id' => $id ) );
			return $id;
		}
		$row['group_id']   = isset( $data['group_id'] ) ? (int) $data['group_id'] : 0;
		$row['slug']       = self::unique_slug( $t['options'], isset( $data['slug'] ) ? $data['slug'] : $row['label'], 'group_id = ' . $row['group_id'] );
		$row['created_at'] = current_time( 'mysql', true );
		$wpdb->insert( $t['options'], $row );
		return (int) $wpdb->insert_id;
	}

	
	public static function assign_group( $product_id, $group_id, $sort_order = 0 ) {
		global $wpdb;
		$t = self::t();
		$product_id = (int) $product_id;
		$group_id   = (int) $group_id;
		$existing   = (int) $wpdb->get_var( $wpdb->prepare(
			
			"SELECT id FROM {$t['assignments']} WHERE product_id = %d AND group_id = %d",
			$product_id, $group_id
		) );
		if ( $existing > 0 ) {
			$wpdb->update( $t['assignments'], array( 'sort_order' => (int) $sort_order, 'enabled' => 1 ), array( 'id' => $existing ) );
			return $existing;
		}
		$wpdb->insert( $t['assignments'], array(
			'product_id' => $product_id,
			'group_id'   => $group_id,
			'sort_order' => (int) $sort_order,
			'enabled'    => 1,
			'created_at' => current_time( 'mysql', true ),
		) );
		return (int) $wpdb->insert_id;
	}

	
	public static function upsert_rule( array $data ) {
		global $wpdb;
		$t   = self::t();
		$row = array(
			'option_id'           => isset( $data['option_id'] ) ? (int) $data['option_id'] : 0,
			'scope_assignment_id' => isset( $data['scope_assignment_id'] ) ? (int) $data['scope_assignment_id'] : 0,
			'when_group_id'       => isset( $data['when_group_id'] ) ? (int) $data['when_group_id'] : 0,
			'when_option_id'      => isset( $data['when_option_id'] ) ? (int) $data['when_option_id'] : 0,
			'price_delta'         => isset( $data['price_delta'] ) ? round( (float) $data['price_delta'], 4 ) : 0,
			'updated_at'          => current_time( 'mysql', true ),
		);
		$existing = (int) $wpdb->get_var( $wpdb->prepare(
			
			"SELECT id FROM {$t['prices']} WHERE option_id=%d AND scope_assignment_id=%d AND when_group_id=%d AND when_option_id=%d",
			$row['option_id'], $row['scope_assignment_id'], $row['when_group_id'], $row['when_option_id']
		) );
		if ( $existing > 0 ) {
			$wpdb->update( $t['prices'], array( 'price_delta' => $row['price_delta'], 'updated_at' => $row['updated_at'] ), array( 'id' => $existing ) );
			return $existing;
		}
		$row['created_at'] = current_time( 'mysql', true );
		$wpdb->insert( $t['prices'], $row );
		return (int) $wpdb->insert_id;
	}

	
	public static function delete( $which, $id ) {
		global $wpdb;
		$t = self::t();
		if ( ! isset( $t[ $which ] ) ) { return; }
		$id = (int) $id;
		$wpdb->delete( $t[ $which ], array( 'id' => $id ) );
		
		if ( 'groups' === $which ) {
			$opt_ids = $wpdb->get_col( $wpdb->prepare( "SELECT id FROM {$t['options']} WHERE group_id = %d", $id ) ); 
			if ( $opt_ids ) {
				$oin = implode( ',', array_map( 'intval', $opt_ids ) );
				$wpdb->query( "DELETE FROM {$t['prices']} WHERE option_id IN ({$oin})" ); 
			}
			$wpdb->delete( $t['options'], array( 'group_id' => $id ) );
			$wpdb->delete( $t['assignments'], array( 'group_id' => $id ) );
		} elseif ( 'options' === $which ) {
			$wpdb->delete( $t['prices'], array( 'option_id' => $id ) );
		}
	}

	
	
	

	private static function sanitize_availability( $v ) {
		$v = (string) $v;
		return in_array( $v, array( 'auto', 'in_stock', 'out_of_stock' ), true ) ? $v : 'auto';
	}

	
	private static function unique_slug( $table, $source, $extra_where = '' ) {
		global $wpdb;
		$base = sanitize_title( (string) $source );
		if ( '' === $base ) { $base = 'opt'; }
		$slug = $base;
		$i    = 2;
		$cond = $extra_where ? ( ' AND ' . $extra_where ) : '';
		
		while ( (int) $wpdb->get_var( $wpdb->prepare( "SELECT COUNT(*) FROM {$table} WHERE slug = %s{$cond}", $slug ) ) > 0 ) {
			$slug = $base . '-' . $i;
			$i++;
		}
		return $slug;
	}
}