-- 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);