<?php

ini_set('display_errors', 1);


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

require_once $base_path_to_resources . "includes/config.php"; // use require for mandatory files
require_once $base_path_to_resources . 'lib/function.php';
require_once $base_path_to_resources . "lib/currency_functions.php";
require_once $base_path_to_resources . 'lib/validate_api_signature.php';
require_once $base_path_to_resources . 'lib/validate_function.php';

//valdation file

require_once $base_path . 'user_management/authenticate.php';

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

$postdata = json_decode(file_get_contents("php://input"), true);
checkIfNoPhpContentsReceive();
validateApiSignature($postdata['api_signature']);

//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'] == "dashboard_book") {
    
    $today = date('Y-m-d');
    if (isset($postdata['date']) and !empty($postdata['date']) and strtotime($postdata['date'])) {
        $today = trim($postdata['date']);
    } else {
        $today = date('Y-m-d');
    }

    $company_id = trim($postdata['company_id']);
    $branch_id = trim($postdata['branch_id']);
    $book_id = trim($postdata['book_id']);
    
    $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);
        
    }
    
    //total ledgers
     $sql = "SELECT  count(*) as total_rows FROM  act_ledgerdetails  where workspace_id = ? and  company_id = ? and branch_id = ? and book_id =?";
    if ($role == null) {
        $sql .= " AND created_by = ? ";
        $array_params = [$workspace_id, $company_id, $branch_id,$book_id, $created_by];
    } else {
        $array_params = [$workspace_id, $company_id, $branch_id,$book_id];
    }
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute($array_params);
    $result_total = $stmt->fetchAll();
    $totalledgers = $result_total[0]['total_rows'];
    
    // ledger count that have been created today
    $sql = "SELECT  count(*) as total_rows FROM  act_ledgerdetails  where workspace_id = ? and  company_id = ? and branch_id = ? and book_id = ? and date_created = ?";
    if ($role == null) {
        $sql .= " AND created_by = ? ";
        $array_params = [$workspace_id, $company_id, $branch_id,$book_id, $today, $created_by];
    } else {
        $array_params = [$workspace_id, $company_id, $branch_id, $book_id,$today];
    }
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute($array_params);
    $result_total = $stmt->fetchAll();
    $total_ledgers = $result_total[0]['total_rows'];

    // transaction count for each type that are recorded for the date
    $sql = 'SELECT transactiontype, COUNT(*) as count FROM act_transactiondata WHERE workspace_id =? and  company_id = ? and branch_id = ? and book_id= ? and date = ?  ';
    if ($role == null) {
        $sql .= " AND (entry_by = ? or last_modified_by = ?)";
        $array_params = [$workspace_id, $company_id, $branch_id,$book_id, $today, $created_by, $created_by];
    } else {
        $array_params = [$workspace_id, $company_id, $branch_id,$book_id, $today];
    }
    $sql .= "GROUP BY transactiontype";
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute($array_params);
    $result_transactions = $stmt->fetchAll();

   $transaction_info = array();
    $total_transactions = 0;
    foreach ($result_transactions as $rows) {
        
        $transaction_info[$rows['transactiontype']] = $rows['count'];
        $total_transactions += $rows['count'];
    }
    
  


   // entried that have been done today instead of transaciton data
    $start_time_today = strtotime($today." 00:00");
    $end_time_today = strtotime($today." 23:59");
   
    $sql = "Select COUNT(*) as total from act_transactiondata where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and time >=  ? and time <= ?";
    $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $start_time_today, $end_time_today];

    if ($role == null) {
        $sql .= " AND entry_by = ?";
        
          $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $start_time_today, $end_time_today,$created_by];

    }else{
        
         $array_params = [$workspace_id, $company_id, $branch_id, $book_id, $start_time_today, $end_time_today];
    }
 
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute($array_params);
    $transactions_created_today = array();
    $total_transactionstype = $stmt->fetchAll()[0]['total'];
     
    
    $start_time_today = strtotime($today." 00:00");
    $end_time_today = strtotime($today." 23:59");
    $sql = "Select COUNT(*) as count from act_transactiondata where workspace_id = ? and company_id = ? and branch_id = ? and book_id = ? and last_updated >= ? and last_updated <= ?";
    if ($role == null) {
        $sql .= " AND last_modified_by = ?";
        $array_params = [$workspace_id, $company_id, $branch_id,$book_id, $start_time_today, $end_time_today, $created_by];
    } else {
        $array_params = [$workspace_id, $company_id, $branch_id,$book_id, $start_time_today, $end_time_today];
    }
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute($array_params);
    $transactionscreated_today = array();
    $totaltransactionstype = $stmt->fetchAll()[0]['count'];
     

    $sql = "SELECT COALESCE(SUM(tr.amount), 0) AS total_debited_amount
        FROM act_transactionrecord tr
        JOIN act_transactiondata td ON tr.date = td.date
        WHERE td.workspace_id = ? 
            AND td.company_id = ? 
            AND td.branch_id = ? 
            AND td.book_id = ? 
            AND (tr.main_ledgerhead = 'Cash-in-Hand' OR tr.main_ledgerhead = 'Bank Account')
            AND (td.transactiontype = 'sales' OR td.transactiontype = 'receipt')
            AND DATE(td.date) = ?
             AND tr.balancetype = ?";
    if ($role == null) {
        $sql .= " AND entry_by = ? ";
        $array_params = [$workspace_id, $company_id, $branch_id,$book_id, $today, 'dr', $created_by];
    } else {
        $array_params = [$workspace_id, $company_id, $branch_id,$book_id, $today, 'dr'];
    }
    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute($array_params);
    $result_data = $stmt->fetchAll();
    $total_sales = $result_data[0]['total_debited_amount'];


    $sql = "SELECT COALESCE(SUM(tr.amount), 0) AS total_debited_amount
            FROM act_transactionrecord tr
            JOIN act_transactiondata td ON tr.date = td.date
            WHERE td.workspace_id = ? 
                AND td.company_id = ? 
                AND td.branch_id = ? 
                 AND td.book_id = ? 
                AND (tr.main_ledgerhead = 'Cash-in-Hand' OR tr.main_ledgerhead = 'Bank Account')
                AND (td.transactiontype = 'payment' OR td.transactiontype = 'purchase')
                AND DATE(td.date) = ?
                AND tr.balancetype = ?";
    if ($role == null) {
        $sql .= " AND entry_by = ? ";
        $array_params = [$workspace_id, $company_id, $branch_id,$book_id,$today, 'cr', $created_by];
    } else {
        $array_params = [$workspace_id, $company_id, $branch_id,$book_id, $today, 'cr'];
    }


    $stmt = $specific_db_conn->prepare($sql);
    $stmt->execute($array_params);
    $result_data = $stmt->fetchAll();
    $total_purchase = $result_data[0]['total_debited_amount'];


    $data = array('total_ledgers' => $totalledgers,'count_ledgers' => $total_ledgers, 'transaction_info' => $transaction_info,'total_transactions'=>$total_transactions,'transactions_created_today'=>$total_transactionstype, 
    'total_transactions_created_by_user' =>$totaltransactionstype, 'total_sales' => $total_sales, 'total_purchase' => $total_purchase);
    successreturn(array('data' => $data));
}
