PRAGMA writable_schema = 1;
PRAGMA auto_vacuum = 1;
PRAGMA encoding = "UTF-8";
PRAGMA user_version = 42;

CREATE TABLE Parametres(
_id INTEGER PRIMARY KEY,
Synchronise INTEGER NOT NULL, 
AgentId INTEGER,
IsBusinessGeographicEnabled INTEGER NOT NULL DEFAULT 0, 
IsAgentMagasinier INTEGER NOT NULL,
NomUtilisateurAtal TEXT);

CREATE TABLE Synchro(
_id INTEGER PRIMARY KEY,
Identifiant TEXT NOT NULL, 
DateDerniereSynchronisation TEXT NOT NULL);
					
CREATE TABLE Interventions(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,                            
Lieu TEXT, 
Numero TEXT,
DateDemande TEXT NOT NULL,
Objet TEXT NOT NULL,
Description TEXT,
NatureActiviteId INTEGER,
EtatTravaux INTEGER,
TiersDemandeurId INTEGER NOT NULL,
CoordonneeX TEXT,
CoordonneeY TEXT,
PrioriteId INTEGER,
ProjetId INTEGER,
Demandeur TEXT,
TiersGestionnaireId INTEGER);
							
CREATE TABLE BonsDeTravaux(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
Numero TEXT, 
InterventionId INTEGER NOT NULL REFERENCES Interventions ON UPDATE CASCADE ON DELETE CASCADE, 
ServiceExecutantId INTEGER NOT NULL, 
TypeActiviteId INTEGER, 
DescriptionDesTravaux TEXT, 
DateProgrammeeDebut TEXT, 
DateProgrammeeFin TEXT, 
Observations TEXT, 
EtatTravaux INTEGER NOT NULL, 
MaitreDOeuvreId INTEGER,
PrioriteId INTEGER,
QteUniteTravaux NUMERIC);

CREATE TABLE DemandesDeTravaux(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
NumeroDemande TEXT, 
Lieu TEXT, 
Objet TEXT NOT NULL, 
Description TEXT, 
CoordonneeX REAL, 
CoordonneeY REAL);

CREATE TABLE NaturesDActivite (
_id INTEGER PRIMARY KEY,
LibelleComplet TEXT NOT NULL,
Archive NUMERIC NOT NULL,
Autorise NUMERIC NOT NULL);

CREATE TABLE NaturesDesTravaux (
_id INTEGER PRIMARY KEY,
Libelle TEXT NOT NULL);

CREATE TABLE Projet (
_id INTEGER PRIMARY KEY,
Libelle TEXT NOT NULL,
Numero TEXT NOT NULL,
EtatTravaux INTEGER NOT NULL);

CREATE TABLE ObjetsDesInterventions(
_id INTEGER PRIMARY KEY, 
Libelle TEXT NOT NULL);							
							
CREATE TABLE PiecesJointes(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
MimeType TEXT, 
Fichier BLOB, 
NomDuFichier TEXT, 
Libelle TEXT);

CREATE TABLE TypesHeure(
_id INTEGER PRIMARY KEY, 
Libelle TEXT NOT NULL);

CREATE TABLE HeuresSupplementaires(
_id INTEGER PRIMARY KEY, 
Taux NUMERIC NOT NULL, 
Libelle TEXT NOT NULL);

CREATE TABLE OrdresDeReparation(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
Numero TEXT, 
TiersExecutantId INTEGER, 
Objet TEXT, 
Description TEXT, 
DateProgrammeeDebut TEXT, 
DateProgrammeeFin TEXT, 
ServiceExecutantId INTEGER NOT NULL, 
EtatTravaux INTEGER, 
Compteur INTEGER NOT NULL,
NatureActiviteId INTEGER,
VehiculeId INTEGER NOT NULL,
DateOR TEXT NOT NULL, 
MaitreDOeuvreId INTEGER,
PrioriteId INTEGER);	

CREATE TABLE Taches(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
TypeTache INTEGER NOT NULL, 
Numero TEXT, 
ServiceExecutantId INTEGER NOT NULL, 
NatureActiviteId INTEGER, 
TypeActiviteId INTEGER, 
NatureActivite INTEGER, 
Objet TEXT, 
Observations TEXT, 
Date TEXT NOT NULL,
EtatTravaux INTEGER);

CREATE TABLE TypeFrais(
_id INTEGER PRIMARY KEY,
Libelle TEXT NOT NULL,
TauxFrais NUMERIC NOT NULL);

CREATE TABLE HeuresAgent(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
AgentId INTEGER, 
ServiceId INTEGER NOT NULL, 
HeureSupplementaireId INTEGER REFERENCES HeuresSupplementaires ON UPDATE CASCADE, 
DateRealisation TEXT NOT NULL, 
NbHeures NUMERIC NOT NULL, 
TauxHoraire NUMERIC NOT NULL, 
TauxHeuresSupplementaires NUMERIC NOT NULL, 
Facturable NUMERIC NOT NULL, 
BonDeTravauxId INTEGER REFERENCES BonsDeTravaux ON UPDATE CASCADE ON DELETE CASCADE, 
OrdreDeReparationId INTEGER REFERENCES OrdresDeReparation ON UPDATE CASCADE ON DELETE CASCADE,
TacheId INTEGER REFERENCES Taches ON UPDATE CASCADE ON DELETE CASCADE,
Libelle TEXT,
TypeActiviteId INTEGER,
TypeHeureId INTEGER,
TypeFraisId INTEGER REFERENCES TypeFrais ON UPDATE CASCADE,
NbFrais NUMERIC NOT NULL DEFAULT 0,
TauxFrais NUMERIC NOT NULL DEFAULT 0);							
							
CREATE TABLE Patrimoines(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
Text TEXT NOT NULL, 
Libelle TEXT NOT NULL, 
TypePatrimoine INTEGER NOT NULL, 
CompleteText TEXT NOT NULL, 
PatrimoinePereId INTEGER, 
NumeroPatrimoine1 TEXT, 
NumeroPatrimoine2 TEXT, 
NumeroPatrimoine3 TEXT, 
Quantite NUMERIC NOT NULL,
CodeAnalytique TEXT,
Autorise NUMERIC NOT NULL,
Affiche NUMERIC NOT NULL,
NaturePatrimoineId INTEGER,
ArchiveMobile NUMERIC NOT NULL,
EtatPatrimoineId INTEGER,
TiersId INTEGER);		

CREATE TABLE Equipements(
_id INTEGER PRIMARY KEY REFERENCES Patrimoines ON UPDATE CASCADE ON DELETE CASCADE, 
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
Latitude NUMERIC, 
Longitude NUMERIC,
Observations TEXT);

CREATE TABLE InterventionPatrimoine(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
InterventionId INTEGER NULL REFERENCES Interventions ON UPDATE CASCADE ON DELETE CASCADE, 
OrdreDeReparationId INTEGER NULL REFERENCES OrdresDeReparation ON UPDATE CASCADE ON DELETE CASCADE, 
PatrimoineId INTEGER NOT NULL REFERENCES Patrimoines ON UPDATE CASCADE ON DELETE CASCADE,
TacheId INTEGER NULL REFERENCES Taches ON UPDATE CASCADE ON DELETE CASCADE,
Ordre INTEGER NULL);						

CREATE TABLE Adresses(
_id INTEGER PRIMARY KEY,
AdresseResume TEXT,
Adresse1 TEXT,
CodePostal TEXT,
Ville TEXT);
							
CREATE TABLE PatrimoinesAdresses(
PatrimoineId INTEGER NOT NULL REFERENCES Patrimoines ON UPDATE CASCADE ON DELETE CASCADE, 
AdresseId INTEGER NOT NULL REFERENCES Adresses ON UPDATE CASCADE ON DELETE CASCADE, 
PRIMARY KEY (PatrimoineId, AdresseId));

CREATE TABLE Tiers(
_id INTEGER PRIMARY KEY,
Nom TEXT NOT NULL, 
Prenom TEXT, 
Numero TEXT, 
CodeComplet TEXT, 
CurrentTiersParentId INTEGER, 
Type INTEGER NOT NULL,
Restricted INTEGER NOT NULL DEFAULT 1,
RestrictedExecutantOR INTEGER NOT NULL DEFAULT 1,
RestrictedDemandeur INTEGER NOT NULL DEFAULT 1,
RestrictedGestionnaireInter INTEGER NOT NULL DEFAULT 1,
CodeAnalytique TEXT);

							
CREATE TABLE TiersAgent(
_id INTEGER PRIMARY KEY REFERENCES Tiers ON UPDATE CASCADE ON DELETE CASCADE,
TauxHoraire NUMERIC NOT NULL);

CREATE TABLE Menus(_id INTEGER PRIMARY KEY);

CREATE TABLE ConfigurationApplication (
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
ValeurTexte TEXT, 
ValeurBool NUMERIC NOT NULL, 
ValeurInt INTEGER, 
ValeurReel NUMERIC);
							
CREATE TABLE ConfigurationUtilisateur(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
ValeurTexte TEXT, 
ValeurBool NUMERIC NOT NULL, 
ValeurInt INTEGER);

CREATE TABLE Actions(
_id INTEGER PRIMARY KEY,
Fonction INTEGER NOT NULL, 
TypeAction INTEGER NOT NULL, 
Valeur1 NUMERIC NOT NULL, 
Valeur2 NUMERIC NOT NULL, 
Valeur3 NUMERIC NOT NULL);						

CREATE TABLE TypesDActivite(
_id INTEGER PRIMARY KEY,
LibelleComplet TEXT NOT NULL,
ParcAuto NUMERIC NOT NULL,
UniteTravaux TEXT);		

CREATE TABLE EtatsControles(
_id INTEGER PRIMARY KEY,
Libelle TEXT NOT NULL,
CategorieEtatsId INTEGER NOT NULL);		

CREATE TABLE ModelesControle(
_id INTEGER PRIMARY KEY,
Libelle TEXT NOT NULL, 
CategorieEtatsId INTEGER NOT NULL,
TypeControle INTEGER NOT NULL,
MasqueTypePatrimoine INTEGER NOT NULL,
MasqueTypeBon INTEGER NOT NULL,
NatureActiviteId INTEGER,
NbOccurrenceMaxi INTEGER,
EtatControleDefautId INTEGER REFERENCES EtatsControles ON UPDATE CASCADE);

CREATE TABLE Controles(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
ControlePereId INTEGER REFERENCES Controles ON UPDATE CASCADE,
PatrimoineId INTEGER NOT NULL REFERENCES Patrimoines ON UPDATE CASCADE,
ModeleControleId INTEGER NOT NULL REFERENCES ModelesControle ON UPDATE CASCADE,
Periodicite1 TEXT,
Periodicite2 TEXT,
DateDebut TEXT NOT NULL,
DateFin TEXT,
Observations TEXT,
DateDernierControle TEXT, 
DateProchainControle TEXT,
CompteurDernierControle INTEGER,
CompteurProchainControle INTEGER,
DernierInterventionControleId INTEGER,
NbOccurrenceMaxi INTEGER,
NbControlesEffectues INTEGER NOT NULL,
Alertable INTEGER NOT NULL,
NbJourAnticipationAlerte INTEGER NOT NULL,
CompteurAnticipationAlerte INTEGER NOT NULL);

CREATE TABLE InterventionControle(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
Date TEXT NOT NULL, 
EtatControleId INTEGER REFERENCES EtatsControles ON UPDATE CASCADE, 
InterventionPatrimoineId INTEGER REFERENCES InterventionPatrimoine ON UPDATE CASCADE ON DELETE CASCADE,
Commentaires TEXT,
CompteurControle INTEGER,
ControleId INTEGER NOT NULL REFERENCES Controles ON UPDATE CASCADE,
NumeroBon TEXT);
							
CREATE TABLE HeuresVehicule(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
VehiculeId INTEGER NOT NULL, 
DateRealisation TEXT NOT NULL, 
NbHeures NUMERIC NOT NULL, 
NbKm INTEGER NOT NULL, 
NbKmSupplementaires INTEGER NOT NULL, 
NbForfaits NUMERIC NOT NULL, 
CoutHoraire NUMERIC NOT NULL, 
CoutKmNormal NUMERIC NOT NULL, 
CoutKmSupplementaire v NOT NULL, 
CoutForfaitaire NUMERIC NOT NULL, 
TypeCoutUtilisationVehicule INTEGER NOT NULL, 
Facturable NUMERIC NOT NULL, 
BonDeTravauxId INTEGER REFERENCES BonsDeTravaux ON UPDATE CASCADE ON DELETE CASCADE, 
OrdreDeReparationId INTEGER REFERENCES OrdresDeReparation ON UPDATE CASCADE ON DELETE CASCADE, 
TacheId INTEGER REFERENCES Taches ON UPDATE CASCADE ON DELETE CASCADE);

CREATE TABLE Vehicules (
_id INTEGER PRIMARY KEY REFERENCES Patrimoines ON UPDATE CASCADE ON DELETE CASCADE, 
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
Utilitaire NUMERIC NOT NULL,
Affecte NUMERIC NOT NULL DEFAULT 0, 
TypeCoutUtilisation INTEGER NOT NULL, 
CoutHoraire NUMERIC NOT NULL, 
CoutKilometriqueNormal NUMERIC NOT NULL, 
CoutKilometriqueSupplementaire NUMERIC NOT NULL, 
CoutForfaitaire NUMERIC NOT NULL,
ORV NUMERIC NOT NULL,
Compteur INTEGER NOT NULL);				
							
CREATE TABLE PointsAVerifier(
_id INTEGER PRIMARY KEY,
ModeleControleId INTEGER NOT NULL REFERENCES ModelesControle ON UPDATE CASCADE ON DELETE CASCADE, 
CategorieEtatsId INTEGER NOT NULL, 
Libelle TEXT,
EtatControleDefautId INTEGER REFERENCES EtatsControles ON UPDATE CASCADE);
							
CREATE TABLE InterventionControlePointsAVerifier(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
InterventionControleId INTEGER NOT NULL REFERENCES InterventionControle ON UPDATE CASCADE ON DELETE CASCADE, 
PointAVerifierId INTEGER NOT NULL REFERENCES PointsAVerifier ON UPDATE CASCADE ON DELETE CASCADE, 
EtatId INTEGER REFERENCES EtatsControles ON UPDATE CASCADE,
Commentaires TEXT);	

CREATE TABLE CategoriesCaracteristique(
_id INTEGER PRIMARY KEY, 
Libelle TEXT NOT NULL, 
TypePatrimoine INTEGER NOT NULL,
Ordre INTEGER NOT NULL,
Actif INTEGER NOT NULL);

CREATE TABLE GroupesCaracteristique(
_id INTEGER PRIMARY KEY, 
CategorieId INTEGER NOT NULL REFERENCES CategoriesCaracteristique ON UPDATE CASCADE ON DELETE CASCADE, 
Libelle TEXT NOT NULL, 
Ordre INTEGER NOT NULL);

CREATE TABLE CaracteristiqueListes(
_id INTEGER PRIMARY KEY,
CaracteristiqueId INTEGER NOT NULL, 
ParentId INTEGER REFERENCES CaracteristiqueListes ON UPDATE CASCADE ON DELETE CASCADE, 
Libelle TEXT NOT NULL);			

CREATE TABLE Caracteristiques(
_id INTEGER PRIMARY KEY,
CaracteristiquePereId INTEGER REFERENCES Caracteristiques ON UPDATE CASCADE ON DELETE CASCADE, 
CategorieId INTEGER NOT NULL REFERENCES CategoriesCaracteristique ON UPDATE CASCADE ON DELETE CASCADE, 
TypeValeurCaracteristique INTEGER NOT NULL, 
Libelle TEXT NOT NULL, 
Ordre INTEGER NOT NULL, 
Identifiant INTEGER, 
Unite TEXT, 
Obligatoire NUMERIC NOT NULL, 
ModeleCaracteristiqueId INTEGER, 
Releve NUMERIC NOT NULL, 
Actif NUMERIC NOT NULL,
GroupeId INTEGER REFERENCES GroupesCaracteristique ON UPDATE CASCADE ON DELETE CASCADE);

CREATE TABLE CaracteristiqueValeurDefaut(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
ElementDeclaratifId INTEGER, 
CaracteristiqueId INTEGER NOT NULL REFERENCES Caracteristiques ON UPDATE CASCADE ON DELETE CASCADE, 
CaracteristiqueListeId INTEGER REFERENCES CaracteristiqueListes ON UPDATE CASCADE ON DELETE CASCADE, 
ValeurNumerique NUMERIC,
ValeurDate TEXT, 
ValeurTexte TEXT);

CREATE TABLE EquipementValeurCaracteristique(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
PatrimoineId INTEGER NOT NULL REFERENCES Patrimoines ON UPDATE CASCADE ON DELETE CASCADE, 
CaracteristiqueId INTEGER NOT NULL REFERENCES Caracteristiques ON UPDATE CASCADE ON DELETE CASCADE, 
CaracteristiqueListeId INTEGER REFERENCES CaracteristiqueListes ON UPDATE CASCADE ON DELETE CASCADE, 
ValeurNumerique TEXT, 
ValeurDate TEXT, 
ValeurTexte TEXT, 
DateReleve TEXT,
PieceJointeId INTEGER REFERENCES PiecesJointes ON UPDATE CASCADE ON DELETE CASCADE);			

CREATE TABLE ElementValeurCaracteristique(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
PatrimoineId INTEGER NOT NULL REFERENCES Patrimoines ON UPDATE CASCADE ON DELETE CASCADE, 
CaracteristiqueId INTEGER NOT NULL REFERENCES Caracteristiques ON UPDATE CASCADE ON DELETE CASCADE, 
CaracteristiqueListeId INTEGER REFERENCES CaracteristiqueListes ON UPDATE CASCADE ON DELETE CASCADE, 
ValeurNumerique TEXT, 
ValeurDate TEXT, 
ValeurTexte TEXT, 
DateReleve TEXT,
PieceJointeId INTEGER REFERENCES PiecesJointes ON UPDATE CASCADE ON DELETE CASCADE);							

CREATE TABLE InterventionsPieceJointe(
InterventionId INTEGER NOT NULL REFERENCES Interventions ON UPDATE CASCADE ON DELETE CASCADE,
PieceJointeId INTEGER NOT NULL REFERENCES PiecesJointes ON UPDATE CASCADE ON DELETE CASCADE, 
PRIMARY KEY (InterventionId, PieceJointeId));
									
CREATE TABLE BonsDeTravauxPieceJointe(
BonDeTravauxId INTEGER NOT NULL REFERENCES BonsDeTravaux ON UPDATE CASCADE ON DELETE CASCADE, 
PieceJointeId INTEGER NOT NULL REFERENCES PiecesJointes ON UPDATE CASCADE ON DELETE CASCADE, 
PRIMARY KEY (BonDeTravauxId, PieceJointeId));

CREATE TABLE OrdresDeReparationPieceJointe(
OrdreDeReparationId INTEGER NOT NULL REFERENCES OrdresDeReparation ON UPDATE CASCADE ON DELETE CASCADE, 
PieceJointeId INTEGER NOT NULL REFERENCES PiecesJointes ON UPDATE CASCADE ON DELETE CASCADE, 
PRIMARY KEY (OrdreDeReparationId, PieceJointeId));

CREATE TABLE DemandesDeTravauxPieceJointe(
DemandeDeTravauxId INTEGER NOT NULL REFERENCES DemandesDeTravaux ON UPDATE CASCADE ON DELETE CASCADE, 
PieceJointeId INTEGER NOT NULL REFERENCES PiecesJointes ON UPDATE CASCADE ON DELETE CASCADE, 
PRIMARY KEY (DemandeDeTravauxId, PieceJointeId));	

CREATE TABLE PatrimoinePieceJointe(
PatrimoineId INTEGER NOT NULL REFERENCES Patrimoines ON UPDATE CASCADE ON DELETE CASCADE, 
PieceJointeId INTEGER NOT NULL REFERENCES PiecesJointes ON UPDATE CASCADE ON DELETE CASCADE, 
PRIMARY KEY (PatrimoineId, PieceJointeId));	
							
CREATE TABLE TachesPieceJointe(
TacheId INTEGER NOT NULL REFERENCES Taches ON UPDATE CASCADE ON DELETE CASCADE, 
PieceJointeId INTEGER NOT NULL REFERENCES PiecesJointes ON UPDATE CASCADE ON DELETE CASCADE, 
PRIMARY KEY (TacheId, PieceJointeId));	

CREATE TABLE ElementDeclaratif (
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
Libelle TEXT, 
TypeElement INTEGER NOT NULL, 
ModeleId INTEGER, 
ParentId INTEGER, 
TypePatrimoine INTEGER NOT NULL,
IconId INTEGER NOT NULL);							
							
CREATE TABLE PatrimoineElementDeclaratif(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
PatrimoineId INTEGER NOT NULL REFERENCES Patrimoines ON UPDATE CASCADE ON DELETE CASCADE, 
ElementDeclaratifId INTEGER NOT NULL REFERENCES ElementDeclaratif ON UPDATE CASCADE ON DELETE CASCADE);

CREATE TABLE TiersTypeActivite(
TiersId INTEGER NOT NULL REFERENCES Tiers ON UPDATE CASCADE ON DELETE CASCADE, 
TypeActiviteId INTEGER REFERENCES TypesDActivite ON UPDATE CASCADE ON DELETE CASCADE, 
PRIMARY KEY (TiersId, TypeActiviteId));
									
CREATE TABLE TiersRoleAffectation(
TiersId INTEGER NOT NULL REFERENCES Tiers ON UPDATE CASCADE ON DELETE CASCADE, 
RoleId INTEGER NOT NULL, 
PRIMARY KEY (TiersId, RoleId));
							
CREATE TABLE EquipementValeurCaractListe(
EtatEntite INTEGER NOT NULL,
EquipementValeurCaracteristiqueId INTEGER REFERENCES EquipementValeurCaracteristique ON UPDATE CASCADE ON DELETE CASCADE,
CaracteristiqueListeId INTEGER REFERENCES CaracteristiqueListes ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (EquipementValeurCaracteristiqueId, CaracteristiqueListeId));

CREATE TABLE ElementValeurCaractListe(
EtatEntite INTEGER NOT NULL,
ElementDeclaratifValeurCaracteristiqueId INTEGER NOT NULL REFERENCES ElementValeurCaracteristique ON UPDATE CASCADE ON DELETE CASCADE,
CaracteristiqueListeId INTEGER NOT NULL REFERENCES CaracteristiqueListes ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (ElementDeclaratifValeurCaracteristiqueId, CaracteristiqueListeId));
							
CREATE TABLE EquipementValeurCaracteristiqueReleve(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
DateReleve TEXT NOT NULL, 
EquipementValeurCaracteristiqueId INTEGER NOT NULL REFERENCES EquipementValeurCaracteristique ON UPDATE CASCADE ON DELETE CASCADE,
CaracteristiqueListeId INTEGER REFERENCES CaracteristiqueListes ON UPDATE CASCADE ON DELETE CASCADE, 
ValeurNumerique TEXT,
ValeurDate TEXT, 
ValeurTexte TEXT);
							
CREATE TABLE ElementValeurCaracteristiqueReleve(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
DateReleve TEXT NOT NULL, 
ElementValeurCaracteristiqueId INTEGER NOT NULL REFERENCES ElementValeurCaracteristique ON UPDATE CASCADE ON DELETE CASCADE,
CaracteristiqueListeId INTEGER REFERENCES CaracteristiqueListes ON UPDATE CASCADE ON DELETE CASCADE, 
ValeurNumerique TEXT,
ValeurDate TEXT, 
ValeurTexte TEXT);							
							
CREATE TABLE EquipementValeurCaractListeReleve(
EtatEntite INTEGER NOT NULL,
EquipementValeurCaracteristiqueReleveId INTEGER REFERENCES EquipementValeurCaracteristiqueReleve ON UPDATE CASCADE ON DELETE CASCADE,
CaracteristiqueListeId INTEGER REFERENCES CaracteristiqueListes ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (EquipementValeurCaracteristiqueReleveId, CaracteristiqueListeId));
							
CREATE TABLE ElementValeurCaractListeReleve(
EtatEntite INTEGER NOT NULL,
ElementValeurCaracteristiqueReleveId INTEGER NOT NULL REFERENCES ElementValeurCaracteristiqueReleve ON UPDATE CASCADE ON DELETE CASCADE,
CaracteristiqueListeId INTEGER NOT NULL REFERENCES CaracteristiqueListes ON UPDATE CASCADE ON DELETE CASCADE,
PRIMARY KEY (ElementValeurCaracteristiqueReleveId, CaracteristiqueListeId));

CREATE TABLE Magasins(
_id INTEGER PRIMARY KEY,
Libelle TEXT NOT NULL,
Number TEXT);

CREATE TABLE TauxDeTVA(
_id INTEGER PRIMARY KEY,
Taux NUMERIC NOT NULL
);

CREATE TABLE Articles(
_id INTEGER PRIMARY KEY,
Libelle TEXT NOT NULL,
Number TEXT NOT NULL,
fAffecter INTEGER NOT NULL,
UniteDeGestion TEXT,
Divisibilite INTEGER NOT NULL,
CodeNatureArticle INTEGER NOT NULL,
GestionLot INTEGER NOT NULL,
ArticleModeleId INTEGER NOT NULL,
fPret INTEGER NOT NULL,
ReferenceFabriquant TEXT,
QuantiteEnCommande NUMERIC NOT NULL,
PrixDeVente NUMERIC NOT NULL,
TauxDeTVAId INTEGER NOT NULL REFERENCES TauxDeTVA,
TypeArticle INTEGER NOT NULL,
Generique INTEGER NOT NULL,
HorsStock INTEGER NOT NULL,
Archive INTEGER NOT NULL);

CREATE TABLE ArticlesMagasin(
_id INTEGER PRIMARY KEY,
ArticleId INTEGER NOT NULL,
MagasinId INTEGER NOT NULL,
MagasinLocalisationId INTEGER,
LibelleLocalisation TEXT,
LocalisationParDefaut INTEGER NOT NULL,
QuantiteEnStock NUMERIC NOT NULL);

CREATE TABLE Operations(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
Id2 INTEGER,
CodeAm INTEGER,
Description TEXT,
Art TEXT,
ArtId INTEGER,
Dest TEXT,
MagasinId INTEGER NOT NULL,
MagasinierId INTEGER NOT NULL,
NumInv TEXT,
Qte Numeric NOT NULL,
QteCassee Numeric,
QtePerdue Numeric,
Retirant TEXT,
TypeOperation INTEGER NOT NULL,
ArtDescription TEXT,
NumeroColis TEXT,
Id3 INTEGER);

CREATE TABLE Priorites (
_id INTEGER PRIMARY KEY,
Libelle TEXT NOT NULL,
TypePriorite INTEGER NOT NULL,
Description TEXT,
Couleur INTEGER);

CREATE TABLE NaturesPatrimoine (
_id INTEGER PRIMARY KEY,
TypePatrimoine INTEGER NOT NULL,
LibelleComplet TEXT NOT NULL);

CREATE TABLE EtatsPatrimoine (
_id INTEGER PRIMARY KEY,
TypePatrimoine INTEGER NOT NULL,
Libelle TEXT NOT NULL);

CREATE TABLE DemandeFourniture (
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
Numero TEXT NULL,
DateDemande TEXT NOT NULL,
Objet TEXT,
EtatDemande INTEGER NOT NULL,
EtatTravaux INTEGER NOT NULL,
TypeVentil INTEGER NOT NULL,
PrioriteId INTEGER NULL REFERENCES Priorites ON UPDATE CASCADE ON DELETE CASCADE,
TiersDemandeurId INTEGER NULL REFERENCES Tiers ON UPDATE CASCADE ON DELETE CASCADE,
ServiceGestionnaireId INTEGER NULL REFERENCES Tiers ON UPDATE CASCADE ON DELETE CASCADE,
BonDeTravauxId INTEGER NULL REFERENCES BonsDeTravaux ON UPDATE CASCADE ON DELETE CASCADE,
OrdreDeReparationId INTEGER NULL REFERENCES OrdresDeReparation ON UPDATE CASCADE ON DELETE CASCADE,
TypePatrimoine INTEGER NULL,
PatrimoineId INTEGER NULL REFERENCES Patrimoines ON UPDATE CASCADE ON DELETE CASCADE,
MagasinId INTEGER NULL REFERENCES Magasins ON UPDATE CASCADE ON DELETE CASCADE,
Demandeur TEXT NULL,
DateSouhaitee TEXT NULL);

CREATE TABLE DemandeFournitureLigne (
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
DemandeFournitureId INTEGER NOT NULL REFERENCES DemandeFourniture ON UPDATE CASCADE ON DELETE CASCADE,
ArticleId INTEGER,
Designation TEXT,
QteDemandee Numeric NOT NULL,
QteAcceptee Numeric NOT NULL,
Commentaire TEXT);

CREATE TABLE DemandeFourniturePieceJointe(
DemandeFournitureId INTEGER NOT NULL REFERENCES DemandeFourniture ON UPDATE CASCADE ON DELETE CASCADE, 
PieceJointeId INTEGER NOT NULL REFERENCES PiecesJointes ON UPDATE CASCADE ON DELETE CASCADE, 
PRIMARY KEY (DemandeFournitureId, PieceJointeId));

CREATE TABLE PatrimoineArticle(
_id INTEGER PRIMARY KEY REFERENCES Patrimoines ON UPDATE CASCADE ON DELETE CASCADE, 
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
ArticleId INTEGER NOT NULL REFERENCES Articles ON UPDATE CASCADE ON DELETE CASCADE,
Affecte INTEGER NOT NULL,
Inventorie INTEGER NOT NULL,
ArticleMagasinId INTEGER,
LibelleAffectationCourante TEXT,
CodeGesti TEXT
);

CREATE TABLE PreparationStock(
_id INTEGER PRIMARY KEY,
DemandeFournitureId INTEGER NOT NULL,
NumeroDFO TEXT ,
ArticleMagasinId INTEGER,
MagasinId INTEGER NOT NULL,
MagasinierId INTEGER,
MagasinNumero TEXT,
NumeroArticle TEXT,
ArticleId INTEGER NOT NULL,
Qte NUMERIC NOT NULL,
BordereauLivLigneDeDemandeId INTEGER,
NumeroColis TEXT
);

CREATE TABLE CauseRebut(
_id INTEGER PRIMARY KEY,
Libelle TEXT NOT NULL
);

CREATE TABLE ControlesHOM(
_id INTEGER PRIMARY KEY,
ModeleControleId INTEGER NOT NULL,
PatrimoineId INTEGER NOT NULL,
NbControlesEffectues INTEGER NOT NULL,
NbOccurenceMaxi INTEGER
);

CREATE TABLE PreparationHOM(
_id INTEGER PRIMARY KEY,
DemandeFournitureId INTEGER NOT NULL,
NumeroDFO TEXT ,
ArticleMagasinId INTEGER,
MagasinId INTEGER NOT NULL,
MagasinierId INTEGER,
MagasinNumero TEXT,
NumeroArticle TEXT,
ArticleId INTEGER NOT NULL,
NumInv TEXT,
Qte NUMERIC NOT NULL,
BordereauLivLigneDeDemandeId INTEGER,
NumeroColis TEXT
);

CREATE TABLE Livreurs(
_id INTEGER PRIMARY KEY,
Login TEXT,
Password TEXT,
Name TEXT NOT NULL,
Number TEXT
);

CREATE TABLE BonInventaireLigne(
_id INTEGER PRIMARY KEY,
ArticleId INTEGER NOT NULL,
NumeroInventaire TEXT,
PatrimoineIdOrigine INTEGER,
PatrimoineIdDestination INTEGER,
Qte NUMERIC NOT NULL,
Checked INTEGER NOT NULL
);

CREATE TABLE FFNatureTarification(
_id INTEGER PRIMARY KEY,
TypeTarificationCaracteristiqueListeId INTEGER NOT NULL,
Libelle TEXT NOT NULL,
Valide INTEGER NOT NULL
);

CREATE TABLE FFConsommationType(
_id INTEGER PRIMARY KEY,
FFNatureTarificationId INTEGER NOT NULL REFERENCES FFNatureTarification ON UPDATE CASCADE ON DELETE CASCADE,
Libelle TEXT NOT NULL,
UniteDeConsommation TEXT,
Ordre INTEGER NOT NULL,
Valide INTEGER NOT NULL
);

CREATE TABLE FFReleve(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
Numero TEXT,
PDCPatrimoineId INTEGER NOT NULL REFERENCES Patrimoines ON UPDATE CASCADE ON DELETE CASCADE, 
DateReleve TEXT NOT NULL,
Contact TEXT,
Observations TEXT
);

CREATE TABLE FFVentilConso(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
FFReleveId INTEGER NOT NULL REFERENCES FFReleve ON UPDATE CASCADE ON DELETE CASCADE,
FFConsommationTypeId INTEGER NOT NULL REFERENCES FFConsommationType ON UPDATE CASCADE ON DELETE CASCADE,
LibelleTypeConso TEXT NOT NULL,
IndexFin NUMERIC NOT NULL
);

CREATE TABLE DiversFournitures(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
Libelle TEXT NOT NULL,
DateFourniture TEXT NOT NULL,
Quantite NUMERIC NOT NULL,
MontantUnitaireHT NUMERIC NOT NULL,
TauxDeTVA NUMERIC,
Facturable INTEGER NOT NULL,
BonDeTravauxId INTEGER REFERENCES BonsDeTravaux ON UPDATE CASCADE ON DELETE CASCADE,
OrdreDeReparationId INTEGER REFERENCES OrdresDeReparation ON UPDATE CASCADE ON DELETE CASCADE,
TacheId INTEGER REFERENCES Taches ON UPDATE CASCADE ON DELETE CASCADE,
ArticleId INTEGER REFERENCES Articles ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE TABLE MenusStock(
_id INTEGER PRIMARY KEY,
Enabled INTEGER NOT NULL,
Habillement INTEGER NOT NULL,
Outillage INTEGER NOT NULL,
Materiel INTEGER NOT NULL
);

/*CREATE TABLE ReleveCompteur(
_id INTEGER PRIMARY KEY,
TimeStamp TEXT,
EtatEntite INTEGER NOT NULL,
NumeroPrise TEXT NOT NULL,
PetrolierId INTEGER NOT NULL REFERENCES Tiers ON UPDATE CASCADE ON DELETE CASCADE,
DatePrise TEXT NOT NULL,
PatrimoineId INTEGER NOT NULL REFERENCES Patrimoine ON UPDATE CASCADE ON DELETE CASCADE,
Compteur NUMERIC NOT NULL,
Observations TEXT
);*/


CREATE INDEX IF NOT EXISTS IX_InterventionsId ON Interventions (_id);
CREATE INDEX IF NOT EXISTS IX_InterventionsNo ON Interventions (Numero);
CREATE INDEX IF NOT EXISTS IX_BonsDeTravauxId ON BonsDeTravaux (_id);
CREATE INDEX IF NOT EXISTS IX_BonsDeTravauxInterventionsId ON BonsDeTravaux (InterventionId);
CREATE INDEX IF NOT EXISTS IX_OrdresDeReparationId ON OrdresDeReparation (_id);
CREATE INDEX IF NOT EXISTS IX_TachesId ON Taches (_id);
CREATE INDEX IF NOT EXISTS IX_HeuresAgentId ON HeuresAgent (_id);
CREATE INDEX IF NOT EXISTS IX_HeuresAgentBonDeTravauxId ON HeuresAgent (BonDeTravauxId);
CREATE INDEX IF NOT EXISTS IX_HeuresAgentOrdreDeReparationId ON HeuresAgent (OrdreDeReparationId);
CREATE INDEX IF NOT EXISTS IX_HeuresAgentTacheId ON HeuresAgent (TacheId);
CREATE INDEX IF NOT EXISTS IX_HeuresVehiculeId ON HeuresVehicule (_id);
CREATE INDEX IF NOT EXISTS IX_HeuresVehiculeBonDeTravauxId ON HeuresVehicule (BonDeTravauxId);
CREATE INDEX IF NOT EXISTS IX_HeuresVehiculeOrdreDeReparationId ON HeuresVehicule (OrdreDeReparationId);
CREATE INDEX IF NOT EXISTS IX_HeuresVehiculeOrdreTacheId ON HeuresVehicule (TacheId);
CREATE INDEX IF NOT EXISTS IX_AdressesId ON Adresses (_id);
CREATE INDEX IF NOT EXISTS IX_PatrimoinesAdressesId ON PatrimoinesAdresses (PatrimoineId, AdresseId);
CREATE INDEX IF NOT EXISTS IX_PatrimoinesAdressesId2 ON PatrimoinesAdresses (PatrimoineId);
CREATE INDEX IF NOT EXISTS IX_PatrimoinesAdressesId3 ON PatrimoinesAdresses (AdresseId);
CREATE INDEX IF NOT EXISTS IX_PatrimoinesId ON Patrimoines (_id);
CREATE INDEX IF NOT EXISTS IX_PatrimoinesNo ON Patrimoines (NumeroPatrimoine1);
CREATE INDEX IF NOT EXISTS IX_EquipementsId ON Equipements (_id);
CREATE INDEX IF NOT EXISTS IX_InterventionPatrimoineId ON InterventionPatrimoine (_id);
CREATE INDEX IF NOT EXISTS IX_InterventionPatrimoineInterventionId ON InterventionPatrimoine (InterventionId);
CREATE INDEX IF NOT EXISTS IX_InterventionPatrimoineOrdreDeReparationId ON InterventionPatrimoine (OrdreDeReparationId);
CREATE INDEX IF NOT EXISTS IX_InterventionPatrimoineTacheId ON InterventionPatrimoine (TacheId);
CREATE INDEX IF NOT EXISTS IX_InterventionPatrimoinePatrimoineId ON InterventionPatrimoine (PatrimoineId);
CREATE INDEX IF NOT EXISTS IX_EquipementValeurCaracteristiqueId ON EquipementValeurCaracteristique (_id);
CREATE INDEX IF NOT EXISTS IX_EquipementValeurCaracteristiqueListeId ON EquipementValeurCaracteristique (CaracteristiqueListeId);
CREATE INDEX IF NOT EXISTS IX_EquipementValeurCaracteristiquePatId ON EquipementValeurCaracteristique (PatrimoineId);
CREATE INDEX IF NOT EXISTS IX_EquipementValeurCaracteristiqueReleveId ON EquipementValeurCaracteristiqueReleve (_id);
CREATE INDEX IF NOT EXISTS IX_EquipementValeurCaracteristiqueReleveVCId ON EquipementValeurCaracteristiqueReleve (EquipementValeurCaracteristiqueId);
CREATE INDEX IF NOT EXISTS IX_EquipementValeurCaracteristiqueReleveListeId ON EquipementValeurCaracteristiqueReleve (CaracteristiqueListeId);
CREATE INDEX IF NOT EXISTS IX_CaracteristiqueListesId ON CaracteristiqueListes (_id);
CREATE INDEX IF NOT EXISTS IX_EquipementValeurCaractListeReleveId ON EquipementValeurCaractListeReleve (EquipementValeurCaracteristiqueReleveId, CaracteristiqueListeId);
CREATE INDEX IF NOT EXISTS IX_EquipementValeurCaractListeReleveId2 ON EquipementValeurCaractListeReleve (EquipementValeurCaracteristiqueReleveId);
CREATE INDEX IF NOT EXISTS IX_EquipementValeurCaractListeReleveId3 ON EquipementValeurCaractListeReleve (CaracteristiqueListeId);
CREATE INDEX IF NOT EXISTS IX_Magasind ON Magasins (_id);
CREATE INDEX IF NOT EXISTS IX_ArticleId ON Articles (_id);
CREATE INDEX IF NOT EXISTS IX_ArticleMagasinId ON ArticlesMagasin (_id);
CREATE INDEX IF NOT EXISTS IX_OperationsId ON Operations (_id);
CREATE INDEX IF NOT EXISTS IX_PrioritesId ON Priorites (_id);
CREATE INDEX IF NOT EXISTS IX_NaturesPatrimoineId ON NaturesPatrimoine (_id);
CREATE INDEX IF NOT EXISTS IX_EtatsPatrimoineId ON EtatsPatrimoine (_id);
CREATE INDEX IF NOT EXISTS IX_DemandeFournitureId ON DemandeFourniture (_id);
CREATE INDEX IF NOT EXISTS IX_DemandeFournitureLigneId ON DemandeFournitureLigne (_id);
CREATE INDEX IF NOT EXISTS IX_PatrimoineArticleId ON PatrimoineArticle (_id);
CREATE INDEX IF NOT EXISTS IX_PatrimoineArticleArticleId ON PatrimoineArticle (ArticleId);
CREATE INDEX IF NOT EXISTS IX_ControlesId ON ControlesHOM (_id);
CREATE INDEX IF NOT EXISTS IX_TiersId ON Tiers (_id);
CREATE INDEX IF NOT EXISTS IX_TiersNo ON Tiers (Numero);
CREATE INDEX IF NOT EXISTS IX_BonInventaireLigneId ON BonInventaireLigne (_id);
CREATE INDEX IF NOT EXISTS IX_FFNatureTarificationId ON FFNatureTarification (_id);
CREATE INDEX IF NOT EXISTS IX_FFConsommationTypeId ON FFConsommationType (_id);
CREATE INDEX IF NOT EXISTS IX_FFReleveId ON FFReleve (_id);
CREATE INDEX IF NOT EXISTS IX_FFVentilConsoId ON FFVentilConso (_id);
CREATE INDEX IF NOT EXISTS IX_ControlesId ON Controles (_id);
CREATE INDEX IF NOT EXISTS IX_InterventionControleId ON InterventionControle (_id);
CREATE INDEX IF NOT EXISTS IX_VehiculeId ON Vehicules (_id);
CREATE INDEX IF NOT EXISTS IX_TypeFraisId ON TypeFrais (_id);
CREATE INDEX IF NOT EXISTS IX_DiversFournituresId ON DiversFournitures (_id);
CREATE INDEX IF NOT EXISTS IX_DiversFournituresORVId ON DiversFournitures (OrdreDeReparationId);
CREATE INDEX IF NOT EXISTS IX_DiversFournituresTacheId ON DiversFournitures (TacheId);
CREATE INDEX IF NOT EXISTS IX_DiversFournituresBTId ON DiversFournitures (BonDeTravauxId);
--CREATE INDEX IF NOT EXISTS IX_SPCPriseId ON ReleveCompteur (_id);

PRAGMA writable_schema = 0;							
							