database.sql

CREATE DATABASE IF NOT EXISTS megachat;
USE megachat;

CREATE TABLE IF NOT EXISTS users (
    user_id BIGINT PRIMARY KEY,
    username VARCHAR(255),
    lang VARCHAR(5) DEFAULT 'it',
    age INT,
    gender VARCHAR(20),
    looking_for VARCHAR(20),
    interests TEXT,
    bio TEXT,
    status ENUM('idle', 'registering', 'waiting', 'chatting') DEFAULT 'registering',
    partner_id BIGINT DEFAULT NULL,
    tier ENUM('free', 'premium', 'gold', 'ultra') DEFAULT 'free',
    subscription_end DATE DEFAULT NULL,

    -- Gamification & Stats
    karma INT DEFAULT 100,
    xp INT DEFAULT 0,
    level INT DEFAULT 1,
    title VARCHAR(50) DEFAULT 'Beginner',
    likes_received INT DEFAULT 0,
    dislikes_received INT DEFAULT 0,
    report_count INT DEFAULT 0,

    -- Daily limits
    daily_matches INT DEFAULT 0,
    last_match_date DATE DEFAULT NULL,
    is_banned BOOLEAN DEFAULT FALSE,
    last_active TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    INDEX idx_status (status),
    INDEX idx_tier (tier),
    INDEX idx_xp (xp DESC)
);

CREATE TABLE IF NOT EXISTS reports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    reporter_id BIGINT NOT NULL,
    reported_id BIGINT NOT NULL,
    reason TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_reported (reported_id)
);