<?php
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "med";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

// sql to create table USER
$sql_user = "CREATE TABLE User (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(30) NOT NULL,
    firstname VARCHAR(30) NOT NULL,
    lastname VARCHAR(30) NOT NULL,
    date_of_birth DATE,
    cnp INT(13),
    password VARCHAR(30) NOT NULL
)";

if ($conn->query($sql_user) === TRUE) {
    echo "Table User created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

// sql to create table SERVICES
$sql_services = "CREATE TABLE Services (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(30) NOT NULL,
    price INT(6)
)";

if ($conn->query($sql_services) === TRUE) {
    echo "Table User created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

// sql to create table APPOINTMENT
$sql_appointment = "CREATE TABLE Appointment (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    u_id INT(6) UNSIGNED,
    s_id INT(6) UNSIGNED,
    first_name VARCHAR(60),
    last_name VARCHAR(60),
    date DATE,
    start INT(3),
    end INT(3),
    status VARCHAR(60) DEFAULT 'active',
    CONSTRAINT fk_a1 FOREIGN KEY (u_id) REFERENCES User(id),
    CONSTRAINT fk_a2 FOREIGN KEY (s_id) REFERENCES Services(id)
)";

if ($conn->query($sql_appointment) === TRUE) {
    echo "Table User created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}

// sql to create table INVESTIGATION
$sql_investigation = "CREATE TABLE Investigation (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    u_id INT(6) UNSIGNED,
    a_id INT(6) UNSIGNED,
    investigation VARCHAR(255),
    CONSTRAINT fk_i1 FOREIGN KEY (u_id) REFERENCES User(id),
    CONSTRAINT fk_i2 FOREIGN KEY (a_id) REFERENCES Appointment(id)
)";


$insert_sql1 = "INSERT INTO User(username, firstname, lastname, date_of_birth, cnp, password)
VALUES('admin', 'admin', 'admin', '', '', 'admin')";
$execute = mysqli_query($conn, $insert_sql1);

$insert_sql2 = "INSERT INTO services(name, price)
                VALUES
                    ('Extractie dentara cu anestezie', '240'),
                    ('Tratamentul cariei simple', '125'),
                    ('Reparatie/rebazare proteza', '50'),
                    ('Reparatie aparat ortodontic', '100')";
$execute = mysqli_query($conn, $insert_sql2);




if ($conn->query($sql_investigation) === TRUE) {
    echo "Table User created successfully";
} else {
    echo "Error creating table: " . $conn->error;
}


$conn->close();
?>