<?php

namespace COREPOS\pos\install\db;
use COREPOS\pos\lib\CoreState;

class Creator
{
    private static $op_models = array(
        '\\COREPOS\\pos\\lib\\models\\op\\AutoCouponsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\CouponAltsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\CouponCodesModel',
        '\\COREPOS\\pos\\lib\\models\\op\\CustdataModel',
        '\\COREPOS\\pos\\lib\\models\\op\\CustomerNotificationsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\CustPreferencesModel',
        '\\COREPOS\\pos\\lib\\models\\op\\CustReceiptMessageModel',
        '\\COREPOS\\pos\\lib\\models\\op\\CustomReceiptModel',
        '\\COREPOS\\pos\\lib\\models\\op\\DateRestrictModel',
        '\\COREPOS\\pos\\lib\\models\\op\\DepartmentsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\DisableCouponModel',
        '\\COREPOS\\pos\\lib\\models\\op\\DrawerOwnerModel',
        '\\COREPOS\\pos\\lib\\models\\op\\EmployeesModel',
        '\\COREPOS\\pos\\lib\\models\\op\\GlobalValuesModel',
        '\\COREPOS\\pos\\lib\\models\\op\\HouseCouponsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\HouseCouponItemsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\HouseVirtualCouponsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\IgnoredBarcodesModel',
        '\\COREPOS\\pos\\lib\\models\\op\\MasterSuperDeptsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\MemberCardsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\MemtypeModel',
        '\\COREPOS\\pos\\lib\\models\\op\\ParametersModel',
        '\\COREPOS\\pos\\lib\\models\\op\\ProductsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\ShrinkReasonsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\SpecialDeptMapModel',
        '\\COREPOS\\pos\\lib\\models\\op\\SubDeptsModel',
        '\\COREPOS\\pos\\lib\\models\\op\\TendersModel',
        '\\COREPOS\\pos\\lib\\models\\op\\UnpaidArTodayModel',
        // depends on custdata
        '\\COREPOS\\pos\\lib\\models\\op\\MemberCardsViewModel',
    );

    /**
      Create opdata tables and views
      @param $db [SQLManager] database connection
      @param $name [string] database name
      @return [array] of error messages
    */
    public static function createOpDBs($db, $name)
    {
        $errors = array();
        if (\CoreLocal::get('laneno') == 0) {
            $errors[] = array(
                'struct' => _('No structures created for lane #0'),
                'query' => _('None'),
                'details' => _('Zero is reserved for server'),
            );

            return $errors;
        }

        foreach (self::$op_models as $class) {
            $obj = new $class($db);
            $errors[] = $obj->createIfNeeded($name);
        }
        
        $sample_data = array(
            'couponcodes',
            'customReceipt',
            'globalvalues',
            'parameters',
            'tenders',
        );

        foreach ($sample_data as $table) {
            $chk = $db->query('SELECT * FROM ' . $table, $name);
            if ($db->numRows($chk) === 0) {
                $loaded = \COREPOS\pos\install\data\Loader::loadSampleData($db, $table, true);
                if (!$loaded) {
                    $errors[] = array(
                        'struct' => $table,
                        'query' => _('None'),
                        'error' => _('Failed loading sample data'),
                        'details' => _('Failed loading sample data'),
                    );
                }
            } else {
                $db->endQuery($chk);
            }
        }

        $chk = $db->query('SELECT drawer_no FROM drawerowner', $name);
        if ($db->num_rows($chk) == 0){
            $db->query('INSERT INTO drawerowner (drawer_no) VALUES (1)', $name);
            $db->query('INSERT INTO drawerowner (drawer_no) VALUES (2)', $name);
        }

        CoreState::loadParams();
        
        return $errors;
    }

    private static $trans_models = array(
        '\\COREPOS\\pos\\lib\\models\\trans\\DTransactionsModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\LocalTransModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\LocalTransArchiveModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\LocalTransTodayModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\LocalTempTransModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\SuspendedModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\TaxRatesModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\CouponAppliedModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\PaycardTransactionsModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\CapturedSignatureModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\EmvReceiptModel',
        // placeholder,
        '__LTT__',
        // Views
        '\\COREPOS\\pos\\lib\\models\\trans\\MemDiscountAddModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\MemDiscountRemoveModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\StaffDiscountAddModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\StaffDiscountRemoveModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\ScreenDisplayModel',
        '\\COREPOS\\pos\\lib\\models\\trans\\TaxViewModel',
    );

    /**
      Create translog tables and views
      @param $db [SQLManager] database connection
      @param $name [string] database name
      @return [array] of error messages
    */
    public static function createTransDBs($db, $name)
    {
        $errors = array();
        $type = $db->dbmsName();

        if (\CoreLocal::get('laneno') == 0) {
            $errors[] = array(
                'struct' => _('No structures created for lane #0'),
                'query' => _('None'),
                'details' => _('Zero is reserved for server'),
            );

            return $errors;
        }

        /* lttsummary, lttsubtotals, and subtotals
         * always get rebuilt to account for tax rate
         * changes */
        if (!function_exists('buildLTTViews')) {
            include(__DIR__ . '/../buildLTTViews.php');
        }

        foreach (self::$trans_models as $class) {
            if ($class == '__LTT__') {
                $errors = buildLTTViews($db,$type,$errors);
                continue;
            }
            $obj = new $class($db);
            $errors[] = $obj->createIfNeeded($name);
        }
        
        /**
          Not using models for receipt views. Hopefully many of these
          can go away as deprecated.
        */
        $lttR = "CREATE view rp_ltt_receipt as 
            select
            l.description as description,
            case 
                when voided = 5 
                    then 'Discount'
                when trans_status = 'M'
                    then 'Mbr special'
                when trans_status = 'S'
                    then 'Staff special'
                when unitPrice = 0.01
                    then ''
                when scale <> 0 and quantity <> 0 
                    then ".$db->concat('quantity', "' @ '", 'unitPrice','')."
                when abs(itemQtty) > 1 and abs(itemQtty) > abs(quantity) and discounttype <> 3 and quantity = 1
                    then ".$db->concat('volume', "' / '", 'unitPrice','')."
                when abs(itemQtty) > 1 and abs(itemQtty) > abs(quantity) and discounttype <> 3 and quantity <> 1
                    then ".$db->concat('quantity', "' @ '", 'volume', "' /'", 'unitPrice','')."
                when abs(itemQtty) > 1 and discounttype = 3
                    then ".$db->concat('ItemQtty', "' / '", 'unitPrice','')."
                when abs(itemQtty) > 1
                    then ".$db->concat('quantity', "' @ '", 'unitPrice','')."
                when matched > 0
                    then '1 w/ vol adj'
                else ''
            end
            as comment,
            total,
            case 
                when trans_status = 'V' 
                    then 'VD'
                when trans_status = 'R'
                    then 'RF'
                when tax = 1 and foodstamp <> 0
                    then 'TF'
                when tax = 1 and foodstamp = 0
                    then 'T' 
                when tax = 0 and foodstamp <> 0
                    then 'F'
                WHEN (tax > 1 and foodstamp <> 0)
                    THEN ".$db->concat('SUBSTR(t.description,1,1)',"'F'",'')."
                WHEN (tax > 1 and foodstamp = 0)
                    THEN SUBSTR(t.description,1,1)
                when tax = 0 and foodstamp = 0
                    then '' 
            end
            as Status,
            trans_type,
            unitPrice,
            voided,
            CASE 
                WHEN upc = 'DISCOUNT' THEN (
                SELECT MAX(trans_id) FROM localtemptrans WHERE voided=3
                )-1
                WHEN trans_type = 'T' THEN trans_id+99999    
                ELSE trans_id
            END AS trans_id,
            l.emp_no,
            l.register_no,
            l.trans_no
            from localtranstoday as l
            left join taxrates as t
            on l.tax = t.id
            where voided <> 5 and UPC <> 'TAX'
            AND trans_type <> 'L'";
        self::dbStructureModify($db,'rp_ltt_receipt','DROP VIEW rp_ltt_receipt',$errors);
        if(!$db->tableExists('rp_ltt_receipt',$name)){
            self::dbStructureModify($db,'rp_ltt_receipt',$lttR,$errors);
        }

        $receiptV = "CREATE VIEW rp_receipt AS
            select
            case 
                when trans_type = 'T'
                    then     ".$db->concat( "SUBSTR(".$db->concat('UPPER(TRIM(description))',$db->space(44),'').", 1, 44)" 
                        , "right(".$db->concat( $db->space(8), $db->numberFormat('-1 * total'),'').", 8)" 
                        , "right(".$db->concat( $db->space(4), 'status','').", 4)",'')."
                when voided = 3 
                    then     ".$db->concat("SUBSTR(".$db->concat('description', $db->space(30),'').", 1, 30)"
                        , $db->space(9)
                        , "'TOTAL'"
                        , 'right('.$db->concat( $db->space(8), $db->numberFormat('unitPrice'),'').', 8)','')."
                when voided = 2
                    then     description
                when voided = 4
                    then     description
                when voided = 6
                    then     description
                when voided = 7 or voided = 17
                    then     ".$db->concat("SUBSTR(".$db->concat('description', $db->space(30),'').", 1, 30)"
                        , $db->space(14)
                        , 'right('.$db->concat( $db->space(8), $db->numberFormat('unitPrice'),'').', 8)'
                        , 'right('.$db->concat( $db->space(4), 'status','').', 4)','')."
                else
                    ".$db->concat("SUBSTR(".$db->concat('description', $db->space(30),'').", 1, 30)"
                    , "' '" 
                    , "SUBSTR(".$db->concat('comment', $db->space(13),'').", 1, 13)"
                    , 'right('.$db->concat($db->space(8), $db->numberFormat('total'),'').', 8)'
                    , 'right('.$db->concat($db->space(4), 'status','').', 4)','')."
            end
            as linetoprint,
            emp_no,
            register_no,
            trans_no,
            trans_id
            from rp_ltt_receipt
            order by trans_id";
        if ($type == 'mssql') {
            $receiptV = "CREATE  VIEW rp_receipt AS
            select top 100 percent
            case 
                when trans_type = 'T'
                    then     right((space(44) + upper(rtrim(Description))), 44) 
                        + right((space(8) + convert(varchar, (-1 * Total))), 8) 
                        + right((space(4) + status), 4)
                when voided = 3 
                    then     left(Description + space(30), 30) 
                        + space(9) 
                        + 'TOTAL' 
                        + right(space(8) + convert(varchar, UnitPrice), 8)
                when voided = 2
                    then     description
                when voided = 4
                    then     description
                when voided = 6
                    then     description
                when voided = 7 or voided = 17
                    then     left(Description + space(30), 30) 
                        + space(14) 
                        + right(space(8) + convert(varchar, UnitPrice), 8) 
                        + right(space(4) + status, 4)
                when sequence < 1000
                    then     description
                else
                    left(Description + space(30), 30)
                    + ' ' 
                    + left(Comment + space(13), 13) 
                    + right(space(8) + convert(varchar, Total), 8) 
                    + right(space(4) + status, 4)
            end
            as linetoprint,
            sequence,
            emp_no,
            register_no,
            trans_no,
            trans_id
            from rp_ltt_receipt
            order by sequence";
        } elseif($type == 'pdolite'){
            $receiptV = str_replace('right(','str_right(',$receiptV);
            $receiptV = str_replace('FORMAT(','ROUND(',$receiptV);
        }

        self::dbStructureModify($db,'rp_receipt','DROP VIEW rp_receipt',$errors);
        if(!$db->tableExists('rp_receipt',$name)){
            self::dbStructureModify($db,'rp_receipt',$receiptV,$errors);
        }

        return $errors;
    }

    public static function createMinServer($db, $name)
    {
        $errors = array();
        $type = $db->dbmsName();
        if (\CoreLocal::get('laneno') == 0) {
            $errors[] = array(
                'struct' => _('No structures created for lane #0'),
                'query' => _('None'),
                'details' => _('Zero is reserved for server'),
            );

            return $errors;
        }

        $models = array(
            '\COREPOS\pos\lib\models\trans\DTransactionsModel',
            '\COREPOS\pos\lib\models\trans\SuspendedModel',
            '\COREPOS\pos\lib\models\trans\PaycardTransactionsModel',
            '\COREPOS\pos\lib\models\trans\CapturedSignatureModel',
        );
        foreach ($models as $class) {
            $obj = new $class($db);
            $errors[] = $obj->createIfNeeded($name);
        }

        $errors = self::createDlog($db, $name, $errors);

        return $errors;
    }

    private static function createDlog($db, $name, $errors)
    {
        $dlogQ = "CREATE VIEW dlog AS
            SELECT datetime AS tdate,
                register_no,
                emp_no,
                trans_no,
                upc,
                description,
                CASE 
                    WHEN trans_subtype IN ('CP','IC') OR upc LIKE '%000000052' THEN 'T' 
                    WHEN upc = 'DISCOUNT' THEN 'S' 
                    ELSE trans_type 
                END AS trans_type,
                CASE 
                    WHEN upc = 'MAD Coupon' THEN 'MA' 
                    WHEN upc LIKE '%00000000052' THEN 'RR' 
                    ELSE trans_subtype 
                END AS trans_subtype,
                trans_status,
                department,
                quantity,
                unitPrice,
                total,
                tax,
                foodstamp,
                ItemQtty,
                voided,
                memType,
                staff,
                numflag,
                charflag,
                card_no,
                trans_id, "
                . $db->concat(
                    $db->convert('emp_no','char'),"'-'",
                    $db->convert('register_no','char'),"'-'",
                    $db->convert('trans_no','char'),
                    '') . " AS trans_num
            FROM dtransactions
            WHERE trans_status NOT IN ('D','X','Z')
                AND emp_no <> 9999 
                AND register_no <> 99";
        if (!$db->table_exists("dlog",$name)) {
            $errors = self::dbStructureModify($db,'dlog',$dlogQ,$errors);
        }

        return $errors;
    }

    public static function dbStructureModify($sql, $struct_name, $queries, &$errors=array())
    {
        if (!is_array($queries)) {
            $queries = array($queries);
        }

        $error = array(
            'struct' => $struct_name,
            'error' => 0,
            'query' => '',
            'details' => '',
        );
        foreach ($queries as $query) {
            $try = $sql->query($query);
            if ($try === false){
                $error['query'] .= $query . '; ';
                // failing to drop a view is fine
                if (!(stristr($query, "DROP ") && stristr($query,"VIEW "))) {
                    $error['error'] = 1;
                    $error['details'] = $sql->error() . '; ';
                    $error['error_msg'] = $error['details'];
                    $error['important'] = true;
                }
            }
        }
        $errors[] = $error;

        return $errors;
    }

}

