supabase-version-schema.sql

/*
KSTools 版本管理系統 - Supabase Schema
Project 2: kstools-public (版本系統)

這個 schema 應該在第二個 Supabase Project 中執行
主要用於儲存版本資訊、下載記錄和檔案存儲
*/

-- 啟用必要的擴展
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ================================
-- 移除現有外鍵約束（如果存在）
-- ================================

-- 安全移除外鍵約束，不影響現有資料
DO $$
BEGIN
    IF EXISTS (
        SELECT 1 FROM information_schema.table_constraints
        WHERE constraint_name = 'fk_version'
        AND table_name = 'download_logs'
    ) THEN
        ALTER TABLE download_logs DROP CONSTRAINT fk_version;
    END IF;
END $$;

-- ================================
-- 版本管理表（安全建立，如果不存在）
-- ================================

-- 版本表
CREATE TABLE IF NOT EXISTS versions (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    version VARCHAR(20) NOT NULL UNIQUE,
    title VARCHAR(255),
    release_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    is_active BOOLEAN DEFAULT true,
    download_url TEXT NOT NULL,
    file_size BIGINT,
    file_type VARCHAR(10) CHECK (file_type IN ('zip', 'exe', 'msi')) DEFAULT 'zip',
    file_name TEXT NOT NULL,
    changelog TEXT,
    min_revit_version VARCHAR(10),
    released_by VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 下載記錄表
CREATE TABLE IF NOT EXISTS download_logs (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    version VARCHAR(20),
    license_key VARCHAR(255),
    action VARCHAR(20) CHECK (action IN ('download', 'update_check', 'install')),
    user_agent TEXT,
    ip_address INET,
    downloaded_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- ================================
-- 索引優化
-- ================================

-- 版本表索引
CREATE INDEX IF NOT EXISTS idx_versions_version ON versions(version);
CREATE INDEX IF NOT EXISTS idx_versions_release_date ON versions(release_date DESC);
CREATE INDEX IF NOT EXISTS idx_versions_is_active ON versions(is_active);

-- 下載記錄表索引
CREATE INDEX IF NOT EXISTS idx_download_logs_version ON download_logs(version);
CREATE INDEX IF NOT EXISTS idx_download_logs_downloaded_at ON download_logs(downloaded_at DESC);
CREATE INDEX IF NOT EXISTS idx_download_logs_action ON download_logs(action);

-- ================================
-- Row Level Security (RLS) 政策
-- ================================

-- 啟用 RLS（如果尚未啟用）
DO $$
BEGIN
    ALTER TABLE versions ENABLE ROW LEVEL SECURITY;
EXCEPTION
    WHEN OTHERS THEN NULL;
END $$;

DO $$
BEGIN
    ALTER TABLE download_logs ENABLE ROW LEVEL SECURITY;
EXCEPTION
    WHEN OTHERS THEN NULL;
END $$;

-- 安全建立 RLS 政策（先刪除再建立）
DO $$
BEGIN
    -- 版本表政策
    DROP POLICY IF EXISTS "Public read versions" ON versions;
    CREATE POLICY "Public read versions" ON versions
        FOR SELECT USING (true);

    DROP POLICY IF EXISTS "Service role insert versions" ON versions;
    CREATE POLICY "Service role insert versions" ON versions
        FOR INSERT WITH CHECK (auth.role() = 'service_role');

    DROP POLICY IF EXISTS "Service role update versions" ON versions;
    CREATE POLICY "Service role update versions" ON versions
        FOR UPDATE USING (auth.role() = 'service_role');

    -- 下載記錄表政策
    DROP POLICY IF EXISTS "Service role all download_logs" ON download_logs;
    CREATE POLICY "Service role all download_logs" ON download_logs
        FOR ALL USING (auth.role() = 'service_role');
END $$;

-- ================================
-- Storage Bucket 設定
-- ================================

-- 安全建立 Storage Bucket（如果不存在）
DO $$
BEGIN
    INSERT INTO storage.buckets (id, name, public)
    VALUES ('plugin-releases', 'plugin-releases', true);
EXCEPTION
    WHEN unique_violation THEN NULL; -- Bucket 已存在
END $$;

-- Storage RLS 政策設定（安全建立）
DO $$
BEGIN
    -- 1. 允許公開讀取發布檔案
    DROP POLICY IF EXISTS "Public read plugin releases" ON storage.objects;
    CREATE POLICY "Public read plugin releases" ON storage.objects
        FOR SELECT USING (bucket_id = 'plugin-releases');

    -- 2. 允許 Service Role 管理檔案（上傳、更新、刪除）
    DROP POLICY IF EXISTS "Service role manage plugin releases" ON storage.objects;
    CREATE POLICY "Service role manage plugin releases" ON storage.objects
        FOR ALL USING (auth.role() = 'service_role' AND bucket_id = 'plugin-releases');

    -- 3. 允許認證用戶上傳檔案（可選，如果需要讓登入用戶上傳）
    DROP POLICY IF EXISTS "Authenticated users upload plugin releases" ON storage.objects;
    CREATE POLICY "Authenticated users upload plugin releases" ON storage.objects
        FOR INSERT WITH CHECK (
            bucket_id = 'plugin-releases' AND
            auth.role() = 'authenticated'
        );
END $$;


-- ================================
-- 觸發器：自動更新 updated_at
-- ================================

-- 建立更新時間觸發器函數
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

-- 安全建立觸發器（先刪除再建立）
DO $$
BEGIN
    DROP TRIGGER IF EXISTS update_versions_updated_at ON versions;
    CREATE TRIGGER update_versions_updated_at
        BEFORE UPDATE ON versions
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
END $$;

-- ================================
-- 有用的查詢視圖
-- ================================

-- 安全建立視圖（先刪除再建立）
DROP VIEW IF EXISTS latest_version;
CREATE VIEW latest_version AS
SELECT *
FROM versions
WHERE is_active = true
ORDER BY release_date DESC
LIMIT 1;

DROP VIEW IF EXISTS version_stats;
CREATE VIEW version_stats AS
SELECT
    v.version,
    v.release_date,
    COUNT(dl.id) as download_count,
    COUNT(CASE WHEN dl.action = 'download' THEN 1 END) as actual_downloads,
    COUNT(CASE WHEN dl.action = 'update_check' THEN 1 END) as update_checks
FROM versions v
LEFT JOIN download_logs dl ON v.version = dl.version
WHERE v.is_active = true
GROUP BY v.id, v.version, v.release_date
ORDER BY v.release_date DESC;

-- ================================
-- 資料表註解
-- ================================

COMMENT ON TABLE versions IS '插件版本資訊表';
COMMENT ON COLUMN versions.version IS '版本號，格式：x.y.z';
COMMENT ON COLUMN versions.title IS '版本標題，簡短描述版本特色';
COMMENT ON COLUMN versions.download_url IS '下載連結URL';
COMMENT ON COLUMN versions.file_size IS '檔案大小（位元組）';
COMMENT ON COLUMN versions.file_type IS '檔案類型：zip, exe, msi';
COMMENT ON COLUMN versions.file_name IS '檔案名稱，包含副檔名';
COMMENT ON COLUMN versions.changelog IS '更新日誌（Markdown格式）';
COMMENT ON COLUMN versions.min_revit_version IS '最小支援的Revit版本';

COMMENT ON TABLE download_logs IS '下載記錄表';
COMMENT ON COLUMN download_logs.action IS '操作類型：download, update_check, install';