CREATE DATABASE IF NOT EXISTS tag_db CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE tag_db;

-- USERS
CREATE TABLE users (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_tag_id VARCHAR(30) NOT NULL UNIQUE,
    full_name VARCHAR(120) NOT NULL,
    phone VARCHAR(25) NOT NULL UNIQUE,
    email VARCHAR(150) NOT NULL UNIQUE,
    age TINYINT UNSIGNED NOT NULL,
    sex ENUM('male','female','other') NOT NULL,
    avatar_url VARCHAR(255) NULL,
    phone_verified_at DATETIME NULL,
    email_verified_at DATETIME NULL,
    password VARCHAR(255) NULL,
    status ENUM('active','suspended','deleted') NOT NULL DEFAULT 'active',
    last_seen_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- OTP REQUESTS
CREATE TABLE otp_requests (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    phone VARCHAR(25) NOT NULL,
    email VARCHAR(150) NOT NULL,
    otp_code VARCHAR(10) NOT NULL,
    purpose ENUM('signup','login','email_verify','phone_verify') NOT NULL DEFAULT 'signup',
    expires_at DATETIME NOT NULL,
    verified_at DATETIME NULL,
    attempts INT NOT NULL DEFAULT 0,
    is_used TINYINT(1) NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_phone_email (phone, email)
);

-- USER DEVICES / TOKENS TRACKING
CREATE TABLE user_devices (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    device_name VARCHAR(120) NULL,
    device_uuid VARCHAR(120) NULL,
    platform VARCHAR(50) NULL,
    push_token VARCHAR(255) NULL,
    last_login_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_user_devices_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- TAG BOOK (CONTACTS)
CREATE TABLE tag_books (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    owner_user_id BIGINT UNSIGNED NOT NULL,
    contact_user_id BIGINT UNSIGNED NOT NULL,
    alias_name VARCHAR(120) NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tag_book_pair (owner_user_id, contact_user_id),
    CONSTRAINT fk_tag_book_owner FOREIGN KEY (owner_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_tag_book_contact FOREIGN KEY (contact_user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- CONTACT QR CODES
CREATE TABLE contact_qr_codes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL UNIQUE,
    qr_token VARCHAR(150) NOT NULL UNIQUE,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    expires_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_contact_qr_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- TAG SPACES
CREATE TABLE tag_spaces (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    uuid CHAR(36) NOT NULL UNIQUE,
    creator_user_id BIGINT UNSIGNED NOT NULL,
    title VARCHAR(150) NOT NULL,
    description TEXT NULL,
    cover_image_url VARCHAR(255) NULL,
    visibility ENUM('public','private') NOT NULL DEFAULT 'public',
    join_code VARCHAR(50) NULL,
    max_members INT NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_tag_space_creator FOREIGN KEY (creator_user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- TAG SPACE SESSIONS (ANONYMOUS MEMBERSHIP)
CREATE TABLE tag_space_sessions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_space_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    space_tag_code VARCHAR(40) NOT NULL UNIQUE,
    joined_at DATETIME NOT NULL,
    left_at DATETIME NULL,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_space_active (tag_space_id, is_active),
    CONSTRAINT fk_space_session_space FOREIGN KEY (tag_space_id) REFERENCES tag_spaces(id) ON DELETE CASCADE,
    CONSTRAINT fk_space_session_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- TAG SPACE POSTS
CREATE TABLE tag_space_posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_space_id BIGINT UNSIGNED NOT NULL,
    tag_space_session_id BIGINT UNSIGNED NOT NULL,
    body TEXT NULL,
    visibility ENUM('space_only','shared_to_public') NOT NULL DEFAULT 'space_only',
    shared_public_post_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_space_post_space FOREIGN KEY (tag_space_id) REFERENCES tag_spaces(id) ON DELETE CASCADE,
    CONSTRAINT fk_space_post_session FOREIGN KEY (tag_space_session_id) REFERENCES tag_space_sessions(id) ON DELETE CASCADE
);

CREATE TABLE tag_space_post_media (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_space_post_id BIGINT UNSIGNED NOT NULL,
    media_type ENUM('image','video','audio') NOT NULL,
    media_url VARCHAR(255) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_space_post_media_post FOREIGN KEY (tag_space_post_id) REFERENCES tag_space_posts(id) ON DELETE CASCADE
);

CREATE TABLE tag_space_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_space_post_id BIGINT UNSIGNED NOT NULL,
    tag_space_session_id BIGINT UNSIGNED NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_space_comment_post FOREIGN KEY (tag_space_post_id) REFERENCES tag_space_posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_space_comment_session FOREIGN KEY (tag_space_session_id) REFERENCES tag_space_sessions(id) ON DELETE CASCADE
);

CREATE TABLE tag_space_reactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_space_post_id BIGINT UNSIGNED NOT NULL,
    tag_space_session_id BIGINT UNSIGNED NOT NULL,
    reaction_type ENUM('like','love','fire','laugh','sad','angry') NOT NULL DEFAULT 'like',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_space_reaction (tag_space_post_id, tag_space_session_id, reaction_type),
    CONSTRAINT fk_space_reaction_post FOREIGN KEY (tag_space_post_id) REFERENCES tag_space_posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_space_reaction_session FOREIGN KEY (tag_space_session_id) REFERENCES tag_space_sessions(id) ON DELETE CASCADE
);

-- TEMPORARY TAG SPACE CHAT
CREATE TABLE tag_space_chat_threads (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_space_id BIGINT UNSIGNED NOT NULL,
    created_by_session_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_space_chat_thread_space FOREIGN KEY (tag_space_id) REFERENCES tag_spaces(id) ON DELETE CASCADE,
    CONSTRAINT fk_space_chat_thread_session FOREIGN KEY (created_by_session_id) REFERENCES tag_space_sessions(id) ON DELETE CASCADE
);

CREATE TABLE tag_space_chat_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_space_chat_thread_id BIGINT UNSIGNED NOT NULL,
    sender_session_id BIGINT UNSIGNED NOT NULL,
    message_type ENUM('text','image','audio','video','system') NOT NULL DEFAULT 'text',
    body TEXT NULL,
    media_url VARCHAR(255) NULL,
    sent_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_space_chat_message_thread FOREIGN KEY (tag_space_chat_thread_id) REFERENCES tag_space_chat_threads(id) ON DELETE CASCADE,
    CONSTRAINT fk_space_chat_message_session FOREIGN KEY (sender_session_id) REFERENCES tag_space_sessions(id) ON DELETE CASCADE
);

-- PUBLIC POSTS
CREATE TABLE public_posts (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    body TEXT NULL,
    source_type ENUM('native','shared_from_tag_space') NOT NULL DEFAULT 'native',
    source_tag_space_post_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_public_post_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE public_post_media (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_post_id BIGINT UNSIGNED NOT NULL,
    media_type ENUM('image','video','audio') NOT NULL,
    media_url VARCHAR(255) NOT NULL,
    sort_order INT NOT NULL DEFAULT 0,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_public_post_media_post FOREIGN KEY (public_post_id) REFERENCES public_posts(id) ON DELETE CASCADE
);

CREATE TABLE public_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_post_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_public_comment_post FOREIGN KEY (public_post_id) REFERENCES public_posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_public_comment_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE public_reactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    public_post_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    reaction_type ENUM('like','love','fire','laugh','sad','angry') NOT NULL DEFAULT 'like',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_public_reaction (public_post_id, user_id, reaction_type),
    CONSTRAINT fk_public_reaction_post FOREIGN KEY (public_post_id) REFERENCES public_posts(id) ON DELETE CASCADE,
    CONSTRAINT fk_public_reaction_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- TAG VIBES
CREATE TABLE tag_vibes (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    user_id BIGINT UNSIGNED NOT NULL,
    caption TEXT NULL,
    original_vibe_id BIGINT UNSIGNED NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_tag_vibe_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE tag_vibe_media (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_vibe_id BIGINT UNSIGNED NOT NULL,
    media_type ENUM('video','audio') NOT NULL,
    media_url VARCHAR(255) NOT NULL,
    thumbnail_url VARCHAR(255) NULL,
    duration_seconds INT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_tag_vibe_media_vibe FOREIGN KEY (tag_vibe_id) REFERENCES tag_vibes(id) ON DELETE CASCADE
);

CREATE TABLE tag_vibe_comments (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_vibe_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_tag_vibe_comment_vibe FOREIGN KEY (tag_vibe_id) REFERENCES tag_vibes(id) ON DELETE CASCADE,
    CONSTRAINT fk_tag_vibe_comment_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE tag_vibe_reactions (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    tag_vibe_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    reaction_type ENUM('like','love','fire','laugh','sad','angry') NOT NULL DEFAULT 'like',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_tag_vibe_reaction (tag_vibe_id, user_id, reaction_type),
    CONSTRAINT fk_tag_vibe_reaction_vibe FOREIGN KEY (tag_vibe_id) REFERENCES tag_vibes(id) ON DELETE CASCADE,
    CONSTRAINT fk_tag_vibe_reaction_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- PRIVATE CHATS
CREATE TABLE private_conversations (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    conversation_type ENUM('direct') NOT NULL DEFAULT 'direct',
    created_by BIGINT UNSIGNED NOT NULL,
    last_message_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_private_conv_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE private_conversation_participants (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    private_conversation_id BIGINT UNSIGNED NOT NULL,
    user_id BIGINT UNSIGNED NOT NULL,
    joined_at DATETIME NOT NULL,
    UNIQUE KEY uq_private_conv_user (private_conversation_id, user_id),
    CONSTRAINT fk_private_conv_participant_conv FOREIGN KEY (private_conversation_id) REFERENCES private_conversations(id) ON DELETE CASCADE,
    CONSTRAINT fk_private_conv_participant_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

CREATE TABLE private_messages (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    private_conversation_id BIGINT UNSIGNED NOT NULL,
    sender_user_id BIGINT UNSIGNED NOT NULL,
    message_type ENUM('text','image','audio','video','system') NOT NULL DEFAULT 'text',
    body TEXT NULL,
    media_url VARCHAR(255) NULL,
    sent_at DATETIME NOT NULL,
    read_at DATETIME NULL,
    deleted_at DATETIME NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_private_message_conv FOREIGN KEY (private_conversation_id) REFERENCES private_conversations(id) ON DELETE CASCADE,
    CONSTRAINT fk_private_message_sender FOREIGN KEY (sender_user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- REPORTS
CREATE TABLE reports (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    reporter_user_id BIGINT UNSIGNED NOT NULL,
    target_type ENUM('user','public_post','tag_space_post','tag_vibe','comment','message','tag_space') NOT NULL,
    target_id BIGINT UNSIGNED NOT NULL,
    reason VARCHAR(255) NOT NULL,
    details TEXT NULL,
    status ENUM('pending','reviewed','actioned','dismissed') NOT NULL DEFAULT 'pending',
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    CONSTRAINT fk_reports_user FOREIGN KEY (reporter_user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- BLOCKS
CREATE TABLE blocks (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    blocker_user_id BIGINT UNSIGNED NOT NULL,
    blocked_user_id BIGINT UNSIGNED NOT NULL,
    created_at TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE KEY uq_block_pair (blocker_user_id, blocked_user_id),
    CONSTRAINT fk_blocker_user FOREIGN KEY (blocker_user_id) REFERENCES users(id) ON DELETE CASCADE,
    CONSTRAINT fk_blocked_user FOREIGN KEY (blocked_user_id) REFERENCES users(id) ON DELETE CASCADE
);