USE myhq_2;


CREATE TABLE session (
        id       CHAR(32) NOT NULL PRIMARY KEY,
	a_session       TEXT NOT NULL
);



# user :
CREATE TABLE user(
        userid              INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
        name                VARCHAR(50),

        username            VARCHAR(50),
        password            VARCHAR(250),
        email               VARCHAR(250),
        hash                VARCHAR(50), # utilized to verify the email address
        status              SMALLINT,    # 0     normal user, unregistered
                                         # 1     normal unverified user, access to account functions.
                                         # 2     normal verified user, access to add comments
                                         # 3     + ability to add links (and edit self links)
                                         # 4     + ability to add categories
                                         # 5     + moderator + ability to edit/delete listings (not of self)
                                         # 9     super user
                                         # -1    deactivated user
                                         # -9    banned user
        defaultstatus       SMALLINT,    # same as above but is used as the default when the userstatus is updated (ex. when email changes.)
        updated             TIMESTAMP,
        created             TIMESTAMP,
	index(username,password),
        index(email(10)),
        index(hash(5))
);

INSERT INTO user(name, username, password, status) VALUES ('Janssen Choong', 'jchoong','mailsas','9');


# for symbolic links only(?). 
CREATE TABLE link_symlink(
        catlinkid           INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	parentid            INTEGER UNSIGNED,
	categoryid          INTEGER UNSIGNED,
	type                CHAR(1),  # S = symbolic/shortcut. Otherwise, normal

	userid              INTEGER UNSIGNED NOT NULL,
        updated             TIMESTAMP,
        created             TIMESTAMP,
	index(categoryid, userid)

);


CREATE TABLE link_category(
	categoryid          INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	parentcategoryid    INTEGER UNSIGNED,

        title               VARCHAR(50),
	tag                 VARCHAR(255),
        description         TEXT,

	# used for a flat layout
	xpos                SMALLINT UNSIGNED, 
	ypos                SMALLINT UNSIGNED, 

	public              CHAR(1),     # is the category public

	
        sorttype            VARCHAR(10),  # alphabetical, numeric, rating, self_visit, pub_visit, all_visit - for links
	
	userid              INTEGER UNSIGNED NOT NULL,
	updated             TIMESTAMP,
        created             TIMESTAMP,
	index(userid)
);




CREATE TABLE link_link(
	linkid              INTEGER UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	categoryid          INTEGER UNSIGNED NOT NULL,
	title               TEXT,
        url                 TEXT,
        description         TEXT,

	tag                 VARCHAR(255),     

        position            SMALLINT,     # if not alphabetically sorted, the position relative to other links
        sortvalue           INTEGER UNSIGNED,    #  if sorted numerically
   
        numvisited          MEDIUMINT UNSIGNED,  # number of times visited by owner
        pubvisited          INTEGER UNSIGNED,    # number of times visited by public

	rating              SMALLINT,     # 1-10

	status              VARCHAR(10),  # active, dead, etc for link checking
	public              CHAR(1),      # if link is public
	adult               CHAR(1),      # if adult content
 
        userid              INTEGER UNSIGNED NOT NULL,
        updated             TIMESTAMP,
        created             TIMESTAMP,
	index(userid),
	index(tag)
);

