-- Encodage UTF-8 pour la session
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;
SET collation_connection = 'utf8mb4_0900_ai_ci';

-- Création de la base de données
CREATE DATABASE IF NOT EXISTS theo2020_sp_partner
DEFAULT CHARACTER SET utf8mb4
DEFAULT COLLATE utf8mb4_0900_ai_ci;

USE theo2020_sp_partner;

-- Table des droits
CREATE TABLE IF NOT EXISTS `rights` (
    `right_id` VARCHAR(255) PRIMARY KEY,
    `type_right` VARCHAR(255),
    `description` VARCHAR(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table des partenaires
CREATE TABLE IF NOT EXISTS `partners` (
    `partner_id` VARCHAR(50) PRIMARY KEY,
    `fullname` VARCHAR(255) NOT NULL,
    `email` VARCHAR(200) DEFAULT NULL,
    `mobile` VARCHAR(20) DEFAULT NULL,
    `department` VARCHAR(200) DEFAULT NULL,
    `jobTitle` TEXT,
    `employeeType` VARCHAR(10) DEFAULT NULL,
    `status` VARCHAR(10) DEFAULT NULL,
    `startDate` VARCHAR(20) DEFAULT NULL,
    `endDate` VARCHAR(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Table de liaison entre partenaires et droits (many-to-many)
CREATE TABLE IF NOT EXISTS `partners_rights` (
    `partner_id` VARCHAR(50),
    `right_id` VARCHAR(255),
    PRIMARY KEY (`partner_id`, `right_id`),
    FOREIGN KEY (`partner_id`) REFERENCES `partners`(`partner_id`) ON DELETE CASCADE,
    FOREIGN KEY (`right_id`) REFERENCES `rights`(`right_id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Données de la table rights
INSERT INTO `rights` (`right_id`, `type_right`, `description`) VALUES
('financeAdminApi', 'Finance Admin API', 'Finance API Administrator'),
('supportAdminApi', 'Support Admin API', 'Support API Administrator'),
('marketingAdminApi', 'Marketing Admin API', 'Marketing API Administrator'),
('hrAdminApi', 'HR Admin API', 'HR API Administrator'),
('iamAdminApi', 'IAM Admin API', 'IAM API Administrator'),

('financeApi', 'Finance API', 'Finance API User'),
('supportApi', 'Support API', 'Support API User'),
('marketingApi', 'Marketing API', 'Marketing API User'),
('hrApi', 'HR API', 'HR API User'),
('iamApi', 'IAM API', 'IAM API User'),

('paymentValidationApi', 'Payment Validation API', 'Payment Validation API SaaS'),
('purchaseCreationApi', 'Purchase Creation API', 'Purchase Creation API SaaS');

-- Données de la table partners
INSERT INTO `partners` (`partner_id`, `fullname`, `email`, `mobile`, `department`, `jobTitle`, `employeeType`, `status`, `startDate`, `endDate`) VALUES
('STTI1231', 'Stephanie Tiago', 'stephanie.tiago@gmail.com', '+86782065678', 'Finance', 'MANAGER', 'Intern', 'A', '2016-12-12', '2050-12-12'),
('HEYA1232', 'Herve Sam Yameni', 'herve.yameni@gmail.com', '+62786639012', 'HR', 'MANAGER', 'Intern', 'A', '2017-09-14', '2049-12-12'),
('CEFO1233', 'Cedric Fotso Wafo', 'cedric.fotso@gmail.com', '+41772173456', 'Support IT', 'MANAGER', 'Intern', 'A', '2024-05-19', '2048-12-12'),
('YVDO1234', 'Yves Donfack', 'yves.donfack@gmail.com', '+23779957890', 'Marketing', 'MANAGER', 'Intern', 'A', '2018-01-05', '2047-12-12'),
('THTC1247', 'Theophile Tchoffo', 'theophile.tchoffo@gmail.com', '+41788130565', 'IAM', 'MANAGER', 'Intern', 'A', '2016-06-25', '2046-12-12');

-- Associations (exemple avec quelques utilisateurs)
INSERT INTO `partners_rights` (`partner_id`, `right_id`) VALUES
('STTI1231', 'financeAdminApi'),
('STTI1231', 'financeApi'),
('HEYA1232', 'financeAdminApi'),
('HEYA1232', 'financeApi'),
('CEFO1233', 'supportAdminApi'),
('CEFO1233', 'supportApi'),
('YVDO1234', 'marketingAdminApi'),
('YVDO1234', 'marketingApi'),
('THTC1247', 'iamApi'),
('THTC1247', 'iamAdminApi');