-- ***************************************************************************************************
--
-- Seagull CMS source code
-- (C)2006 by umop ap!sdn (aka E. Lee Gagnon)
-- 
-- Module: build_users_table.sql
--
-- These MySQL commands must be executed when installing the CMS if no RepleteBB board has already been
-- installed on the same database. For completeness, all the RepleteBB fields are included here, even 
-- those not used by the CMS, so that a board may be added later.
--
-- ***************************************************************************************************

CREATE TABLE users
             (user_id      INT,
              user_name    VARCHAR(58),
              password     VARCHAR(29),
              join_date    DATETIME,
              avatar_url   VARCHAR(255),
              signature    TEXT,
              INDEX(signature(1)),
              location     VARCHAR(255),
              post_count   INT,
              time_zone    DECIMAL(4,2),         -- Hours and minutes
              dst          TINYINT,              -- Nonzero if DST is in effect
              showtoken    MEDIUMINT,            -- 1s bit = show avatars
                                                 -- 2s bit = show signatures
                                                 -- 4s bit = show posted images
                                                 -- 8s bit = show emoticons
												 -- 16s bit = subscribe to threads by default when posting
                                                 -- 32s bit = enable sig by default
                                                 -- 64s bit = hide email address from other members
                                                 -- 128s bit = enable private messaging
												 -- 256s bit = send email on receipt of new pvt msg
												 -- 512s and 1024s bits: 00 = default to no email notification
												 --                      01 = default to instant email notification
												 --                      10 = default to daily digest
												 --                      11 = default to weekly digest
												 -- 2048s bit = open threads in new page
												 -- 4096s bit = send HTML thread reply notifications
              last_active  DATETIME,
              last_visit   DATETIME,             -- Updated only when either the user logs out or their previous session has expired
              online       TINYINT,              -- Nonzero if online now
              home_page    VARCHAR(255),
              birthday     DATE,                 -- Year is 0000 if unspecified
              occupation   VARCHAR(255),
              biography    VARCHAR(255),
              aim          VARCHAR(29),
              icq          VARCHAR(29),
              msn          VARCHAR(29),
              yim          VARCHAR(29),
              blog         VARCHAR(255),
              email        VARCHAR(255),
              gender       TINYINT,              -- 0 = no answer
                                                 -- 1 = M
                                                 -- 2 = F
              group_id     INT,
              member_title VARCHAR(58),
              CONSTRAINT user_id_pk PRIMARY KEY (user_id),
              CONSTRAINT user_name_uk UNIQUE (user_name)
             );
ALTER TABLE users ADD (validation_code VARCHAR(20));
ALTER TABLE users ADD (thread_perpage TINYINT);
ALTER TABLE users ADD (lockout TINYINT);
ALTER TABLE users ADD (mbrflags MEDIUMINT);      -- 1s bit = freeze member group - no auto advance
CREATE TABLE banned_ip
             (ip_addr      VARCHAR(15));
CREATE TABLE skins
             (skin_id      TINYINT,              
              skin_name    VARCHAR(29),          -- The name which appears in the drop down box
              skin_dir     VARCHAR(58),          -- Local directory containing the skin files. Must end with a forward slash!
              CONSTRAINT skin_id_pk PRIMARY KEY (skin_id)
             );
INSERT INTO skins VALUES(0, 'Default', 'default_skin/');
INSERT INTO skins VALUES(1, 'Hot Pink', 'pink_skin/');
INSERT INTO skins VALUES(2, 'Keylime', 'lime_skin/');
INSERT INTO skins VALUES(3, 'Castle', 'castle_skin/');
INSERT INTO skins VALUES(4, 'Newsprint', 'newsprint_skin/');
INSERT INTO skins VALUES(5, 'Lavender', 'lavender_skin/');