sql_queries

CREATE DATABASE project_kgb;

use project_kgb;

CREATE TABLE Admins (
    id_admin INT PRIMARY KEY NOT NULL AUTO_INCREMENT,
    name_admin VARCHAR(50) NOT NULL,
    firstname_admin VARCHAR(50) NOT NULL,
    email_admin VARCHAR(100) NOT NULL,
    password_admin VARCHAR(100) NOT NULL,
    creation_admin TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
    ON UPDATE CURRENT_TIMESTAMP,
    secret_admin VARCHAR(100) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Targets (
    code_target VARCHAR(50) PRIMARY KEY UNIQUE NOT NULL,
    name_target VARCHAR(50) NOT NULL,
    firstname_target VARCHAR(50) NOT NULL,
    datebirthday_target DATE NOT NULL,
    nationality_target VARCHAR(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Contacts (
    code_contact VARCHAR(50) PRIMARY KEY UNIQUE NOT NULL,
    name_contact VARCHAR(50) NOT NULL,
    firstname_contact VARCHAR(50) NOT NULL,
    datebirthday_contact DATE NOT NULL,
    nationality_contact VARCHAR(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Agents (
    id_agent INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
    name_agent VARCHAR(50) NOT NULL,
    firstname_agent VARCHAR(50) NOT NULL,
    datebirthday_agent DATE NOT NULL,
    nationality_agent VARCHAR(50) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Specialities (
    name_speciality VARCHAR(50) PRIMARY KEY UNIQUE NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Specialities_agents (
    name_speciality VARCHAR(50) NOT NULL,
    id_agent INT NOT NULL,
    PRIMARY KEY (name_speciality, id_agent),
    CONSTRAINT fk_name_speciality
    FOREIGN KEY (name_speciality) REFERENCES Specialities (name_speciality) 
    ON UPDATE CASCADE ON DELETE CASCADE,
    KEY id_agent (id_agent)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Hideouts (
    id_hideout INT PRIMARY KEY UNIQUE NOT NULL,
    address_hideout TEXT NOT NULL,
    country_hideout VARCHAR(50) NOT NULL,
    type_hideout VARCHAR(30) NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Durations (
    id_duration INT PRIMARY KEY UNIQUE NOT NULL,
    start_duration DATE NOT NULL,
    end_duration DATE NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Status (
    code_status VARCHAR(20) PRIMARY KEY UNIQUE NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Types (
    name_type VARCHAR(30) PRIMARY KEY UNIQUE NOT NULL
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Missions (
    code_mission VARCHAR(50) PRIMARY KEY UNIQUE NOT NULL,
    title_mission VARCHAR(100) NOT NULL,
    description_mission TEXT,
    country_mission VARCHAR(50) NOT NULL,
    id_duration INT NOT NULL,
    CONSTRAINT fk_id_duration
    FOREIGN KEY (id_duration) REFERENCES Durations (id_duration)
    ON UPDATE CASCADE ON DELETE CASCADE,
    code_status VARCHAR(20) NOT NULL,
    CONSTRAINT fk_code_status
    FOREIGN KEY (code_status) REFERENCES Status (code_status)
    ON UPDATE CASCADE ON DELETE CASCADE,
    name_type VARCHAR(30) NOT NULL,
    CONSTRAINT fk_name_type
    FOREIGN KEY (name_type) REFERENCES Types (name_type)ON
    UPDATE CASCADE ON DELETE CASCADE,
    KEY name_speciality (name_speciality)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Targets_missions (
    code_target VARCHAR(50) NOT NULL,
    code_mission VARCHAR(50) NOT NULL,
    PRIMARY KEY (code_target, code_mission),
    CONSTRAINT fk_code_target
    FOREIGN KEY (code_target) REFERENCES Targets (code_target) 
    ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_code_mission
    FOREIGN KEY (code_mission) REFERENCES Missions (code_mission) 
    ON UPDATE CASCADE ON DELETE CASCADE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Agents_missions (
    id_agent INT NOT NULL,
    code_mission VARCHAR(50) NOT NULL,
    PRIMARY KEY (id_agent, code_mission),
    CONSTRAINT fk_id_agent
    FOREIGN KEY (id_agent) REFERENCES Agents (id_agent) 
    ON UPDATE CASCADE ON DELETE CASCADE,
    KEY code_mission (code_mission)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Contacts_missions (
    code_contact VARCHAR(50) NOT NULL,
    code_mission VARCHAR(50) NOT NULL,
    PRIMARY KEY (code_contact, code_mission),
    CONSTRAINT fk_code_contact
    FOREIGN KEY (code_contact) REFERENCES Contacts (code_contact)
    ON UPDATE CASCADE ON DELETE CASCADE,
    KEY code_mission (code_mission)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE Hideouts_missions (
    id_hideout INT NOT NULL,
    code_mission VARCHAR(50) NOT NULL,
    PRIMARY KEY (id_hideout, code_mission),
    CONSTRAINT fk_id_hideout
    FOREIGN KEY (id_hideout) REFERENCES Hideouts (id_hideout) 
    ON UPDATE CASCADE ON DELETE CASCADE,
    KEY code_mission (code_mission)
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


/* Insertion de quelques données en dur : */

INSERT INTO Admins (name_admin, firstname_admin, email_admin, password_admin, secret_admin) VALUES ('NameExample', 'FirstNameExample',
'emailExample@gmail.com', SHA1('example'), SHA1('example'));

/* In the login function of the AdminController for instance, add the line code and copy the hashed password : 
echo password_hash("passwordToHash", PASSWORD_DEFAULT);
Then, replace 'hashedPassword' by the hashed password :
*/
UPDATE `Admins` SET `password_admin` = 'hashedPassword' WHERE `Admins`.`id_admin` = 1;

INSERT INTO Types (name_type) VALUES ('Surveillance');

INSERT INTO Status (code_status) VALUES ('en preparation');

INSERT INTO Durations (id_duration, start_duration, end_duration) VALUES (1, '2022-07-19', '2022-09-19');

INSERT INTO Missions (code_mission, title_mission, description_mission, country_mission, id_duration, code_status, name_type) 
VALUES ('Hercules', 'Surveiller un pirate informatique','Cette mission consiste à surveiller un hackeur', 'France', 1, 'en
préparation', 'Surveillance');