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

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

USE 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_general_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_general_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_general_ci;

-- Données de la table rights
INSERT INTO `rights` (`right_id`, `type_right`, `description`) VALUES
('paymentValidation', 'Payment Validation', 'Payment Validation Role'),
('purchaseCreation', 'Purchase Creation', 'Purchase Creation Role'),
('userManagement', 'User Management', 'Ability to create, update and delete users'),
('reportGeneration', 'Report Generation', 'Access to generate and view business reports'),
('contractApproval', 'Contract Approval', 'Right to review and approve partner contracts'),
('partnerManagement', 'Partner Management', 'Access to create and manage partner profiles'),
('dataExport', 'Data Export', 'Right to export system data in CSV or Excel format'),
('systemConfiguration', 'System Configuration', 'Right to modify system-level parameters and settings');

-- 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', '2036-12-12'),
('CEFO1233', 'Cedric Fotso Wafo', 'cedric.fotso@gmail.com', '+41772173456', 'Support IT', 'MANAGER', 'Intern', 'A', '2024-05-19', '2029-12-12'),
('YVDO1234', 'Yves Donfack', 'yves.donfack@gmail.com', '+23779957890', 'Marketing', 'MANAGER', 'Intern', 'A', '2018-01-05', '2036-12-12'),
('LUSA1235', 'Luca Sanchez', 'luca.sanchez@gmail.com', '+33789222345', 'Support IT', 'SUPPLIER', 'Intern', 'A', '2023-06-12', '2036-12-12'),
('DASO1236', 'David Sonkeng', 'david.sokeng@gmail.com', '+41766516789', 'Support IT', 'EMPLOYEE', 'Intern', 'A', '2017-08-04', '2033-12-12'),
('VITA1237', 'Vincent Paul Tamo', 'vincent.tamo@gmail.com', '+237675050123', 'Marketing', 'TRAINING', 'Intern', 'A', '2020-06-13', '2028-12-12'),
('RUMA1238', 'Ruth Maffo', 'ruth.maffo@gmail.com', '+41774998901', 'Support IT', 'EMPLOYEE', 'Intern', 'A', '2018-11-01', '2036-12-12'),
('ANZU1239', 'Anne Josee Zuber', 'anne.zuber@gmail.com', '+3375825234', 'HR', 'CUSTOMER', 'Extern', 'A', '2021-10-30', '2029-12-12'),
('LUBA1240', 'Lucius Bagnoli', 'lucius.bagnoli@gmail.com', '+3375825234', 'Marketing', 'SUPPLIER', 'Intern', 'A', '2021-10-30', '2035-09-20'),
('SEBO1241', 'Sean Bonnet', 'sean.bonnet@gmail.com', '+3375825234', 'HR', 'EMPLOYEE', 'Intern', 'A', '2021-10-30', '2029-12-12'),
('SABO1242', 'Samantha Bordwell', 'samantha.bordwell@yahoo.com', '+3375825000', 'HR', 'EMPLOYEE', 'Intern', 'A', '2022-10-30', '2031-12-12'),
('HEPH1243', 'Hendrika Philipet', 'hendrika.philipet@yahoo.com', '+866399215487', 'HR', 'TRAINING', 'Intern', 'A', '2016-12-12', '2035-12-12'),
('NAAY1244', 'Natasha Ayres', 'natasha.ayres@yahoo.com', '+622372058768', 'Support IT', 'EMPLOYEE', 'Intern', 'A', '2018-09-14', '2029-12-12'),
('COAL1245', 'Corenda Aldin', 'corenda.aldin@yahoo.com', '+625721431500', 'Marketing', 'EMPLOYEE', 'Intern', 'A', '2021-04-23', '2037-12-12'),
('EMBL1246', 'Emlynne Blazhevich', 'emlynne.blazhevich@yahoo.com', '+632053425605', 'Marketing', 'EMPLOYEE', 'Intern', 'A', '2014-10-28', '2035-12-12'),
('THTC1247', 'Theophile Tchoffo', 'theo.tchoffo@lordsolution.com', '+41788130565', 'IAM', 'MANAGER', 'Intern', 'A', '2016-06-25', '2049-12-12'),
('HEDJ1248', 'Heloise Djuidje Nono', 'heloisedivine@gmail.com', '+237694232616', 'IAM', 'CUSTOMER', 'Intern', 'A', '2019-11-12', '2028-12-12'),
('ALTC1249', 'Alain Bruno Tchapnda', 'atchapnda@yahoo.fr', '+237673511998', 'IAM', 'CUSTOMER', 'Intern', 'A', '2019-12-24', '2029-12-12'),
('FRYE1250', 'Franklin Yemeli', 'sokningyemeli@gmail.com', '+33749764049', 'IAM', 'CUSTOMER', 'Intern', 'A', '2016-11-28', '2030-12-12'),
('EMSA1253', 'Emmanuel Saha', 'emmanuel.saha@gmail.com', '+237690000004', 'IAM', 'MANAGER', 'Intern', 'A', '2024-11-28', '2030-12-12'),
('ROKE1251', 'Robert Kenne', 'robert.kenne@gmail.com', '+237690000002', 'IAM', 'TRAINING', 'Intern', 'A', '2024-11-12', '2029-12-12'),
('STKA1252', 'Stephane Kamnang', 'stephane.kamnang@gmail.com', '+237690000003', 'IAM', 'CUSTOMER', 'Extern', 'A', '2024-10-24', '2029-12-12'),
('USTE1255', 'Usaman Tezigue', 'usaman.tezigue@gmail.com', '+237690000010', 'Marketing', 'CUSTOMER', 'Intern', 'A', '2016-12-12', '2028-12-12'),
('TEUS1254', 'Teddy Ustoki', 'teddy.ustoki@gmail.com', '+237690002009', 'IAM', 'TRAINING', 'Extern', 'I', '2024-10-24', '2025-10-08'),
('ALMO1256', 'Alice Mofack', 'alice.mofack@gmail.com', '+41782065678', 'Finance', 'MANAGER', 'Intern', 'A', '2018-12-12', '2040-12-12'),
('JUTE1257', 'Julien Teguia', 'julien.teguia@gmail.com', '+23782123456', 'Finance', 'EMPLOYEE', 'Extern', 'A', '2021-12-12', '2030-12-12');

-- Associations (exemple avec quelques utilisateurs)
INSERT INTO `partners_rights` (`partner_id`, `right_id`) VALUES
('STTI1231', 'paymentValidation'),
('STTI1231', 'partnerManagement'),

('HEYA1232', 'purchaseCreation'),
('HEYA1232', 'userManagement'),

('CEFO1233', 'reportGeneration'),
('CEFO1233', 'partnerManagement'),

('YVDO1234', 'paymentValidation'),
('YVDO1234', 'contractApproval'),

('LUSA1235', 'purchaseCreation'),
('LUSA1235', 'dataExport'),

('DASO1236', 'reportGeneration'),
('DASO1236', 'dataExport'),

('VITA1237', 'purchaseCreation'),
('VITA1237', 'reportGeneration'),

('RUMA1238', 'paymentValidation'),
('RUMA1238', 'dataExport'),

('ANZU1239', 'userManagement'),
('ANZU1239', 'partnerManagement'),

('LUBA1240', 'contractApproval'),
('LUBA1240', 'dataExport'),

('SEBO1241', 'purchaseCreation'),
('SEBO1241', 'userManagement'),

('SABO1242', 'partnerManagement'),
('SABO1242', 'reportGeneration'),

('HEPH1243', 'contractApproval'),
('HEPH1243', 'paymentValidation'),

('NAAY1244', 'reportGeneration'),
('NAAY1244', 'dataExport'),

('COAL1245', 'purchaseCreation'),
('COAL1245', 'contractApproval'),

('EMBL1246', 'paymentValidation'),
('EMBL1246', 'reportGeneration'),

('THTC1247', 'systemConfiguration'),
('THTC1247', 'partnerManagement'),

('HEDJ1248', 'purchaseCreation'),
('HEDJ1248', 'reportGeneration'),

('ALTC1249', 'contractApproval'),
('ALTC1249', 'dataExport'),

('FRYE1250', 'userManagement'),
('FRYE1250', 'reportGeneration'),

('ROKE1251', 'paymentValidation'),
('ROKE1251', 'purchaseCreation'),

('STKA1252', 'partnerManagement'),
('STKA1252', 'contractApproval'),

('EMSA1253', 'systemConfiguration'),
('EMSA1253', 'userManagement'),

('TEUS1254', 'paymentValidation'),
('TEUS1254', 'reportGeneration'),

('USTE1255', 'purchaseCreation'),
('USTE1255', 'dataExport'),

('ALMO1256', 'purchaseCreation'),
('ALMO1256', 'paymentValidation'),

('JUTE1257', 'reportGeneration'),
('JUTE1257', 'paymentValidation');
