<?php

namespace Hilco\Models;

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

/**
 * M3 price lists table with quantity breaks
 * @property-read string $PRRF
 * @property-read string $SAPR
 * @property-read string $NTCD
 */
class OPRICL extends Model implements PriceIngestModel {
    protected $table = "OPRICL";

    /**
     * @return mixed
     */
    public function getPriceList() {
        return $this->PRRF;
    }

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

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

    /**
     * @param $priceListDesc , e.g. "US_CATALOG" (CustomerSegments.def_pricelist_desc)
     * @param $custNo, e.g., "70727975" (Customers.cust_no) OR ""
     * @param $currency , e.g., "USD" (Customers.currency)
     * @param $itemNo , e.g. "1097264" (Parts.part_no, WebParts.part_number, etc)
     * @param $quantity , e.g., 1
     * @return OPRICL|null
     */
    public static function findPriceRecordForItem ($priceListDesc, $custNo, $currency, $itemNo, $quantity): ?OPRICL {
        return
            OPRICL::where('PRRF', $priceListDesc)
                    ->where('CUNO', $custNo)
                    ->where('CUCD', $currency)
                    ->where('ITNO', $itemNo)
                    ->where('QTYL', '<=', $quantity)
                    ->where('FVDT', '<=', DB::raw("DATE_FORMAT(CURDATE(), '%Y%m%d')"))
                    ->where('VFDT', '<=', DB::raw("DATE_FORMAT(CURDATE(), '%Y%m%d')"))
                    ->where("LVDT", ">=", DB::raw("DATE_FORMAT(CURDATE(), '%Y%m%d')"))
                    ->orderBy('QTYL', 'DESC')
                    ->limit(1)
                    ->first();
    }

    /**
     * @return string
     */
    public static function getTemporaryTableCreateSQL(): string {
        return <<<SQLEND
CREATE TEMPORARY TABLE `m3`.`OPRICL_staging` (
  `CONO` varchar(3) NOT NULL,
  `DIVI` varchar(3) NOT NULL,
  `PRRF` varchar(10) NOT NULL,
  `CMNO` varchar(8) NOT NULL,
  `CUCD` varchar(3) NOT NULL,
  `CUNO` varchar(8) NOT NULL,
  `FVDT` varchar(8) NOT NULL,
  `ITNO` varchar(10) NOT NULL,
  `QTYL` int(11) NOT NULL,
  `QTLB` varchar(12) NOT NULL,
  `SGGU` varchar(3) NOT NULL,
  `SAPR` varchar(13) NOT NULL,
  `SACD` varchar(4) NOT NULL,
  `SPUN` varchar(3) NOT NULL,
  `PCFS` varchar(11) NOT NULL,
  `COFS` varchar(14) NOT NULL,
  `DMCS` varchar(1) NOT NULL,
  `NTCD` varchar(1) NOT NULL,
  `BNCD` varchar(1) NOT NULL,
  `PRAC` varchar(1) NOT NULL,
  `TINC` varchar(1) NOT NULL,
  `VTCD` varchar(1) NOT NULL,
  `PCOF` varchar(14) NOT NULL,
  `PRIX` varchar(1) NOT NULL,
  `SCMO` varchar(5) NOT NULL,
  `LVDT` varchar(8) NOT NULL,
  `MLIN` varchar(1) NOT NULL,
  `FMID` varchar(8) NOT NULL,
  `RESI` 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,
  `OBV1` varchar(8) NOT NULL,
  `OBV2` varchar(8) NOT NULL,
  `OBV3` varchar(8) NOT NULL,
  `OBV4` varchar(8) NOT NULL,
  `OBV5` varchar(8) NOT NULL,
  `VFDT` varchar(8) NOT NULL,
  `MIGI` varchar(8) NOT NULL,
  `PRLP` varchar(1) 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',
            'DIVI',
            'PRRF',
            'CMNO',
            'CUCD',
            'CUNO',
            'FVDT',
            'ITNO',
            'QTYL',
            'QTLB',
            'SGGU',
            'SAPR',
            'SACD',
            'SPUN',
            'PCFS',
            'COFS',
            'DMCS',
            'NTCD',
            'BNCD',
            'PRAC',
            'TINC',
            'VTCD',
            'PCOF',
            'PRIX',
            'SCMO',
            'LVDT',
            'MLIN',
            'FMID',
            'RESI',
            'RGDT',
            'RGTM',
            'LMDT',
            'CHNO',
            'CHID',
            'LMTS',
            'OBV1',
            'OBV2',
            'OBV3',
            'OBV4',
            'OBV5',
            'VFDT',
            'MIGI',
            'PRLP',
            'accountingEntity',
            'deleted',
            'timestamp',
            'variationNumber',
        ];
    }
}