database.sql

DROP DATABASE IF EXISTS  `codoc19`;
CREATE DATABASE `codoc19`;
USE `codoc19`;

CREATE TABLE `users`(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    platform ENUM ('TELEGRAM','FACEBOOK') NOT NULL,
    first_name TEXT,
    username TEXT,
    user_id INT NOT NULL UNIQUE KEY,
    created_at TIMESTAMP NOT NULL DEFAULT  CURRENT_TIMESTAMP
);

CREATE TABLE `analytics`(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    feature ENUM('QA','TEST','QUIZ','STATS','STATS_CHART') NOT NULL,
    data TEXT,
    created_at TIMESTAMP NOT NULL DEFAULT  CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES users(`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE `charts`(
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    user_id INT NOT NULL,
    tg_file_id TEXT,
    chart_type ENUM ('DEATH','CASE','RECOVERED','DEATH_DAILY','CASE_DAILY','RECOVERED_DAILY') NOT NULL,
    country VARCHAR (100) NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT  CURRENT_TIMESTAMP,
    FOREIGN KEY (`user_id`) REFERENCES users(`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

ALTER TABLE `analytics` CHANGE `data` `data` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;
ALTER TABLE `users` CHANGE `first_name` `first_name` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL;