CREATE DATABASE IF NOT EXISTS `hyperdrive` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
USE `hyperdrive`;

-- mysql -h localhost -P 3306 --protocol=tcp -u root -p
-- Creating tables

CREATE TABLE IF NOT EXISTS Users (
    login varchar(60) NOT NULL,
    passwd varchar(256) NOT NULL,
    PRIMARY KEY (login)
);

CREATE TABLE IF NOT EXISTS Paths (
    paths varchar(400) NOT NULL,
    login varchar(60) NOT NULL,
    parent varchar(400),
    PRIMARY KEY (paths),
    FOREIGN KEY (login) REFERENCES Users(login),
    FOREIGN KEY (parent) REFERENCES Paths(paths)
);

CREATE TABLE IF NOT EXISTS Files (
    file_id varchar(100) NOT NULL,
    file_name varchar(50) NOT NULL,
    login varchar(60) NOT NULL,
    paths varchar(400) NOT NULL,
    lat FLOAT,
    lng FLOAT,
    PRIMARY KEY (file_id),
    FOREIGN KEY (paths) REFERENCES Paths(paths),
    FOREIGN KEY (login) REFERENCES Users(login)
);

CREATE TABLE IF NOT EXISTS Shares (
    pseudo_1 varchar(60) NOT NULL,
    pseudo_2 varchar(60) NOT NULL,
    file_id varchar(100) NOT NULL,
    PRIMARY KEY (pseudo_1, pseudo_2, file_id),
    FOREIGN KEY (pseudo_1) REFERENCES Users(login),
    FOREIGN KEY (pseudo_2) REFERENCES Users(login),
    FOREIGN KEY (file_id) REFERENCES Files(file_id)
);


-- Inserting datas
INSERT INTO Users
VALUES 
    ("a", "test"),
    ("b", "test"),
    ("c", "test"),
    ("d", "test"),
    ("e", "test"),
    ("noe", "prov");

INSERT INTO Paths
VALUES
    ("/a", "a", NULL),
    ("/a/coucou", "a", "/a"),
    ("/b", "b", NULL),
    ("/c", "c", NULL),
    ("/c/test", "c", "/c"),
    ("/d", "d", NULL),
    ("/e", "e", NULL),
    ("/n", "noe", NULL);

INSERT INTO Files
VALUES
    ("abcd", "un", 'a', "/a", 46.2054, 6.1459),
    ("ab", "deux", 'a', "/a", 46.2054, 6.1459),
    ("@dfsg", "trois", 'c', "/c/test", 46.2054, 6.1459),
    ("gbvaf", "quatre", 'b', "/b", 46.2054, 6.1459),
    ("dsfgh", "cinq", 'd', "/d", 46.2054, 6.1459),
    ("sdfa", "six.txt", 'e', "/e", 46.2054, 6.1459),
    ("liblb", "myfile.txt", 'noe', "/n", 46.2054, 6.1459);

INSERT INTO Shares
VALUES
    ("a", "b", "abcd"),
    ("a", "c", "abcd"),
    ("a", "d", "abcd"),
    ("c", "e", "@dfsg");


SELECT pseudo_2, Files.file_id, Files.file_name
FROM Shares LEFT JOIN Files ON Shares.file_id=Files.file_id
WHERE pseudo_2='b';