<?php

require __DIR__ . '/vendor/autoload.php';

$dotenv = Dotenv\Dotenv::createImmutable(__DIR__ . "/../");
$dotenv->load();

date_default_timezone_set("Asia/Jerusalem");
$year = date("Y",strtotime("-1 month"));
// $month = date("m");
$month = date("m",strtotime("-1 month"));
$days = cal_days_in_month(CAL_GREGORIAN, $month, $year);
$start = $year . "-" . $month . "-" . "01" . " " . "00:00:00";
$end = $year . "-" . $month . "-" . $days . " " . "23:59:59";

$host_ip = $_SERVER['HOST_IP'];
$db_user = $_SERVER['DB_USER'];
$db_pass = $_SERVER['DB_PASS'];
$db_name = $_SERVER['DB_NAME'];

###############################

# Email attributes

//$to = 'nasirhafeez2002@gmail.com';
$to = 'hello@tenecall.com';
$from = 'mailer@wifi-ten.com'; 
$fromName = 'WiFi Ten Mailer'; 
 
// Header for sender info 
$headers = "From: $fromName"." <".$from.">"; 
 
// Boundary  
$semi_rand = md5(time());  
$mime_boundary = "==Multipart_Boundary_x{$semi_rand}x";  
 
// Headers for attachment  
$headers .= "\nMIME-Version: 1.0\n" . "Content-Type: multipart/mixed;\n" . " boundary=\"{$mime_boundary}\""; 

###############################

$con = mysqli_connect($host_ip, $db_user, $db_pass, $db_name);

if (mysqli_connect_errno()) {
  echo "Failed to connect to SQL: " . mysqli_connect_error();
}

$listdbtables = array_column($con->query('SHOW TABLES')->fetch_all(),0);

foreach ($listdbtables as $table_name) {

  $message = $fp = $data = null;
  $file = "/var/lib/mysql-files/" . $year . "_" . $month . "_" . $table_name . ".csv";
  $subject = $table_name . " - " . 'WiFi Ten Monthly Report';

  $result = mysqli_query($con, "SELECT * FROM `$table_name` WHERE last_updated >= '$start' AND last_updated <= '$end'");

  mysqli_query($con, "SELECT 'id', 'phone', 'mac', 'apmac', 'last_updated'  UNION ALL SELECT * FROM `$table_name` WHERE last_updated >= '$start' AND last_updated <= '$end' INTO OUTFILE '$file' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'");

  if(!$result){
    $row_cnt = 0;
  } else {
    $row_cnt = $result->num_rows;
  }

  // Email body content 
  $htmlContent = ' 
    <h3>Summary</h3> 
    <p>Total Users: ' . $row_cnt . '</p>';

  // Multipart boundary  
  $message = "--{$mime_boundary}\n" . "Content-Type: text/html; charset=\"UTF-8\"\n" . 
  "Content-Transfer-Encoding: 7bit\n\n" . $htmlContent . "\n\n";  

  // Preparing attachment 
  if(!empty($file) > 0){ 
    if(is_file($file)){ 
      $message .= "--{$mime_boundary}\n"; 
      $fp =    @fopen($file,"rb"); 
      $data =  @fread($fp,filesize($file)); 

      @fclose($fp); 
      $data = chunk_split(base64_encode($data)); 
      $message .= "Content-Type: application/octet-stream; name=\"".basename($file)."\"\n" .  
      "Content-Description: ".basename($file)."\n" . 
      "Content-Disposition: attachment;\n" . " filename=\"".basename($file)."\"; size=".filesize($file).";\n" .  
      "Content-Transfer-Encoding: base64\n\n" . $data . "\n\n"; 
    } 
  }

  $message .= "--{$mime_boundary}--"; 
  $returnpath = "-f" . $from; 

  // Send email 
  $mail = @mail($to, $subject, $message, $headers, $returnpath);  

  // Email sending status 
  echo $mail?"<h1>Email Sent Successfully!</h1>":"<h1>Email sending failed.</h1>"; 

}

mysqli_close($con);

?>