CREATE TABLE Match( /* Skill/job matchs expression/term according to France Travail API */
    match_identity MEDIUMINT NOT NULL AUTO_INCREMENT,
    extracted_term_or_expression_identity MEDIUMINT NOT NULL,
    skill_or_job VARCHAR2(250) NOT NULL,
    is_skill BOOLEAN,
    CONSTRAINT Match_key PRIMARY KEY(match_identity),
    CONSTRAINT Extracted_term_or_expression_foreign_key FOREIGN KEY(extracted_term_or_expression_identity) references Extracted_term_or_expression(extracted_term_or_expression_identity));
INSERT INTO Match VALUES(1,"FRANCE TRAVAIL compétence",TRUE);

CREATE TABLE Curriculum_vitae(
    curriculum_vitae_identity MEDIUMINT NOT NULL AUTO_INCREMENT,
    production_date DATE NOT NULL,
    production_place VARCHAR2(100) NOT NULL,
    surname VARCHAR2(100) NOT NULL,
    forname VARCHAR2(100) NOT NULL,
    birth_date DATE,
    identity_number VARCHAR(13),
    checksum VARCHAR(2),
    mobile_phone VARCHAR2(100) NOT NULL,
    e_mail VARCHAR2(100) NOT NULL,
    audio MEDIUMBLOB NOT NULL,
    video MEDIUMBLOB NOT NULL, 
/* Compte France Travail */
    CONSTRAINT Curriculum_vitae_key PRIMARY KEY(curriculum_vitae_identity));
INSERT INTO Curriculum_vitae VALUES(DATE('2024-09-14'),'Pau','Barbier','Franck',NULL,NULL,NULL,'+336060606','Franck.Barbier@FranckBarbier.com',"","");

CREATE TABLE Extracted_term_or_expression(
    extracted_term_or_expression_identity MEDIUMINT NOT NULL AUTO_INCREMENT,
    extracted_term_or_expression VARCHAR2(250) NOT NULL,
    is_term BOOLEAN,
    from TIME NOT NULL, /* Point temporel dans l'audio (début) */
    to TIME NOT NULL, /* Point temporel dans l'audio (fin) */
    curriculum_vitae_identity MEDIUMINT NOT NULL,
    CONSTRAINT Extracted_term_or_expression_key PRIMARY KEY(extracted_term_or_expression_identity),
    CONSTRAINT Curriculum_vitae_foreign_key FOREIGN KEY(curriculum_vitae_identity) references Curriculum_vitae(curriculum_vitae_identity));
INSERT INTO Extracted_term_or_expression VALUES('SOUDEUR',TRUE,'00:00:30','00:00:31',1);