AeThex-OS/migrations/0009_add_aethex_language_tables.sql

-- AeThex Language Integration Migration
-- Adds tables for AeThex apps, installations, and reviews

CREATE TABLE IF NOT EXISTS aethex_apps (
    id VARCHAR PRIMARY KEY,
    owner_id VARCHAR NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    source_code TEXT NOT NULL,
    compiled_js TEXT,
    compiled_lua TEXT,
    icon_url TEXT,
    category TEXT DEFAULT 'utility',
    is_public BOOLEAN DEFAULT false,
    is_featured BOOLEAN DEFAULT false,
    install_count INTEGER DEFAULT 0,
    rating DECIMAL(3, 2) DEFAULT 0,
    rating_count INTEGER DEFAULT 0,
    version TEXT DEFAULT '1.0.0',
    targets JSON DEFAULT '["javascript"]',
    tags JSON DEFAULT '[]',
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    organization_id VARCHAR
);

CREATE TABLE IF NOT EXISTS aethex_app_installations (
    id VARCHAR PRIMARY KEY,
    app_id VARCHAR NOT NULL REFERENCES aethex_apps(id) ON DELETE CASCADE,
    user_id VARCHAR NOT NULL,
    installed_at TIMESTAMP DEFAULT NOW(),
    last_used_at TIMESTAMP,
    settings JSON
);

CREATE TABLE IF NOT EXISTS aethex_app_reviews (
    id VARCHAR PRIMARY KEY,
    app_id VARCHAR NOT NULL REFERENCES aethex_apps(id) ON DELETE CASCADE,
    user_id VARCHAR NOT NULL,
    rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
    review_text TEXT,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_aethex_apps_owner ON aethex_apps(owner_id);
CREATE INDEX IF NOT EXISTS idx_aethex_apps_public ON aethex_apps(is_public) WHERE is_public = true;
CREATE INDEX IF NOT EXISTS idx_aethex_apps_featured ON aethex_apps(is_featured) WHERE is_featured = true;
CREATE INDEX IF NOT EXISTS idx_aethex_apps_category ON aethex_apps(category);
CREATE INDEX IF NOT EXISTS idx_aethex_app_installations_user ON aethex_app_installations(user_id);
CREATE INDEX IF NOT EXISTS idx_aethex_app_installations_app ON aethex_app_installations(app_id);
CREATE INDEX IF NOT EXISTS idx_aethex_app_reviews_app ON aethex_app_reviews(app_id);
CREATE INDEX IF NOT EXISTS idx_aethex_app_reviews_user ON aethex_app_reviews(user_id);