-- App Builds table for APK version tracking and distribution
-- Run: psql -U efsi -d kinosave -f sql/app_builds_migration.sql

CREATE TABLE IF NOT EXISTS app_builds (
    build_id SERIAL PRIMARY KEY,
    version_name VARCHAR(20) NOT NULL,        -- "1.2.3"
    version_code INTEGER NOT NULL,             -- auto-incrementing build number
    filename VARCHAR(255) NOT NULL,            -- "kinometric-v1.2.3-build14.apk"
    file_size BIGINT NOT NULL,                 -- bytes
    sha256 VARCHAR(64) NOT NULL,               -- hex digest for integrity
    upload_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    uploaded_by VARCHAR(50) NOT NULL,          -- "kino-app" or username
    summary VARCHAR(200),                      -- short one-liner for version history
    release_notes TEXT,                        -- detailed bullet points
    is_current BOOLEAN NOT NULL DEFAULT false, -- only one row should be true
    file_on_disk BOOLEAN NOT NULL DEFAULT true -- false when purged (>7 builds)
);

-- Only one current build at a time
CREATE UNIQUE INDEX IF NOT EXISTS idx_app_builds_current
    ON app_builds (is_current) WHERE is_current = true;

-- Quick lookup by version
CREATE INDEX IF NOT EXISTS idx_app_builds_version_code
    ON app_builds (version_code DESC);

-- Insert a seed row so the download page works before first upload
-- (commented out — uncomment if you want a placeholder)
-- INSERT INTO app_builds (version_name, version_code, filename, file_size, sha256, uploaded_by, summary, release_notes, is_current)
-- VALUES ('0.0.0', 0, 'placeholder.apk', 0, '', 'system', 'Initial placeholder', 'No builds yet.', true);
