create table Article(
	reference varchar(30),
	designation varchar(30) not null,
	type_fabrication_achat varchar(30) not null,
	unite_achat_stock varchar(30) not null,
	delai_en_semaine integer not null,
	prix_standard float,
	lot_de_reapprovisionnement integer,
	stock_mini integer,
	stock_maxi integer,
	pourcentage_de_perte float,
	inventaire integer,
	PF_ou_MP_ou_Piece_ou_SE char(2) not null,
	constraint Article_pk primary key(reference),
	constraint Article_unique unique(designation),
	constraint Article_check check(PF_ou_MP_ou_Piece_ou_SE = 'PF' or PF_ou_MP_ou_Piece_ou_SE = 'MP' or PF_ou_MP_ou_Piece_ou_SE = 'Pi' or PF_ou_MP_ou_Piece_ou_SE = 'SE'));

create table Lien_de_nomenclature(
	-- lien_de_nomenclature_id long,
	compose varchar(30),
	composant varchar(30),
	quantite_de_composition float not null,
	constraint Lien_de_nomenclature_pk primary key(compose,composant),
	constraint Lien_de_nomenclature_fk_1 foreign key(compose) references Article(reference) on delete cascade,
	constraint Lien_de_nomenclature_fk_2 foreign key(composant) references Article(reference) on delete cascade);
	-- constraint Lien_de_nomenclature_check check(compose <> composant)

create table Remplacement(
	remplace_compose varchar(30),
	remplace_composant varchar(30),
 	remplacant_compose varchar(30),
	remplacant_composant varchar(30),
	date_de_remplacement date not null,
	-- constraint Remplacement_pk primary key(remplace_compose,remplace_composant,remplacant_compose,remplacant_composant),
	constraint Remplacement_fk_1 foreign key(remplace_compose,remplace_composant) references Lien_de_nomenclature(compose,composant) on delete cascade,
	constraint Remplacement_fk_2 foreign key(remplacant_compose,remplacant_composant) references Lien_de_nomenclature(compose,composant) on delete cascade);
	-- constraint Remplacement_check check(remplace <> remplacant)

create table Poste_de_charge(
	numero_section integer,
	numero_sous_section integer,
	est_machine smallint,
	designation varchar(30) not null,
	taux_horaire_ou_forfait integer not null,
	nombre_de_postes integer not null,
	capacite_nominale integer not null,
	type_taux_horaire_ou_forfait char(2) not null,
	constraint Poste_de_charge_pk primary key(numero_section,numero_sous_section,est_machine),
	constraint Poste_de_charge_check_1 check(est_machine = 0 or est_machine = 1),
	constraint Poste_de_charge_check_2 check(type_taux_horaire_ou_forfait = 'TH' or type_taux_horaire_ou_forfait = 'F'));

create table Operation(
	gamme_de_fabrication varchar(30),
	numero_operation integer,
	machine_numero_section integer,
	machine_numero_sous_section integer,
	machine_est_machine smallint,
	main_d_oeuvre_numero_section integer,
	main_d_oeuvre_numero_sous_section integer,
	main_d_oeuvre_est_machine smallint,
	temps_preparation integer,
	temps_execution integer,
	temps_transfert integer not null,
	libelle_operation varchar(30) not null,
	constraint Operation_pk primary key(gamme_de_fabrication,numero_operation),
	constraint Operation_fk1 foreign key(gamme_de_fabrication) references Article(reference) on delete cascade,
	constraint Operation_fk2 foreign key(machine_numero_section,machine_numero_sous_section,machine_est_machine) references Poste_de_charge(numero_section,numero_sous_section,est_machine),
	constraint Operation_fk3 foreign key(main_d_oeuvre_numero_section,main_d_oeuvre_numero_sous_section,main_d_oeuvre_est_machine) references Poste_de_charge(numero_section,numero_sous_section,est_machine),
	constraint Operation_check_1 check(machine_est_machine = 1),
	constraint Operation_check_2 check(main_d_oeuvre_est_machine = 0));
	
create table Mouvement_de_stock(
	reference varchar(30),
	numero_magasin integer,
	quantite integer,
	periode date,
	type char(6) not null,
	-- constraint Mouvement_de_stock_pk primary key(reference,periode,type),
	constraint Mouvement_de_stock_fk foreign key(reference) references Article(reference),
	constraint Mouvement_de_stock_check check(type = 'Entree' or type = 'Sortie'));

insert into Article values('ROUE50','roue de camion','achat par lot','unite',6,1.5,500,500,2000,null,1850,'Pi');
insert into Article values('ES000','essieu monte','fabr. par lot','unite',2,null,500,750,1500,null,null,'SE');
insert into Article values('CH005','chassis monte','fabr. par lot','unite',1,null,300,null,900,null,null,'SE');

insert into Article values('H000','conteneur bleu','fabr. par lot','unite',1,null,150,350,800,null,null,'SE');
insert into Article values('H001','conteneur bleu special','fabr. a la commande','unite',1,null,150,350,null,null,null,'SE');
insert into Article values('CD100','camion demenagement bleu','fabr. par lot','unite',2,null,200,null,600,null,null,'PF');

insert into Lien_de_nomenclature values('ES000','ROUE50',2);
insert into Lien_de_nomenclature values('CH005','ES000',2);
insert into Lien_de_nomenclature values('CD100','CH005',1);

insert into Lien_de_nomenclature values('CD100','H000',1);
insert into Lien_de_nomenclature values('CD100','H001',1);

insert into Remplacement values('CD100','H000','CD100','H001',DATE('2007-09-13'));

insert into Poste_de_charge values(500,450,1,'Rectifieuse',80,1,39,'TH');
insert into Poste_de_charge values(500,450,0,'Rectifieur',80,1,39,'TH');

insert into Operation values('ES000',020,500,450,1,500,450,0,0.5,0.05,0.2,'Rectification');

SELECT * FROM Article WHERE PF_ou_MP_ou_Piece_ou_SE = 'PF'; 
SELECT reference FROM Article ORDER BY designation DESC WHERE PF_ou_MP_ou_Piece_ou_SE = 'PF'; 

CREATE TABLE GPAO_XML_data(XML_data XML);
INSERT INTO GPAO_XML_data(XML_data) VALUES(XMLPARSE(DOCUMENT '<GPAO>
    <CD100>
        <reference>CD100</reference>
        <designation>camion demenagement bleu</designation>
        <type_fabrication_achat>fabr. par lot</type_fabrication_achat>
        <unite_achat_stock>unite</unite_achat_stock>
        <delai_en_semaine>2</delai_en_semaine>
        <!--<prix_standard></prix_standard>-->
        <lot_de_reapprovisionnement>200</lot_de_reapprovisionnement>
        <!--<stock_mini></stock_mini>-->
        <stock_maxi>600</stock_maxi>
        <!--<pourcentage_de_perte></pourcentage_de_perte>-->
        <!--<inventaire></inventaire>-->
        <PF_ou_MP_ou_Piece_ou_SE>PF</PF_ou_MP_ou_Piece_ou_SE>
    </CD100>
    <!--Etc.-->
</GPAO>' PRESERVE WHITESPACE));
-- 'CLOB' <=> "Character Large OBject"
SELECT XMLSERIALIZE(XML_data AS CLOB) FROM GPAO_XML_data;
