-- Mobile app version management for OTA updates
-- Supports Android (APK) and iOS (IPA)
CREATE TABLE IF NOT EXISTS app_versions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    platform VARCHAR(10) NOT NULL CHECK (platform IN ('ANDROID', 'IOS')),
    version_code INT NOT NULL,
    version_name VARCHAR(32) NOT NULL,
    build_number VARCHAR(64) NOT NULL,
    download_url TEXT NOT NULL,
    file_size BIGINT NOT NULL DEFAULT 0,
    file_sha256 VARCHAR(64) NOT NULL,
    min_os_version VARCHAR(16),
    changelog TEXT NOT NULL DEFAULT '',
    is_force_update BOOLEAN NOT NULL DEFAULT FALSE,
    is_enabled BOOLEAN NOT NULL DEFAULT TRUE,
    release_date TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    created_by UUID,
    updated_by UUID,
    version INT NOT NULL DEFAULT 1
);

CREATE INDEX idx_app_versions_platform ON app_versions(platform, is_enabled);
CREATE INDEX idx_app_versions_code ON app_versions(platform, version_code DESC);
CREATE UNIQUE INDEX idx_app_versions_platform_code ON app_versions(platform, version_code);