---
--- 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);