<?php

// require('branchdata.class.php');

class mysqlDataProvider extends DataProvider
{
    public function add_record($fname,
    $minitial,
    $lname,
    $address,
    $age,
    $registeredEmail,
    $contactNumber,
    $unitName,
    $dateOfPurch,
    $receiptNumber,
    $receiptAmount,
    $target
    )
    {
        $db = $this->connect();

        if ($db == null) {
            return;

        }


        $timezone = date_default_timezone_get();
      // echo "The current server timezone is: " . $timezone;

        date_default_timezone_set('Asia/Manila');
        $date = date('Y-m-d H:i:s');
    //    include 'sn.php';
    //    include 'pof.php';

        // validation for erros



            // Generating Promo Code

        $promoCode = 'PK-' . getCode(20);

        $query = 'INSERT INTO raffle_registration(SYSDATE, firstName,middleInitial,lastName,address,age,registeredMail,contactNumber,
                                                unitName, dateOfPurchase,receiptNumber,receiptAmount,imgProofOfPurchase, acceptCondition )

                    VALUES( :sysdate, :fname, :minitial, :lname, :address, :age, :registeredEmail, :contactNumber,
                                                :unitName, :dateOfPurch, :receiptNumber,  :receiptAmount, :imgProofPurchase , 1)';
        $stmt = $db->prepare($query);

        $stmt->execute([
            ':sysdate' => $date,

            ':fname' => $fname,
            ':minitial' => $minitial,
            ':lname' => $lname,
            ':address' => $address,
            ':age' => $age,
            ':registeredEmail' => $registeredEmail,
            ':contactNumber' => $contactNumber,
            ':unitName' => $unitName,
            ':dateOfPurch' => $dateOfPurch,
            ':receiptNumber' => $receiptNumber,
            ':receiptAmount' => $receiptAmount,
            ':imgProofPurchase' => $target,
        ]);
// insert record to raffle_promocode
        Data::add_record_code($date, $promoCode, $receiptNumber);


        $fullname = $fname . " " . $minitial . " " . $lname;
        // insert record to ticket
        // Data::add_record_ticket( $promoCode, $fullname );

// auto respond mail once user inserted
        ini_set( 'display_errors', 1 );
        error_reporting( E_ALL );
        // $toreport = "services@platinumkaraoke.com";
        $from = "events@whitehausstore.ph"; // mail from server gator
        $to = $registeredEmail; // receiving mail

        $subject = "GameXtreme – Your E-Raffle Code 🎟️"; // testing subject
     // message from mail
        $message = '
        Hi '. $fullname .',
        Thank you for joining the GameXtreme Holiday XP E-Raffle Promo! 🎉
        Here’s your official raffle code: '. $promocode .'

        Please keep this code safe! The raffle draw will take place on January 29, 2026, in the presence of a DTI representative. Best of luck!

        📅 Promo Duration
        November 20, 2025 – January 15, 2026

        🎟 Raffle Highlights
        • Verified entries will be included in the official raffle pool.
        • Prizes are non-transferable and not convertible to cash.

        📢 Follow GameXtreme for Updates
        • Facebook: facebook.com/GameXtreme
        • Instagram: instagram.com/gamextremeph
        • TikTok: tiktok.com/@gamextreme.ph

        📞 Customer Assistance
        For inquiries, please contact:
        • Hotline: 0917-123-4567
        • Email: [Insert Email]
        • Office Hours: Monday to Saturday | 9:00 AM – 5:00 PM

        Thank you for being part of our Holiday XP E-Raffle Promo! Per DTI FTEB Permit No. ______, Series of 2025.';

        $headers = "From: " . $from;


        // mail($to,$subject,$message2,$headers);
        if (mail ($to,$subject,$message,$headers)) {
            // echo "The email message was sent.";
        } else {
            // echo "The email message was not sent.";
        }


        $stmt = null;
        $db = null;
    }

    public function get_records($num_per_page)
    {
        $db = $this->connect();

        if ($db == null) {
            return [];
        }
        $link = "";
        if (isset($_GET['page'])) {
            $page = $_GET['page'];
        } else {
            $page = 1;
        }


        $start_from = ($page - 1) * $num_per_page;

        $query = $db->query("SELECT * FROM raffle_registration where receiptNumber !='' order by SYSDATE desc limit $start_from, $num_per_page ");

        $data = $query->fetchAll();

        $query = null;
        $db = null;

        return $data;
    }
    public function get_records_prev($num_per_page)
    {
        $db = $this->connect();

        if ($db == null) {
            return [];
        }


        $query = "SELECT * FROM raffle_registration where receiptNumber !=''";

        $stmt = $db->prepare($query);
        $stmt->execute();
        $total_record = $stmt->rowCount();


        $link = "";
        if (isset($_GET['page'])) {
            $page = $_GET['page'];
        } else {
            $page = 1;
        }

        // $page = min($total_page, filter_input(INPUT_GET, 'page', FILTER_VALIDATE_INT, array(
        //     'options' => array(
        //         'default'   => 1,
        //         'min_range' => 1,
        //     ),
        // )));
        // $limit = 5;





        $total_page = ceil($total_record / $num_per_page);
        $offset = ($page - 1) * $num_per_page;
        $start = $offset + 1;
        $end = min(($offset + $num_per_page), $total_record);

        // The "back" link
        $prevLink = ($page > 1) ? " <a class='active' href=raffle-registered.php?page=1 class='btn btn-prev'>&laquo;</a>
        <a class='active' href=raffle-registered.php?page=" . ($page - 1) . " >&lsaquo;</a>" :
        '<span class="disabled">&laquo;</span> <span class="disabled">&lsaquo;</span>';

         // The "forward" link
         $nextLink = ($page < $total_page) ? "<a class='active' href=raffle-registered.php?page=" . ($page + 1) . " >&rsaquo;</a>
         <a class='active' href=raffle-registered.php?page=" . $total_page . " >&raquo;</a>" :
         '<span class="disabled">&rsaquo;</span> <span class="disabled">&raquo;</span>';


        echo '<div class="pagination">', $prevLink ,' ', $nextLink ,' Page ', $page, ' of ', $total_page, ' pages, displaying ', $start, '-', $end, ' of ', $total_record, ' results <a href="../draw/" style="text-decoration: none; font-size: 15px;">Go to Draw</a> </div>';


    }

    // backup code
    // public function get_records_prev($num_per_page)
    // {
    //     $db = $this->connect();

    //     if ($db == null) {
    //         return [];
    //     }


    //     $query = "SELECT * FROM raffle_registration where receiptNumber !=''";

    //     $stmt = $db->prepare($query);
    //     $stmt->execute();
    //     $total_record = $stmt->rowCount();

    //     if (isset($_GET['page'])) {
    //         $page = $_GET['page'];
    //     } else {
    //         $page = 1;
    //     }

    //     $total_page = ceil($total_record / $num_per_page);

    //     if ($page > 1) {
    //         echo "<a class='btn btn-prev' href=raffle-registered.php?page=" . ($page - 1) . " >Prev</a>";
    //     }

    //     for ($i = 1; $i < $total_page; $i++) {
    //         echo "<a class='btn btn-primary' href=raffle-registered.php?page=$i >$i</a>";
    //     }
    //     if ($i > $page) {
    //         echo "<a class='btn btn-next' href=raffle-registered.php?page=" . ($page + 1) . " >Next</a>";
    //     }

    //     // return $data;


    // }
    public function add_record_code($date, $promocode, $receiptNumber)
    {
        $db = $this->connect();

        if ($db == null) {
            return [];
        }

        $sql = "INSERT INTO raffle_promocode(SYSDATE, promoCode,receiptNumber) VALUES('$date','$promocode','$receiptNumber' )";
        $db->exec($sql);

        $query = null;
        $db = null;

        // return $data;
    }
    public function add_record_ticket( $promocode, $fullname)
    {
        $db = $this->connect();

        if ($db == null) {
            return [];
        }

        $sql = "INSERT INTO tickets( code ,name) VALUES('$promocode','$fullname' )";
        $db->exec($sql);

        $query = null;
        $db = null;

        // return $data;
    }

    public function get_record_item()
    {
        $db = $this->connect();

        if ($db == null) {
            return;
        }

        // $query = 'SELECT unitName FROM raffle_unit';
        // $stmt = $db->prepare($query);

        $stmt = $db->query('SELECT unitName FROM raffle_unit');
        $data = $stmt->fetchAll();

        $stmt = null;
        $db = null;
        return $data;
    }
    public function select_record_registered($receiptNumber)
    {
        $db = $this->connect();

        if ($db == null) {
            return;
        }

        $sql = "SELECT * FROM `raffle_registration` WHERE `receiptNumber` = '$receiptNumber'";
        $stmt= $db->prepare($sql);
        $stmt->execute();
        $record = $stmt->rowCount() > 0;

        $stmt = null;
        $db = null;
        return $record;
    }


    public function get_record_login($username, $password)
    {
        $db = $this->connect();

        if ($db == null) {
            return;
        }

        $query = 'SELECT * FROM tbl_login where username = :username and password = :password';
        $stmt = $db->prepare($query);

        $stmt->execute([
            ':username' => $username,
            ':password' => $password
        ]);
        $count = $stmt->rowCount();
        if ($count > 0) {
            Data::add_user_to_online($username);
            return $stmt->fetch(PDO::FETCH_ASSOC);
        }

        $stmt = null;
        $db = null;
        return false;
    }

    public function get_record_login_date()
    {
        $db = $this->connect();

        if ($db == null) {
            return;
        }

        // ✅ Use parameter placeholder
        $query = "SELECT * FROM tbl_online WHERE username = :username LIMIT 1";
        $stmt = $db->prepare($query);

        // ✅ Bind the parameter properly
        $stmt->execute([':username' => $_SESSION['username']]);

        $row = $stmt->fetch(PDO::FETCH_ASSOC);

        if ($row && isset($row['login_date'])) {
            $data = $row['login_date'];
            Data::add_user_to_history($data);
        } else {
            // Optional: handle no record found
            echo "No record found for this username.";
        }

        $stmt = null;
        $db = null;
    }

    // public function get_record_login_date()
    // {
    //     $db = $this->connect();

    //     if ($db == null) {
    //         return;
    //     }

    //     $query = "Select * from tbl_online where username = '" . $_SESSION['username'] . "'  LIMIT 1";
    //     $stmt = $db->prepare($query);

    //     // $stmt->execute();

    //     // $row = $stmt->fetch(PDO::FETCH_ASSOC);
    //     // $data = $row['login_date'];

    //     // Data::add_user_to_history($data);
    //     $stmt->execute([':username' => $_SESSION['username']]);

    //     $row = $stmt->fetch(PDO::FETCH_ASSOC);

    //     if ($row && isset($row['login_date'])) {
    //         $data = $row['login_date'];
    //         Data::add_user_to_history($data);
    //     } else {
    //         // Optional: Log or handle if no record found
    //         // echo "No login date found for user.";
    //     }

    //     $stmt = null;
    //     $db = null;
    // }

    public function add_user_to_history($data)
    {
        $db = $this->connect();

        if ($db == null) {
            return;
        }



        $query = 'INSERT INTO tbl_login_history(SYSDTTM, username, login_date) VALUES(:sysdttm, :username, :login_date)';
        $stmt = $db->prepare($query);
        $stmt->execute([
            ':sysdttm' => date('Y-m-d H:i:s'),
            ':username' => $_SESSION['username'],
            ':login_date' => $data
        ]);

        $stmt = null;
        $db = null;
    }

    public function get_record_is_online($username)
    {
        $db = $this->connect();

        if ($db == null) {
            return;
        }

        $query = 'SELECT * FROM tbl_online where username = :username';
        $stmt = $db->prepare($query);

        $stmt->execute([
            ':username' => $username
        ]);
        $count = $stmt->rowCount();
        if ($count > 0) {
            redirect('login.php');
        }

        $stmt = null;
        $db = null;
    }

    public function get_record_is_locked($username)
    {
        $db = $this->connect();

        if ($db == null) {
            return;
        }

        $query = 'SELECT stts FROM tbl_login where username = :username';
        $stmt = $db->prepare($query);

        $stmt->execute([
            ':username' => $username
        ]);

        $data = $stmt->fetch(PDO::FETCH_ASSOC);

        // print_r($data['stts'] = 1);
        if ($data['stts'] === 1) {
            //  login_message_error($data);
            redirect('login.php');
        }

        $stmt = null;
        $db = null;
    }

    public function add_user_to_online($username)
    {
        $db = $this->connect();

        if ($db == null) {
            return;
        }


        $query = 'Insert into tbl_online (username) values( :username )';
        $stmt = $db->prepare($query);
        $stmt->execute([
            ':username' => $username
        ]);
        $_SESSION['username'] = $username;
    }

    public function search_record($search,$whereto)
    {
        $db = $this->connect();

        if ($db == null) {
            return;
        }

        $query = "SELECT * FROM `raffle_registration` WHERE `$whereto` LIKE '%$search%'";
        $stmt = $db->prepare($query);

        $stmt->execute();
        $data = $stmt->fetchAll();

        return $data;
    }



    public function update_term($original_term, $new_term, $definition)
    {
    }

    public function delete_record($username)
    {
        $db = $this->connect();

        if ($db == null) {
            return;
        }

        $query = "DELETE FROM tbl_online WHERE username = :username";
        $stmt = $db->prepare($query);
        $stmt->execute([
            ':username' => $username
        ]);
    }

    private function connect()
    {
        try {
            return new PDO($this->source, CONFIG['db_user'], CONFIG['db_password']);
        } catch (PDOException $e) {
            return null;
        }
    }
}
