CREATE TABLE subscriptions (
  id  INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id  INT NOT NULL,
  CONSTRAINT fk_user_id
    FOREIGN KEY (user_id) REFERENCES users (ID)
    ON UPDATE CASCADE,
  begins_at  DATE NOT NULL,
  expires_at  DATE NOT NULL,
  order_source  CHAR(4) NOT NULL,
  order_code  VARCHAR(32) NOT NULL,
  CONSTRAINT fk_payment_product
    FOREIGN KEY (order_source, order_code)
    REFERENCES payments (order_source, order_code),
  product_id  INT NOT NULL,
  CONSTRAINT fk_product_id
    FOREIGN KEY (product_id)
    REFERENCES products (id)
    ON UPDATE CASCADE,
  auth_sub_id  DECIMAL(8) UNSIGNED NULL,
  cc_expiry DATE NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE payments (
  order_source  CHAR(4) NOT NULL,
  order_code  VARCHAR(32) NOT NULL,
  PRIMARY KEY (order_source, order_code),
  sequence  DECIMAL(3) UNSIGNED ZEROFILL NOT NULL DEFAULT 0,
  currency CHAR(3) NOT NULL DEFAULT 'USD',
  price    DECIMAL(9,2) SIGNED NOT NULL DEFAULT 0,
  paid    DECIMAL(9,2) SIGNED NOT NULL DEFAULT 0,
  transaction_id  DECIMAL(12) UNSIGNED NULL,
  payer    VARCHAR(100) NULL,
  stamp    DATETIME NOT NULL,
  verified  ENUM('Y','N') NOT NULL DEFAULT 'N',
  payment_for  ENUM('DIGIT','SHIP','ENROL','NIL') NOT NULL
               DEFAULT 'ENROL',
  was_successful ENUM('Y','N') NOT NULL DEFAULT 'Y',
  was_test ENUM('Y','N') NOT NULL DEFAULT 'N',
  result_reason VARCHAR(50) NULL -- ,
--  payer INT NULL,
--  CONSTRAINT fk_payer_user
--    FOREIGN KEY (payer)
--    REFERENCES users (ID)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE user_addresses (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  user_id INT NOT NULL,
  CONSTRAINT fk_user_of_address_id
    FOREIGN KEY (user_id)
    REFERENCES users (ID),
  address1 VARCHAR(50) NOT NULL,
  address2 VARCHAR(50) NULL,
  city VARCHAR(50) NULL,
  province VARCHAR(100) NULL,
  country CHAR(2) NOT NULL,
  active ENUM('Y','N') NOT NULL DEFAULT 'Y',
  verified ENUM('Y','N') NOT NULL DEFAULT 'N'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE user_address_preferences (
  user_id INT NOT NULL PRIMARY KEY,
  CONSTRAINT fk_user_of_addx_pref_id
    FOREIGN KEY (user_id)
    REFERENCES users (ID),
  billing_address INT UNSIGNED NOT NULL,
  mailing_address INT UNSIGNED NOT NULL,
  shipping_address INT UNSIGNED NOT NULL,
  promo_mail ENUM('Y','N') NOT NULL DEFAULT 'N',
  CONSTRAINT fk_billing
    FOREIGN KEY (billing_address)
    REFERENCES user_addresses (id),
  CONSTRAINT fk_mailing
    FOREIGN KEY (mailing_address)
    REFERENCES user_addresses (id),
  CONSTRAINT fk_shipping
    FOREIGN KEY (shipping_address)
    REFERENCES user_addresses (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE authorize_net_payments (
  order_source CHAR(4) NOT NULL,
  order_code VARCHAR(32) NOT NULL,
  CONSTRAINT fk_payment_product
    FOREIGN KEY (order_source, order_code)
    REFERENCES payments (order_source, order_code),
  CONSTRAINT order_source = 'auth',
  approval_code VARCHAR(32),
  billing_address INT UNSIGNED NULL,
  CONSTRAING fk_auth_address (billing_address)
    REFERENCES user_addresses (ID)
);

UPDATE subscription 
   SET renewalProduct=(SELECT id
                         FROM products
                        WHERE productCode='unknown')
 WHERE renewalProduct IS NULL;

UPDATE subscription
   SET renewalProduct=(SELECT id
                         FROM products
                        WHERE productCode='lifetime')
 WHERE orderID LIKE 'LIFE-%';

INSERT  DELAYED
  INTO  subscriptions
    ( user_id, order_source, order_code, begins_at, expires_at,
      product_id, auth_sub_id )
SELECT  s.userID, 'A1TA', SUBSTR(s.orderID, 5), l.runDate,
  s.expirationDate, s.renewalProduct, s.subscriptionID
  FROM  subscription     AS s,
        subscriptionLog  AS l
 WHERE     s.orderID LIKE 'A1TA%'
       AND s.orderID=l.orderID;
       
INSERT  DELAYED
  INTO  payments
        ( order_source, order_code, sequence, price, paid,
          transaction_id, stamp, verified )
SELECT  'A1TA', SUBSTR(s.orderID, 5), 0, p.price, p.price,
        s.transactionID, l.runDate, 'Y'
  FROM  subscription     AS s,
        subscriptionLog  AS l,
        products         AS p
 WHERE      s.orderID LIKE 'A1TA%'
        AND s.orderID = l.orderID
        AND s.renewalProduct = p.id;



INSERT  DELAYED
  INTO  subscriptions
    ( user_id, order_source, order_code, begins_at, expires_at,
      product_id, auth_sub_id )
SELECT  s.userID, 'test', SUBSTR(s.orderID, 5), l.runDate,
  s.expirationDate, s.renewalProduct, s.subscriptionID
  FROM  subscription     AS s,
        subscriptionLog  AS l
 WHERE     s.orderID LIKE 'test%'
       AND s.orderID=l.orderID;
       
INSERT  DELAYED
  INTO  payments
        ( order_source, order_code, sequence, price, paid,
          transaction_id, stamp, verified )
SELECT  'test', SUBSTR(s.orderID, 5), 0, p.price, p.price,
        s.transactionID, l.runDate, 'Y'
  FROM  subscription     AS s,
        subscriptionLog  AS l,
        products         AS p
 WHERE      s.orderID LIKE 'test%'
        AND s.orderID = l.orderID
        AND s.renewalProduct = p.id;


INSERT  DELAYED
  INTO  subscriptions
    ( user_id, order_source, order_code, begins_at, expires_at,
      product_id, auth_sub_id )
SELECT  s.userID, 'LIFE', SUBSTR(s.orderID, 6), l.runDate,
  s.expirationDate, s.renewalProduct, s.subscriptionID
  FROM  subscription     AS s,
        subscriptionLog  AS l
 WHERE     s.orderID LIKE 'LIFE-%'
       AND s.orderID=l.orderID;
       
INSERT  DELAYED
  INTO  payments
        ( order_source, order_code, sequence, price, paid,
          transaction_id, stamp, verified )
SELECT  'LIFE', SUBSTR(s.orderID, 6), 0, p.price, p.price,
        s.transactionID, l.runDate, 'Y'
  FROM  subscription     AS s,
        subscriptionLog  AS l,
        products         AS p
 WHERE      s.orderID LIKE 'LIFE-%'
        AND s.orderID = l.orderID
        AND s.renewalProduct = p.id;
        
        

INSERT  DELAYED
  INTO  subscriptions
    ( user_id, order_source, order_code, begins_at, expires_at,
      product_id, auth_sub_id )
SELECT  s.userID, 'auth', SUBSTR(s.orderID, 6), l.runDate,
  s.expirationDate, s.renewalProduct, s.subscriptionID
  FROM  subscription     AS s,
        subscriptionLog  AS l
 WHERE     s.orderID LIKE 'auth-%'
       AND s.orderID=l.orderID;
       
INSERT  DELAYED
  INTO  payments
        ( order_source, order_code, sequence, price, paid,
          transaction_id, stamp, verified )
SELECT  'auth', SUBSTR(s.orderID, 6), 0, p.price, p.price,
        s.transactionID, l.runDate, 'Y'
  FROM  subscription     AS s,
        subscriptionLog  AS l,
        products         AS p
 WHERE      s.orderID LIKE 'auth-%'
        AND s.orderID = l.orderID
        AND s.renewalProduct = p.id;

INSERT  DELAYED
  INTO  subscriptions
    ( user_id, order_source, order_code, begins_at, expires_at,
      product_id, auth_sub_id )
SELECT  s.userID, 'BRUN', SUBSTR(s.orderID, 5), l.runDate,
  s.expirationDate, s.renewalProduct, s.subscriptionID
  FROM  subscription     AS s,
        subscriptionLog  AS l
 WHERE     s.orderID LIKE 'BRUN%'
       AND s.orderID=l.orderID;
       
INSERT  DELAYED
  INTO  payments
        ( order_source, order_code, sequence, price, paid,
          transaction_id, stamp, verified )
SELECT  'BRUN', SUBSTR(s.orderID, 5), 0, p.price, p.price,
        s.transactionID, l.runDate, 'Y'
  FROM  subscription     AS s,
        subscriptionLog  AS l,
        products         AS p
 WHERE      s.orderID LIKE 'BRUN%'
        AND s.orderID = l.orderID
        AND s.renewalProduct = p.id;


INSERT  DELAYED
  INTO  subscriptions
    ( user_id, order_source, order_code, begins_at, expires_at,
      product_id, auth_sub_id )
SELECT  s.userID, 'EMEM', SUBSTR(s.orderID, 3), l.runDate,
  s.expirationDate, s.renewalProduct, s.subscriptionID
  FROM  subscription     AS s,
        subscriptionLog  AS l
 WHERE     s.orderID LIKE 'EM%'
       AND s.orderID=l.orderID;
       
INSERT  DELAYED
  INTO  payments
        ( order_source, order_code, sequence, price, paid,
          transaction_id, stamp, verified )
SELECT  'EMEM', SUBSTR(s.orderID, 3), 0, p.price, p.price,
        s.transactionID, l.runDate, 'Y'
  FROM  subscription     AS s,
        subscriptionLog  AS l,
        products         AS p
 WHERE      s.orderID LIKE 'EM%'
        AND s.orderID = l.orderID
        AND s.renewalProduct = p.id;
        
INSERT  DELAYED
  INTO  subscriptions
    ( user_id, order_source, order_code, begins_at, expires_at,
      product_id, auth_sub_id )
SELECT  s.userID, 'card', SUBSTR(s.orderID, 3), l.runDate,
  s.expirationDate, s.renewalProduct, s.subscriptionID
  FROM  subscription     AS s,
        subscriptionLog  AS l
 WHERE     s.orderID LIKE 'S#%'
       AND s.orderID=l.orderID;
       
INSERT  DELAYED
  INTO  payments
        ( order_source, order_code, sequence, price, paid,
          transaction_id, stamp, verified )
SELECT  'card', SUBSTR(s.orderID, 3), 0, p.price, p.price,
        s.transactionID, l.runDate, 'Y'
  FROM  subscription     AS s,
        subscriptionLog  AS l,
        products         AS p
 WHERE      s.orderID LIKE 'S#%'
        AND s.orderID = l.orderID
        AND s.renewalProduct = p.id;


INSERT  DELAYED
  INTO  subscriptions
    ( user_id, order_source, order_code, begins_at, expires_at,
      product_id, auth_sub_id )
SELECT  s.userID, 'unkn', SUBSTR(s.orderID, 1, 32), l.runDate,
  s.expirationDate, s.renewalProduct, s.subscriptionID
  FROM  subscription     AS s,
        subscriptionLog  AS l
 WHERE      s.orderID NOT LIKE 'A1TA%'
        AND s.orderID NOT LIKE 'EM%'
        AND s.orderID NOT LIKE 'BRUN%'
        AND s.orderID NOT LIKE 'LIFE-%'
        AND s.orderID NOT LIKE 'test%'
        AND s.orderID NOT LIKE 'S#%'
        AND s.orderID NOT LIKE 'auth-%'   
        AND s.orderID=l.orderID;
       
INSERT  DELAYED
  INTO  payments
        ( order_source, order_code, sequence, price, paid,
          transaction_id, stamp, verified )
SELECT  'unkn', SUBSTR(s.orderID, 1, 32), 0, p.price, p.price,
        s.transactionID, l.runDate, 'Y'
  FROM  subscription     AS s,
        subscriptionLog  AS l,
        products         AS p
 WHERE       s.orderID NOT LIKE 'A1TA%'
        AND s.orderID NOT LIKE 'EM%'
        AND s.orderID NOT LIKE 'BRUN%'
        AND s.orderID NOT LIKE 'LIFE-%'
        AND s.orderID NOT LIKE 'test%'
        AND s.orderID NOT LIKE 'S#%'
        AND s.orderID NOT LIKE 'auth-%'   
        AND s.orderID = l.orderID
        AND s.renewalProduct = p.id;
        
        


CREATE VIEW old_subscription AS
SELECT  s.user_id    AS userID,
  CONCAT(s.order_source, '-', s.order_code)
        AS orderID,
  s.expires_at    AS expirationDate,
  s.product_id    AS renewalProduct,
  s.expires_at    AS renewalDate,
  p.transaction_id  AS transactionID,
  DATE(p.stamp)    AS lastRenewal,
  s.auth_sub_id    AS subscriptionID
  FROM  subscriptions  AS s
    LEFT JOIN payments AS p
      ON  s.order_source  = p.order_source
         AND  s.order_code  = p.order_code
    WHERE p.sequence =
      (SELECT  MAX(p2.sequence)
         FROM  payments AS p2
        WHERE  s.order_source  = p2.order_source
          AND s.order_code  = p2.order_code
          AND p2.verified = 'Y');
  
CREATE VIEW old_subscriptionLog AS
SELECT  s.user_id    AS userID,
  CONCAT(s.order_source, '-', s.order_code)
        AS orderID,
  s.begins_at    AS runDate
  FROM  subscriptions  AS s;  

CREATE TABLE languages (
 id CHAR(2) NOT NULL PRIMARY KEY,
 title VARCHAR(30) NOT NULL,
 title_en VARCHAR(30) NOT NULL UNIQUE KEY
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

INSERT INTO languages (id, title, title_en)
 VALUES ('en', 'English', 'English');

CREATE TABLE dialects (
 language CHAR(2) NOT NULL,
 CONSTRAINT FOREIGN KEY (language) REFERENCES languages (id),
 dialect CHAR(2) NOT NULL,
 PRIMARY KEY (language, dialect),
 title VARCHAR(30) NOT NULL,
 title_en VARCHAR(30) NOT NULL UNIQUE KEY
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

CREATE TABLE avatarClasses (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 filename VARCHAR(50) NOT NULL,
 forFree ENUM('Y','N') NOT NULL DEFAULT 'N',
 forVIT ENUM('Y','N') NOT NULL DEFAULT 'N',
 title VARCHAR(25) NOT NULL UNIQUE KEY,
 defaultBaseColor INT UNSIGNED NOT NULL DEFAULT X'FFFFFF',
 defaultExtraColor INT UNSIGNED NOT NULL DEFAULT X'000000',
 defaultPattern INT UNSIGNED NOT NULL,
 -- CONSTRAINT FOREIGN KEY (defaultPattern) REFERENCES itemTemplates (id),
 defaultPatternColor INT UNSIGNED NOT NULL DEFAULT X'808080'
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

INSERT INTO avatarClasses (id, title, filename, forFree)
 VALUES
 (0, 'No Avatar', '', 'N'),
 (1, 'Zap Quad', 'zapQuad.swf', 'Y'),
 (2, 'Dottie Quad', 'dottieQuad.swf', 'Y'),
 (3, 'Superstar Quad', 'superstarQuad.swf', 'Y'),
 (4, 'Li''l MC Quad', 'lilMcQuad.swf', 'Y'),
 (5, 'Sparkle Quad', 'sparkleQuad.swf', 'Y'),
 (6, 'Moo Quad', 'mooQuad.swf', 'Y'),
 (7, 'Cupid Quad', 'cupidQuad.swf', 'Y'),
 (8, 'Flora Quad', 'floraQuad.swf', 'Y');

INSERT INTO avatarClasses (id, title, filename, forVIT)
 VALUES (9, 'Biped Toot', 'masterToot.swf', 'Y');

CREATE TABLE parents (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 mail VARCHAR(255) NOT NULL UNIQUE KEY,
 mailConfirmSent DATE NULL,
 mailConfirmed DATE NULL,
 canContact ENUM('Y','N') NOT NULL DEFAULT 'N',
 givenName VARCHAR(30) NULL
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

CREATE TABLE kickedReasons (
 id CHAR(3) NOT NULL PRIMARY KEY,
 message VARCHAR(100) NOT NULL UNIQUE KEY,
 secretMessage TEXT NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

CREATE TABLE neighborhoods (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 title VARCHAR(30) NOT NULL UNIQUE KEY
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

INSERT INTO neighborhoods (id, title) VALUES
 (0, 'City Limits'),
 (1, 'Tootlantis'),
 (2, 'Big Tootoona'),
 (3, 'Comic World'),
 (4, 'Dino Land'),
 (5, 'Enchanted Forest'),
 (6, 'Moonbase T'),
 (7, 'Pirates'' Cove'),
 (8, 'Toot Castle'),
 (9, 'Toot Safari');

CREATE TABLE staffLevels (
 id DECIMAL(1,0) NOT NULL PRIMARY KEY,
 title VARCHAR(20) NOT NULL UNIQUE KEY
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

INSERT INTO staffLevels VALUES
 (0, 'Public user'),
 (1, 'Staff member'),
 (2, 'Moderator'),
 (3, 'Account Service'),
 (4, 'Designer'),
 (8, 'Developer');

CREATE TABLE users (
 ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
 userName VARCHAR(30) NULL,
 password VARCHAR(30) NOT NULL DEFAULT X'0A',
 avatarClass INT UNSIGNED NOT NULL DEFAULT 0,
 CONSTRAINT FOREIGN KEY (avatarClass) REFERENCES avatarClasses (ID),
 baseColor INT UNSIGNED NOT NULL DEFAULT X'FFFFFF',
 extraColor INT UNSIGNED NOT NULL DEFAULT X'FFFFFF',
 mail VARCHAR(255) NULL,
 mailConfirmed DATE NULL,
 birthDate DATE NOT NULL DEFAULT '0000-00-00',
 ageGroup ENUM('K','T','A','X') NOT NULL DEFAULT 'X',
 language CHAR(2) NOT NULL DEFAULT 'en',
 dialect CHAR(2) NOT NULL DEFAULT 'US',
 CONSTRAINT FOREIGN KEY (language) REFERENCES languages (id),
 CONSTRAINT FOREIGN KEY (language, dialect) REFERENCES dialects (language, dialect),
 parentID INT UNSIGNED NULL,
 CONSTRAINT FOREIGN KEY (parentID) REFERENCES parents (id),
 approvedDate DATE NULL,
 emailPlusDate DATE NULL,
 canTalk ENUM('Y','N') NOT NULL DEFAULT 'N',
 canEnterChatZone ENUM('Y','N') NOT NULL DEFAULT 'N',
 canEnterMenuZone ENUM('Y','N') NOT NULL DEFAULT 'Y',
 givenName VARCHAR(30) NULL,
 kickedUntil DATETIME NULL,
 kickedReasonCode CHAR(3) NULL,
 kickedBy INT NULL,
 CONSTRAINT FOREIGN KEY (kickedBy) REFERENCES users (ID),
 CONSTRAINT FOREIGN KEY (kickedReasonCode) REFERENCES kickedReasons (id),
 isActive ENUM('OK','CAN','BAN') NOT NULL DEFAULT 'OK',
 needsNaming ENUM('Y','N') NOT NULL DEFAULT 'N',
 staffLevel DECIMAL (1,0) NOT NULL DEFAULT 0,
 CONSTRAINT FOREIGN KEY (staffLevel) REFERENCES staffLevels (id),
 peanuts DECIMAL(9,0) UNSIGNED NOT NULL DEFAULT 0,
 tootTimeLeft DECIMAL (2,0) UNSIGNED NOT NULL DEFAULT 99,
 tootTimeLeftMinutes DECIMAL(2,0) UNSIGNED NOT NULL DEFAULT 99,
 tootTimeRefill DECIMAL (2,0) UNSIGNED NOT NULL DEFAULT 99,
 tootTimerType ENUM('D','W') NOT NULL DEFAULT 'D',
 canContact ENUM('Y','N') NOT NULL DEFAULT 'N'
) ENGINE=InnoDB DEFAULT CHARSET='utf8';


CREATE TABLE userHistory (
 ID INT NOT NULL,
 CONSTRAINT FOREIGN KEY (ID) REFERENCES users (ID),
 KEY (ID),
 stamp DATETIME NOT NULL,
 KEY (ID, stamp),
 userName VARCHAR(30) NULL,
 KEY (userName),
 password VARCHAR(30) NOT NULL DEFAULT X'0A',
 avatarClass INT UNSIGNED NOT NULL DEFAULT 0,
 CONSTRAINT FOREIGN KEY (avatarClass) REFERENCES avatarClasses,
 baseColor INT UNSIGNED NOT NULL DEFAULT X'FFFFFF',
 extraColor INT UNSIGNED NOT NULL DEFAULT X'FFFFFF',
 mail VARCHAR(255) NULL,
 mailConfirmed DATE NULL,
 birthDate DATE NOT NULL DEFAULT '0000-00-00',
 ageGroup ENUM('K','T','A','X') NOT NULL DEFAULT 'X',
 language CHAR(2) NOT NULL DEFAULT 'en',
 dialect CHAR(2) NOT NULL DEFAULT 'US',
 CONSTRAINT FOREIGN KEY (language) REFERENCES languages (id),
 CONSTRAINT FOREIGN KEY (language, dialect) REFERENCES dialects (language, dialect),
 parentID INT UNSIGNED NULL,
 CONSTRAINT FOREIGN KEY (parentID) REFERENCES parents (id),
 approvedDate DATE NULL,
 emailPlusDate DATE NULL,
 canTalk ENUM('Y','N') NOT NULL DEFAULT 'N',
 canEnterChatZone ENUM('Y','N') NOT NULL DEFAULT 'N',
 canEnterMenuZone ENUM('Y','N') NOT NULL DEFAULT 'Y',
 givenName VARCHAR(30) NULL,
 kickedUntil DATETIME NULL,
 kickedReasonCode CHAR(3) NULL,
 kickedBy INT NULL,
 CONSTRAINT FOREIGN KEY (kickedBy) REFERENCES users (ID),
 CONSTRAINT FOREIGN KEY (kickedReasonCode) REFERENCES kickedReasons (id),
 isActive ENUM('OK','CAN','BAN') NOT NULL DEFAULT 'OK',
 needsNaming ENUM('Y','N') NOT NULL DEFAULT 'N',
 staffLevel DECIMAL (1,0) NOT NULL DEFAULT 0,
 CONSTRAINT FOREIGN KEY (staffLevel) REFERENCES staffLevels (id),
 peanuts DECIMAL(9,0) UNSIGNED NOT NULL DEFAULT 0,
 tootTimeLeft DECIMAL (2,0) UNSIGNED NOT NULL DEFAULT 99,
 tootTimeLeftMinutes DECIMAL(2,0) UNSIGNED NOT NULL DEFAULT 99,
 tootTimeRefill DECIMAL (2,0) UNSIGNED NOT NULL DEFAULT 99,
 tootTimerType ENUM('D','W') NOT NULL DEFAULT 'D',
 canContact ENUM('Y','N') NOT NULL DEFAULT 'N',
 parentApprovedName ENUM('Y','N') NOT NULL DEFAULT 'N',
 requestedName VARCHAR(30) NOT NULL DEFAULT '',
 nameApprovedAt DATETIME NULL,
 nameRequestedAt DATETIME NULL,
 nameApprovedByUserID INT UNSIGNED NULL,
 CONSTRAINT FOREIGN KEY (nameApprovedByUserID) REFERENCES users (ID)
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

CREATE TABLE userHouses (
 userID INT NOT NULL UNIQUE KEY,
 CONSTRAINT FOREIGN KEY (userID) REFERENCES users (ID),
 houseNeighborhood INT UNSIGNED NOT NULL DEFAULT 0,
 CONSTRAINT FOREIGN KEY (houseNeighborhood) REFERENCES neighborhoods (id),
 houseStreet DECIMAL(2,0) UNSIGNED NOT NULL DEFAULT 0,
 houseStreetDirection ENUM('N','S','E','W') NOT NULL DEFAULT 'N',
 houseNumber DECIMAL(3,0) UNSIGNED NOT NULL DEFAULT 0,
 PRIMARY KEY (houseNeighborhood, houseNumber, houseStreet, houseStreetDirection)
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

CREATE TABLE houseRooms (
 houseNeighborhood INT UNSIGNED NOT NULL DEFAULT 0,
 CONSTRAINT FOREIGN KEY (houseNeighborhood) REFERENCES neighborhoods (id),
 houseStreet DECIMAL(2,0) UNSIGNED NOT NULL DEFAULT 0,
 houseStreetDirection ENUM('N','S','E','W') NOT NULL DEFAULT 'N',
 houseNumber DECIMAL(3,0) UNSIGNED NOT NULL DEFAULT 0,
 roomID DECIMAL(2,0) UNSIGNED NOT NULL DEFAULT 0,
 PRIMARY KEY (houseNeighborhood, houseNumber, houseStreet, houseStreetDirection, roomID),
 connectN DECIMAL(2,0) UNSIGNED NULL,
 connectS DECIMAL(2,0) UNSIGNED NULL,
 connectE DECIMAL(2,0) UNSIGNED NULL,
 connectW DECIMAL(2,0) UNSIGNED NULL,
 connectU DECIMAL(2,0) UNSIGNED NULL,
 connectD DECIMAL(2,0) UNSIGNED NULL
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

CREATE TABLE items (
 id INT UNSIGNED NOT NULL AUTO_INCREMENT,
 
) ENGINE=InnoDB DEFAULT CHARSET='utf8';

CREATE TABLE journal (
 id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
 verb VARCHAR(20) NOT NULL,
 zone VARCHAR(30) NOT NULL,
 user VARCHAR(30) NOT NULL,
 object VARCHAR(100) NULL,
 stamp DATETIME NOT NULL,
 INDEX (stamp)
) ENGINE=InnoDB DEFAULT CHARSET='utf8';
  
CREATE TABLE filterNames (ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, title VARCHAR(6) NOT NULL UNIQUE KEY) ENGINE=InnoDB;

INSERT INTO filterNames (title) VALUES ('chat'),('names');
CREATE TABLE filterPatterns (ID INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, pattern VARCHAR(255) NOT NULL UNIQUE KEY, defaultAction ENUM('W','K','X') NOT NULL DEFAULT 'W', matcher ENUM('S','R') NOT NULL DEFAULT 'R');
INSERT INTO filterPatterns (pattern, defaultAction) SELECT word, 'W' FROM new_blacklist WHERE severity='BAD';
INSERT INTO filterPatterns (pattern, defaultAction) SELECT word, 'K' FROM new_blacklist WHERE severity='WORSE';
CREATE TABLE filterExceptions (ID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY , patternID INT UNSIGNED NOT NULL, CONSTRAINT FOREIGN KEY (patternID) REFERENCES filterPatterns (ID), exception VARCHAR(255) NOT NULL, action ENUM('W','K','X') NOT NULL DEFAULT 'X')  ENGINE=InnoDB;

ALTER TABLE roomActiveItems CHANGE COLUMN direction direction VARCHAR(2) NOT NULL DEFAULT 'S';

ALTER TABLE userItems ADD COLUMN isActive ENUM('Y','N') NOT NULL DEFAULT 'N';
DELETE FROM activeItems WHERE itemID IN (34,2);
INSERT INTO userItems (userID, itemID, isActive) SELECT userID, itemID, 'Y' FROM activeItems;
DROP TABLE activeItems;
ALTER TABLE users ADD COLUMN basic8Choice ENUM('1','2','3','4','5','6','7','8') NOT NULL DEFAULT '6';
ALTER TABLE users ADD COLUMN passRecoverQ VARCHAR(100) NOT NULL DEFAULT 'What is your favorite food?' ;
ALTER TABLE users ADD COLUMN passRecoverA VARCHAR(100) NULL ;
CREATE TABLE ignoreList (userID INT NOT NULL, buddyID INT NOT NULL, PRIMARY KEY (userID, buddyID), CONSTRAINT FOREIGN KEY (buddyID) REFERENCES users (ID) ON UPDATE CASCADE, CONSTRAINT FOREIGN KEY (userID) REFERENCES users (ID) ON UPDATE CASCADE) ENGINE=InnoDB ;
ALTER TABLE avatarClasses ADD COLUMN canColor ENUM('Y','N') NOT NULL DEFAULT 'N';
INSERT INTO avatarClasses (id, filename, forFree,forVIT,title,defaultBaseColor,defaultExtraColor,defaultPatternColor, canColor) VALUES (10, 'toot.swf','N','Y','V.I.T.', X'ffffff', 0, X'ccccff', 'Y');
CREATE TABLE moderatorEvents ( id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, verb CHAR(4) NOT NULL, r0 INT, r1 INT, r2 INT, posted DATETIME NOT NULL, handled DATETIME NULL, handledBy INT NULL, handledRating DECIMAL(1) UNSIGNED NULL, ratedBy INT NULL, CONSTRAINT FOREIGN KEY (handledBy) REFERENCES users (id), CONSTRAINT FOREIGN KEY (ratedBy) REFERENCES users (id) ) ENGINE=InnoDB ;
