supabase-version-schema-autocad.sql

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

這個 schema 擴展現有的 Revit 版本系統以支援 AutoCAD
使用獨立表格但共用同一個 Storage bucket 的不同資料夾
*/

-- ================================
-- AutoCAD 版本管理表
-- ================================

-- AutoCAD 版本表
CREATE TABLE IF NOT EXISTS versions_autocad (
    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_autocad_version VARCHAR(10),
    released_by VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- AutoCAD 下載記錄表
CREATE TABLE IF NOT EXISTS download_logs_autocad (
    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()
);

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

-- AutoCAD 版本表索引
CREATE INDEX IF NOT EXISTS idx_versions_autocad_version ON versions_autocad(version);
CREATE INDEX IF NOT EXISTS idx_versions_autocad_release_date ON versions_autocad(release_date DESC);
CREATE INDEX IF NOT EXISTS idx_versions_autocad_is_active ON versions_autocad(is_active);

-- AutoCAD 下載記錄表索引
CREATE INDEX IF NOT EXISTS idx_download_logs_autocad_version ON download_logs_autocad(version);
CREATE INDEX IF NOT EXISTS idx_download_logs_autocad_downloaded_at ON download_logs_autocad(downloaded_at DESC);
CREATE INDEX IF NOT EXISTS idx_download_logs_autocad_action ON download_logs_autocad(action);

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

-- 啟用 RLS
DO $$
BEGIN
    ALTER TABLE versions_autocad ENABLE ROW LEVEL SECURITY;
EXCEPTION
    WHEN OTHERS THEN NULL;
END $$;

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

-- 安全建立 RLS 政策
DO $$
BEGIN
    -- AutoCAD 版本表政策
    DROP POLICY IF EXISTS "Public read versions autocad" ON versions_autocad;
    CREATE POLICY "Public read versions autocad" ON versions_autocad
        FOR SELECT USING (true);

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

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

    DROP POLICY IF EXISTS "Service role delete versions autocad" ON versions_autocad;
    CREATE POLICY "Service role delete versions autocad" ON versions_autocad
        FOR DELETE USING (auth.role() = 'service_role');

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

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

-- 使用現有的更新時間觸發器函數
DO $$
BEGIN
    DROP TRIGGER IF EXISTS update_versions_autocad_updated_at ON versions_autocad;
    CREATE TRIGGER update_versions_autocad_updated_at
        BEFORE UPDATE ON versions_autocad
        FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
END $$;

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

-- AutoCAD 最新版本視圖
DROP VIEW IF EXISTS latest_version_autocad;
CREATE VIEW latest_version_autocad AS
SELECT *
FROM versions_autocad
WHERE is_active = true
ORDER BY release_date DESC
LIMIT 1;

-- AutoCAD 版本統計視圖
DROP VIEW IF EXISTS version_stats_autocad;
CREATE VIEW version_stats_autocad 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_autocad v
LEFT JOIN download_logs_autocad 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;

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

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

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

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

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

/*
Storage 結構說明：

Revit 插件：
- Bucket: plugin-releases
- 檔案命名：KSTools-v{version}-Revit.{ext}
- 範例：plugin-releases/KSTools-v1.0.0-Revit.zip

AutoCAD 插件：
- Bucket: plugin-releases-autocad
- 檔案命名：KSTools-AutoCAD-v{version}.{ext}
- 範例：plugin-releases-autocad/KSTools-AutoCAD-v1.0.0.zip

優點：
- 透過不同 bucket 完全隔離兩個產品
- 檔名加上產品類型後綴，易於識別
- 避免版本管理混淆
*/

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

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

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

-- ================================
-- 執行完成提示
-- ================================

DO $$
BEGIN
    RAISE NOTICE '✅ AutoCAD 版本管理 Schema 已成功建立或更新';
    RAISE NOTICE '📦 Revit Storage bucket: plugin-releases';
    RAISE NOTICE '📦 AutoCAD Storage bucket: plugin-releases-autocad';
    RAISE NOTICE '📝 Revit 檔案命名：KSTools-Revit-v{version}.{ext}';
    RAISE NOTICE '📝 AutoCAD 檔案命名：KSTools-AutoCAD-v{version}.{ext}';
    RAISE NOTICE '';
    RAISE NOTICE '⚠️  重要：現有 Revit 檔案需要重新命名加上 -Revit 後綴';
    RAISE NOTICE '   或在 API 中加入相容邏輯以支援舊檔名';
END $$;