<?php

if ( ! defined( 'ABSPATH' ) ) {
    exit; // Exit if accessed directly
}

/**
 * Get all products
 *
 * @return mixed
 */
function erp_acct_get_all_inventory_items( $args = [] ) {
    global $wpdb;

    $defaults = [
        'number'  => 20,
        'offset'  => 0,
        'orderby' => 'id',
        'order'   => 'DESC',
        'count'   => false,
        's'       => '',
    ];

    $args = wp_parse_args( $args, $defaults );

    $last_changed        = erp_cache_get_last_changed( 'accounting', 'product_inventories', 'erp-inventory' );
    $cache_key           = 'erp-get-product-inventories-' . md5( serialize( $args ) ) . ": $last_changed";
    $product_inventories = wp_cache_get( $cache_key, 'erp-inventory' );

    $cache_key_count           = 'erp-get-product-inventories-count-' . md5( serialize( $args ) ) . ": $last_changed";
    $product_inventories_count = wp_cache_get( $cache_key_count, 'erp-inventory' );

    if ( false === $product_inventories ) {
        $limit = '';

        if ( $args['number'] != '-1' ) {
            $limit = "LIMIT {$args['number']} OFFSET {$args['offset']}";
        }

        $sql = "SELECT";

        if ( $args['count'] ) {
            $sql .= " COUNT( product.id ) as total_number";
        } else {
            $sql .= " product.id,
                product.name,
                product.product_type_id,
                product.cost_price,
                product.sale_price,
                (SUM(product_detail.stock_in) - SUM(product_detail.stock_out)) as stock,
                product.tax_cat_id,
                tax_cat.name as tax_cat_name,

                people.id AS vendor,
                CONCAT(people.first_name, ' ',  people.last_name) AS vendor_name,
                cat.id AS category_id,
                cat.name AS cat_name";
        }

        $sql .= " FROM {$wpdb->prefix}erp_acct_products AS product
            LEFT JOIN {$wpdb->prefix}erp_peoples AS people ON product.vendor = people.id
            LEFT JOIN {$wpdb->prefix}erp_acct_product_categories AS cat ON product.category_id = cat.id
            LEFT JOIN {$wpdb->prefix}erp_acct_product_details AS product_detail ON product.id = product_detail.product_id
            LEFT JOIN {$wpdb->prefix}erp_acct_tax_categories AS tax_cat ON tax_cat.id = product.tax_cat_id
            WHERE product.product_type_id=1 GROUP BY product.id";

        if ( $args['count'] ) {
            $product_inventories_count = count( $wpdb->get_col( $sql, 0 ) );

            wp_cache_set( $cache_key_count, $product_inventories_count, 'erp-inventory' );
        } else {
            $sql .= " ORDER BY product.{$args['orderby']} {$args['order']} {$limit}";

            $product_inventories = $wpdb->get_results( $sql, ARRAY_A );

            wp_cache_set( $cache_key, $product_inventories, 'erp-inventory' );
        }
    }

    if ( $args['count'] ) {
        return $product_inventories_count;
    }

    return $product_inventories;
}

/**
 * Insert inventory data on purchase create
 *
 * @param array $data
 * @param int $voucher_no
 * 
 * @return mixed
 */
function erp_acct_inventory_purchase_create( $data, $voucher_no ) {
    global $wpdb;

    $user_id = get_current_user_id();

    $data['created_at'] = date( 'Y-m-d H:i:s' );
    $data['created_by'] = $user_id;
    $data['updated_at'] = date( 'Y-m-d H:i:s' );
    $data['updated_by'] = $user_id;

    try {
        $wpdb->query( 'START TRANSACTION' );

        $items = $data['line_items'];

        foreach ( $items as $key => $item ) {

            $data['product_type_id'] = erp_acct_get_product_type_id_by_product_id( $item['product_id'] );

            if ( $data['product_type_id'] != 1 ) {
                continue;
            }

            $wpdb->insert( $wpdb->prefix . 'erp_acct_product_details', array(
                'trn_no'     => $voucher_no,
                'product_id' => $item['product_id'],
                'stock_in'   => $item['qty'],
                'stock_out'  => 0,
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by']
            ) );

            $wpdb->insert( $wpdb->prefix . 'erp_acct_product_price', array(
                'trn_no'     => $voucher_no,
                'product_id' => $item['product_id'],
                'price'      => $item['item_total'],
                'trn_date'   => $data['trn_date'],
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by'],
            ) );
        }

        erp_inventory_purge_cache( ['list' => 'product_inventories'] );

        $wpdb->query( 'COMMIT' );

    } catch ( Exception $e ) {
        $wpdb->query( 'ROLLBACK' );
        return new WP_error( 'inventory-exception', $e->getMessage() );
    }

}

/**
 * Insert inventory data on purchase return
 * 
 * @since 1.3.7
 *
 * @param array $data
 * @param int $voucher_no
 * 
 * @return mixed
 */
function erp_acct_inventory_purchase_return( $data, $voucher_no ) {
    global $wpdb;

    $user_id = get_current_user_id();

    $data['created_at'] = date( 'Y-m-d H:i:s' );
    $data['created_by'] = $user_id;
    $data['updated_at'] = date( 'Y-m-d H:i:s' );
    $data['updated_by'] = $user_id;

    try {
        $wpdb->query( 'START TRANSACTION' );

        $items = $data['line_items'];

        foreach ( $items as $key => $item ) {

            $data['product_type_id'] = erp_acct_get_product_type_id_by_product_id( $item['product_id'] );

            if ( $data['product_type_id'] != 1 ) {
                continue;
            }

            $wpdb->insert( $wpdb->prefix . 'erp_acct_product_details', array(
                'trn_no'     => $voucher_no,
                'product_id' => $item['product_id'],
                'stock_in'   => 0,
                'stock_out'  => $item['qty'],
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by']
            ) );

            $wpdb->insert( $wpdb->prefix . 'erp_acct_product_price', array(
                'trn_no'     => $voucher_no,
                'product_id' => $item['product_id'],
                'price'      => (float) $item['price'] * (float) $item['qty'],
                'trn_date'   => $data['trn_date'],
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by'],
            ) );
        }

        erp_inventory_purge_cache( ['list' => 'product_inventories'] );

        $wpdb->query( 'COMMIT' );

    } catch ( Exception $e ) {
        $wpdb->query( 'ROLLBACK' );
        return new WP_error( 'inventory-exception', $e->getMessage() );
    }

}

/**
 * Insert inventory data on purchase update
 * 
 * @param array $data
 * @param int $voucher_no
 * 
 * @return mixed
 */
function erp_acct_inventory_purchase_update( $data, $voucher_no ) {
    global $wpdb;

    $user_id = get_current_user_id();

    $data['updated_at'] = date( 'Y-m-d H:i:s' );
    $data['updated_by'] = $user_id;

    try {
        $wpdb->query( 'START TRANSACTION' );

        $items = $data['line_items'];

        foreach ( $items as $key => $item ) {

            $data['product_type_id'] = erp_acct_get_product_type_id_by_product_id( $item['product_id'] );

            if ( $data['product_type_id'] != 1 ) {
                continue;
            }

            $wpdb->update( $wpdb->prefix . 'erp_acct_product_details', array(
                'product_id' => $item['product_id'],
                'stock_in'   => $item['qty'],
                'stock_out'  => 0,
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by']
            ), array(
                'trn_no' => $voucher_no,
            ) );

            $wpdb->update( $wpdb->prefix . 'erp_acct_product_price', array(
                'product_id' => $item['product_id'],
                'price'      => $item['item_total'],
                'trn_date'   => $data['trn_date'],
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by'],
            ), array(
                'trn_no' => $voucher_no,
            ) );
        }

        erp_inventory_purge_cache( ['list' => 'product_inventories'] );

        $wpdb->query( 'COMMIT' );

    } catch ( Exception $e ) {
        $wpdb->query( 'ROLLBACK' );
        return new WP_error( 'inventory-exception', $e->getMessage() );
    }

}

/**
 * Insert inventory sales data on sale create
 *
 * @param array $data
 * @param int $voucher_no
 * 
 * @return mixed
 */
function erp_acct_inventory_items_sales_create( $data, $voucher_no ) {
    global $wpdb;

    $user_id = get_current_user_id();

    $data['created_at'] = date( 'Y-m-d H:i:s' );
    $data['created_by'] = $user_id;
    $data['updated_at'] = date( 'Y-m-d H:i:s' );
    $data['updated_by'] = $user_id;

    try {
        $wpdb->query( 'START TRANSACTION' );

        $items = $data['line_items'];

        foreach ( $items as $key => $item ) {

            $data['product_type_id'] = erp_acct_get_product_type_id_by_product_id( $item['product_id'] );

            if ( $data['product_type_id'] != 1 ) {
                continue;
            }

            $wpdb->insert( $wpdb->prefix . 'erp_acct_product_details', array(
                'trn_no'     => $voucher_no,
                'product_id' => $item['product_id'],
                'stock_in'   => 0,
                'stock_out'  => $item['qty'],
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by']
            ) );

            $wpdb->insert( $wpdb->prefix . 'erp_acct_product_price', array(
                'trn_no'     => $voucher_no,
                'product_id' => $item['product_id'],
                'price'      => $item['item_total'],
                'trn_date'   => isset( $data['date'] ) ? $data['date'] : $data['created_at'],
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by'],
            ) );
        }

        erp_inventory_purge_cache( ['list' => 'product_inventories'] );

        $wpdb->query( 'COMMIT' );

    } catch ( Exception $e ) {
        $wpdb->query( 'ROLLBACK' );
        return new WP_error( 'inventory-exception', $e->getMessage() );
    }

}

/**
 * Insert inventory data on sales return
 * 
 * @since 1.3.7
 * 
 * @param array $data
 * @param int $voucher_no
 * 
 * @return mixed
 */
function erp_acct_inventory_sale_return( $data, $voucher_no ) {
    global $wpdb;

    $user_id = get_current_user_id();

    $data['created_at'] = erp_current_datetime()->format( 'Y-m-d H:i:s' );
    $data['created_by'] = $user_id;
    $data['updated_at'] = erp_current_datetime()->format( 'Y-m-d H:i:s' );
    $data['updated_by'] = $user_id;

    try {
        $wpdb->query( 'START TRANSACTION' );

        $items = $data['line_items'];

        foreach ( $items as $key => $item ) {

            $data['product_type_id'] = erp_acct_get_product_type_id_by_product_id( $item['product_id'] );

            if ( $data['product_type_id'] != 1 ) {
                continue;
            }

            $wpdb->insert( $wpdb->prefix . 'erp_acct_product_details', array(
                'trn_no'     => $voucher_no,
                'product_id' => $item['product_id'],
                'stock_in'   => $item['qty'],
                'stock_out'  => 0,
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by']
            ) );

            $wpdb->insert( $wpdb->prefix . 'erp_acct_product_price', array(
                'trn_no'     => $voucher_no,
                'product_id' => $item['product_id'],
                'price'      => $item['line_total'],
                'trn_date'   => $data['return_date'],
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by'],
            ) );
        }

        erp_inventory_purge_cache( ['list' => 'product_inventories'] );

        $wpdb->query( 'COMMIT' );

    } catch ( Exception $e ) {
        $wpdb->query( 'ROLLBACK' );
        return new WP_error( 'inventory-exception', $e->getMessage() );
    }
}

/**
 * Insert inventory data on sales update
 * 
 * @param array $data
 * @param int $voucher_no
 * 
 * @return mixed
 */
function erp_acct_inventory_sales_updates( $data, $voucher_no ) {
    global $wpdb;

    $user_id = get_current_user_id();

    $data['updated_at'] = date( 'Y-m-d H:i:s' );
    $data['updated_by'] = $user_id;

    try {
        $wpdb->query( 'START TRANSACTION' );

        $items = $data['line_items'];

        foreach ( $items as $key => $item ) {

            $data['product_type_id'] = erp_acct_get_product_type_id_by_product_id( $item['product_id'] );

            if ( $data['product_type_id'] != 1 ) {
                continue;
            }

            $wpdb->update( $wpdb->prefix . 'erp_acct_product_details', array(
                'product_id' => $item['product_id'],
                'stock_in'   => 0,
                'stock_out'  => $item['qty'],
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by']
            ), array(
                'trn_no' => $voucher_no,
            ) );

            $wpdb->update( $wpdb->prefix . 'erp_acct_product_price', array(
                'product_id' => $item['product_id'],
                'price'      => $item['item_total'],
                'trn_date'   => $data['date'],
                'created_at' => $data['created_at'],
                'created_by' => $data['created_by'],
                'updated_at' => $data['updated_at'],
                'updated_by' => $data['updated_by'],
            ), array(
                'trn_no' => $voucher_no,
            ) );
        }

        erp_inventory_purge_cache( ['list' => 'product_inventories'] );

        $wpdb->query( 'COMMIT' );

    } catch ( Exception $e ) {
        $wpdb->query( 'ROLLBACK' );
        return new WP_error( 'inventory-exception', $e->getMessage() );
    }

}

/**
 * Get Inventory stock overview
 */
function erp_acct_get_inventory_stock_overview( $args = [] ) {
    global $wpdb;

    $where = '';

    if ( ! empty( $args['start_date'] ) ) {
        $where .= " AND invoice.trn_date BETWEEN '{$args['start_date']}' AND '{$args['end_date']}'";
    }

    $sql = "SELECT SUM(stock_in) as stock_in, SUM(stock_out) as stock_out
        FROM {$wpdb->prefix}erp_acct_product_details";

    return $wpdb->get_row( $sql, ARRAY_A );
}

/**
 * Get Inventory transactions overview
 */
function erp_acct_get_inventory_transactions_overview( $args = [] ) {
    global $wpdb;

    $where = '';

    if ( ! empty( $args['start_date'] ) ) {
        $where .= " AND invoice.trn_date BETWEEN '{$args['start_date']}' AND '{$args['end_date']}'";
    }

    $sql1 = "SELECT SUM(price) as sales
        FROM {$wpdb->prefix}erp_acct_product_price AS poduct_price
        LEFT JOIN {$wpdb->prefix}erp_acct_voucher_no AS voucher ON voucher.id = poduct_price.trn_no WHERE voucher.type='invoice' {$where}";

    $trn_data['sales'] = $wpdb->get_var( $sql1 );

    $sql2 = "SELECT SUM(price) as sales
        FROM {$wpdb->prefix}erp_acct_product_price AS poduct_price
        LEFT JOIN {$wpdb->prefix}erp_acct_voucher_no AS voucher ON voucher.id = poduct_price.trn_no WHERE voucher.type='purchase' {$where}";

    $trn_data['purchase'] = $wpdb->get_var( $sql2 );

    return $trn_data;
}

/**
 * Purge cache data for Inventory addons
 *
 * Remove all cache for Inventory addons
 *
 * @since 1.3.4
 *
 * @param array $args
 *
 * @return void
 */
function erp_inventory_purge_cache( $args = [] ) {

    $defaults = [
        'group' => 'erp-inventory',
        'list'  => 'product_inventories'
    ];

    $args = wp_parse_args( $args, $defaults );

    if ( isset( $args['list'] ) ) {
        erp_acct_purge_cache( [ 'group' => $args['group'], 'list' => $args['list'] ] );
    }
}
