1  -- Asteroid Radio Database Initialization Script
  2  -- PostgreSQL Schema for persistent storage
  3  
  4  -- Enable UUID extension for generating unique IDs
  5  CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
  6  
  7  -- Users table
  8  CREATE TABLE IF NOT EXISTS "USERS" (
  9      _id SERIAL PRIMARY KEY,
 10      username VARCHAR(255) UNIQUE NOT NULL,
 11      email VARCHAR(255) UNIQUE NOT NULL,
 12      "password-hash" TEXT NOT NULL,
 13      role VARCHAR(50) DEFAULT 'listener',
 14      active integer DEFAULT 1,
 15      "created-date" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 16      "last-login" TIMESTAMP,
 17      CONSTRAINT valid_role CHECK (role IN ('listener', 'dj', 'admin'))
 18  );
 19  
 20  -- Create index on username and email for faster lookups
 21  CREATE INDEX idx_users_username ON "USERS"(username);
 22  CREATE INDEX idx_users_email ON "USERS"(email);
 23  
 24  -- Tracks table
 25  CREATE TABLE IF NOT EXISTS tracks (
 26      _id SERIAL PRIMARY KEY,
 27      title VARCHAR(500) NOT NULL,
 28      artist VARCHAR(500),
 29      album VARCHAR(500),
 30      duration INTEGER DEFAULT 0,
 31      format VARCHAR(50),
 32      bitrate integer,
 33      "file-path" TEXT NOT NULL UNIQUE,
 34      "play-count" INTEGER DEFAULT 0,
 35      "added-date" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 36      "last-played" TIMESTAMP
 37  );
 38  
 39  -- Create indexes for common queries
 40  CREATE INDEX idx_tracks_artist ON tracks(artist);
 41  CREATE INDEX idx_tracks_album ON tracks(album);
 42  CREATE INDEX idx_tracks_title ON tracks(title);
 43  
 44  -- Playlists table
 45  CREATE TABLE IF NOT EXISTS playlists (
 46      _id SERIAL PRIMARY KEY,
 47      "user-id" INTEGER NOT NULL REFERENCES "USERS"(_id) ON DELETE CASCADE,
 48      name VARCHAR(255) NOT NULL,
 49      description TEXT,
 50      "created-date" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 51      "modified-date" TIMESTAMP DEFAULT CURRENT_TIMESTAMP
 52  );
 53  
 54  -- Create index on user_id for faster user playlist lookups
 55  CREATE INDEX idx_playlists_user_id ON playlists("user-id");
 56  
 57  -- Playlist tracks junction table (many-to-many relationship)
 58  CREATE TABLE IF NOT EXISTS playlist_tracks (
 59      _id SERIAL PRIMARY KEY,
 60      playlist_id INTEGER NOT NULL REFERENCES playlists(_id) ON DELETE CASCADE,
 61      track_id INTEGER NOT NULL REFERENCES tracks(_id) ON DELETE CASCADE,
 62      position INTEGER NOT NULL,
 63      added_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 64      UNIQUE(playlist_id, track_id, "position")
 65  );
 66  
 67  -- Create indexes for playlist track queries
 68  CREATE INDEX idx_playlist_tracks_playlist_id ON playlist_tracks(playlist_id);
 69  CREATE INDEX idx_playlist_tracks_track_id ON playlist_tracks(track_id);
 70  
 71  -- Sessions table (for Radiance session management)
 72  -- CREATE TABLE IF NOT EXISTS sessions (
 73  --     _id VARCHAR(255) PRIMARY KEY,
 74  --     "user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
 75  --     data JSONB,
 76  --     created_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 77  --     expires_at TIMESTAMP NOT NULL
 78  -- );
 79  
 80  -- Create index on user_id and expires_at
 81  -- CREATE INDEX idx_sessions_user_id ON sessions(user_id);
 82  -- CREATE INDEX idx_sessions_expires_at ON sessions(expires_at);
 83  
 84  -- Create default admin user (password: admin - CHANGE THIS!)
 85  -- Password hash for 'admin' using bcrypt
 86  INSERT INTO "USERS" (username, email, "password-hash", role, active)
 87  -- VALUES ('admin', 'admin@asteroid.radio', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYqYqYqYqYq', 'admin', 1)
 88  VALUES ('admin', 'admin@asteroid.radio','8c6976e5b5410415bde908bd4dee15dfb167a9c873fc4bb8a81f6f2ab448a918', 'admin', 1)
 89  ON CONFLICT (username) DO NOTHING;
 90  
 91  -- Create a test listener user
 92  INSERT INTO "USERS" (username, email, "password-hash", role, active)
 93  VALUES ('listener', 'listener@asteroid.radio', '$2a$12$LQv3c1yqBWVHxkd0LHAkCOYz6TtxMQJqhN8/LewY5GyYqYqYqYqYq', 'listener', 1);
 94  
 95  -- Grant necessary permissions
 96  GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO asteroid;
 97  GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO asteroid;
 98  
 99  -- Create function to update modified_date automatically
100  CREATE OR REPLACE FUNCTION update_modified_date()
101  RETURNS TRIGGER AS $$
102  BEGIN
103      NEW.modified_date = CURRENT_TIMESTAMP;
104      RETURN NEW;
105  END;
106  $$ LANGUAGE plpgsql;
107  
108  -- Create trigger for playlists table
109  CREATE TRIGGER update_playlists_modified_date
110      BEFORE UPDATE ON playlists
111      FOR EACH ROW
112      EXECUTE FUNCTION update_modified_date();
113  
114  -- Success message
115  DO $$
116  BEGIN
117      RAISE NOTICE 'Asteroid Radio database initialized successfully!';
118      RAISE NOTICE 'Database: asteroid';
119      RAISE NOTICE 'User: asteroid';
120      RAISE NOTICE 'Default admin user created: admin / admin (CHANGE PASSWORD!)';
121  END $$;