-- ============================================================
--  Spaceship — Run ONCE in phpMyAdmin SQL tab → Go
-- ============================================================
CREATE DATABASE IF NOT EXISTS wachat CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE wachat;

CREATE TABLE IF NOT EXISTS users (
    id           INT          AUTO_INCREMENT PRIMARY KEY,
    username     VARCHAR(50)  NOT NULL UNIQUE,
    display_name VARCHAR(80)  NOT NULL DEFAULT '',
    password      VARCHAR(255) NOT NULL,
    public_key    TEXT         NOT NULL,
    key_backup    MEDIUMTEXT   DEFAULT NULL,
    key_backup_iv VARCHAR(64)  NOT NULL DEFAULT '',
    avatar        VARCHAR(255) NOT NULL DEFAULT '',
    about        VARCHAR(255) NOT NULL DEFAULT 'Hey there! I am using Spaceship.',
    timezone     VARCHAR(64)  NOT NULL DEFAULT '',
    country      VARCHAR(8)   NOT NULL DEFAULT '',
    chat_wallpaper VARCHAR(255) NOT NULL DEFAULT '',
    last_seen    DATETIME     DEFAULT NULL,
    created_at   DATETIME     DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS messages (
    id          BIGINT       AUTO_INCREMENT PRIMARY KEY,
    from_user   VARCHAR(50)  NOT NULL,
    to_user     VARCHAR(50)  NOT NULL,
    cipher_text MEDIUMTEXT   NOT NULL,
    iv          VARCHAR(50)  NOT NULL,
    msg_type    ENUM('text','image','video','audio','document','voice') NOT NULL DEFAULT 'text',
    file_name   VARCHAR(255) NOT NULL DEFAULT '',
    file_size   INT          NOT NULL DEFAULT 0,
    file_mime   VARCHAR(100) NOT NULL DEFAULT '',
    reply_to    BIGINT       DEFAULT NULL,
    forwarded   TINYINT(1)   NOT NULL DEFAULT 0,
    starred     TINYINT(1)   NOT NULL DEFAULT 0,
    status      ENUM('sent','delivered','read') NOT NULL DEFAULT 'sent',
    status_at   DATETIME     DEFAULT CURRENT_TIMESTAMP,
    deleted_for TEXT         DEFAULT NULL,
    sent_at     DATETIME     DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_conv (from_user, to_user),
    INDEX idx_to   (to_user),
    INDEX idx_time (sent_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS reactions (
    id         INT         AUTO_INCREMENT PRIMARY KEY,
    message_id BIGINT      NOT NULL,
    username   VARCHAR(50) NOT NULL,
    emoji      VARCHAR(10) NOT NULL,
    created_at DATETIME    DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_rxn (message_id, username)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS presence (
    username   VARCHAR(50) PRIMARY KEY,
    last_seen  DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    timezone   VARCHAR(64) NOT NULL DEFAULT '',
    country    VARCHAR(8)  NOT NULL DEFAULT '',
    is_typing  TINYINT(1)  NOT NULL DEFAULT 0,
    typing_to  VARCHAR(50) NOT NULL DEFAULT '',
    typing_at  DATETIME    DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS archived_chats (
    owner       VARCHAR(50) NOT NULL,
    peer        VARCHAR(50) NOT NULL,
    archived_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (owner, peer),
    INDEX idx_archived_owner (owner)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS protected_chats (
    owner     VARCHAR(50) NOT NULL,
    peer      VARCHAR(50) NOT NULL,
    locked_at DATETIME    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (owner, peer),
    INDEX idx_protected_owner (owner)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;


CREATE TABLE IF NOT EXISTS call_sessions (
    id            BIGINT AUTO_INCREMENT PRIMARY KEY,
    caller        VARCHAR(50) NOT NULL,
    receiver      VARCHAR(50) NOT NULL,
    call_type     ENUM('voice','video') NOT NULL DEFAULT 'voice',
    status        ENUM('ringing','accepted','declined','ended') NOT NULL DEFAULT 'ringing',
    offer_sdp     MEDIUMTEXT NULL,
    answer_sdp    MEDIUMTEXT NULL,
    caller_ice    MEDIUMTEXT NULL,
    receiver_ice  MEDIUMTEXT NULL,
    created_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at    DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    ended_at      DATETIME NULL,
    INDEX idx_call_receiver_status (receiver, status, updated_at),
    INDEX idx_call_caller (caller, updated_at),
    INDEX idx_call_receiver (receiver, updated_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
