<?php
ini_set("display_errors", 1);

require_once '../../../../resources/base_path_include.php';

require_once $base_path_to_resources . "includes/config.php";
require_once $base_path_to_resources . 'lib/function.php';
require_once $base_path_to_resources . "lib/currency_functions.php";
require_once $base_path . 'user_management/authenticate.php';
require_once $base_path_to_resources . 'lib/validate_api_signature.php';

//spreadsheet function
require_once $base_path . "vendor/autoload.php";

//creating dynamic db
require_once $base_path_to_resources . 'includes/specific_db_config.php';

$postdata = json_decode(file_get_contents("php://input"), true);

if (
    !isset($postdata['company_id']) or
    !isset($postdata['branch_id']) or
    !isset($postdata['book_id']) or
    !isset($postdata['api_signature']) or
    !isset($postdata['function_type']) or
    !isset($postdata['jwt_token'])
) {
    errorreturn("All fields are required.");
}
//authenticate jwt token
validateApiSignature($postdata['api_signature']);
$jwttoken = $postdata['jwt_token'];
$jwt_validate = authenticate_jwt($jwttoken);
//then workspace_id is decoded in data of jwt use from there
$userid = $jwt_validate->data->id;
$workspace_id = $jwt_validate->data->workspace_id;
$role = $jwt_validate->data->role;
$created_by = $jwt_validate->data->username;

use \PhpOffice\PhpSpreadsheet\Spreadsheet;
use \PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Font;
use PhpOffice\PhpSpreadsheet\Style\Color;


if ($postdata['function_type']  == "download_stocks") {
    // Create a new spreadsheet
    $spreadsheet = new Spreadsheet();

    // Select the active sheet
    $worksheet = $spreadsheet->getActiveSheet();
    // Set the column width for columns A to G
    for ($column = 'A'; $column <= 'L'; $column++) {
        $worksheet->getColumnDimension($column)->setWidth(20); // Set the width as desired (15 in this example)
    }

    $company_id = $postdata['company_id'];
    $branch_id = $postdata['branch_id'];
    $book_id = $postdata['book_id'];

    $function_code = $postdata['function_type'];
    $confirmed_role_user = validatePrimaryAccess($conn, $workspace_id, $role, $userid, ['superadmin', 'admin', null]);
    if (!$confirmed_role_user) {
        errorreturn('Access denied.');
    } else {
        $specific_db_conn = dynamicDbConnection($confirmed_role_user);
        if ($role == null) {
            checkCompanyBranchFunctionAccess($specific_db_conn, $role, $userid, $workspace_id, $company_id, $branch_id, $book_id,  $function_code);
        }
    }


    $res = $specific_db_conn->prepare("SELECT * FROM act_books WHERE id = ? and workspace_id =? and company_id = ? and branch_id =?");
    $res->execute(array($book_id, $workspace_id, $company_id, $branch_id));
    $result1 = $res->fetchAll();
    if (count($result1) == 1) {

        $book_name = $result1[0]['name'];
        $book_opening_date = strtotime($result1[0]['book_opening_date']);
        $book_closing_date = strtotime($result1[0]['book_closing_date']);
    }
    $opening_date = date('j-M-y', $book_opening_date);
    $closing_date = date('j-M-y', $book_closing_date);

    // Add headers to the Excel sheet
    $worksheet->mergeCells('A1:M1');
    $worksheet->setCellValue('A1', 'Stock Import Rejoice Finware');

    // Get the style of the cell and configure it to be centered and bold
    $style = $worksheet->getStyle('A1');
    $style->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);
    $style->getFont()->setSize(12);

    $worksheet->mergeCells('A3:M3');
    $worksheet->setCellValue('A3', $book_name);
    $style->getFont()->setSize(12);
    // Get the style of the cell and configure it to be centered and bold
    $style = $worksheet->getStyle('A3');
    $style->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);

    $worksheet->mergeCells('A4:M4');
    $worksheet->setCellValue('A4', $opening_date  . ' to ' .  $closing_date);
    // Get the style of the cell and configure it to be centered and bold
    $style = $worksheet->getStyle('A4');
    $style->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);

    // Merge cells A6 to A7
    $worksheet->setCellValue('A6', 'Stock Name');

    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('A6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

    $worksheet->setCellValue('B6', 'Group Name');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('B6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    // Create a list of options for the dropdown
    //$dropdownOptions = ['Option 1', 'Option 2', 'Option 3'];
    $sql1 = "SELECT group_name FROM  inventory_groups_info where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ?";
    $stmt = $specific_db_conn->prepare($sql1);
    $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id));
    $result1 = $stmt->fetchAll();

    // Create an array to store the dropdown options
    $ParentdropdownOptions = [];
    foreach ($result1 as $row) {
        // Append each ledgerhead_name to the $dropdownOptions array
        $ParentdropdownOptions[] = $row['group_name'];
    }
    // Define the range for phone number validation
    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'B';    // Column to validate

    for ($row = $startRow; $row <= $endRow; $row++) {
        $coordinate = $column . $row;

        // Get the cell for the current coordinate
        $cell = $worksheet->getCell($coordinate);

        // Apply data validation to the cell
        $validation = $cell->getDataValidation();
        $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
        $validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('Invalid Input');
        $validation->setError('Please select a value from the list');
        $dropdown_option = implode(',', $ParentdropdownOptions);
        $validation->setFormula1("\"$dropdown_option\"");
    }

    //select unit name from unit creation
    $worksheet->setCellValue('C6', 'Unit Name');
    $style = $worksheet->getStyle('C6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);


    $sql1 = "SELECT formal_name FROM  unit_creation where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ?";
    $stmt = $specific_db_conn->prepare($sql1);
    $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id));
    $result1 = $stmt->fetchAll();
    $unitdropdownOptions = [];
    foreach ($result1 as $row) {

        $unitdropdownOptions[] = $row['formal_name'];
    }
    // Define the range for phone number validation
    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'C';    // Column to validate

    for ($row = $startRow; $row <= $endRow; $row++) {
        $coordinate = $column . $row;
        $cell = $worksheet->getCell($coordinate);
        // Apply data validation to the cell
        $validation = $cell->getDataValidation();
        $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
        $validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('Invalid Input');
        $validation->setError('Please select a value from the list');

        $unit_dropdownOption = implode(',', $unitdropdownOptions);
        $validation->setFormula1("\"$unit_dropdownOption\"");
    }

    //select region name from region details
    $worksheet->setCellValue('D6', 'Region Name');
    $style = $worksheet->getStyle('D6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);


    //select region name from region details
    $sql1 = "SELECT name FROM  inventory_region_details where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ?";
    $stmt = $specific_db_conn->prepare($sql1);
    $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id));
    $result1 = $stmt->fetchAll();

    $regiondropdownOptions = [];
    foreach ($result1 as $row) {

        $regiondropdownOptions[] = $row['name'];
    }
    // Define the range for phone number validation
    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'D';    // Column to validate

    for ($row = $startRow; $row <= $endRow; $row++) {
        $coordinate = $column . $row;
        $cell = $worksheet->getCell($coordinate);
        $validation = $cell->getDataValidation();
        $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
        $validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('Invalid Input');
        $validation->setError('Please select a value from the list');
        $regiondropdownOption = implode(',', $regiondropdownOptions);
        $validation->setFormula1("\"$regiondropdownOption\"");
    }


    $worksheet->setCellValue('E6', 'Product Type');
    $style = $worksheet->getStyle('E6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

    $product_type = ["Goods", "Services"];

    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'E';    // Column to validate

    for ($row = $startRow; $row <= $endRow; $row++) {
        $coordinate = $column . $row;
        $cell = $worksheet->getCell($coordinate);
        $validation = $cell->getDataValidation();
        $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
        $validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('Invalid Input');
        $validation->setError('Please select a value from the list');
        $dropdownOption = implode(',', $product_type);
        $validation->setFormula1("\"$dropdownOption\"");
    }


    $worksheet->setCellValue('F6', 'Intra Tax');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('F6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    // Create a list of options for the dropdown
    //$dropdownOptions = ['Option 1', 'Option 2', 'Option 3'];
    $sql = "SELECT ledger_name FROM  act_ledgerdetails where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and ledger_parent_group LIKE ?";
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id, 'Duties & Taxes'));
    $intra_tax_result = $stmt->fetchAll();

    // Create an array to store the dropdown options
    $IntraParentdropdownOptions = [];
    foreach ($intra_tax_result as $row) {
        // Append each ledgerhead_name to the $dropdownOptions array
        $IntraParentdropdownOptions[] = $row['ledger_name'];
    }
    // Define the range for phone number validation
    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'F';    // Column to validate

    for ($row = $startRow; $row <= $endRow; $row++) {
        $coordinate = $column . $row;

        // Get the cell for the current coordinate
        $cell = $worksheet->getCell($coordinate);

        // Apply data validation to the cell
        $validation = $cell->getDataValidation();
        $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
        $validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('Invalid Input');
        $validation->setError('Please select a value from the list');
        $dropdown_option = implode(',', $IntraParentdropdownOptions);
        $validation->setFormula1("\"$dropdown_option\"");
    }

    $worksheet->setCellValue('G6', 'Inter Tax');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('G6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    // Create a list of options for the dropdown
    //$dropdownOptions = ['Option 1', 'Option 2', 'Option 3'];
    $sql = "SELECT ledger_name FROM  act_ledgerdetails where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and ledger_parent_group LIKE ?";
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id, 'Duties & Taxes'));
    $inter_tax_result = $stmt->fetchAll();

    // Create an array to store the dropdown options
    $InterParentdropdownOptions = [];
    foreach ($inter_tax_result as $row) {
        // Append each ledgerhead_name to the $dropdownOptions array
        $InterParentdropdownOptions[] = $row['ledger_name'];
    }
    // Define the range for phone number validation
    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'G';    // Column to validate

    for ($row = $startRow; $row <= $endRow; $row++) {
        $coordinate = $column . $row;

        // Get the cell for the current coordinate
        $cell = $worksheet->getCell($coordinate);

        // Apply data validation to the cell
        $validation = $cell->getDataValidation();
        $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
        $validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);
        $validation->setShowDropDown(true);

        $validation->setErrorTitle('Invalid Input');
        $validation->setError('Please select a value from the list');
        $dropdown_option = implode(',', $InterParentdropdownOptions);
        $validation->setFormula1("\"$dropdown_option\"");
    }

    $worksheet->setCellValue('H6', 'Other Tax');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('H6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    // Create a list of options for the dropdown
    //$dropdownOptions = ['Option 1', 'Option 2', 'Option 3'];
    $sql = "SELECT ledger_name FROM  act_ledgerdetails where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and ledger_parent_group LIKE ?";
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id, 'Duties & Taxes'));
    $other_tax_result = $stmt->fetchAll();

    // Create an array to store the dropdown options
    $OtherParentdropdownOptions = [];
    foreach ($other_tax_result as $row) {
        // Append each ledgerhead_name to the $dropdownOptions array
        $OtherParentdropdownOptions[] = $row['ledger_name'];
    }
    // Define the range for phone number validation
    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'H';    // Column to validate

    for ($row = $startRow; $row <= $endRow; $row++) {
        $coordinate = $column . $row;

        // Get the cell for the current coordinate
        $cell = $worksheet->getCell($coordinate);

        // Apply data validation to the cell
        $validation = $cell->getDataValidation();
        $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
        $validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('Invalid Input');
        $validation->setError('Please select a value from the list');
        $dropdown_option = implode(',', $OtherParentdropdownOptions);
        $validation->setFormula1("\"$dropdown_option\"");
    }

    $worksheet->setCellValue('I6', 'Inventory Applicable');
    $style = $worksheet->getStyle('I6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

    $product_type = ["Yes", "No"];

    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'I';    // Column to validate

    for ($row = $startRow; $row <= $endRow; $row++) {
        $coordinate = $column . $row;
        $cell = $worksheet->getCell($coordinate);
        $validation = $cell->getDataValidation();
        $validation->setType(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::TYPE_LIST);
        $validation->setErrorStyle(\PhpOffice\PhpSpreadsheet\Cell\DataValidation::STYLE_STOP);
        $validation->setShowDropDown(true);
        $validation->setErrorTitle('Invalid Input');
        $validation->setError('Please select a value from the list');
        $dropdownOption = implode(',', $product_type);
        $validation->setFormula1("\"$dropdownOption\"");
    }

    $worksheet->setCellValue('J6', 'HSN Code');
    $style = $worksheet->getStyle('J6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

    $worksheet->setCellValue('K6', 'Opening Quantity');
    $style = $worksheet->getStyle('K6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

    $worksheet->setCellValue('L6', 'Opening Balance');
    $style = $worksheet->getStyle('L6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);

    $worksheet->setCellValue('M6', 'Default Selling Price');
    $style = $worksheet->getStyle('M6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_LEFT);


    ob_end_clean();
    // Set the headers to force a download
    // Set the appropriate content-type and headers for download


    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Stock Import Rejoice Finware.xlsx"');
    header('Cache-Control: max-age=0');

    // Create a writer for the XLSX format and save to php://output
    $writer = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($spreadsheet);
    $writer->save('php://output');
    exit;
}
