<?php

/**
 * DocumentSegmentModel class file
 *
 * PHP Version 7.2
 *
 * @category Model
 * @package  Ez
 * @author   Imenso Software <admin@imensosoftware.com>
 * @license  http://imensosoftware/license.php GNU Public License
 * @link     http://imensosoftware.com/recipes
 */

namespace App\Models;

use DB;
use Illuminate\Database\Eloquent\Model;
use App\Models\Document;
use Storage;

/**
 * DocumentSegmentModel class
 *
 * The class manage Document Segment related queries
 *
 * @category DocumentSegmentModel
 * @package  Ez
 * @author   Imenso Software <admin@imensosoftware.com>
 * @license  http://imensosoftware/license.php GNU Public License
 * @link     http://imensosoftware.com/recipes
 */

class DocumentSegment extends Model
{
    public $table = 'document_segments';
    
    public $fillable = [
        'document_id',
        'segment_id',
        'document_part_id',
        'from_index',
        'to_index',
        'no_of_index',
        'no_of_words',
        'no_of_column',
        'no_of_row',
        'document_sheet_id',
        'is_skip'
    ];

    public $timestamps = false;

    /**
     * GetDocumentSegments function gets the segment list of specified document
     * and return the document segment list.
     *
     * @param  array request
     * @return \App\Models\DocumentSegment
     */
    public static function getDocumentSegments($request)
    {
        $documentId = $request->document_id ;
        $documentSegments = self::
            leftJoin('document_parts', 'document_parts.id', '=', 'document_segments.document_part_id')
            ->leftJoin('document_sheets', 'document_sheets.id', '=', 'document_segments.document_sheet_id')
            ->select(
                'document_segments.*',
                'document_parts.part_id',
                'document_sheets.sheet_id',
                'document_sheets.split_type_id',
                DB::raw("CONCAT('Part ',document_parts.part_id) as part_name"),
                DB::raw(
                    "(SELECT if(SUM(x.no_of_index) ,SUM(x.no_of_index) ,0 ) FROM
                    document_segments x WHERE x.document_id = $documentId and x.segment_id <
                    document_segments.segment_id) AS from_index ,
                    (SELECT SUM(y.no_of_index)-1 FROM  document_segments y WHERE  y.document_id = 
                    $documentId and y.segment_id <= document_segments.segment_id) AS to_index"
                )
            );
        
        if ($request->document_id) {
            $documentSegments = $documentSegments->where(
                'document_segments.document_id',
                $request->document_id
            );
        }
        $documentSegments = $documentSegments->orderBy('document_segments.segment_id', 'ASC')
            ->groupBy('document_segments.id')->get();
        return $documentSegments;
    }
    //  end getDocumentSegments()

    /**
     * getDocumentSegmentFromToIndex function gets the from to to index in segment of specified document
     * and return the document segment from to to index.
     *
     * @param  array $documentId
     * @return \App\Models\DocumentSegment
     */
    public static function getDocumentSegmentFromToIndex($documentId)
    {
        return  DocumentSegment::
            select(
                "document_segments.id",
                "document_segments.segment_id",
                "document_segments.document_part_id",
                "document_parts.part_id",
                DB::raw(
                    "(SELECT if(SUM(x.no_of_index) ,SUM(x.no_of_index) ,0 ) FROM
                    document_segments x WHERE x.document_id = $documentId and x.segment_id <
                    document_segments.segment_id) AS from_index ,
                    (SELECT SUM(y.no_of_index)-1 FROM  document_segments y WHERE  y.document_id = 
                    $documentId and y.segment_id <= document_segments.segment_id) AS to_index"
                )
            )
            ->leftJoin('document_parts', 'document_parts.id', '=', 'document_segments.document_part_id')
            ->where('document_segments.document_id', $documentId)
            ->orderBy('document_segments.segment_id', 'ASC')->get();
    }
    //  end getDocumentSegmentFromToIndex()

    /**
     * updateWordCount function update word count of specified document segment
     * and return void.
     *
     * @param  $documentId
     * @return void
     */
    public static function updateWordCount($documentId)
    {
        $segmentFromToIndexs = self::getDocumentSegmentFromToIndex($documentId);
        $document = Document::find($documentId);

        $jsonString = Storage::disk('s3')->get($document->json_path);

        //$jsonString = file_get_contents(public_path("docs" . $document->json_path));
        $jsonObj = json_decode($jsonString, true);
        foreach ($segmentFromToIndexs as $segmentFromToIndex) {
            $noOfWords = 0 ;
            for ($i = $segmentFromToIndex->from_index; $i <= $segmentFromToIndex->to_index; $i++) {
                if (isset($jsonObj[$i])) {
                    $noOfWords = $noOfWords + $jsonObj[$i]['index_wc'];
                }
            }
            DocumentSegment::where('id', $segmentFromToIndex->id)->update(['no_of_words' => $noOfWords]);
        }
    }
    //  end updateWordCount()

    /**
     * getDocumentSegmentFromToIndex function gets the from to to index in segment of specified document
     * and return the document segment from to to index.
     *
     * @param  array $documentId
     * @return \App\Models\DocumentSegment
     */
    public static function getDocumentSegmentExcelFromToIndex($documentSheetId)
    {
        $document = DocumentSegment::where('document_sheet_id', $documentSheetId)->first();
        $documentId = $document->id;
        return  DocumentSegment::
            select(
                "document_segments.id",
                DB::raw(
                    "
                    (SELECT if(SUM(x.no_of_index) ,SUM(x.no_of_index) ,0 ) FROM  document_segments
                    x WHERE x.document_sheet_id = $documentSheetId and x.segment_id < 
                    document_segments.segment_id ) AS from_index ,
                    (SELECT SUM(y.no_of_index)-1 FROM  document_segments y WHERE 
                    y.document_sheet_id = $documentSheetId and y.segment_id <= 
                    document_segments.segment_id ) AS to_index,
                    (SELECT if(SUM(x.no_of_column) ,SUM(x.no_of_column) ,0 ) FROM
                    document_segments x WHERE x.document_sheet_id = $documentSheetId and 
                    x.segment_id < document_segments.segment_id ) AS from_column ,
                    (SELECT SUM(y.no_of_column)-1 FROM  document_segments y WHERE  
                    y.document_sheet_id = $documentSheetId and y.segment_id <=
                    document_segments.segment_id ) AS to_column,
                    (SELECT if(SUM(x.no_of_row) ,SUM(x.no_of_row) ,0 ) FROM  document_segments x
                    WHERE x.document_sheet_id = $documentSheetId and x.segment_id < 
                    document_segments.segment_id ) AS from_row ,
                    (SELECT SUM(y.no_of_row)-1 FROM  document_segments y WHERE  
                    y.document_sheet_id = $documentSheetId and y.segment_id <= 
                    document_segments.segment_id ) AS to_row"
                )
            )
            ->where('document_segments.document_sheet_id', $documentSheetId)
            ->orderBy('document_segments.segment_id', 'ASC')->get();
    }
    //  end getDocumentSegmentFromToIndex()

    /**
     * updateWordCount function update word count of specified document segment
     * and return void.
     *
     * @param  $documentId
     * @return void
     */
    public static function updateExcelWordCount($documentSheetId)
    {
        $segmentFromToIndexs = self::getDocumentSegmentExcelFromToIndex($documentSheetId);
        $documentSheet = \App\Models\DocumentSheet::find($documentSheetId);
        $document = Document::find($documentSheet->document_id);
        $jsonString = Storage::disk('s3')->get($document->json_path);
        $sheetId = $documentSheet->sheet_id - 1  ;
        if ($documentSheet->split_type_id == 1) 
        {
            $jsonObj = json_decode($jsonString, true);
            $jsonObjKeys = array_keys($jsonObj);
            $array =  $jsonObj[$jsonObjKeys[$sheetId]]['column_word_counts'];
            DocumentSegment::where('document_sheet_id', $documentSheet->id)
            ->update(['no_of_words' => array_sum($array)]);
        } 
        elseif ($documentSheet->split_type_id == 2) 
        {
            $jsonObj = json_decode($jsonString, true);
            $jsonObjKeys = array_keys($jsonObj);
            $array =  $jsonObj[$jsonObjKeys[$sheetId]]['column_word_counts'];
            $keys = array_keys($jsonObj[$jsonObjKeys[$sheetId]]['column_word_counts']);
            foreach ($segmentFromToIndexs as $segmentFromToIndex) 
            {
                $noOfWords = 0 ;
                for ($i = $segmentFromToIndex->from_column; $i <= $segmentFromToIndex->to_column; $i++) 
                {
                    $noOfWords = $noOfWords + $array[$keys[$i]];
                }
                DocumentSegment::where('id', $segmentFromToIndex->id)->update(['no_of_words' => $noOfWords]);
            }
        } 
        elseif ($documentSheet->split_type_id == 3) 
        {
            $jsonObj = json_decode($jsonString, true);
            $jsonObjKeys = array_keys($jsonObj);
            $array =  $jsonObj[$jsonObjKeys[$sheetId]]['row_word_count'];
            foreach ($segmentFromToIndexs as $segmentFromToIndex) {
                $noOfWords = 0 ;
                for ($i = ($segmentFromToIndex->from_row + 1); $i <= $segmentFromToIndex->to_row; $i++) {
                    $noOfWords = $noOfWords + $array[$i];
                }
                DocumentSegment::where('id', $segmentFromToIndex->id)->update(['no_of_words' => $noOfWords]);
            }
        }
    }
    //  end updateWordCount()
}
//  end class