---
--- Settings de la aplicació
---
CREATE TABLE IF NOT EXISTS settings (
	ident	VARCHAR(90) NOT NULL,
	classe	VARCHAR(90) DEFAULT NULL,
	nom		VARCHAR(90) NOT NULL,
	valor	TEXT		NOT NULL DEFAULT ''
);

CREATE INDEX IF NOT EXISTS idx_settings_nom ON settings(ident, nom);
CREATE INDEX IF NOT EXISTS idx_settings_ident ON settings(ident);
CREATE INDEX IF NOT EXISTS idx_settings_setting ON settings(ident, classe, nom);

-- 
-- Definició de elements de la apliació
-- 
CREATE TABLE IF NOT EXISTS definicions (
	id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
	path	VARCHAR(90)		NOT NULL,
	ident	VARCHAR(90)		NOT NULL,
	nom			VARCHAR(90)		DEFAULT (NULL),
	descripcio	VARCHAR(150)	DEFAULT (NULL),
	tipus		VARCHAR(90)		DEFAULT (NULL),
	classe		VARCHAR(90)		DEFAULT (NULL)
);

CREATE INDEX IF NOT EXISTS idx_definicions_path ON definicions (path);
CREATE INDEX IF NOT EXISTS idx_definicions_ident ON definicions (path, ident);

-- 
-- Valors de elements de la aplicació
-- 
CREATE TABLE IF NOT EXISTS elements (
	id		INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
	context	VARCHAR(90)	NOT NULL,
	tipus	VARCHAR(90)	DEFAULT (NULL),
	ident	VARCHAR(90)	NOT NULL,
	idioma	CHAR(2)		DEFAULT (NULL),
	valor	TEXT		DEFAULT ( '' )
);

CREATE INDEX IF NOT EXISTS idx_elements_context ON elements (context);
CREATE INDEX IF NOT EXISTS idx_elements_context_tipus ON elements (context,tipus);
CREATE INDEX IF NOT EXISTS idx_elements_context_idioma ON elements (context,idioma);
CREATE INDEX IF NOT EXISTS idx_elements_context_ident ON elements (context,ident);
CREATE INDEX IF NOT EXISTS idx_elements_context_ident_idioma ON elements (context,ident,idioma);

---
--- Accions
---
CREATE TABLE IF NOT EXISTS accions (
	id         INTEGER        PRIMARY KEY AUTOINCREMENT
							  NOT NULL
							  UNIQUE,
	pagina_id INTEGER DEFAULT NULL,
	idioma CHAR(2) DEFAULT NULL,
	url		TEXT DEFAULT  NULL,
	accio	TEXT DEFAULT NULL,
	param	TEXT DEFAULT NULL,
	template	TEXT DEFAULT NULL,
	layout		TEXT DEFAULT NULL,
	settings	TEXT DEFAULT (NULL)
);

CREATE TABLE IF NOT EXISTS accions_titols (
	accio_id INTEGER KEY NOT NULL REFERENCES pagines(id) ON DELETE CASCADE,
	idioma	CHAR(2)		NOT NULL,
	titol	TEXT		DEFAULT ( '' )
);

CREATE INDEX IF NOT EXISTS idx_accions_url ON accions(url);
CREATE INDEX IF NOT EXISTS idx_accions_pagina ON accions(pagina_id);
CREATE INDEX IF NOT EXISTS idx_accions_idioma_url_idx ON accions(idioma, url);

---
--- Enrutador
---
CREATE TABLE IF NOT EXISTS router (
	id        INTEGER     PRIMARY KEY AUTOINCREMENT
						  NOT NULL
						  UNIQUE,
	origen TEXT KEY DEFAULT NULL,
	idioma CHAR(2) DEFAULT NULL,
	url TEXT DEFAULT  NULL,
	pagina_id INTEGER DEFAULT NULL,
	accio TEXT DEFAULT NULL,
	param TEXT DEFAULT NULL
);

CREATE INDEX IF NOT EXISTS router_url_idx ON router(url);
CREATE INDEX IF NOT EXISTS router_idioma_url_idx ON router(idioma, url);
























CREATE TABLE IF NOT EXISTS pagines (
	id			INTEGER		PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
	nom			VARCHAR(90)	NOT NULL DEFAULT (''),
	ident		VARCHAR(90)	DEFAULT (NULL),
	template	VARCHAR(90)	DEFAULT (NULL),
	layout		VARCHAR(90)	DEFAULT (NULL),
	creada		DATETIME,
	modificada	DATETIME	DEFAULT (CURRENT_TIMESTAMP),
	status		INTEGER		NOT NULL DEFAULT ('0')
);

CREATE TABLE IF NOT EXISTS pagines_urls (
	pagina_id INTEGER KEY NOT NULL REFERENCES pagines(id) ON DELETE CASCADE,
	idioma	CHAR(2)	NOT NULL,
	url		TEXT	DEFAULT (''),
	titol	TEXT	DEFAULT ('')
);

CREATE TABLE IF NOT EXISTS pagines_mptt (
	id        INTEGER     PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE,
	pagina_id INTEGER KEY REFERENCES pagines(id) ON DELETE CASCADE,
	parent_id INTEGER     DEFAULT (0),
	pes       INTEGER,
	lft       INTEGER,
	rgt       INTEGER
);

CREATE TABLE IF NOT EXISTS pagines_slugs (
	pagina_id INTEGER KEY NOT NULL REFERENCES pagines(id) ON DELETE CASCADE,
	idioma	CHAR(2)		NOT NULL,
	slug	TEXT		DEFAULT ( '' ),
	titol	TEXT		DEFAULT ( '' )
);

CREATE TABLE IF NOT EXISTS pagines_idents (
	pagina_id INTEGER KEY NOT NULL REFERENCES pagines ( id ) ON DELETE CASCADE,
	ident     TEXT        DEFAULT ( '' )
);