CREATE TABLE [cards](
    [front] TEXT NOT NULL, 
    [back] TEXT NOT NULL);

CREATE TABLE [cardsfiltresvalues](
    [card] TEXT NOT NULL REFERENCES cards, 
    [filtervalue] TEXT NOT NULL REFERENCES filtresvalues, 
    [filter] TEXT NOT NULL REFERENCES filtres, 
    UNIQUE([card], [filter]));

CREATE TABLE [filtres](
    [name] TEXT NOT NULL);

CREATE TABLE [filtresvalues](
    [filter] TEXT NOT NULL REFERENCES filtres, 
    [value] TEXT NOT NULL);

CREATE TABLE [photos] (
	[photo_id]	integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	[photo_value]	nvarchar(100) NOT NULL COLLATE NOCASE

);

CREATE TABLE [photos_tags_values](
    [photo_id] integer NOT NULL REFERENCES photos([photo_id]) ON DELETE NO ACTION ON UPDATE CASCADE, 
    [tag_id] integer NOT NULL, 
    [tag_value_id] integer NOT NULL, 
    PRIMARY KEY([tag_id], [tag_value_id]), 
    FOREIGN KEY([tag_id], [tag_value_id]) REFERENCES tag_values([tag_id], [tag_value_id]) ON DELETE NO ACTION ON UPDATE CASCADE);

CREATE TABLE [tags] (
	[tag_id]	integer PRIMARY KEY AUTOINCREMENT NOT NULL,
	[tag_value]	varchar(100) NOT NULL COLLATE NOCASE

);

CREATE TABLE [tag_values](
    [tag_value_id] integer NOT NULL, 
    [tag_id] integer NOT NULL REFERENCES tags([tag_id]) ON DELETE NO ACTION ON UPDATE CASCADE, 
    [value] varchar(30) NOT NULL COLLATE NOCASE, 
    PRIMARY KEY([tag_id], [tag_value_id]));

CREATE UNIQUE INDEX [photos_tags_values_photos_tags_values_uq]
ON [photos_tags_values](
    [photo_id] DESC, 
    [tag_id] DESC);

CREATE UNIQUE INDEX [tag_values_tag_values_uq]
ON [tag_values](
    [tag_id] DESC, 
    [value] DESC);

CREATE UNIQUE INDEX [tag_values_UQ__tag_valu__AD4EDF21FEECC733]
ON [tag_values](
    [tag_value_id] DESC);