
-----------------------------------------------------------------------------
-- nclasificaciondeseguridad
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [nclasificaciondeseguridad];


CREATE TABLE [nclasificaciondeseguridad]
(
	[idclasificaciondeseguridad] INTEGER  NOT NULL,
	[nombredescriptivo] VARCHAR(64)  NOT NULL,
	PRIMARY KEY ([idclasificaciondeseguridad])
);

-----------------------------------------------------------------------------
-- nestado
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [nestado];


CREATE TABLE [nestado]
(
	[idestado] INTEGER  NOT NULL,
	[nombredescriptivo] VARCHAR(64)  NOT NULL,
	PRIMARY KEY ([idestado])
);

-----------------------------------------------------------------------------
-- nformacaptacion
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [nformacaptacion];


CREATE TABLE [nformacaptacion]
(
	[idformacaptacion] INTEGER  NOT NULL,
	[tiporecurso] VARCHAR(50)  NOT NULL,
	PRIMARY KEY ([idformacaptacion])
);

-----------------------------------------------------------------------------
-- ntipodeindicador
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [ntipodeindicador];


CREATE TABLE [ntipodeindicador]
(
	[idtipodeindicador] INTEGER  NOT NULL,
	[nombredescriptivo] VARCHAR(64)  NOT NULL,
	PRIMARY KEY ([idtipodeindicador])
);

-----------------------------------------------------------------------------
-- ntipodemodelo
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [ntipodemodelo];


CREATE TABLE [ntipodemodelo]
(
	[idtipodemodelo] INTEGER  NOT NULL,
	[nombredescriptivo] VARCHAR(64)  NOT NULL,
	PRIMARY KEY ([idtipodemodelo])
);

-----------------------------------------------------------------------------
-- tbaspecto
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbaspecto];


CREATE TABLE [tbaspecto]
(
	[idalias] VARCHAR(16)  NOT NULL,
	[aliassup] VARCHAR(16),
	[nombredescriptivo] VARCHAR(255)  NOT NULL,
	[notametodologica] VARCHAR(255),
	PRIMARY KEY ([idalias]),
	FOREIGN KEY ([aliassup]) REFERENCES tbaspecto ([idalias]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([aliassup]) REFERENCES tbaspecto ([idalias])

-----------------------------------------------------------------------------
-- tbclasificacion
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbclasificacion];


CREATE TABLE [tbclasificacion]
(
	[codclasificador] VARCHAR(10)  NOT NULL,
	[codclasificacion] VARCHAR(15)  NOT NULL,
	[nombredescriptivo] VARCHAR(255),
	PRIMARY KEY ([codclasificador],[codclasificacion]),
	FOREIGN KEY ([codclasificador]) REFERENCES tbclasificador ([codclasificador]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([codclasificador]) REFERENCES tbclasificador ([codclasificador])

-----------------------------------------------------------------------------
-- tbclasificador
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbclasificador];


CREATE TABLE [tbclasificador]
(
	[codclasificador] VARCHAR(10)  NOT NULL,
	[alias] VARCHAR(25),
	[nombredescriptivo] VARCHAR(255),
	PRIMARY KEY ([codclasificador])
);

-----------------------------------------------------------------------------
-- tbcorrelacionadordeindicadores
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbcorrelacionadordeindicadores];


CREATE TABLE [tbcorrelacionadordeindicadores]
(
	[idcodindicador] VARCHAR(11)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idnumpagina] INTEGER  NOT NULL,
	[codactividadasociada] INTEGER,
	[descripcion] VARCHAR(255),
	PRIMARY KEY ([idcodindicador],[idsubnummodelo],[idnummodelo],[idnumpagina]),
	FOREIGN KEY ([idcodindicador], [idsubnummodelo], [idnummodelo], [idnumpagina]) REFERENCES tbindicadorpagina ([idcodindicador], [idsubnummodelo], [idnummodelo], [idnumpagina]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idcodindicador]) REFERENCES tbindicadorpagina ([idcodindicador])

-----------------------------------------------------------------------------
-- tbcorrelacionadordevariante
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbcorrelacionadordevariante];


CREATE TABLE [tbcorrelacionadordevariante]
(
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[idcodvariante] INTEGER  NOT NULL,
	[codvariante_i] INTEGER,
	[codvariante_ii] INTEGER,
	[nombrevariante_i] VARCHAR(255),
	[nombrevariante_ii] VARCHAR(255),
	PRIMARY KEY ([idnummodelo],[idsubnummodelo],[idcodvariante]),
	FOREIGN KEY ([idnummodelo],[idsubnummodelo],[idcodvariante]) REFERENCES tbvariante ([idnummodelo],[idsubnummodelo],[idcodvariante]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idnummodelo]) REFERENCES tbvariante ([idnummodelo])

-----------------------------------------------------------------------------
-- tbcuadredevalidacion
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbcuadredevalidacion];


CREATE TABLE [tbcuadredevalidacion]
(
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[cuadre] VARCHAR(255)  NOT NULL,
	PRIMARY KEY ([idnummodelo],[idsubnummodelo]),
	FOREIGN KEY ([idnummodelo],[idsubnummodelo]) REFERENCES tbdescriptordelmodelo ([idnummodelo],[idsubnummodelo]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idnummodelo]) REFERENCES tbdescriptordelmodelo ([idnummodelo])

-----------------------------------------------------------------------------
-- tbdependencia
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbdependencia];


CREATE TABLE [tbdependencia]
(
	[idpreguntadep] VARCHAR(15)  NOT NULL,
	[idpreguntaref] VARCHAR(15)  NOT NULL,
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[idnumpagina] INTEGER  NOT NULL,
	[respuesta] VARCHAR(15)  NOT NULL,
	PRIMARY KEY ([idpreguntadep],[idpreguntaref],[idnummodelo],[idsubnummodelo],[idnumpagina]),
	FOREIGN KEY ([idpreguntaref],[idnummodelo],[idsubnummodelo],[idnumpagina]) REFERENCES tbpregunta ([idpregunta],[idnummodelo],[idsubnummodelo],[idnumpagina]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idpreguntaref]) REFERENCES tbpregunta ([idpregunta])

-----------------------------------------------------------------------------
-- tbdescriptordelmodelo
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbdescriptordelmodelo];


CREATE TABLE [tbdescriptordelmodelo]
(
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[idestado] INTEGER  NOT NULL,
	[idtipodemodelo] INTEGER  NOT NULL,
	[idclasificaciondeseguridad] INTEGER default 1 NOT NULL,
	[idpiedefirma] INTEGER  NOT NULL,
	[periodicidad] INTEGER  NOT NULL,
	[aproximacion] INTEGER  NOT NULL,
	[nombredescriptivo] VARCHAR(255)  NOT NULL,
	[notametodologica] VARCHAR(255),
	[idsistemadeinformacion] VARCHAR(5)  NOT NULL,
	PRIMARY KEY ([idnummodelo],[idsubnummodelo]),
	FOREIGN KEY ([idestado]) REFERENCES nestado ([idestado]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY ([idtipodemodelo]) REFERENCES ntipodemodelo ([idtipodemodelo]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY ([idclasificaciondeseguridad]) REFERENCES nclasificaciondeseguridad ([idclasificaciondeseguridad]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY ([idpiedefirma]) REFERENCES tbpiedefirma ([idpiedefirma]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY ([idsistemadeinformacion]) REFERENCES tbsistemadeinformacion ([idsistemadeinformacion]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idestado]) REFERENCES nestado ([idestado])

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idtipodemodelo]) REFERENCES ntipodemodelo ([idtipodemodelo])

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idclasificaciondeseguridad]) REFERENCES nclasificaciondeseguridad ([idclasificaciondeseguridad])

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idpiedefirma]) REFERENCES tbpiedefirma ([idpiedefirma])

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idsistemadeinformacion]) REFERENCES tbsistemadeinformacion ([idsistemadeinformacion])

-----------------------------------------------------------------------------
-- tbfechadecaptacion
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbfechadecaptacion];


CREATE TABLE [tbfechadecaptacion]
(
	[idfecha] INTEGER  NOT NULL,
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[fechadecaptacion] DATETIME  NOT NULL,
	[nota] VARCHAR(255),
	PRIMARY KEY ([idfecha],[idnummodelo],[idsubnummodelo]),
	FOREIGN KEY ([idnummodelo], [idsubnummodelo]) REFERENCES tbdescriptordelmodelo ([idnummodelo], [idsubnummodelo]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idnummodelo]) REFERENCES tbdescriptordelmodelo ([idnummodelo])

-----------------------------------------------------------------------------
-- tbindicador
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbindicador];


CREATE TABLE [tbindicador]
(
	[idcodindicador] VARCHAR(11)  NOT NULL,
	[idclasificaciondeseguridad] INTEGER  NOT NULL,
	[idtipodeindicador] INTEGER  NOT NULL,
	[idcodtematica] INTEGER  NOT NULL,
	[nombredescriptivo] VARCHAR(255)  NOT NULL,
	[unidaddemedida] VARCHAR(25)  NOT NULL,
	[periodicidad] INTEGER  NOT NULL,
	[notametodologica] VARCHAR(255),
	[essumable] INTEGER,
	PRIMARY KEY ([idcodindicador]),
	FOREIGN KEY ([idclasificaciondeseguridad]) REFERENCES nclasificaciondeseguridad ([idclasificaciondeseguridad]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY ([idtipodeindicador]) REFERENCES ntipodeindicador ([idtipodeindicador]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY ([idcodtematica]) REFERENCES tbtematica ([idcodtematica]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idclasificaciondeseguridad]) REFERENCES nclasificaciondeseguridad ([idclasificaciondeseguridad])

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idtipodeindicador]) REFERENCES ntipodeindicador ([idtipodeindicador])

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idcodtematica]) REFERENCES tbtematica ([idcodtematica])

-----------------------------------------------------------------------------
-- tbindicadorpagina
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbindicadorpagina];


CREATE TABLE [tbindicadorpagina]
(
	[idcodindicador] VARCHAR(11)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idnumpagina] INTEGER  NOT NULL,
	[niveldeinteres] INTEGER  NOT NULL,
	[posiciondelafila] INTEGER,
	[codfila] VARCHAR(15)  NOT NULL,
	[nombredescriptivo] VARCHAR(255)  NOT NULL,
	[idseccion] VARCHAR(13),
	PRIMARY KEY ([idcodindicador],[idsubnummodelo],[idnummodelo],[idnumpagina]),
	FOREIGN KEY ([idcodindicador]) REFERENCES tbindicador ([idcodindicador]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY ([idnummodelo], [idsubnummodelo], [idnumpagina]) REFERENCES tbpagina ([idnummodelo], [idsubnummodelo], [idnumpagina]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY ([idseccion]) REFERENCES tbseccion ([idseccion]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idcodindicador]) REFERENCES tbindicador ([idcodindicador])

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idnummodelo]) REFERENCES tbpagina ([idnummodelo])

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idseccion]) REFERENCES tbseccion ([idseccion])


-----------------------------------------------------------------------------
-- tbpagina
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbpagina];


CREATE TABLE [tbpagina]
(
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[idnumpagina] INTEGER  NOT NULL,
	[nombredescriptivo] VARCHAR(255),
	[sumadecontrol] INTEGER  NOT NULL,
	PRIMARY KEY ([idnummodelo],[idsubnummodelo],[idnumpagina]),
	FOREIGN KEY ([idnummodelo], [idsubnummodelo]) REFERENCES tbdescriptordelmodelo ([idnummodelo], [idsubnummodelo]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idnummodelo]) REFERENCES tbdescriptordelmodelo ([idnummodelo])

-----------------------------------------------------------------------------
-- tbpaginaaspecto
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbpaginaaspecto];


CREATE TABLE [tbpaginaaspecto]
(
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[idnumpagina] INTEGER  NOT NULL,
	[idalias] VARCHAR(16)  NOT NULL,
	[numcolumna] INTEGER,
	PRIMARY KEY ([idnummodelo],[idsubnummodelo],[idnumpagina],[idalias]),
	FOREIGN KEY ([idalias]) REFERENCES tbaspecto ([idalias]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY ([idnummodelo], [idsubnummodelo], [idnumpagina]) REFERENCES tbpagina ([idnummodelo], [idsubnummodelo], [idnumpagina]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idnummodelo]) REFERENCES tbpagina ([idnummodelo])

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idalias]) REFERENCES tbaspecto ([idalias])

-----------------------------------------------------------------------------
-- tbpiedefirma
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbpiedefirma];


CREATE TABLE [tbpiedefirma]
(
	[idpiedefirma] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
	[denominaciondelpie] VARCHAR(50)  NOT NULL,
	[primercargo] VARCHAR(50)  NOT NULL,
	[segundocargo] VARCHAR(50),
	[notadecertificacion] VARCHAR(255)
);

-----------------------------------------------------------------------------
-- tbpregunta
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbpregunta];


CREATE TABLE [tbpregunta]
(
	[idpregunta] VARCHAR(15)  NOT NULL,
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[idnumpagina] INTEGER  NOT NULL,
	[codclasificador] VARCHAR(10),
	[posicionpregunta] INTEGER  NOT NULL,
	[descripciondelapregunta] VARCHAR(255),
	[formadecaptacion] VARCHAR(255),
	[notametodologica] VARCHAR(255),
	PRIMARY KEY ([idpregunta],[idnummodelo],[idsubnummodelo],[idnumpagina]),
	FOREIGN KEY ([codclasificador]) REFERENCES tbclasificador ([codclasificador]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY ([idnummodelo], [idsubnummodelo], [idnumpagina]) REFERENCES tbpagina ([idnummodelo], [idsubnummodelo], [idnumpagina]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idnummodelo]) REFERENCES tbpagina ([idnummodelo])

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([codclasificador]) REFERENCES tbclasificador ([codclasificador])


-----------------------------------------------------------------------------
-- tbseccion
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbseccion];


CREATE TABLE [tbseccion]
(
	[idseccion] VARCHAR(13)  NOT NULL,
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[idnumpagina] INTEGER  NOT NULL,
	[titulo] VARCHAR(255),
	[posiciondelafila] INTEGER  NOT NULL,
	PRIMARY KEY ([idseccion]),
    FOREIGN KEY ([idnummodelo], [idsubnummodelo], [idnumpagina]) REFERENCES tbpagina ([idnummodelo], [idsubnummodelo], [idnumpagina]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

CREATE INDEX [FKI_idnummodelo] ON [tbseccion] ([idnummodelo],[idsubnummodelo]);

CREATE INDEX [FKI_idnumpagina] ON [tbseccion] ([idnumpagina],[idnummodelo],[idsubnummodelo]);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idnummodelo]) REFERENCES tbpagina ([idnummodelo])

-----------------------------------------------------------------------------
-- tbsistemadeinformacion
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbsistemadeinformacion];


CREATE TABLE [tbsistemadeinformacion]
(
	[idsistemadeinformacion] VARCHAR(5)  NOT NULL,
	[imagenlogo] BLOB,
	[tipodearchivo] VARCHAR(3),
	[denominacion] VARCHAR(255)  NOT NULL,
	PRIMARY KEY ([idsistemadeinformacion])
);

-----------------------------------------------------------------------------
-- tbtematica
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbtematica];


CREATE TABLE [tbtematica]
(
	[idcodtematica] INTEGER  NOT NULL PRIMARY KEY AUTOINCREMENT,
	[nombredescriptivo] VARCHAR(255)  NOT NULL
);

-----------------------------------------------------------------------------
-- tbvariante
-----------------------------------------------------------------------------

DROP TABLE IF EXISTS [tbvariante];


CREATE TABLE [tbvariante]
(
	[idnummodelo] VARCHAR(8)  NOT NULL,
	[idsubnummodelo] VARCHAR(2)  NOT NULL,
	[idcodvariante] INTEGER  NOT NULL,
	[codvariantesup] INTEGER,
	[nombredevariante] VARCHAR(255)  NOT NULL,
	[notametodologica] VARCHAR(255),
	PRIMARY KEY ([idnummodelo],[idsubnummodelo],[idcodvariante]),
	FOREIGN KEY ([idnummodelo], [idsubnummodelo]) REFERENCES tbdescriptordelmodelo ([idnummodelo], [idsubnummodelo]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE,
	FOREIGN KEY ([idnummodelo], [idsubnummodelo], [codvariantesup]) REFERENCES tbvariante ([idnummodelo], [idsubnummodelo], [codvariantesup]) MATCH SIMPLE ON UPDATE CASCADE ON DELETE CASCADE
);

-- SQLite does not support foreign keys; this is just for reference
-- FOREIGN KEY ([idnummodelo]) REFERENCES tbvariante ([idnummodelo])
