<?php
/**
 * Class with methods to generate required Sales reports
 *
 * @author: Uldis Zvirbulis <uldis.zvirbulis@mtcmedia.co.uk>
 * @version: 10/24/2017
 */

namespace Mtc\Shop;

use Carbon\Carbon;
use Illuminate\Support\Facades\DB;
use Mtc\Plugins\MembersMessaging\Classes\Message;
use Mtc\Plugins\Refunds\Classes\Refund;
use Mtc\Plugins\Refunds\Classes\RefundItem;

class Report
{
    public static $types = [
        'orders_report_combined' => [
            'name' => 'Orders (combined)',
            'function' => 'ordersReportCombined',
        ],
        'orders_report_detailed' => [
            'name' => 'Orders (detailed)',
            'function' => 'ordersReportDetailed',
        ],
        'tax_report' => [
            'name' => 'Tax Report',
            'function' => 'taxReport',
        ],
        'profit_report' => [
            'name' => 'Profit Report',
            'function' => 'profitReport',
        ],
        'doctor_report' => [
            'name' => 'Doctor Report',
            'function' => 'doctorReport',
        ],
        'rejected_report' => [
            'name' => 'Rejected Orders Report',
            'function' => 'rejectedReport',
        ],
    ];

    public static function runReport(string $report_type, array $params, bool $output_csv = true) :bool
    {
        $function_name = self::$types[$report_type]['function'];
        if (method_exists(new self(), $function_name)) {
            $report_rows = self::$function_name($params);
            if ($output_csv) {
                $filename = $report_type . '_' . date('Y-m-d_H:i:s') . '.csv';

                $download_file = fopen($filename, 'w');
                foreach ($report_rows as $row) {
                    fputcsv($download_file, $row, ',');
                }
                fclose($download_file);

                \Util::force_download($filename, file_get_contents($filename));
                unlink($filename);
                exit;
            }
            return true;
        }
        return false;
    }

    public static function taxReport(array $params) : array
    {
        $rows = [];
        $columns = [
            'Date',
            'Number of items sold',
            'Number of orders',
            'Average net sales amount',
            'Coupon amount',
            'Shipping amount',
            'Shipping Refund',
            'Shipping Net',
            'Gross Sales amount',
            'Item Refund',
            'Net Sales amount',
        ];

        $rows[] = $columns;

        // Get the months from dates
        $start_date = new \DateTime($params['start_date']);
        $end_date = new \DateTime($params['end_date']);
        $interval = new \DateInterval('P1M');
        $periods = new \DatePeriod($start_date, $interval, $end_date);
        foreach ($periods as $period) {
            $month_date = $period->format('Y-m-d');
            $from_date = date('Y-m-01', strtotime($month_date));
            $to_date = date('Y-m-t', strtotime($month_date));

            $order_items = \Mtc\Shop\Order\Item::query()
                ->join('order', function ($join) use ($from_date, $to_date) {
                    $join->on('order.id', '=', 'order_items.order_id')
                        ->where('order.paid', '1')
                        ->where('order.failed', '0')
                        ->whereNotIn('order.status', array_keys(Order::$failure_statuses))
                        ->where('order.date', '>=', $from_date . ' 00:00:00')
                        ->where('order.date', '<=', $to_date . ' 23:59:59');
                });
            $orders = Order::query()
                ->successful()
                ->where('order.date', '>=', $from_date . ' 00:00:00')
                ->where('order.date', '<=', $to_date . ' 23:59:59');


            if (!empty($params['category_id'])) {
                $order_items = $order_items->whereIn(
                    'item_id',
                    DB::table('items_categories')
                        ->where('cat_id', $params['category_id'])
                        ->pluck('item_id')
                        ->toArray()
                );
                $orders = $orders->groupBy('order.id')
                    ->join('order_items', function ($join) use ($params) {
                        $join->on('order.id', '=', 'order_items.order_id')
                            ->whereIn(
                                'order_items.item_id',
                                DB::table('items_categories')
                                    ->where('cat_id', $params['category_id'])
                                    ->pluck('item_id')
                                    ->toArray()
                            );
                    });
            }


            $refund_items = RefundItem::query()
                ->where('order_refund_items.created_at', '>=', $from_date . ' 00:00:00')
                ->where('order_refund_items.created_at', '<=', $to_date . ' 23:59:59');

            if (!empty($params['vat_code'])) {

                if ($params['vat_code'] === 'Z') {
                    $order_items = $order_items->leftJoin('items', 'items.id', '=', 'order_items.item_id')
                        ->where(function($query) use ($params) {
                            $query->where('items.vat_rate', Item::$vat_codes[$params['vat_code']])
                                ->orWhere('order_items.PLU', 'private_prescription')
                                ->orWhere('order_items.nhs_prescription', '1');
                        });
                } else {
                    $order_items = $order_items->join('items', function ($join) use ($params) {
                        $join->on('items.id', '=', 'order_items.item_id')
                            ->where('vat_rate', Item::$vat_codes[$params['vat_code']]);
                    });
                }

                $orders = $orders->groupBy('order.id')
                    ->join('order_items', 'order.id', '=', 'order_items.order_id')
                    ->join('items', function($join) use ($params) {
                        $join->on('items.id', '=', 'order_items.item_id')
                            ->where('items.vat_rate', Item::$vat_codes[$params['vat_code']]);
                    });

                if ($params['vat_code'] === 'Z') {
                    $refund_items = $refund_items->join('order_items', 'order_items.id', '=', 'order_refund_items.order_item_id')
                        ->leftJoin('items', 'items.id', '=', 'order_refund_items.item_id')
                        ->where(function($query) use ($params) {
                            $query->where('items.vat_rate', Item::$vat_codes[$params['vat_code']])
                                ->orWhere('order_items.PLU', 'private_prescription')
                                ->orWhere('order_items.nhs_prescription', '1');
                        });
                } else {
                    $refund_items = $refund_items->join('items', function($join) use ($params) {
                        $join->on('items.id', '=', 'order_refund_items.item_id')
                            ->where('vat_rate', Item::$vat_codes[$params['vat_code']]);
                    });
                }

                $delivery_refund = 0;

            } else {

                $refunds = Refund::query()
                    ->where('order_refunds.created_at', '>=', $from_date . ' 00:00:00')
                    ->where('order_refunds.created_at', '<=', $to_date . ' 23:59:59');

                $delivery_refund = $refunds->sum('delivery_refund_amount');

            }

            $item_refunds_total = $refund_items->sum('amount_refunded');

            if (!empty($params['vat_code'])) {
                $shipping_total = 0;
            } else {
                $shipping_total = $orders->sum('delivery_cost');
            }

            $items_sold = $order_items->sum('quantity');

            $price_paid_total = $order_items->get()->sum(function ($order_item) {
                return $order_item->quantity * $order_item->price_paid;
            });

            $price_total = $order_items->get()->sum(function ($order_item) {
                return $order_item->quantity * $order_item->item_price;
            });

            $coupons_total = $price_total - $price_paid_total;

            $net_sales = $price_total - $item_refunds_total;
            //$gross_sales = $net_sales + $shipping_total;

            $rows[] = [
                'Date' => date('Y-n', strtotime($month_date)),
                'Number of items sold' => $items_sold,
                'Number of orders' => $orders->get()->count(),
                'Average net sales amount' => '',
                'Coupon amount' => number_format($coupons_total, 2, '.', ''),
                'Shipping amount' => !empty($params['vat_code']) ? 'N/A' : number_format($shipping_total, 2, '.', ''),
                'Shipping Refund' => !empty($params['vat_code']) ? 'N/A' : number_format($delivery_refund, 2, '.', ''),
                'Shipping Net' => !empty($params['vat_code']) ? 'N/A' : number_format($shipping_total - $delivery_refund, 2, '.', ''),
                'Gross Sales amount' => number_format($price_total, 2, '.', ''),
                'Item Refund' => number_format($item_refunds_total, 2, '.', ''),
                'Net Sales amount' => number_format($net_sales, 2, '.', ''),
            ];
        }
        $rows[] = [];
        $rows[] = [
            'Date' => 'Shipping amount: Total shipping amount'
        ];
        $rows[] = [
            'Date' => 'Shipping Refund: Sum of delivery refunds'
        ];
        $rows[] = [
            'Date' => 'Shipping Net: Shipping amount - Shipping Refund'
        ];
        $rows[] = [
            'Date' => 'Gross Sales amount: Amount paid for order items'
        ];
        $rows[] = [
            'Date' => 'Item Refund: Sum of item refunds'
        ];
        $rows[] = [
            'Date' => 'Net Sales amount: Gross Sales amount - Item Refund'
        ];

        return $rows;
    }

    public static function profitReport(array $params) : array
    {
        $rows = [];
        $columns = [
            'Product Name',
            'Variation Attributes',
            'Quantity Sold',
            'Variation COG',
            'Gross Sales',
        ];

        $rows[] = $columns;

        $exclude_statuses = array_keys(Order::$failure_statuses);

        // Exclude refunded orders as well
        $exclude_statuses[] = 6;

        $order_items = \Mtc\Shop\Order\Item::query()
            ->selectRaw('
                order_items.sizeid as size_id,
                items_sizes.strength,
                items_sizes.quantity,
                items.name AS item_name,
                sum(order_items.price_paid * order_items.quantity) as gross_sales,
                sum(order_items.quantity) as quantity_sold,
                items_custom.cog_cost as item_cog_cost,
                items_sizes_custom.cog_cost as size_cog_cost
            ')
            ->join('order', function($join) use ($params, $exclude_statuses) {
                $join->on('order.id', '=', 'order_items.order_id')
                    ->where('order.paid', '1')
                    ->where('order.failed', '0')
                    ->whereNotIn('order.status', $exclude_statuses)
                    ->where('order.date', '>=', $params['start_date']  . ' 00:00:00')
                    ->where('order.date', '<=', $params['end_date'] . ' 23:59:59');
            });

        if (!empty($params['vat_code'])) {
            $order_items = $order_items->join('items', function ($join) use ($params) {
                $join->on('items.id', '=', 'order_items.item_id')
                    ->where('vat_rate', Item::$vat_codes[$params['vat_code']]);
            });
        } else {
            $order_items = $order_items->join('items', 'items.id', '=', 'order_items.item_id');
        }

        $order_items = $order_items->leftJoin('items_sizes', 'items_sizes.id', '=', 'order_items.sizeid')
            ->leftJoin('items_custom', 'items_custom.item_id', '=', 'order_items.item_id')
            ->leftJoin('items_sizes_custom', 'items_sizes_custom.size_id', '=', 'order_items.sizeid')
            ->groupBy(['order_items.item_id', 'order_items.sizeid']);

        if (!empty($params['category_id'])) {
            $order_items = $order_items->whereIn(
                'order_items.item_id',
                DB::table('items_categories')
                    ->where('cat_id', $params['category_id'])
                    ->pluck('item_id')
                    ->toArray()
            );
        }

        $order_items = $order_items->get();

        foreach ($order_items as $order_item) {
            $rows[] = [
                'Product Name' => $order_item->item_name,
                'Variation Attributes' => $order_item->strength . ' ' . $order_item->quantity,
                'Quantity Sold' => $order_item->quantity_sold,
                'Variation COG' => $order_item->size_id > 0 ? $order_item->size_cog_cost : $order_item->item_cog_cost,
                'Gross Sales' => number_format($order_item->gross_sales, 2, '.', ''),
            ];
        }

        return $rows;
    }

    public static function doctorReport(array $params) : array
    {

        $rows = [];
        $columns = [
            'Date From',
            'Date To',
            'Doctor Name',
            'Order Items Approved',
        ];

        $rows[] = $columns;

        $approvals = \ApprovalLog::query()
            ->selectRaw('DISTINCT approval_log.user_id, count(DISTINCT approval_log.order_item_id) as item_count')
            ->where('approval_log.created_at', '>=', $params['start_date']  . ' 00:00:00')
            ->where('approval_log.created_at', '<=', $params['end_date'] . ' 23:59:59')
            //->where('approval_log.status', '=', 'Approved')
            ->groupBy('approval_log.user_id');

        if (!empty($params['category_id'])) {
            $approvals = $approvals->join('order_items', function ($join) use ($params) {
                $join->on('approval_log.order_item_id', '=', 'order_items.id')
                    ->whereIn(
                        'order_items.item_id',
                        DB::table('items_categories')
                            ->where('cat_id', $params['category_id'])
                            ->pluck('item_id')
                            ->toArray()
                    );
            });
        }

        $approvals = $approvals->get();

        foreach ($approvals as $approval) {
            $rows[] = [
                'Date From' => (new Carbon($params['start_date']))->format('d/m/Y'),
                'Date To' => (new Carbon($params['end_date']))->format('d/m/Y'),
                'Doctor Name' => $approval->doctor->name,
                'Order Items Approved' => $approval->item_count,
            ];
        }

        return $rows;
    }

    public static function rejectedReport(array $params) : array
    {

        $rows = [];
        $columns = [
            'Download Date',
            'Date From',
            'Date To',
            'Order Number',
            'Item',
        ];

        $rows[] = $columns;

        // Exclude any orders that have not yet been processed
        $exclude_statuses = [
            Order::STATUS_ORDER_RECEIVED,
            Order::STATUS_PROCESSING,
            Order::STATUS_WAITING_FOR_RESPONSE,
            Order::STATUS_AWAITING_GP_CONFIRMATION,
            Order::STATUS_AWAITING_PRESCRIPTION_STATUS,
        ];

        $rejected_items = Order\Item::query()
            ->join('items', 'items.id', '=', 'order_items.item_id')
            ->join('order', function ($join) use ($exclude_statuses, $params) {
                $join->on('order.id', '=', 'order_items.order_id')
                    ->where('order.paid', '=', '1')
                    ->whereNotIn('order.status', $exclude_statuses)
                    ->where('order.date', '>=', $params['start_date']  . ' 00:00:00')
                    ->where('order.date', '<=', $params['end_date'] . ' 23:59:59');
            })
            ->where('approved', '=', '2');

        if (!empty($params['category_id'])) {
            $rejected_items = $rejected_items->whereIn(
                'order_items.item_id',
                DB::table('items_categories')
                    ->where('cat_id', $params['category_id'])
                    ->pluck('item_id')
                    ->toArray()
            );
        }

        $rejected_items = $rejected_items->get();

        foreach ($rejected_items as $rejected_item) {
            $rows[] = [
                'Download Date' => Carbon::now()->format('d/m/Y'),
                'Date From' => (new Carbon($params['start_date']))->format('d/m/Y'),
                'Date To' => (new Carbon($params['end_date']))->format('d/m/Y'),
                'Order Number' => $rejected_item->order_id,
                'Item' => $rejected_item->name . ' ' . $rejected_item->size,
            ];
        }

        return $rows;
    }


    private static function get_orders_query(array $params) : \Illuminate\Database\Query\Builder
    {
        $params['end_date'] .= ' 23:59:59';
        $item_statuses = ['Approved', 'Rejected', 'Awaiting'];

        $query_builder = DB::table('order AS o')
            ->join('order_items AS oi',    'oi.order_id', '=', 'o.id')
            ->join('order_statuses AS os', 'os.id',       '=', 'o.status')
            ->join('order_address AS oa', function($join) {
                $join
                    ->on('oa.order_id', '=', 'o.id')
                    ->on('oa.type',     '=', DB::raw('"billing"'));
            })
            ->leftJoin('order_refund_items AS ori', 'ori.order_item_id', '=', 'oi.id')
            ->leftJoin('order_refunds AS oro',      'oro.order_id',      '=', 'o.id')
            ->leftJoin('items AS i',                'i.id',              '=', 'oi.item_id')
            ->leftJoin('judopay_payments AS jp',    'jp.order_id',       '=', 'o.id')

            ->where('o.date', '>=', $params['start_date'])
            ->where('o.date', '<=', $params['end_date'])

            ->selectRaw('oi.order_id                                                 AS mtc__order_id')
            ->selectRaw('oi.id                                                       AS mtc__order_item_id')
            ->selectRaw('i.id                                                        AS mtc__item_id')
            ->selectRaw('IFNULL(o.magento_entity_id, "n/a")                          AS magento__order_entity_id')
            ->selectRaw('o.order_ref                                                 AS order_ref')
            ->selectRaw('oa.firstname                                                AS firstname')
            ->selectRaw('oa.lastname                                                 AS lastname')
            ->selectRaw('o.date                                                      AS order_date')
            ->selectRaw('o.paid                                                      AS order_is_paid')
            ->selectRaw('os.name                                                     AS order_status')
            ->selectRaw('IF (oi.approved = 1, ?, IF (oi.approved = 2, ?, ?))         AS item_approval_status', $item_statuses)
            ->selectRaw('oi.item_name                                                AS item_name')
            ->selectRaw('oi.PLU                                                      AS item_sku')
            ->selectRaw('FORMAT((oi.price_paid * 100) / (100 + IFNULL(i.vat_rate, 0)), 2)   AS items_cost')
            ->selectRaw('FORMAT(oi.price_paid, 2)                                    AS items_total_paid')
            ->selectRaw('FORMAT(IFNULL(ori.amount_refunded, 0), 2)                   AS items_amount_refunded')
            ->selectRaw('FORMAT(o.delivery_cost, 2)                                  AS order_delivery')
            ->selectRaw('FORMAT(IFNULL(oro.delivery_refund_amount, 0), 2)            AS order_delivery_refund')
            ->selectRaw('FORMAT(o.cost_total, 2)                                     AS order_total_paid')
            ->selectRaw('CONCAT("JUDO:", jp.receipt_id)                              AS payment_receipt_id')

            ->groupBy('oi.id')

            ->orderBy('o.date')
            ->orderBy('o.id')
        ;

        return $query_builder;
    }


    // One line per order item.
    public static function ordersReportDetailed(array $params) : array
    {
        $query = self::get_orders_query($params);

        $results = DB::query()->fromSub($query, 'sss')
            ->addSelect('mtc__order_id')
            ->addSelect('mtc__order_item_id')
            ->addSelect('mtc__item_id')
            ->addSelect('magento__order_entity_id')
            ->addSelect('order_ref')
            ->addSelect('payment_receipt_id')
            ->addSelect('firstname')
            ->addSelect('lastname')
            ->addSelect('order_date')
            ->addSelect('order_is_paid')
            ->addSelect('order_status')
            ->addSelect('item_approval_status')
            ->selectRaw('item_name')
            ->selectRaw('item_sku')
            ->selectRaw('items_cost')
            ->selectRaw('items_total_paid - items_cost AS items_vat')
            ->addSelect('items_total_paid')
            ->addSelect('order_delivery')
            ->addSelect('order_total_paid')
            ->addSelect('items_amount_refunded')
            ->addSelect('order_delivery_refund')
            ->get()
        ;

        $rows = [];
        if ($results) {
            $header_row = array_keys((array) json_decode(json_encode($results[0] ?? []), true));

            $rows[] = $header_row;
            foreach ($results as $result) {
                $rows[] = json_decode(json_encode($result), true);
            }
        }

        return $rows;
    }


    // One line per order.
    public static function ordersReportCombined(array $params) : array
    {
        $query = self::get_orders_query($params);

        $results = DB::query()->fromSub($query, 'sss')
            ->addSelect('mtc__order_id')
            ->addSelect('mtc__order_item_id')
            ->addSelect('magento__order_entity_id')
            ->addSelect('order_ref')
            ->addSelect('payment_receipt_id')
            ->addSelect('firstname')
            ->addSelect('lastname')
            ->addSelect('order_date')
            ->addSelect('order_is_paid')
            ->addSelect('order_status')
            ->selectRaw('GROUP_CONCAT(item_name) AS items_names')
            ->selectRaw('GROUP_CONCAT(item_sku) AS items_SKUs')
            ->selectRaw('FORMAT(SUM(items_cost), 2) AS items_cost')
            ->selectRaw('SUM(items_total_paid) - SUM(items_cost) AS items_vat')
            ->selectRaw('FORMAT(SUM(items_total_paid), 2) AS items_total_paid')
            ->addSelect('order_delivery')
            ->addSelect('order_total_paid')
            ->selectRaw('FORMAT(SUM(items_amount_refunded), 2) AS items_amount_refunded')
            ->addSelect('order_delivery_refund')

            ->groupBy('mtc__order_id')
            ->orderBy('order_date')
            ->orderBy('mtc__order_id')
            ->get()
        ;

        $rows = [];
        if ($results) {
            $header_row = array_keys((array) json_decode(json_encode($results[0] ?? []), true));

            $rows[] = $header_row;
            foreach ($results as $result) {
                $rows[] = json_decode(json_encode($result), true);
            }
        }

        return $rows;
    }


    public static function unio()
    {
        //$data = Member::query()
        //    ->with('addresses')
        //    ->with('nhs_member.doctor_surgery')
        //    ->with('referrer.nhs_member.doctor_surgery')
        //    ->whereIn('id', [4, 5, 6, 7, 8]) // 32 - refererred; 36 - self-referred
        //    ->get()
        //;

        //$data = Order::query()
        //    ->with('items.prescription_item')
        //    ->with('info')
        //    ->with('customer.nhs_member')
        //    ->with('delivery')
        //    ->with('address')
        //    ->with('notes')
        //    ->with('assessments')
        //    ->where('id', request()->input('id'))
        //    ->get()
        //;


        $data = Message::query()
            ->with('thread.order')
            ->with('thread.participants')
            ->with('member')
            ->with('admin_user')
            ->where('id', 5)
            ->first()
        ;

        //if ($data->admin) {
        //    $data->load('admin_user');
        //} else {
        //    $data->load('user');
        //}

        header('Content-type: text/json');
        //header('Content-Disposition: attachment; filename=example_customers.json' );

        echo json_encode($data, JSON_PRETTY_PRINT | JSON_UNESCAPED_SLASHES);
        exit;
    }

}
