-- Users table (will store Firebase Auth user IDs)
CREATE TABLE IF NOT EXISTS users (
  id VARCHAR(255) PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  display_name VARCHAR(255),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Sounds table
CREATE TABLE IF NOT EXISTS sounds (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  file VARCHAR(255) NOT NULL,
  category VARCHAR(100) NOT NULL,
  tags TEXT[]
);

-- User mixes table
CREATE TABLE IF NOT EXISTS mixes (
  id SERIAL PRIMARY KEY,
  user_id VARCHAR(255) REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  is_public BOOLEAN DEFAULT false,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Mix sounds (junction table)
CREATE TABLE IF NOT EXISTS mix_sounds (
  id SERIAL PRIMARY KEY,
  mix_id INTEGER REFERENCES mixes(id) ON DELETE CASCADE,
  sound_id INTEGER REFERENCES sounds(id) ON DELETE CASCADE,
  volume DECIMAL(4,3) DEFAULT 0.5 CHECK (volume >= 0 AND volume <= 1),
  UNIQUE(mix_id, sound_id)
);

-- Presets (admin-created mixes)
CREATE TABLE IF NOT EXISTS presets (
  id SERIAL PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Preset sounds (junction table)
CREATE TABLE IF NOT EXISTS preset_sounds (
  id SERIAL PRIMARY KEY,
  preset_id INTEGER REFERENCES presets(id) ON DELETE CASCADE,
  sound_id INTEGER REFERENCES sounds(id) ON DELETE CASCADE,
  volume DECIMAL(4,3) DEFAULT 0.5 CHECK (volume >= 0 AND volume <= 1),
  UNIQUE(preset_id, sound_id)
);

-- Insert initial sounds data from our existing array
INSERT INTO sounds (id, name, file, category, tags)
VALUES 
  (1, 'Rain', '/sounds/rain.mp3', 'Nature', ARRAY['water', 'calm']),
  (2, 'Thunder', '/sounds/thunder.mp3', 'Nature', ARRAY['storm', 'dramatic']),
  (3, 'Forest', '/sounds/forest.mp3', 'Nature', ARRAY['trees', 'birds']),
  (4, 'Ocean Waves', '/sounds/ocean.mp3', 'Water', ARRAY['sea', 'waves']),
  (5, 'Fireplace', '/sounds/fire.mp3', 'Ambience', ARRAY['fire', 'cozy']),
  (6, 'White Noise', '/sounds/white-noise.mp3', 'Noise', ARRAY['focus', 'sleep']),
  (7, 'Coffee Shop', '/sounds/coffee-shop.mp3', 'Ambience', ARRAY['crowd', 'urban']),
  (8, 'Birds', '/sounds/birds.mp3', 'Nature', ARRAY['wildlife', 'morning'])
ON CONFLICT (id) DO NOTHING;

-- Insert initial presets
INSERT INTO presets (id, name, description)
VALUES 
  (1, 'Deep Focus', 'Perfect for concentration and work'),
  (2, 'Rainy Night', 'Relaxing rain sounds for sleep'),
  (3, 'Nature Walk', 'Immerse yourself in nature'),
  (4, 'Cozy Fireplace', 'Warm and comforting atmosphere')
ON CONFLICT (id) DO NOTHING;

-- Insert preset sounds
INSERT INTO preset_sounds (preset_id, sound_id, volume)
VALUES 
  (1, 6, 0.4), -- Deep Focus: White Noise
  (1, 7, 0.3), -- Deep Focus: Coffee Shop
  (2, 1, 0.6), -- Rainy Night: Rain
  (2, 2, 0.3), -- Rainy Night: Thunder
  (3, 3, 0.5), -- Nature Walk: Forest
  (3, 8, 0.4), -- Nature Walk: Birds
  (4, 5, 0.7)  -- Cozy Fireplace: Fireplace
ON CONFLICT (preset_id, sound_id) DO NOTHING;
