#
#Dades particulars de cada Centre
#

DROP TABLE IF EXISTS a_centre;
CREATE TABLE a_centre (
nom varchar(255) NOT NULL,
fons varchar(255) NOT NULL,
idioma varchar(255) NOT NULL,
idiomes text,
emailprojecte varchar(255),
http varchar(255)
);
INSERT into a_centre VALUES ('','#c2c4ac','catala','catala;español','','');

# --------------------------------------------------------
#
# Structure de la table 'chapitre'
#

DROP TABLE IF EXISTS faq_chapitre;
CREATE TABLE faq_chapitre (
   id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
   document int(10) unsigned DEFAULT '0' NOT NULL,
   date_creation datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   date_modif datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   auteur varchar(255) NOT NULL,
   email varchar(255) NOT NULL,
   indexation tinyint(4) DEFAULT '0' NOT NULL,
   PRIMARY KEY (id)
);

#
# Contenu de la table 'chapitre'
#


# --------------------------------------------------------
#
# Structure de la table 'chapitre_content'
#

DROP TABLE IF EXISTS faq_chapitre_content;
CREATE TABLE faq_chapitre_content (
   id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
   id_chapitre int(10) unsigned DEFAULT '0' NOT NULL,
   titre varchar(255) NOT NULL,
   langue char(3) NOT NULL,
   mainteneur varchar(255) NOT NULL,
   email varchar(255) NOT NULL,
   status char(1) NOT NULL,
   PRIMARY KEY (id),
   KEY id_chapitre (id_chapitre),
   KEY langue (langue)
);

#
# Contenu de la table 'chapitre_content'
#


# --------------------------------------------------------
#
# Structure de la table 'doc_security'
#

DROP TABLE IF EXISTS faq_doc_security;
CREATE TABLE faq_doc_security (
   id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
   id_user int(10) unsigned DEFAULT '0' NOT NULL,
   id_doc int(10) unsigned DEFAULT '0' NOT NULL,
   langue char(3) NOT NULL,
   _read tinyint(1) unsigned DEFAULT '0' NOT NULL,
   _write tinyint(1) unsigned DEFAULT '0' NOT NULL,
   _admin tinyint(1) unsigned DEFAULT '0' NOT NULL,
   _create tinyint(1) unsigned DEFAULT '0' NOT NULL,    PRIMARY KEY (id),
   KEY id_user (id_user),
   KEY id_doc (id_doc)
);

#
# Contenu de la table 'doc_security'
#


# --------------------------------------------------------
#
# Structure de la table 'document'
#

DROP TABLE IF EXISTS faq_document;
CREATE TABLE faq_document (
   id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
   date_creation datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   date_modif datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   date_fi date DEFAULT '0000-00-00' NOT NULL,
   auteur varchar(255) NOT NULL,
   email varchar(255) NOT NULL,
   version varchar(255) NOT NULL,
   id_author int(10) unsigned DEFAULT '0',
   PRIMARY KEY (id)
);

#
# Contenu de la table 'document'
#


# --------------------------------------------------------
#
# Structure de la table 'document_content'
#

DROP TABLE IF EXISTS faq_document_content;
CREATE TABLE faq_document_content (
   id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
   id_document int(10) unsigned DEFAULT '0' NOT NULL,
   titre varchar(255) NOT NULL,
   abstract text NOT NULL,
   langue char(3) NOT NULL,
   mainteneur varchar(255) NOT NULL,
   email varchar(255) NOT NULL,
   docmaster char(3) NOT NULL,
   status char(1) NOT NULL,
   fitxer varchar(30) NOT NULL,
   PRIMARY KEY (id),
   KEY id_document (id_document),
   KEY langue (langue)
);

#
# Contenu de la table 'document_content'
#


# --------------------------------------------------------
#
# Structure de la table 'lien_interne'
#

DROP TABLE IF EXISTS faq_lien_interne;
CREATE TABLE faq_lien_interne (
   id_document int(10) unsigned DEFAULT '0' NOT NULL,
   id_chapitre int(10) unsigned DEFAULT '0' NOT NULL,
   id_question int(10) unsigned DEFAULT '0' NOT NULL,
   langue char(3) NOT NULL,
   lien varchar(255) NOT NULL,
   PRIMARY KEY (id_document, id_chapitre, id_question, langue)
);

#
# Contenu de la table 'lien_interne'
#


# --------------------------------------------------------
#
# Structure de la table 'question'
#

DROP TABLE IF EXISTS faq_question;
CREATE TABLE faq_question (
   id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
   chapitre int(10) unsigned DEFAULT '0' NOT NULL,
   date_creation datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   date_modif datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   auteur varchar(255) NOT NULL,
   email varchar(255) NOT NULL,
   indexation tinyint(4) DEFAULT '0' NOT NULL,
   PRIMARY KEY (id)
);

#
# Contenu de la table 'question'
#


# --------------------------------------------------------
#
# Structure de la table 'question_content'
#

DROP TABLE IF EXISTS faq_question_content;
CREATE TABLE faq_question_content (
   id int(10) unsigned DEFAULT '0' NOT NULL auto_increment,
   id_question int(10) unsigned DEFAULT '0' NOT NULL,
   question varchar(255) NOT NULL,
   reponse text NOT NULL,
   langue char(3) NOT NULL,
   mainteneur varchar(255) NOT NULL,
   email varchar(255) NOT NULL,
   status char(1) NOT NULL,
   PRIMARY KEY (id),
   KEY id_question (id_question),
   KEY langue (langue)
);

#
# Contenu de la table 'question_content'
#


# --------------------------------------------------------
#
# Structure de la table 'session'
#

DROP TABLE IF EXISTS faq_session;
CREATE TABLE faq_session (
   id_session varchar(255) NOT NULL,
   id_user int(10) unsigned DEFAULT '0' NOT NULL,
   date_fin bigint(20) unsigned DEFAULT '0' NOT NULL,
   path varchar(255) NOT NULL,
   PRIMARY KEY (id_session)
);

#
# Contenu de la table 'session'
#

#
# genera la taula de les noticies associades al calendari
#

DROP TABLE IF EXISTS n_noticies;
CREATE TABLE n_noticies (id INT not null AUTO_INCREMENT, data DATE not null , noticia TEXT not null ,
id_creada MEDIUMINT UNSIGNED,
id_avisat MEDIUMINT UNSIGNED,
vist TINYINT (1) UNSIGNED not null, 
 INDEX (id, data)); 
INSERT INTO n_noticies VALUES(NULL,'2001-10-23','Hola','1','1','1');

DROP TABLE IF EXISTS c_messages;
CREATE TABLE c_messages (
   type tinyint(1) DEFAULT '0' NOT NULL,
   room varchar(30) NOT NULL,
   username varchar(30) NOT NULL,
   latin1 tinyint(1) DEFAULT '0' NOT NULL,
   m_time int(11) DEFAULT '0' NOT NULL,
   address varchar(30) NOT NULL,
   message text NOT NULL
);

#
# GENERA LES TAULES DEL MODUL CHAT, I LA TAULA D'USUARIS REGISTRATS
#

DROP TABLE IF EXISTS c_users;
CREATE TABLE c_users (
   room varchar(30) NOT NULL,
   username varchar(30) NOT NULL,
   latin1 tinyint(1) DEFAULT '0' NOT NULL,
   u_time int(11) DEFAULT '0' NOT NULL,
   status varchar(1) NOT NULL,
   ip varchar(16) NOT NULL,
   UNIQUE room (room, username)
);

DROP TABLE IF EXISTS c_reg_users;
CREATE TABLE c_reg_users (
   id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
   userlevel TINYINT(3),
   username varchar(30) NOT NULL,
   latin1 tinyint(1) DEFAULT '0' NOT NULL,
   password varchar(32) NOT NULL,
   firstname varchar(64) NOT NULL,
   lastname varchar(64) NOT NULL,
   nom_prenom varchar(128) NOT NULL,
   country varchar(64) NOT NULL,
   website varchar(64) NOT NULL,
   email varchar(64) NOT NULL,
   em_user varchar(30) NOT NULL,
   em_pass varchar(30) NOT NULL,
   em_server varchar(30) NOT NULL,
   showemail tinyint(1) NOT NULL,
   perms varchar(9) NOT NULL,
   rooms varchar(128) NOT NULL,
   reg_time int(11) DEFAULT '0' NOT NULL,
   ip varchar(16) NOT NULL,
   visites mediumint(8) DEFAULT '0' NOT NULL,
   theme tinyint(4) DEFAULT '0' NOT NULL,
   date_last_connection datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   session_multiple tinyint(1) unsigned DEFAULT '0' NOT NULL,
   gender tinyint(1) DEFAULT '0' NOT NULL,
   grup VARCHAR (10) not null,
   primary key (id),
   KEY (username)
);
INSERT INTO c_reg_users VALUES(NULL,'9','admin', '1', 'admin', '', '', '', 'catala', '','','','', '', 0, 'admin', '', '', '', '0', '5', '0000-00-00 00:00:00', '0', '0', '');


DROP TABLE IF EXISTS c_reg_usersini;
CREATE TABLE c_reg_usersini (
   id_reg MEDIUMINT UNSIGNED NOT NULL,
   userlevel TINYINT(3),
   username varchar(30) NOT NULL,
   password varchar(32) NOT NULL,
   firstname varchar(64) NOT NULL,
   lastname varchar(64) NOT NULL,
   grup VARCHAR (10) not null
);
INSERT INTO c_reg_usersini (id_reg, userlevel, username, password, firstname, lastname, grup) VALUES ('1', '9', 'admin', 'admin', '', '', 'profes') ;

DROP TABLE IF EXISTS c_pares_alu;
CREATE TABLE c_pares_alu (
   dnitutor varchar(9) NOT NULL,
   dnialu varchar(9) NOT NULL
);

DROP TABLE IF EXISTS c_ban_users;
CREATE TABLE c_ban_users (
   username varchar(30) NOT NULL,
   latin1 tinyint(1) DEFAULT '0' NOT NULL,
   ip varchar(16) NOT NULL,
   rooms varchar(100) NOT NULL,
   ban_until int(11) DEFAULT '0' NOT NULL
);
DROP TABLE IF EXISTS c_rooms;
CREATE TABLE c_rooms (
id INT not null AUTO_INCREMENT, 
room VARCHAR (15) not null , 
PRIMARY KEY (id), INDEX (id, room)
);
INSERT INTO c_rooms (id, room) VALUES ('1', 'General');


DROP TABLE IF EXISTS c_rooms_seg; 
CREATE TABLE c_rooms_seg ( 
id VARCHAR (30) NOT NULL, id_room TINYINT (4) not null ); 
INSERT INTO c_rooms_seg VALUES ('admin','1');


#
#TAULES RELACIONADES AMB EL FORO
#


DROP TABLE IF EXISTS f_mensajes;
CREATE TABLE f_mensajes (
id_mensaje int(11) DEFAULT '0' NOT NULL auto_increment,
id_phorum int(11) DEFAULT '0' NOT NULL,
id_padre int(11) DEFAULT '0' NOT NULL, asunto varchar(255) NOT NULL,
descripcion text NOT NULL,
autor varchar(255),
autor_mail varchar(255),
autor_host varchar(255),
fecha datetime,
num_hijos int(11) DEFAULT '0' NOT NULL,
PRIMARY KEY (id_mensaje),
KEY id_mensaje (id_mensaje, id_phorum, id_padre),
UNIQUE id_mensaje_2 (id_mensaje)
);
INSERT into f_mensajes VALUES(NULL,'1','0','Avis inicial',
'Esperem que us sigui util','admin','fsuau@cervera.uned.es',
'','2001-10-22','0');

DROP TABLE IF EXISTS f_temes;
CREATE TABLE f_temes (
id MEDIUMINT  not null AUTO_INCREMENT,
id_moder MEDIUMINT UNSIGNED NOT NULL,
f_name VARCHAR (30) not null ,
last_data DATE not null ,
data_fi DATE not null ,
numero TINYINT (4) not null,  
PRIMARY KEY (id)); 
INSERT INTO f_temes (id, id_moder,f_name,last_data,data_fi,numero) 
VALUES (NULL,'1','General','','','0'); 

DROP TABLE IF EXISTS f_temes_seg;
CREATE TABLE f_temes_seg (
id MEDIUMINT (8) UNSIGNED NOT NULL , id_tema TINYINT (4) not null, correu char(1) not null );
INSERT INTO f_temes_seg (id,id_tema,correu)
VALUES ('1','1','1'); 

DROP TABLE IF EXISTS f_mensa_user;
CREATE TABLE f_mensa_user (
id_misnou INT (11)  NOT NULL , id_user MEDIUMINT (8) UNSIGNED NOT NULL, id_foro TINYINT (3) );

#
#TAULES CORREU INTERN
#
DROP TABLE IF EXISTS correu_enviat;
CREATE TABLE correu_enviat (
   id MEDIUMINT UNSIGNED NOT NULL,
   idto MEDIUMINT UNSIGNED NOT NULL,
   assumpte varchar(255) NOT NULL,
   texte text NOT NULL,
   fitxer varchar(255) NOT NULL,
   data bigint(20) UNSIGNED not null );

DROP TABLE IF EXISTS correu_alu_prof;
CREATE TABLE correu_alu_prof (
	id_alumne MEDIUMINT (8),
	id_prof MEDIUMINT (8),
	id_doc int (10) unsigned,
      date_fi date DEFAULT '0000-00-00' NOT NULL
);

#
#TAULES PER REALITZAR EXAMENS
#
DROP TABLE IF EXISTS e_test;
CREATE TABLE e_test (id_preg MEDIUMINT (8) not null AUTO_INCREMENT, pregunta TEXT not null , 
num_resp SMALLINT not null , resp_bona TEXT not null , resp_dol_1 TEXT not null , 
resp_dol_2 TEXT not null , resp_dol_3 TEXT not null , resp_dol_4 TEXT not null ,
 id_tema MEDIUMINT (8) not null, imatge CHAR(1) default '0', PRIMARY KEY(id_preg) ); 

DROP TABLE IF EXISTS e_tema;
CREATE TABLE e_tema (id_tema MEDIUMINT (8) not null AUTO_INCREMENT, tema TEXT not null , 
id_prof MEDIUMINT (8) not null , num_preg MEDIUMINT (8) not null , temps SMALLINT not null ,
 data_fi DATE not null, numerot tinyint (4) not null, PRIMARY KEY(id_tema)) ;

DROP TABLE IF EXISTS e_tema_seg;
CREATE TABLE e_tema_seg (id_alumne MEDIUMINT (8) not null , id_tema MEDIUMINT (8) not null, numero tinyint (4) not null ) ;

DROP TABLE IF EXISTS e_tema_alu;
CREATE TABLE e_tema_alu (id MEDIUMINT (8) DEFAULT '0' not null AUTO_INCREMENT,
id_alumne MEDIUMINT (8) DEFAULT '0' not null ,
id_tema TINYINT (4) DEFAULT '0' not null,  tema TEXT not null ,data DATETIME, PRIMARY KEY(id));

DROP TABLE IF EXISTS e_alu_test;
CREATE TABLE e_alu_test (id0 MEDIUMINT (8) not null AUTO_INCREMENT,
id MEDIUMINT (8),
be TINYINT (3) DEFAULT '0', 
pregunta TEXT not null,
resp_1 TEXT not null, 
resp_2 TEXT not null, 
resp_3 TEXT not null, 
resp_4 TEXT not null, 
resp_5 TEXT not null, 
solucio TINYINT (1),
num_resp SMALLINT not null,
PRIMARY KEY(id0) );

DROP TABLE IF EXISTS p_links;
CREATE TABLE p_links (id MEDIUMINT (8) not null AUTO_INCREMENT,
usuari MEDIUMINT (8),
categoria VARCHAR(40) not null,
nom VARCHAR(255) not null,
url VARCHAR(255) not null,
descripcio VARCHAR(255) not null,
PRIMARY KEY(id) );

#
# taules de les dades acadèmiques
#
DROP TABLE IF EXISTS d_assignatures;
CREATE TABLE d_assignatures (id MEDIUMINT (8) not null AUTO_INCREMENT,
codi VARCHAR(10) not null,
nom VARCHAR(40) not null,
tipus VARCHAR(2) not null,
curs VARCHAR(10) not null,
prof MEDIUMINT (8) not null,
PRIMARY KEY(id) );

DROP TABLE IF EXISTS d_proves;
CREATE TABLE d_proves (id MEDIUMINT (8) not null AUTO_INCREMENT,
data DATE not null,
texte VARCHAR(40) not null,
pes decimal(6,2) not null,
codi_assig VARCHAR (10) not null,
avalua text not null,
PRIMARY KEY(id) );

DROP TABLE IF EXISTS d_prova_alu;
CREATE TABLE d_prova_alu (
id_prova MEDIUMINT (8),
id_alumne MEDIUMINT (8),
nota DECIMAL(5,2) not null,
comenta VARCHAR (255)
);

DROP TABLE IF EXISTS d_assig_alu;
CREATE TABLE d_assig_alu (
id_alumne MEDIUMINT (8),
codi_assig VARCHAR (10) not null
);

DROP TABLE IF EXISTS d_assist;
CREATE TABLE d_assist (
id_alumne MEDIUMINT (8),
data DATE not null,
hora VARCHAR(1) not null,
tipus text not null,
datajust DATE not null,
curs VARCHAR (10) not null
);
DROP TABLE IF EXISTS d_activitats;
CREATE TABLE d_activitats (
id_activ MEDIUMINT (8) not null AUTO_INCREMENT,
data DATE not null,
codi varchar (5) not null,
titol text not null,
fitxer text not null,
id_prof MEDIUMINT (8),
grup_assig text not null,
tipus varchar(1) not null,
PRIMARY KEY(id_activ) 
);
DROP TABLE IF EXISTS d_alu_activ;
CREATE TABLE d_alu_activ (
id_alumne MEDIUMINT (8),
codi_activ varchar (5) not null
);

#
#imatges a la pàgina de presentació
#
DROP TABLE IF EXISTS imatges;
CREATE TABLE imatges (
imatge text ,
texte text,
activa varchar(1) not null
);

DROP TABLE IF EXISTS l_sessions;
CREATE TABLE l_sessions (
   id varchar(32) NOT NULL,
   data text NOT NULL,
   expire int(11) unsigned DEFAULT '0' NOT NULL,
   PRIMARY KEY (id)
);

DROP TABLE IF EXISTS l_categories;
CREATE TABLE l_categories (
   ID smallint(5) unsigned NOT NULL auto_increment,
   Category varchar(32) NOT NULL,
   PID smallint(5) unsigned DEFAULT '0' NOT NULL,
   Children enum('Top','Rand','Desc','Vert') DEFAULT 'Top' NOT NULL,
   TopChildren tinyint(3) unsigned DEFAULT '3' NOT NULL,
   AllowSites enum('Y','N') DEFAULT 'Y' NOT NULL,
   ShowSiteCount enum('Y','N') DEFAULT 'Y' NOT NULL,
   Description blob NOT NULL,
   PRIMARY KEY (ID),
   KEY Category (Category),
   KEY TopChildren (TopChildren),
   KEY Children (Children),
   KEY PID (PID)
);

DROP TABLE IF EXISTS l_links;
CREATE TABLE l_links (
   ID smallint(5) unsigned NOT NULL auto_increment,
   SiteName varchar(100) NOT NULL,
   SiteURL varchar(100) NOT NULL,
   LastUpdate timestamp(14),
   Added varchar(14),
   Description blob NOT NULL,
   Category smallint(5) unsigned DEFAULT '0' NOT NULL,
   Country varchar(100) DEFAULT 'United_States.gif' NOT NULL,
   UserName varchar(16) NOT NULL,
   Password varchar(16) NOT NULL,
   Hint varchar(50) NOT NULL,
   Email varchar(50) NOT NULL,
   HitsIn smallint(5) unsigned DEFAULT '0' NOT NULL,
   HitsOut smallint(5) unsigned DEFAULT '0' NOT NULL,
   InIP varchar(15) NOT NULL,
   OutIP varchar(15) NOT NULL,
   PRIMARY KEY (ID),
   UNIQUE SiteURL (SiteURL),
   KEY Category (Category)
);

DROP TABLE IF EXISTS l_related;
CREATE TABLE l_related (
   id smallint(5) unsigned NOT NULL auto_increment,
   cat_id smallint(5) unsigned DEFAULT '0' NOT NULL,
   rel_id smallint(5) unsigned DEFAULT '0' NOT NULL,
   PRIMARY KEY (id)
);

DROP TABLE IF EXISTS l_reviews;
CREATE TABLE l_reviews (
   ID smallint(5) unsigned NOT NULL auto_increment,
   SiteID smallint(5) unsigned DEFAULT '0' NOT NULL,
   ReviewTitle varchar(100) NOT NULL,
   Review blob NOT NULL,
   Reviewer varchar(50) NOT NULL,
   ReviewerEmail varchar(50) NOT NULL,
   ReviewerURL varchar(100) NOT NULL,
   Rating tinyint(2) unsigned DEFAULT '0' NOT NULL,
   Status enum('New','Show','Hide') DEFAULT 'New' NOT NULL,
   Added timestamp(14),
   PRIMARY KEY (ID),
   KEY Rating (Rating)
);

DROP TABLE IF EXISTS l_settings;
CREATE TABLE l_settings (
   ID tinyint(4) unsigned NOT NULL auto_increment,
   SiteTitle varchar(50) DEFAULT 'phpLinks' NOT NULL,
   Theme varchar(16) DEFAULT 'original' NOT NULL,
   Language varchar(16) DEFAULT 'english' NOT NULL,
   Name varchar(50) NOT NULL,
   Email varchar(50) NOT NULL,
   DateFormat varchar(16) DEFAULT 'M j, Y' NOT NULL,
   DefaultCountry varchar(50) DEFAULT 'United States' NOT NULL,
   ColCount enum('2','3','4'),
   ManuallyValidate enum('Y','N') DEFAULT 'Y' NOT NULL,
   URLValidate enum('Y','N') DEFAULT 'Y' NOT NULL,
   NewSubmissionEmail enum('Y','N') DEFAULT 'Y' NOT NULL,
   SiteAdditionEmail enum('Y','N') DEFAULT 'Y' NOT NULL,
   SiteDeletionEmail enum('Y','N') DEFAULT 'Y' NOT NULL,
   PerPage tinyint(3) DEFAULT '10' NOT NULL,
   NavLinks tinyint(3) DEFAULT '3' NOT NULL,
   BaseURL varchar(50) NOT NULL,
   BasePath varchar(50) NOT NULL,
   OuterFrame enum('Y','N') DEFAULT 'Y' NOT NULL,
   PRIMARY KEY (ID)
);

INSERT INTO l_settings (ID, SiteTitle, Theme, Language, Name, Email, DateFormat, DefaultCountry, ColCount, ManuallyValidate, URLValidate, NewSubmissionEmail, SiteAdditionEmail, SiteDeletionEmail, PerPage, NavLinks, BaseURL, BasePath, OuterFrame) 
VALUES ( '1', 'Recursos a la red', 'original', 'english', 'Projecte', 'fsuau@cervera.uned.es', 'M j, Y', 'Espanya.gif', '3', 'Y', 'N', 'Y', 'Y', 'Y', '10', '4', 'http://216.118.116.194/links', '/path/to/phplinks', 'N');

DROP TABLE IF EXISTS l_specs;
CREATE TABLE l_specs (
   ID tinyint(3) unsigned NOT NULL auto_increment,
   SiteNameMin tinyint(4) DEFAULT '0' NOT NULL,
   SiteNameMax tinyint(4) DEFAULT '0' NOT NULL,
   DescMin tinyint(4) DEFAULT '0' NOT NULL,
   DescMax smallint(4) DEFAULT '0' NOT NULL,
   UserNameMin tinyint(4) DEFAULT '0' NOT NULL,
   UserNameMax tinyint(4) DEFAULT '0' NOT NULL,
   PWMin tinyint(4) DEFAULT '0' NOT NULL,
   PWMax tinyint(4) DEFAULT '0' NOT NULL,
   HintMin tinyint(4) DEFAULT '0' NOT NULL,
   HintMax tinyint(4) DEFAULT '0' NOT NULL,
   EmailSpec varchar(255) DEFAULT '^([a-zA-Z0-9_-])+@([a-zA-Z0-9_-])+(.[a-zA-Z0-9_-])+' NOT NULL,
   ReviewTitleMin tinyint(4) DEFAULT '5' NOT NULL,
   ReviewTitleMax tinyint(4) DEFAULT '120' NOT NULL,
   ReviewerMin tinyint(4) DEFAULT '4' NOT NULL,
   ReviewerMax tinyint(4) DEFAULT '50' NOT NULL,
   ReviewMin tinyint(4) DEFAULT '50' NOT NULL,
   ReviewMax mediumint(9) DEFAULT '1000' NOT NULL,
   ReviewerEmailSpec varchar(255) DEFAULT '^([a-zA-Z0-9_-])+@([a-zA-Z0-9_-])+(.[a-zA-Z0-9_-])+' NOT NULL,
   PRIMARY KEY (ID)
);

INSERT INTO l_specs (ID, SiteNameMin, SiteNameMax, DescMin, DescMax, UserNameMin, UserNameMax, PWMin, PWMax, HintMin, HintMax, EmailSpec, ReviewTitleMin, ReviewTitleMax, ReviewerMin, ReviewerMax, ReviewMin, ReviewMax, ReviewerEmailSpec) 
VALUES ( '1', '5', '120', '12', '384', '1', '16', '1', '16', '1', '50', '^([a-zA-Z0-9_-])+@([a-zA-Z0-9_-])+(\\.[a-zA-Z0-9_-])+', '5', '120', '4', '50', '50', '1000', '^([a-zA-Z0-9_-])+@([a-zA-Z0-9_-])+(\\.[a-zA-Z0-9_-])+');

DROP TABLE IF EXISTS l_temp;
CREATE TABLE l_temp (
   ID int(7) unsigned NOT NULL auto_increment,
   SiteName varchar(100) NOT NULL,
   SiteURL varchar(100) NOT NULL,
   LastUpdate timestamp(14),
   Added varchar(14),
   Description blob NOT NULL,
   Category smallint(6) unsigned DEFAULT '0' NOT NULL,
   Country varchar(100) DEFAULT 'United_States.gif' NOT NULL,
   UserName varchar(16) NOT NULL,
   Password varchar(16) NOT NULL,
   Hint varchar(50) NOT NULL,
   Email varchar(50) NOT NULL,
   HitsIn int(7) unsigned DEFAULT '0' NOT NULL,
   HitsOut int(7) unsigned DEFAULT '0' NOT NULL,
   InIP varchar(15) NOT NULL,
   OutIP varchar(15) NOT NULL,
   PRIMARY KEY (ID),
   UNIQUE SiteURL (SiteURL)
);

DROP TABLE IF EXISTS l_terms;
CREATE TABLE l_terms (
   ID int(10) unsigned NOT NULL auto_increment,
   Term varchar(32) NOT NULL,
   PRIMARY KEY (ID),
   KEY Term (Term)
);

#
#quadres horaris ocupació
#
DROP TABLE IF EXISTS quadres;
CREATE TABLE quadres (
id int(11) DEFAULT '0' NOT NULL auto_increment,
aula text,posicio varchar(2),activitat text,
id_assig varchar(10) not null,nom_assig varchar(40) not null,curs varchar(10) not null,id_prof mediumint(8) not null,
nom_prof varchar(40) not null, dataini date DEFAULT '0000-00-00' NOT NULL, datafi date DEFAULT '0000-00-00' NOT NULL,pt varchar(1),
primary key(id)
);
INSERT INTO quadres (id, aula, posicio, activitat, id_assig, nom_assig, curs, id_prof, nom_prof, dataini, datafi, pt) VALUES ('1', 'qwe', '', '', '', '', '', '', '', '', '', '') ;
DROP TABLE IF EXISTS q_aules;
CREATE TABLE q_aules (
aula text
);

DROP TABLE IF EXISTS utilitzacio;
CREATE TABLE utilitzacio (
   id MEDIUMINT UNSIGNED NOT NULL,
   nom_prenom varchar(128) NOT NULL,
   darrera_conec datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
   menu VARCHAR (5) not null,
   accio VARCHAR (50) not null,
   id_prof MEDIUMINT  NOT NULL
);