<?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';


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

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

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

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


//authenticate jwt token
$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;


if ($postdata['function_type']  == "download_ledgers") {
    // 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 <= 'K'; $column++) {
        $worksheet->getColumnDimension($column)->setWidth(25); // 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'];
    //also check if the company is being created by a super_admin and admin only, if not throw access not granted error.
    $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:Q1');
    $worksheet->setCellValue('A1', 'Ledger 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:Q3');
    $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:Q4');
    $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->mergeCells('A6:A7');
    $worksheet->setCellValue('A6', 'Name');

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


    // Merge cells B6 to C6
    $worksheet->mergeCells('B6:C6');
    $worksheet->setCellValue('B6', 'Opening Balance');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('B6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);


    $worksheet->setCellValue('B7', 'Debit');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('B7');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);


    $worksheet->setCellValue('C7', 'Credit');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('C7');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);


    $worksheet->mergeCells('D6:D7');
    $worksheet->setCellValue('D6', 'Ledger Parent Group');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('D6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);


    // Create a list of options for the dropdown
    //$dropdownOptions = ['Option 1', 'Option 2', 'Option 3'];
    $sql1 = "select id, group_name, main_ledgerhead from (SELECT null as id, ledgerhead_name as group_name, null as main_ledgerhead from act_ledgerheads_basic UNION
        SELECT id, group_name, main_ledgerhead from act_group_info  WHERE workspace_id =? and company_id =? and branch_id =? and book_id = ?) as I
               order by group_name;;";
    // $sql1 = "SELECT ledgerhead_name FROM act_ledgerheads_basic";
    $stmt = $specific_db_conn->prepare($sql1);
    //  $stmt->execute();
    $stmt->execute(array($workspace_id, $company_id, $branch_id, $book_id));
    $result1 = $stmt->fetchAll();

    // Create an array to store the dropdown options
    $dropdownOptions = [];
    $dropdownOptionsString = [];
    foreach ($result1 as $row) {
        // Append each ledgerhead_name to the $dropdownOptions array
        // $dropdownOptions[] = $row['ledgerhead_name'];
        $dropdownOptions[] = $row['group_name'];
    }
    // Use implode to join the dropdown options into a comma-separated list
    // $dropdownOptionsString = implode(',', $dropdownOptions);



    // 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;

        // 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');

        // Use implode to join the dropdown options into a comma-separated list
        $dropdownOption = implode(',', $dropdownOptions);
        $validation->setFormula1("\"$dropdownOption\"");
    }


    $worksheet->mergeCells('E6:E7');
    $worksheet->setCellValue('E6', 'Pan');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('E6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);

    $worksheet->mergeCells('F6:F7');
    $worksheet->setCellValue('F6', 'GST');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('F6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);


    $worksheet->mergeCells('G6:G7');
    $worksheet->setCellValue('G6', 'Contact No.');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('G6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);


    $worksheet->mergeCells('H6:H7');
    $worksheet->setCellValue('H6', 'Email');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('H6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);



    $worksheet->mergeCells('I6:I7');
    $worksheet->setCellValue('I6', 'if Tax,Percentage');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('I6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);

    // select tax type - J
    
    $worksheet->mergeCells('J6:J7');
    $worksheet->setCellValue('J6', 'Tax Type');
    $style = $worksheet->getStyle('J6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

    $tax_type = ["GST", "Others"];

    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'J';    // 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');
        $taxOption = implode(',', $tax_type);
        $validation->setFormula1("\"$taxOption\"");
    }

    // $worksheet->mergeCells('J6:J7');
    // $worksheet->setCellValue('J6', 'Tax Type');
    // // Get the style of the cell and configure it
    // $style = $worksheet->getStyle('J6');
    // $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    // $style->getFont()->setBold(true);

    $sql1 = "SELECT ledgerhead_name FROM act_ledgerheads_basic where ledgerhead_name =  'Duties & Taxes'";
    $stmt = $specific_db_conn->prepare($sql1);
    $stmt->execute();
    $result1 = $stmt->fetchAll();

    // Create an array to store the dropdown options
    $ledgername = [];
    $dropdownOptionsString = [];
    foreach ($result1 as $row) {
        // Append each ledgerhead_name to the $dropdownOptions array
        $ledgername[] = $row['ledgerhead_name'];
    }

    // Define the range for phone number validation
    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'J';    // 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 ledgerhead name from the list');

        // Use implode to join the dropdown options into a comma-separated list
        $ledgerhead_name = implode(',', $ledgername);
        $validation->setFormula1("\"$ledgerhead_name\"");
    }

    $worksheet->mergeCells('K6:K7');
    $worksheet->setCellValue('K6', 'Sub Type');
    $style = $worksheet->getStyle('K6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);

    $sub_type = ['CGST', 'SGST', 'IGST', 'CESS'];

    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'K';    // 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(',', $sub_type);
        $validation->setFormula1("\"$dropdownOption\"");
    }
    
    
    // select sub type - k
    // $worksheet->mergeCells('K6:K7');
    // $worksheet->setCellValue('K6', 'Sub Type');
    // // Get the style of the cell and configure it
    // $style = $worksheet->getStyle('K6');
    // $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    // $style->getFont()->setBold(true);

    $sql1 = "SELECT ledgerhead_name FROM act_ledgerheads_basic where ledgerhead_name =  'Duties & Taxes'";
    $stmt = $specific_db_conn->prepare($sql1);
    $stmt->execute();
    $result1 = $stmt->fetchAll();

    // Create an array to store the dropdown options
    $ledgername = [];
    $dropdownOptionsString = [];
    foreach ($result1 as $row) {
        // Append each ledgerhead_name to the $dropdownOptions array
        $ledgername[] = $row['ledgerhead_name'];
    }

    // Define the range for phone number validation
    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'K';    // 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 ledgerhead name from the list');

        // Use implode to join the dropdown options into a comma-separated list
        $ledgerhead_name = implode(',', $ledgername);
        $validation->setFormula1("\"$ledgerhead_name\"");
    }


    $worksheet->mergeCells('L6:L7');
    $worksheet->setCellValue('L6', 'Addresses Name');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('L6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);


    $worksheet->mergeCells('M6:M7');
    $worksheet->setCellValue('M6', 'address line 1');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('M6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);

    $worksheet->mergeCells('N6:N7');
    $worksheet->setCellValue('N6', 'address line 2');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('N6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);

    //select country - o

    $worksheet->mergeCells('O6:O7');
    $worksheet->setCellValue('O6', 'Country');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('O6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);
    $sql1 = "SELECT name FROM country_info";
    $stmt = $conn->prepare($sql1);
    $stmt->execute();
    $result1 = $stmt->fetchAll();

    // Create an array to store the dropdown options
    $name = [];

    foreach ($result1 as $row) {
        // Append each name to the $dropdownOptions array
        $name[] = $row['name'];
    }

    // Define the range for phone number validation
    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'O';    // 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 country name from the list');

        // Use implode to join the dropdown options into a comma-separated list
        $country_name = implode(',', $name);
        $validation->setFormula1("\"$country_name\"");
    }


    //select state - P

    $worksheet->mergeCells('P6:P7');
    $worksheet->setCellValue('P6', 'State');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('P6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);
    $sql1 = "SELECT state FROM state_info where country_id = ?";
    $stmt = $conn->prepare($sql1);
    $stmt->execute(array('1'));
    $result1 = $stmt->fetchAll();

    // Create an array to store the dropdown options
    $state = [];
    foreach ($result1 as $row) {
        // Append each name to the $dropdownOptions array
        $state[] = $row['state'];
    }

    // Define the range for phone number validation
    $startRow = 7;     // Starting row
    $endRow = 10000;  // Ending row
    $column = 'P';    // 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 state name from the list');

        // Use implode to join the dropdown options into a comma-separated list
        $state_name = implode(',', $state);
        $validation->setFormula1("\"$state_name\"");
    }

    $worksheet->mergeCells('Q6:Q7');
    $worksheet->setCellValue('Q6', 'Postal Code');
    // Get the style of the cell and configure it
    $style = $worksheet->getStyle('Q6');
    $style->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
    $style->getFont()->setBold(true);

    ob_end_clean();
    // Set the headers to force a download
    // Set the appropriate content-type and headers for download
    // Your code to create the spreadsheet and set its content

    // Set headers for download
    header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
    header('Content-Disposition: attachment;filename="Ledger 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;
}
