CREATE TABLE IF NOT EXISTS users (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    nickname VARCHAR(255) DEFAULT NULL,
    passphrase VARCHAR(255) UNIQUE NOT NULL,
    chat_id bigint unsigned NOT NULL,
    language VARCHAR(255) DEFAULT 'en',
    premium TINYINT(1) default 0,
    premium_expire timestamp default null,
    state INTEGER default 0
);

CREATE TABLE IF NOT EXISTS state_map(
    current_state INTEGER DEFAULT NULL,
    next_state INTEGER DEFAULT NULL
);

CREATE TABLE IF NOT EXISTS smtp_configs (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    --     email_id INTEGER NOT NULL,
    host VARCHAR(255) NOT NULL,
    port INTEGER NOT NULL, -- 25/465/587
    encryption VARCHAR(255), -- ssl/tls/null,
    username VARCHAR(255),
    password VARCHAR(255),
    from_email VARCHAR(255) NOT NULL
    --                                         ,
    --     FOREIGN KEY (email_id) REFERENCES emails(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS imap_configs (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    --     email_id INTEGER NOT NULL,
    host VARCHAR(255) NOT NULL,
    port INTEGER NOT NULL, -- 25/465/587
    encryption VARCHAR(255), -- ssl/tls/null,
    username VARCHAR(255),
    password VARCHAR(255),
    mailbox VARCHAR(255) DEFAULT 'INBOX' NOT NULL
    --                                         ,
    --     FOREIGN KEY (email_id) REFERENCES emails(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE email_domains(
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    domain VARCHAR(255) UNIQUE NOT NULL,
    smtp_config INTEGER DEFAULT NULL,
    imap_config INTEGER DEFAULT NULL,
    premium INTEGER default 0,
    email_counter bigint unsigned default 0 null,
    FOREIGN KEY (smtp_config) REFERENCES smtp_configs(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (imap_config) REFERENCES imap_configs(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE email(
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL UNIQUE,
    domain_id INTEGER NOT NULL,
    user_id INTEGER NOT NULL,
    --     FOREIGN KEY (domain_id) REFERENCES domains(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ON UPDATE CASCADE,
    FOREIGN KEY (domain_id) REFERENCES email_domains(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE messages (
    id INTEGER PRIMARY KEY AUTO_INCREMENT,
    chat_id bigint unsigned NOT NULL,
    message_id VARCHAR(255) NOT NULL,
    type VARCHAR(255) DEFAULT NULL
);

ALTER TABLE messages ADD COLUMN is_removed INTEGER DEFAULT 0;

CREATE TABLE IF NOT EXISTS success_invoices
(
    chat_id   bigint unsigned,
    payload   VARCHAR(255),
    type      VARCHAR(255),
    amount    INTEGER,
    currency  VARCHAR(20)    default 'XTR',
    timestamp TIMESTAMP default NOW()
);

ALTER TABLE users ADD COLUMN mail_check_period INTEGER default -1;

ALTER TABLE email ADD COLUMN check_period INTEGER default NULL;

ALTER TABLE email ADD COLUMN is_removed INTEGER default 0;

CREATE TABLE IF NOT EXISTS blacklist_mail_prefix(
    id int unsigned primary key auto_increment,
    name varchar(255) not null
);

CREATE TABLE IF NOT EXISTS letters (
    id bigint unsigned primary key auto_increment,
    email_id int,
    from_name varchar(255) default null,
    from_email varchar(255) not null,
    date varchar(255) default null,
    title text default null,
    body mediumtext default null,
    created_at datetime default now(),
    FOREIGN KEY (email_id) REFERENCES email(id) ON DELETE CASCADE ON UPDATE CASCADE
);

CREATE TABLE IF NOT EXISTS letter_files (
    id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    letter_id BIGINT UNSIGNED NOT NULL,
    name TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
    `key` VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT NOW(),
    FOREIGN KEY (letter_id) REFERENCES letters(id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS logs(
    message text default null,
    trace text default null,
    created_at datetime default now()
);

CREATE TABLE IF NOT EXISTS recovery_attempts (
    id bigint unsigned primary key auto_increment,
    user_id int not null,
    date datetime default now()
);

ALTER TABLE letters ADD COLUMN is_viewed tinyint(1) default 0;

CREATE TABLE IF NOT EXISTS translations(
    language varchar(2) NOT NULL,
    code varchar(255) NOT NULL,
    text text,
    PRIMARY KEY (language, code)
);

ALTER TABLE email ADD COLUMN is_locked tinyint(1) default 0;
ALTER TABLE email ADD COLUMN created_at datetime default now();

CREATE TABLE languages (
    code VARCHAR(2) NOT NULL,
    title VARCHAR(100) NOT NULL,
    enabled TINYINT(1) DEFAULT 1,
    PRIMARY KEY (code)
)
    ENGINE=InnoDB
    DEFAULT CHARSET=utf8mb4
    COLLATE=utf8mb4_unicode_ci;

CREATE TABLE faq (
    id int auto_increment,
    language VARCHAR(2) NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT NOT NULL,
    sort int unsigned default 100,
    PRIMARY KEY (id)
);
