70 lines
2.1 KiB
SQL
70 lines
2.1 KiB
SQL
-- Create extensions
|
|
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
|
|
|
-- Create devices table
|
|
CREATE TABLE IF NOT EXISTS devices (
|
|
id SERIAL PRIMARY KEY,
|
|
name VARCHAR(255) NOT NULL,
|
|
manufacturer VARCHAR(255) NOT NULL,
|
|
release_year INTEGER,
|
|
cpu VARCHAR(255),
|
|
ram_mb INTEGER,
|
|
storage_mb INTEGER,
|
|
display_size VARCHAR(50),
|
|
battery_hours REAL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create ratings table
|
|
CREATE TABLE IF NOT EXISTS ratings (
|
|
id SERIAL PRIMARY KEY,
|
|
device_id INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
|
|
user_id VARCHAR(255) NOT NULL,
|
|
score INTEGER CHECK (score >= 1 AND score <= 5),
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create reviews table
|
|
CREATE TABLE IF NOT EXISTS reviews (
|
|
id SERIAL PRIMARY KEY,
|
|
device_id INTEGER NOT NULL REFERENCES devices(id) ON DELETE CASCADE,
|
|
user_id VARCHAR(255) NOT NULL,
|
|
content TEXT NOT NULL,
|
|
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create device_events table for worker logging
|
|
CREATE TABLE IF NOT EXISTS device_events (
|
|
id SERIAL PRIMARY KEY,
|
|
device_id INTEGER,
|
|
device_name VARCHAR(255),
|
|
event_type VARCHAR(100),
|
|
processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create rating_events table
|
|
CREATE TABLE IF NOT EXISTS rating_events (
|
|
id SERIAL PRIMARY KEY,
|
|
device_id INTEGER,
|
|
user_id VARCHAR(255),
|
|
score INTEGER,
|
|
processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create review_events table
|
|
CREATE TABLE IF NOT EXISTS review_events (
|
|
id SERIAL PRIMARY KEY,
|
|
device_id INTEGER,
|
|
user_id VARCHAR(255),
|
|
processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
-- Create indexes for better performance
|
|
CREATE INDEX IF NOT EXISTS idx_devices_name ON devices(name);
|
|
CREATE INDEX IF NOT EXISTS idx_devices_manufacturer ON devices(manufacturer);
|
|
CREATE INDEX IF NOT EXISTS idx_ratings_device_id ON ratings(device_id);
|
|
CREATE INDEX IF NOT EXISTS idx_ratings_user_id ON ratings(user_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reviews_device_id ON reviews(device_id);
|
|
CREATE INDEX IF NOT EXISTS idx_reviews_user_id ON reviews(user_id);
|