Spaces:
Sleeping
Sleeping
File size: 2,035 Bytes
f1df910 906f430 f1df910 906f430 f1df910 906f430 f1df910 d57862a | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 | -- StrengerChat Pro - Postgres Database Schema (Final Stable Version)
-- Optimized for PostgreSQL 14+
-- 1. Users Table
CREATE TABLE IF NOT EXISTS users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL UNIQUE,
email VARCHAR(100) NOT NULL UNIQUE,
password_hash VARCHAR(255) NOT NULL,
gender VARCHAR(10) NOT NULL,
avatar_type VARCHAR(10) DEFAULT 'default',
age INT,
bio TEXT,
is_premium BOOLEAN DEFAULT FALSE,
is_banned BOOLEAN DEFAULT FALSE,
banned_at TIMESTAMP,
ban_reason TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- 2. Sessions & Geo-Location Table
CREATE TABLE IF NOT EXISTS active_sessions (
user_id INT PRIMARY KEY,
socket_id VARCHAR(100),
latitude FLOAT,
longitude FLOAT,
status VARCHAR(20) DEFAULT 'idle',
looking_for VARCHAR(10) DEFAULT 'both',
last_active TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_user_session FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);
-- 3. Reports Table
CREATE TABLE IF NOT EXISTS reports (
id SERIAL PRIMARY KEY,
reporter_id INT,
reported_id INT,
reason TEXT,
status VARCHAR(20) DEFAULT 'pending', -- pending, dismissed, resolved
action_taken TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_reporter FOREIGN KEY (reporter_id) REFERENCES users(id) ON DELETE SET NULL,
CONSTRAINT fk_reported FOREIGN KEY (reported_id) REFERENCES users(id) ON DELETE CASCADE
);
-- 4. Direct Messages / Friends History Table
CREATE TABLE IF NOT EXISTS direct_messages (
id SERIAL PRIMARY KEY,
sender_id INT,
receiver_id INT,
message TEXT NOT NULL,
is_read BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
CONSTRAINT fk_dm_sender FOREIGN KEY (sender_id) REFERENCES users(id) ON DELETE CASCADE,
CONSTRAINT fk_dm_receiver FOREIGN KEY (receiver_id) REFERENCES users(id) ON DELETE CASCADE
);
ALTER TABLE users ADD COLUMN IF NOT EXISTS last_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP;
|