<?php
$host = "localhost";
$db_user = "root";
$db_password = "pass";
$db_name = "zaklad_pogrzebowy_db";
$link = mysqli_connect($host, $db_user, $db_password);
if (!$link) {
    die('Could not connect: ' . mysqli_error());
}
$db_selected = mysqli_select_db($link, $db_name);
if (!$db_selected) {
    $sqlCreateDatabase = 'CREATE DATABASE ' . $db_name;
    if (mysqli_query($link, $sqlCreateDatabase)) {
        echo "Database created successfully.<br>";
    } else {
        echo 'Error creating database: ' . mysqli_error() . "\n";
    }
}
$polaczenie = @new mysqli($host, $db_user, $db_password, $db_name);
if ($polaczenie->connect_errno!=0)
{
    echo "Error: ".$polaczenie->connect_errno;
}
else
{
    $queryCreateUsersTable = "CREATE TABLE IF NOT EXISTS `USERS` (
        `ID` int(5) unsigned NOT NULL auto_increment,
        `EMAIL` varchar(55) UNIQUE NOT NULL default '',
        `FIRSTNAME` varchar(20) NOT NULL default '',
        `LASTNAME` varchar(30) NOT NULL default '',
        `PASSWORD` varchar(50) NOT NULL default '',
        PRIMARY KEY  (`ID`))";
    $polaczenie->query($queryCreateUsersTable);
    $sqlInsUsers = "INSERT INTO USERS (EMAIL, FIRSTNAME, LASTNAME, PASSWORD)
        VALUES
        ('john@example.com', 'John', 'Mouse', 'john123'),
        ('kamils@gmail.com', 'Kamil', 'Sieradzki', 'haslo123'),
        ('adudek@gmail.com', 'Adam', 'Dudek', 'pwr123')";
    if ($polaczenie->query($sqlInsUsers) === TRUE) {
        echo "New records created successfully <br>";
        $allUser= "SELECT * FROM USERS";
        $result = $polaczenie->query($allUser);
        while($row = $result->fetch_assoc()) {
            echo "id: " . $row["ID"]. " - Name: " . $row["FIRSTNAME"]. " " . $row["LASTNAME"]. " | EMAIL: " . $row["EMAIL"] . " | PASSWORD: " . $row["PASSWORD"] ."<br>";
        }
    } else {
        echo "Error: " . $sqlInsUsers . "<br>" . $polaczenie->error;
    }
    $queryCreateCarsTable = "CREATE TABLE IF NOT EXISTS `CARS` (
        `ID_CAR` int(5) unsigned NOT NULL auto_increment,
        `NAME` varchar(50) NOT NULL UNIQUE default '',
        `PRICE` int(5) NOT NULL,
        PRIMARY KEY  (`ID_CAR`))";
    $polaczenie->query($queryCreateCarsTable);
    $sqlInsCars = "INSERT INTO CARS (NAME, PRICE)
        VALUES
        ('VW VOLKSWAGEN T5', '150'),
        ('Mercedes W212 E-klasa', '250'),
        ('Cadillac Fleetwood', '300')";
    if ($polaczenie->query($sqlInsCars) === TRUE) {
        echo "New cars created successfully <br>";
        $allUser= "SELECT * FROM CARS";
        $result = $polaczenie->query($allUser);
        while($row = $result->fetch_assoc()) {
            echo "id: " . $row["ID_CAR"]. " - Name: " . $row["NAME"]. " | PRICE: " . $row["PRICE"] . "<br>";
        }
    } else {
        echo "Error: " . $sqlInsCars . "<br>" . $polaczenie->error;
    }
    $queryCreateServiesTable = "CREATE TABLE IF NOT EXISTS `SERVICES` (
        `ID_SERVICE` int(5) unsigned NOT NULL auto_increment,
        `NAME` varchar(50) NOT NULL UNIQUE default '',
        `PRICE` float (5) NOT NULL,
        PRIMARY KEY  (`ID_SERVICE`))";
    $polaczenie->query($queryCreateServiesTable);
    $sqlInsServices = "INSERT INTO SERVICES (NAME, PRICE)
        VALUES
        ('Oprawa muzyczna', '199.99'),
        ('Fotograf', '149.99'),
        ('Profesjonalny mowca pogrzebowy', '180.00')";
    if ($polaczenie->query($sqlInsServices) === TRUE) {
        echo "New services created successfully <br>";
        $allUser= "SELECT * FROM SERVICES";
        $result = $polaczenie->query($allUser);
        while($row = $result->fetch_assoc()) {
            echo "id: " . $row["ID_SERVICE"]. " - Name: " . $row["NAME"]. " | PRICE: " . $row["PRICE"] . "<br>";
        }
    } else {
        echo "Error: " . $sqlInsServices . "<br>" . $polaczenie->error;
    }
    $queryCreateOrdersTable = "CREATE TABLE IF NOT EXISTS `ORDERS` (
        `ID_ORDER` int(7) unsigned NOT NULL auto_increment,
        `ID_USER` int(5) unsigned NOT NULL,
        `ID_CAR` int(5) unsigned NOT NULL,
        `COLOUR` varchar(7) NOT NULL,
        `EMAIL` varchar(55) NOT NULL,
        `FIRSTNAME` varchar(20) NOT NULL,
        `LASTNAME` varchar(30) NOT NULL,
        `PHONE` varchar(12) NOT NULL,
        PRIMARY KEY  (`ID_ORDER`),
        FOREIGN KEY (ID_USER) REFERENCES USERS(ID),
        FOREIGN KEY (ID_CAR) REFERENCES CARS(ID_CAR))";
    $polaczenie->query($queryCreateOrdersTable);
    echo "Info create orders: " . $queryCreateOrdersTable . "<br>" . $polaczenie->error;
    $polaczenie->close();
}
?>