text: -- Deploy arunashop:tables to pg
  
  BEGIN;
  
  -- XXX Add DDLs here.
  CREATE TABLE "collection"(
      "id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      "name" TEXT NOT NULL UNIQUE,
      "description" TEXT,
      "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP
  );
  
  CREATE TABLE "category"(
      "id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      "title" TEXT NOT NULL UNIQUE,
      "color" TEXT, -- FORMAT : #ffffff
      "description" TEXT,
      "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP
  );
  
  
  CREATE TABLE "user"(
      "id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      "firstname" TEXT,
      "lastname" TEXT,
      "email" TEXT NOT NULL UNIQUE,
      "password" TEXT NOT NULL,
      "sexe" TEXT,
      "birthday" DATE,
      "phone" TEXT,
      "adresse1" TEXT,
      "adresse2" TEXT,
      "zip" TEXT,
      "city" TEXT,
      "country" TEXT,
      "role" TEXT NOT NULL, -- DOMAINE A CREER // user admin
      "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP
  );
  
  CREATE TABLE "article"(
      "id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      "title" TEXT NOT NULL UNIQUE,
      "description" TEXT NOT NULL,
      "available" TEXT,-- DOMAINE A CREER 'disponible' 'not disponible'"
      "stock" INT,
      "price" MONEY,
      "article_type" TEXT,
      "collection_id" INT NOT NULL REFERENCES "collection"("id"),
      "category_id" INT NOT NULL REFERENCES "category"("id"),
      "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP
  );
  
  CREATE TABLE "comment"(
      "id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      "title" TEXT,
      "description" TEXT,
      "article_id" INT NOT NULL REFERENCES "article"("id"),
      "user_id" INT NOT NULL REFERENCES "user"("id"),
      "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP
  );
  
  CREATE TABLE "rating"(
      "id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      "value" INT NOT NULL,
      "article_id" INT NOT NULL REFERENCES "article"("id"),
      "user_id" INT NOT NULL REFERENCES "user"("id"),
      "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP
  );
  
  
  CREATE TABLE "picture"(
      "id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      "name" TEXT NOT NULL UNIQUE,
      "path" TEXT,
      "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP
  );
  
  CREATE TABLE "ligneDeCommande"(
      "id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      "quantity" INT NOt NULL,
      "article_id" INT NOT NULL REFERENCES "article"("id"),
      -- "user_id" INT NOT NULL REFERENCES "user"("id"),
      "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP
  );
  
  CREATE TABLE "commande"(
      "id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
      "ligneDeCommande_id" INT NOT NULL REFERENCES "ligneDeCommande"("id"),
      "user_id" INT NOT NULL REFERENCES "user"("id"),
      "status" TEXT,
      "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP
  );
  
  -- CREATE TABLE "transaction"(
  --     "id" INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  --     "quantity" INT NOT NULL,
  --     "article_id" INT NOT NULL,
  --     "user_id" INT NOT NULL,
  --     "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  --     "updated_at" TIMESTAMP
  -- );
  
  
  -- ------- ------- n to n à changer ici
  -- CREATE TABLE "cards_has_labels"(
  --   "cards_id" INT NOT NULL REFERENCES "cards"("id") ON DELETE CASCADE,
  --   "labels_id" INT NOT NULL REFERENCES "labels"("id") ON DELETE CASCADE,
  --   "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  --   PRIMARY KEY ("cards_id", "labels_id")
  -- );
  
  -- category_has_picture
  CREATE TABLE "category_has_picture"(
      "category_id" INT NOT NULL REFERENCES "category"("id") ON DELETE CASCADE,
      "picture_id" INT NOT NULL REFERENCES "picture"("id") ON DELETE CASCADE,
      "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP,
      PRIMARY KEY ("category_id", "picture_id")
  );
  
  -- picture_has_article
  CREATE TABLE "article_has_picture"(
      "article_id" INT NOT NULL REFERENCES "article"("id") ON DELETE CASCADE,
      "picture_id" INT NOT NULL REFERENCES "picture"("id") ON DELETE CASCADE,
      "created_at" TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
      "updated_at" TIMESTAMP,
      PRIMARY KEY ("article_id", "picture_id")
  );
  
  
  
  COMMIT;
  