-- P5-5206: collections

CREATE TABLE collections
(
  id SERIAL PRIMARY KEY,
  name TEXT NOT NULL,
  inserted TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT now(),
  deleted TIMESTAMP WITH TIME ZONE
);


CREATE TABLE collections_products
(
  id serial NOT NULL,
  collection_id INTEGER NOT NULL,
  product_id INTEGER,
  UNIQUE (collection_id, product_id)
);


ALTER TABLE collections_products
    ADD CONSTRAINT collection_fkey
    FOREIGN KEY (collection_id)
    REFERENCES collections(id) MATCH SIMPLE;


ALTER TABLE collections_products
    ADD CONSTRAINT products_fkey
    FOREIGN KEY (product_id)
    REFERENCES "product-products"(productid) MATCH SIMPLE;



CREATE TABLE collections_texts
(
  id serial NOT NULL,
  collection_id INTEGER NOT NULL,
  market_id INTEGER NOT NULL,
  meta_title TEXT,
  meta_description TEXT,
  title TEXT,
  description TEXT
);


ALTER TABLE collections_texts
    ADD CONSTRAINT collection_fkey
    FOREIGN KEY (collection_id)
    REFERENCES collections(id) MATCH SIMPLE;


ALTER TABLE collections_texts
    ADD CONSTRAINT market_fkey
    FOREIGN KEY (market_id)
    REFERENCES markets(id) MATCH SIMPLE;