
# This is a fix for InnoDB in MySQL >= 4.1.x
# It "suspends judgement" for fkey relationships until are tables are set.
SET FOREIGN_KEY_CHECKS = 0;

-- ---------------------------------------------------------------------
-- messages
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `messages`;

CREATE TABLE `messages`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `sender_name` VARCHAR(100) NOT NULL,
    `receiver_name` VARCHAR(100) NOT NULL,
    `message` TEXT NOT NULL,
    `timestamp` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `unread_count` INTEGER DEFAULT 0 NOT NULL,
    `read_timestamp` TIMESTAMP NULL,
    PRIMARY KEY (`id`),
    INDEX `messages_fi_b01074` (`sender_name`),
    INDEX `messages_fi_794386` (`receiver_name`),
    CONSTRAINT `messages_fk_b01074`
        FOREIGN KEY (`sender_name`)
        REFERENCES `users` (`username`)
        ON DELETE CASCADE,
    CONSTRAINT `messages_fk_794386`
        FOREIGN KEY (`receiver_name`)
        REFERENCES `users` (`username`)
        ON DELETE CASCADE
) ENGINE=InnoDB CHARACTER SET='utf8mb4' COLLATE='utf8mb4_general_ci';

-- ---------------------------------------------------------------------
-- users
-- ---------------------------------------------------------------------

DROP TABLE IF EXISTS `users`;

CREATE TABLE `users`
(
    `id` INTEGER NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(1000),
    `dob` VARCHAR(15),
    `gender` VARCHAR(6),
    `username` VARCHAR(100) NOT NULL,
    `password` VARCHAR(1000) NOT NULL,
    `push_token` VARCHAR(255) NOT NULL,
    `user_role` VARCHAR(255) NOT NULL,
    `refresh_token` VARCHAR(1000),
    PRIMARY KEY (`id`),
    UNIQUE INDEX `unique_username` (`username`)
) ENGINE=InnoDB CHARACTER SET='utf8mb4' COLLATE='utf8mb4_general_ci';

# This restores the fkey checks, after having unset them earlier
SET FOREIGN_KEY_CHECKS = 1;
