Moodgram Database Schema

-- Enable UUID extension

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Users table

CREATE TABLE users (

    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    email VARCHAR(255) UNIQUE,

    password_hash VARCHAR(255),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    last_active TIMESTAMP WITH TIME ZONE,

    is_anonymous BOOLEAN DEFAULT TRUE,

    preferences JSONB DEFAULT '{}'::jsonb,

    timezone VARCHAR(50)

);

-- Sessions table

CREATE TABLE sessions (

    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    user_id UUID REFERENCES users(id),

    token VARCHAR(255) UNIQUE,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    expires_at TIMESTAMP WITH TIME ZONE,

    ip_address VARCHAR(45),

    user_agent TEXT

);

-- Locations table

CREATE TABLE locations (

    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    latitude DECIMAL(10, 8),

    longitude DECIMAL(11, 8),

    country_code VARCHAR(2),

    region VARCHAR(100),

    city VARCHAR(100),

    timezone VARCHAR(50),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    UNIQUE(latitude, longitude)

);

-- Mood types table

CREATE TABLE mood_types (

    id SMALLINT PRIMARY KEY,

    name VARCHAR(50) UNIQUE,

    description TEXT,

    color_code VARCHAR(7),

    icon_name VARCHAR(50)

);

-- Mood entries table

CREATE TABLE mood_entries (

    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    user_id UUID REFERENCES users(id),

    mood_type_id SMALLINT REFERENCES mood_types(id),

    intensity SMALLINT CHECK (intensity BETWEEN 1 AND 5),

    location_id UUID REFERENCES locations(id),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    recorded_at TIMESTAMP WITH TIME ZONE,

    notes TEXT,

    weather_data JSONB,

    metadata JSONB

);

-- Analytics aggregation table (pre-calculated statistics)

CREATE TABLE mood_analytics (

    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),

    timestamp TIMESTAMP WITH TIME ZONE,

    location_id UUID REFERENCES locations(id),

    mood_type_id SMALLINT REFERENCES mood_types(id),

    count INTEGER,

    avg_intensity DECIMAL(3,2),

    resolution VARCHAR(20), -- 'hour', 'day', 'week'

    UNIQUE(timestamp, location_id, mood_type_id, resolution)

);

-- Indexes

CREATE INDEX idx_mood_entries_user_id ON mood_entries(user_id);

CREATE INDEX idx_mood_entries_created_at ON mood_entries(created_at);

CREATE INDEX idx_mood_entries_location_id ON mood_entries(location_id);

CREATE INDEX idx_locations_coords ON locations(latitude, longitude);

CREATE INDEX idx_mood_analytics_timestamp ON mood_analytics(timestamp);

-- Views

CREATE VIEW hourly_mood_trends AS

SELECT 

    date_trunc('hour', recorded_at) as hour,

    mood_type_id,

    COUNT(*) as entry_count,

    AVG(intensity::float) as avg_intensity

FROM mood_entries

GROUP BY date_trunc('hour', recorded_at), mood_type_id;

-- Functions

CREATE OR REPLACE FUNCTION calculate_regional_mood_average(

    region_code VARCHAR,

    start_time TIMESTAMP WITH TIME ZONE,

    end_time TIMESTAMP WITH TIME ZONE

) RETURNS TABLE (

    mood_type_id SMALLINT,

    avg_intensity DECIMAL(3,2),

    entry_count BIGINT

) AS $$

BEGIN

    RETURN QUERY

    SELECT 

        me.mood_type_id,

        ROUND(AVG(me.intensity)::DECIMAL, 2) as avg_intensity,

        COUNT(*) as entry_count

    FROM mood_entries me

    JOIN locations l ON me.location_id = l.id

    WHERE 

        l.region = region_code AND

        me.recorded_at BETWEEN start_time AND end_time

    GROUP BY me.mood_type_id;

END;

$$ LANGUAGE plpgsql;

-- Triggers

CREATE OR REPLACE FUNCTION update_mood_analytics()

RETURNS TRIGGER AS $$

BEGIN

    -- Update hourly analytics

    INSERT INTO mood_analytics (

        timestamp,

        location_id,

        mood_type_id,

        count,

        avg_intensity,

        resolution

    )

    VALUES (

        date_trunc('hour', NEW.recorded_at),

        NEW.location_id,

        NEW.mood_type_id,

        1,

        NEW.intensity,

        'hour'

    )

    ON CONFLICT (timestamp, location_id, mood_type_id, resolution)

    DO UPDATE SET 

        count = mood_analytics.count + 1,

        avg_intensity = (mood_analytics.avg_intensity * mood_analytics.count + NEW.intensity) / (mood_analytics.count + 1);

    RETURN NEW;

END;

$$ LANGUAGE plpgsql;

CREATE TRIGGER mood_entry_analytics

    AFTER INSERT ON mood_entries

    FOR EACH ROW

    EXECUTE FUNCTION update_mood_analytics();