<?php
// Database connection settings
 $servername = 'localhost';
$servername = "localhost";
$username = "dannygym_k-xprex";
$password = "Sasketchwan,1";
$dbname = "dannygym_ k-xprex";

                $conn = new mysqli($servername, $username, $password, $dbname);

                // Check the connection
                if ($conn->connect_error) {
                    die('Connection failed: ' . $conn->connect_error);
                }

// Create the admin table
$sql_admin = "CREATE TABLE IF NOT EXISTS admin (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL
)";
if ($conn->query($sql_admin) === TRUE) {
    echo "Admin table created successfully.<br>";
} else {
    echo "Error creating admin table: " . $conn->error . "<br>";
}

// Create the clients table
$sql_clients = "CREATE TABLE IF NOT EXISTS clients (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tracking_number VARCHAR(255) NOT NULL,
    sender_name VARCHAR(255) NOT NULL,
    sender_location VARCHAR(255) NOT NULL,
    location_description VARCHAR(255),
    sender_time DATETIME,
    receiver_name VARCHAR(255) NOT NULL,
    receiver_location VARCHAR(255) NOT NULL,
    receiver_phone VARCHAR(255) NOT NULL,
    receiver_email VARCHAR(255) NOT NULL,
    package_weight FLOAT NOT NULL,
    delivery_amount DECIMAL(10, 2) NOT NULL,
    date_sent DATE NOT NULL,
    arrival_date DATE NOT NULL
)";
if ($conn->query($sql_clients) === TRUE) {
    echo "Clients table created successfully.<br>";
} else {
    echo "Error creating clients table: " . $conn->error . "<br>";
}

// Create the location_history table
$sql_location_history = "CREATE TABLE IF NOT EXISTS location_history (
    id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tracking_number VARCHAR(255) NOT NULL,
    location VARCHAR(255) NOT NULL,
    description VARCHAR(255),
    date DATE NOT NULL,
    time TIME NOT NULL
)";
if ($conn->query($sql_location_history) === TRUE) {
    echo "Location History table created successfully.<br>";
} else {
    echo "Error creating Location History table: " . $conn->error . "<br>";
}

// Insert sample values into the admin table
$sql_insert_admin = "INSERT INTO admin (username, password) VALUES ('admin', 'ituku20')";
if ($conn->query($sql_insert_admin) === TRUE) {
    echo "Sample admin values inserted successfully.<br>";
} else {
    echo "Error inserting sample admin values: " . $conn->error . "<br>";
}

// Insert sample values into the clients table
$sql_insert_clients = "INSERT INTO clients (tracking_number, sender_name, sender_location, location_description, sender_time, receiver_name, receiver_location, receiver_phone, receiver_email, package_weight, delivery_amount, date_sent, arrival_date) VALUES
    ('FMC123456ABC', 'John Doe', 'New York', 'Office Address', '2023-07-01 10:00:00', 'Jane Smith', 'Los Angeles', '1234567890', 'jane@example.com', 5.6, 100.00, '2023-06-30', '2023-07-05'),
    ('FMC789012XYZ', 'Alice Johnson', 'London', 'Residential Address', '2023-07-02 15:30:00', 'Bob Wilson', 'Paris', '9876543210', 'bob@example.com', 3.2, 75.00, '2023-07-01', '2023-07-07')";
if ($conn->query($sql_insert_clients) === TRUE) {
    echo "Sample clients values inserted successfully.<br>";
} else {
    echo "Error inserting sample clients values: " . $conn->error . "<br>";
}

// Insert sample values into the location_history table
$sql_insert_location_history = "INSERT INTO location_history (tracking_number, location, description, date, time) VALUES
    ('FMC123456ABC', 'New York', 'Package picked up', '2023-07-01', '10:00:00'),
    ('FMC123456ABC', 'Los Angeles', 'Package in transit', '2023-07-02', '12:00:00'),
    ('FMC123456ABC', 'San Francisco', 'Package arrived', '2023-07-03', '09:00:00'),
    ('FMC789012XYZ', 'London', 'Package picked up', '2023-07-02', '15:30:00'),
    ('FMC789012XYZ', 'Paris', 'Package in transit', '2023-07-03', '17:30:00'),
    ('FMC789012XYZ', 'Berlin', 'Package arrived', '2023-07-04', '14:00:00')";
if ($conn->query($sql_insert_location_history) === TRUE) {
    echo "Sample location history values inserted successfully.<br>";
} else {
    echo "Error inserting sample location history values: " . $conn->error . "<br>";
}

// Close the database connection
$conn->close();
?>
