CREATE TABLE  `status` (
    `id` INTEGER auto_increment , 
    `hashid` CHAR(14), 
    `status_name` VARCHAR(255), 
    PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE  `user` (
    `id` INTEGER auto_increment , 
    `hashid` CHAR(14), 
    `email` VARCHAR(255), 
    `name` VARCHAR(255), 
    `organization` VARCHAR(255), 
    `city` VARCHAR(255), 
    `role` VARCHAR(255), `user` VARCHAR(255), 
    `num_vehicles` VARCHAR(255), 
    `client_type` VARCHAR(255),
    PRIMARY KEY (`id`)) ENGINE=InnoDB;

CREATE TABLE  `conversation` (
    `id` INTEGER auto_increment , 
    `hashid` CHAR(14), 
    `recipient_number` VARCHAR(255), 
    `validation_code` VARCHAR(6), 
    `fk_status` INTEGER, 
    `fk_user` INTEGER, 
    PRIMARY KEY (`id`), 
    FOREIGN KEY (`fk_status`) 
    REFERENCES `status` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE, 
    FOREIGN KEY (`fk_user`) 
    REFERENCES `user` (`id`) 
    ON DELETE SET NULL ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE  `message` (
    `id` INTEGER auto_increment , 
    `hashid` CHAR(14), 
    `whatsapp_id` VARCHAR(255), 
    `content` VARCHAR(255), 
    `time_received` DATETIME, 
    `fk_conversation` INTEGER, 
    PRIMARY KEY (`id`), 
    FOREIGN KEY (`fk_conversation`) 
    REFERENCES `conversation` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;

CREATE TABLE  `status_log` (
    `id` INTEGER auto_increment , 
    `hashid` CHAR(14), 
    `time` DATETIME, 
    `fk_status` INTEGER, 
    `fk_conversation` INTEGER, 
    PRIMARY KEY (`id`), 
    FOREIGN KEY (`fk_status`) 
    REFERENCES `status` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE, 
    FOREIGN KEY (`fk_conversation`) 
    REFERENCES `conversation` (`id`) 
    ON DELETE CASCADE ON UPDATE CASCADE) ENGINE=InnoDB;
