create database stocksClg;
use stocksClg;
---Drop tables
DROP TABLE lignecommande;
DROP TABLE Commandes;
DROP TABLE ARTICLES;
DROP TABLE Clients;


CREATE TABLE Clients
(
idClient SMALLINT IDENTITY(1,1),
nom VARCHAR (20) NOT NULL,
Prenom VARCHAR(20)NOT NULL,
adresse varchar(40),
CONSTRAINT pk_clients PRIMARY KEY (idClient)
);


CREATE TABLE Commandes 
(
idcommande INT IDENTITY(10,1),
date_commande date not null,
idClient SMALLINT not null,
CONSTRAINT pk_commande PRIMARY KEY(idcommande),
CONSTRAINT fk_client FOREIGN KEY (idClient) REFERENCES Clients(idClient)
);

CREATE TABLE Articles
(idArticle INT IDENTITY (1,1),
descriptions VARCHAR(30) not null,
prix MONEY not null,
quantite_stock smallint not null,
CONSTRAINT pk_Article PRIMARY KEY(idArticle),
CONSTRAINT ck_prix CHECK(prix >=0),
CONSTRAINT ck_quantite_stock CHECK(quantite_stock >=1)
);


CREATE TABLE Ligne_commande
(idcommande INT NOT NULL,
idArticle INT NOT NULL,
quantiteCommande int not NULL,
CONSTRAINT pk_lignecommande PRIMARY KEY (idcommande,idArticle),
CONSTRAINT fk_Article FOREIGN KEY(idArticle) REFERENCES Articles(idArticle),
CONSTRAINT fk_commnade FOREIGN KEY (idcommande) REFERENCES commandes(idCommande)
);

---Insesrtion
insert into Clients(nom, prenom, adresse) values 
('Patoche','Alain','14 rue Jupiter, Montréal'),
('LeRoy','Singe','11 avenue de la Lune, Laval'),
('LeRigolo','Coluche','177 rue de Venus, Montréal'),
('Lefou','Ducoin','12 rue de Saturne Laval');
insert into clients values('Le magnifique','Simba','789 rue des Chats, Laval');
insert into clients values('Ce client','prenom','789 rue des clients, Montréal');
----------------

insert into Articles(descriptions, prix, quantite_stock) values
('Imprimantes Laser HP 8000',700,20),
('Écrans tactiles 19p',550,45),
('Routeurs sans fils Azus',200,75),
('Disques durs SSD 500 Go',175,45);

insert into Articles(descriptions, prix, quantite_stock) values ('Chaises de bureau',400,20);
insert into Articles(descriptions, prix, quantite_stock) values ('Cet Article',400,5);
insert into Articles(descriptions, prix, quantite_stock) values ('Mon article',100,10);

----------------
INSERT INTO Commandes(date_commande,idClient) values('2026-08-21',1);
INSERT INTO Commandes(date_commande,idClient) values('2026-08-28',1);
INSERT INTO Commandes(date_commande,idClient) values('2026-09-05',1);
INSERT INTO Commandes(date_commande,idClient) values('2026-08-17',2);
INSERT INTO Commandes(date_commande,idClient) values('2026-09-12',2);
INSERT INTO Commandes(date_commande,idClient) values('202-11-12',3);
----------------------------------------