CREATE TABLE tm_admins (
	id INTEGER NOT NULL AUTO_INCREMENT,
	cduser INTEGER NOT NULL COMMENT 'Relacion con la tabla TM_USERS', 
	fact TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'Fecha de actualizacion del registro',
	cduseract INTEGER NOT NULL COMMENT 'Cod. del usuario que actualiza el registro',
	CONSTRAINT tm_admins_pk PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE tm_rols (
	id VARCHAR(2) NOT NULL,
	name VARCHAR(32) NOT NULL,
	description VARCHAR(32) NOT NULL,
	availablequerys INTEGER NOT NULL COMMENT 'Numero de querys disponibles para el rol',
	CONSTRAINT tm_rols_pk PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE tm_languages (
	id VARCHAR(2),
	name VARCHAR(32) NOT NULL,
	CONSTRAINT tm_languages_pk PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE tm_users (
	id INTEGER NOT NULL AUTO_INCREMENT,
	name VARCHAR(32) NOT NULL,
	surname1 VARCHAR(32),
	surname2 VARCHAR(32),
	emaill VARCHAR(32) NOT NULL,
	pass VARCHAR(32) NOT NULL,
	cdrol VARCHAR(2) DEFAULT 'A' NOT NULL,
	active INTEGER DEFAULT 1 NOT NULL,
	cdlanguage VARCHAR(2) NOT NULL,
	fact TIMESTAMP NOT NULL COMMENT 'Fecha de actualizacion del registro',
	cduseract INTEGER NOT NULL COMMENT 'Cod. del usuario que actualiza el registro',
	CONSTRAINT tm_users_pk PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE tm_querys (
	id INTEGER NOT NULL AUTO_INCREMENT,
	fcreate TIMESTAMP NOT NULL COMMENT 'Fecha en que se crea el registro',
	cduser INTEGER NOT NULL COMMENT 'Relacion con la tabla TM_USERS', 
	CONSTRAINT tm_querys_pk PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE tm_userlocations (
	id INTEGER NOT NULL AUTO_INCREMENT,
	cduser INTEGER NOT NULL COMMENT 'Relacion con la tabla TM_USERS', 
	woeid1 INTEGER DEFAULT 776688 NOT NULL COMMENT 'valencia',	
	woeid2 INTEGER DEFAULT 766273 NOT NULL COMMENT 'Madrid',	
	woeid3 INTEGER DEFAULT 753692 NOT NULL COMMENT 'Barcelona',	
	fupdate	TIMESTAMP COMMENT 'Fecha en que se actualiza registro',
	CONSTRAINT tm_userloc_pk PRIMARY KEY (id)
) ENGINE=InnoDB;

CREATE TABLE tm_locations (
	id INTEGER NOT NULL AUTO_INCREMENT,
	name VARCHAR(32) NOT NULL,
	countrycode	VARCHAR(8),
	countryname VARCHAR(32),
	placecode INTEGER NOT NULL,
	placename VARCHAR(32) NOT NULL,
	url VARCHAR(256) NOT NULL,
	woeid INTEGER NOT NULL,	
	fcreate TIMESTAMP NOT NULL COMMENT 'Fecha en que se crea el registro',
	CONSTRAINT tm_locations_pk PRIMARY KEY (id)
) ENGINE=InnoDB;


CREATE TABLE tm_trends (
	id INTEGER NOT NULL AUTO_INCREMENT,
	accesslevel INTEGER NOT NULL,
	asof TIMESTAMP NOT NULL,
	trendat TIMESTAMP NOT NULL,
	location INTEGER NOT NULL COMMENT 'Relacion con la tabla LOCATIONS',
	fcreate TIMESTAMP NOT NULL COMMENT 'Fecha en que se crea el registro',
	CONSTRAINT tm_trends_pk PRIMARY KEY (id)
) ENGINE=InnoDB;


CREATE TABLE tm_trend (
	id INTEGER NOT NULL AUTO_INCREMENT,
	name VARCHAR(32) NOT NULL,
	query VARCHAR(256) NOT NULL,
	url VARCHAR(256) NOT NULL,
	trends INTEGER COMMENT 'Relacion con la tabla TRENDS',
	fcreate TIMESTAMP NOT NULL COMMENT 'Fecha en que se crea el registro',
	CONSTRAINT tm_trend_pk PRIMARY KEY (id)
) ENGINE=InnoDB;


CREATE TABLE tm_payments (
	id INTEGER NOT NULL AUTO_INCREMENT,
	cduser INTEGER NOT NULL,
	fcreate TIMESTAMP NOT NULL COMMENT 'Fecha en que se crea el registro',
	fpay TIMESTAMP NOT NULL COMMENT 'Fecha en que se realiza el pago',
	quantity INTEGER NOT NULL COMMENT 'Cantidad abonada',
	CONSTRAINT tm_payments_pk PRIMARY KEY (id)
) ENGINE=InnoDB;


CREATE TABLE tm_activelocations (
	id INTEGER NOT NULL AUTO_INCREMENT,
	location INTEGER NOT NULL COMMENT 'Relacion con la tabla LOCATIONS',
	name VARCHAR(32) NOT NULL,
	latitude DECIMAL(10,6), 
	longitude DECIMAL(10,6),
	CONSTRAINT tm_activelocations_pk PRIMARY KEY (id)
) ENGINE=InnoDB;

alter table tm_users add constraint fk_users_language foreign key (cdlanguage) references tm_languages (id) on delete restrict on update restrict;
alter table tm_users add constraint fk_users_rol foreign key (cdrol) references tm_rols (id) on delete restrict on update restrict;
alter table tm_admins add constraint fk_admins_users foreign key (id) references tm_users (id) on delete restrict on update restrict;
alter table tm_trends add constraint fk_trends_location foreign key (location) references tm_locations (id) on delete restrict on update restrict;
alter table tm_trend add constraint fk_trend_trends foreign key (trends) references tm_trends (id) on delete cascade on update cascade;
alter table tm_activelocations add constraint fk_activelocations_locations foreign key (location) references tm_locations (id) on delete restrict on update restrict;
alter table tm_admins add constraint fk_users_admins foreign key (cdUser) references tm_users (id) on delete restrict on update restrict;
alter table tm_userlocations add constraint fk_userlocations foreign key (cdUser) references tm_users (id) on delete restrict on update restrict;
alter table tm_querys add constraint fk_querys_users foreign key (cdUser) references tm_users (id) on delete restrict on update restrict;