CREATE TABLE `dspam_preferences` (
    `uid` INT(10) UNSIGNED NOT NULL,
    `preference` VARCHAR(32) NOT NULL COLLATE 'latin1_general_ci',
    `value` VARCHAR(64) NOT NULL COLLATE 'latin1_general_ci',
    PRIMARY KEY (`uid`, `preference`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

CREATE TABLE `dspam_signature_data` (
    `uid` INT(10) UNSIGNED NOT NULL,
    `signature` CHAR(32) NOT NULL COLLATE 'latin1_general_ci',
    `data` LONGBLOB NOT NULL,
    `length` INT(10) UNSIGNED NOT NULL,
    `created_on` DATE NOT NULL,
    PRIMARY KEY (`uid`, `signature`),
    CONSTRAINT `FK_dspam_signature_data_dspam_virtual_uids` FOREIGN KEY (`uid`) REFERENCES `dspam_virtual_uids` (`uid`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `dspam_stats` (
    `uid` INT(10) UNSIGNED NOT NULL,
    `spam_learned` BIGINT(20) UNSIGNED NOT NULL,
    `innocent_learned` BIGINT(20) UNSIGNED NOT NULL,
    `spam_misclassified` BIGINT(20) UNSIGNED NOT NULL,
    `innocent_misclassified` BIGINT(20) UNSIGNED NOT NULL,
    `spam_corpusfed` BIGINT(20) UNSIGNED NOT NULL,
    `innocent_corpusfed` BIGINT(20) UNSIGNED NOT NULL,
    `spam_classified` BIGINT(20) UNSIGNED NOT NULL,
    `innocent_classified` BIGINT(20) UNSIGNED NOT NULL,
    PRIMARY KEY (`uid`),
    CONSTRAINT `FK_dspam_stats_dspam_virtual_uids` FOREIGN KEY (`uid`) REFERENCES `dspam_virtual_uids` (`uid`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `dspam_token_data` (
    `uid` INT(10) UNSIGNED NOT NULL,
    `token` BIGINT(20) UNSIGNED NOT NULL,
    `spam_hits` BIGINT(20) UNSIGNED NOT NULL,
    `innocent_hits` BIGINT(20) UNSIGNED NOT NULL,
    `last_hit` DATE NOT NULL,
    PRIMARY KEY (`uid`, `token`),
    CONSTRAINT `FK_dspam_token_data_dspam_virtual_uids` FOREIGN KEY (`uid`) REFERENCES `dspam_virtual_uids` (`uid`) ON UPDATE CASCADE ON DELETE CASCADE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
;

CREATE TABLE `dspam_virtual_uids` (
    `uid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `username` VARCHAR(128) NOT NULL,
    PRIMARY KEY (`uid`),
    UNIQUE INDEX `UQ_username` (`username`)
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB;