<?php

namespace Hilco\Models;

use DB;
use Illuminate\Database\Eloquent\Collection;
use Illuminate\Database\Eloquent\Model;

/**
 * M3 discounts matrix
 * @property-read string $DISP
 */
class OGDMTX extends Model implements PriceIngestModel {
    protected $table = "OGDMTX";

    /**
     * @return string
     */
    public function getDiscountPercentage() {
        return $this->DISP;
    }

    /**
     * @param $discModel , e.g., "DISC_USD" (CustomerSegments.discount_model)
     * @param $discBucketNo , e.g., 4 (OGDIPO.DIPO)
     * @param $priorityLevel , e.g., 7 (the 3rd character in the 4-character PC** column names in OGDIPO -- "PC73" would be the 3rd rule in priority level 7)
     * @param $rule1Val
     * @param $rule2Val
     * @param $rule3Val
     * @param $rule4Val
     * @param $rule5Val
     * @param int $quantity
     * @return OGDMTX|null
     */
    public static function findDiscountRecord ($discModel, $discBucketNo, $priorityLevel, $rule1Val, $rule2Val, $rule3Val, $rule4Val, $rule5Val, int $quantity = 1): ?OGDMTX {
        return
            OGDMTX::where('DISY', "$discModel")
                    ->where('DIPO', "$discBucketNo")
                    ->where('PREX', "$priorityLevel")
                    ->where("OBV1", "$rule1Val")
                    ->where("OBV2", "$rule2Val")
                    ->where("OBV3", "$rule3Val")
                    ->where("OBV4", "$rule4Val")
                    ->where("OBV5", "$rule5Val")
                    ->where('LIMT', '<=', $quantity)
                    ->where('FVDT', '<=', DB::raw("DATE_FORMAT(CURDATE(), '%Y%m%d')"))
                    ->orderBy('LIMT', 'DESC')
                    ->limit(1)
                    ->first();
    }

    /**
     * @return string
     */
    public static function getTemporaryTableCreateSQL(): string {
        return <<<SQLEND
CREATE TEMPORARY TABLE `m3`.`OGDMTX_staging` (
  `CONO` varchar(3) NOT NULL,
  `DISY` varchar(10) NOT NULL,
  `DIPO` varchar(1) NOT NULL,
  `FVDT` varchar(8) NOT NULL,
  `PREX` varchar(1) NOT NULL,
  `GENE` varchar(1) NOT NULL,
  `OBV1` varchar(8) NOT NULL,
  `OBV2` varchar(8) NOT NULL,
  `OBV3` varchar(8) NOT NULL,
  `LIMT` int(11) NOT NULL,
  `DITP` varchar(1) NOT NULL,
  `DISP` varchar(6) NOT NULL,
  `DIAM` varchar(6) NOT NULL,
  `CMPN` varchar(1) NOT NULL,
  `ITNO` varchar(10) NOT NULL,
  `ORQT` varchar(8) NOT NULL,
  `SAPR` varchar(8) NOT NULL,
  `FT01` varchar(8) NOT NULL,
  `FT02` varchar(8) NOT NULL,
  `FT03` varchar(8) NOT NULL,
  `RGDT` varchar(8) NOT NULL,
  `RGTM` varchar(6) NOT NULL,
  `LMDT` varchar(8) NOT NULL,
  `CHNO` varchar(3) NOT NULL,
  `CHID` varchar(8) NOT NULL,
  `LMTS` varchar(32) NOT NULL,
  `TEPY` varchar(8) NOT NULL,
  `OBV4` varchar(8) NOT NULL,
  `OBV5` varchar(8) NOT NULL,
  `accountingEntity` varchar(3) NOT NULL,
  `deleted` varchar(5) NOT NULL,
  `timestamp` varchar(32) NOT NULL,
  `variationNumber` varchar(16) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
SQLEND;
    }

    /**
     * @return array
     */
    public static function getInsertableColumns(): array {
        return [
            'CONO',
            'DISY',
            'DIPO',
            'FVDT',
            'PREX',
            'GENE',
            'OBV1',
            'OBV2',
            'OBV3',
            'LIMT',
            'DITP',
            'DISP',
            'DIAM',
            'CMPN',
            'ITNO',
            'ORQT',
            'SAPR',
            'FT01',
            'FT02',
            'FT03',
            'RGDT',
            'RGTM',
            'LMDT',
            'CHNO',
            'CHID',
            'LMTS',
            'TEPY',
            'OBV4',
            'OBV5',
            'accountingEntity',
            'deleted',
            'timestamp',
            'variationNumber',
        ];
    }
}