supabase-schema.sql

-- =====================================================
-- KSTools License Manager - Supabase 資料庫架構
-- =====================================================

-- 清理現有資料表 (如果存在)
DROP TABLE IF EXISTS usage_logs CASCADE;
DROP TABLE IF EXISTS licenses CASCADE;

-- =====================================================
-- 授權表 (licenses)
-- =====================================================
CREATE TABLE licenses (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    license_code VARCHAR(50) UNIQUE NOT NULL,
    hardware_id VARCHAR(100),
    machine_name VARCHAR(255),
    user_name VARCHAR(255) NOT NULL,
    user_email VARCHAR(255),
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    is_active BOOLEAN DEFAULT true,
    activated_at TIMESTAMP WITH TIME ZONE,
    last_used_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 授權表註解
COMMENT ON TABLE licenses IS 'KSTools 授權管理表';
COMMENT ON COLUMN licenses.id IS '唯一識別碼';
COMMENT ON COLUMN licenses.license_code IS '授權碼 (格式: 32位隨機字符，支援XXXX-XXXX-XXXX-XXXX分段顯示)';
COMMENT ON COLUMN licenses.hardware_id IS '硬體指紋ID';
COMMENT ON COLUMN licenses.machine_name IS '機器名稱';
COMMENT ON COLUMN licenses.user_name IS '授權用戶名稱';
COMMENT ON COLUMN licenses.user_email IS '用戶電子郵件';
COMMENT ON COLUMN licenses.expires_at IS '授權到期時間';
COMMENT ON COLUMN licenses.is_active IS '是否啟用';
COMMENT ON COLUMN licenses.activated_at IS '首次啟用時間';
COMMENT ON COLUMN licenses.last_used_at IS '最後使用時間';

-- =====================================================
-- 使用記錄表 (usage_logs)
-- =====================================================
CREATE TABLE usage_logs (
    id BIGSERIAL PRIMARY KEY,
    license_id UUID REFERENCES licenses(id) ON DELETE CASCADE,
    action VARCHAR(50) NOT NULL,        -- 'activate', 'validate', 'error'
    ip_address INET,
    user_agent TEXT,
    hardware_info TEXT,
    machine_name VARCHAR(255),
    error_message TEXT,                 -- 錯誤訊息 (如果有)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 使用記錄表註解
COMMENT ON TABLE usage_logs IS 'KSTools 授權使用記錄表';
COMMENT ON COLUMN usage_logs.license_id IS '關聯的授權ID';
COMMENT ON COLUMN usage_logs.action IS '動作類型: activate/validate/error';
COMMENT ON COLUMN usage_logs.ip_address IS '客戶端IP地址';
COMMENT ON COLUMN usage_logs.user_agent IS '用戶代理字符串';
COMMENT ON COLUMN usage_logs.hardware_info IS '硬體資訊';
COMMENT ON COLUMN usage_logs.machine_name IS '機器名稱';
COMMENT ON COLUMN usage_logs.error_message IS '錯誤訊息 (發生錯誤時)';

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

-- 授權表索引
CREATE INDEX idx_licenses_code ON licenses(license_code);
CREATE INDEX idx_licenses_hardware ON licenses(hardware_id);
CREATE INDEX idx_licenses_machine_name ON licenses(machine_name);
CREATE INDEX idx_licenses_active ON licenses(is_active, expires_at);
CREATE INDEX idx_licenses_user_email ON licenses(user_email);
CREATE INDEX idx_licenses_created_at ON licenses(created_at);

-- 使用記錄表索引
CREATE INDEX idx_usage_logs_license ON usage_logs(license_id);
CREATE INDEX idx_usage_logs_created ON usage_logs(created_at);
CREATE INDEX idx_usage_logs_action ON usage_logs(action);
CREATE INDEX idx_usage_logs_ip ON usage_logs(ip_address);

-- =====================================================
-- 觸發器 - 自動更新 updated_at
-- =====================================================
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_licenses_updated_at 
    BEFORE UPDATE ON licenses 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

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

-- 啟用 RLS
ALTER TABLE licenses ENABLE ROW LEVEL SECURITY;
ALTER TABLE usage_logs ENABLE ROW LEVEL SECURITY;

-- 授權表政策 (允許所有操作 - 由應用程式層控制權限)
CREATE POLICY "Allow all operations on licenses" ON licenses
    FOR ALL USING (true);

-- 使用記錄表政策 (允許插入和查詢)
CREATE POLICY "Allow insert and select on usage_logs" ON usage_logs
    FOR ALL USING (true);

-- =====================================================
-- 視圖 - 授權統計
-- =====================================================
CREATE OR REPLACE VIEW license_stats AS
SELECT 
    COUNT(*) as total_licenses,
    COUNT(*) FILTER (WHERE is_active = true AND expires_at > NOW()) as active_licenses,
    COUNT(*) FILTER (WHERE is_active = false) as inactive_licenses,
    COUNT(*) FILTER (WHERE expires_at <= NOW()) as expired_licenses,
    COUNT(*) FILTER (WHERE is_active = true AND expires_at > NOW()) as valid_licenses,
    COUNT(*) FILTER (WHERE activated_at >= CURRENT_DATE) as today_activations,
    COUNT(*) FILTER (WHERE activated_at >= CURRENT_DATE - INTERVAL '1 day' AND activated_at < CURRENT_DATE) as yesterday_activations,
    COUNT(*) FILTER (WHERE activated_at >= CURRENT_DATE - INTERVAL '7 days') as this_week_activations,
    COUNT(*) FILTER (WHERE activated_at >= CURRENT_DATE - INTERVAL '30 days') as this_month_activations
FROM licenses;

COMMENT ON VIEW license_stats IS '授權統計視圖';

-- =====================================================
-- 視圖 - 授權詳細資訊 (含使用記錄)
-- =====================================================
CREATE OR REPLACE VIEW license_details AS
SELECT 
    l.*,
    (SELECT COUNT(*) FROM usage_logs ul WHERE ul.license_id = l.id) as usage_count,
    (SELECT MAX(created_at) FROM usage_logs ul WHERE ul.license_id = l.id AND action = 'validate') as last_validation,
    CASE 
        WHEN l.expires_at <= NOW() THEN 'expired'
        WHEN l.is_active = false THEN 'inactive'
        WHEN l.activated_at IS NULL THEN 'not_activated'
        ELSE 'active'
    END as status,
    l.machine_name as device_name
FROM licenses l;

COMMENT ON VIEW license_details IS '授權詳細資訊視圖 (含統計)';

-- =====================================================
-- 視圖 - 活動記錄 (前端儀表板使用)
-- =====================================================
-- 重新創建 activity_logs 視圖
DROP VIEW IF EXISTS activity_logs;

CREATE VIEW activity_logs AS
SELECT
    ul.id,
    ul.license_id,
    ul.action,
    ul.ip_address,
    ul.user_agent,
    ul.hardware_info,
    ul.machine_name,
    ul.error_message,
    ul.created_at,
    l.license_code,
    l.user_name,
    l.user_email,
    CASE
        -- 管理員操作：直接顯示為成功，不判斷 error_message
        WHEN ul.action IN ('create', 'extend', 'activate_license', 'deactivate_license', 'delete') THEN ul.action
        -- Plugin 操作：有 error_message 就是錯誤
        WHEN ul.action IN ('activate', 'validate') THEN
            CASE
                WHEN ul.error_message IS NOT NULL THEN 'error'
                ELSE ul.action
            END
        -- 其他操作：保持原邏輯
        ELSE
            CASE
                WHEN ul.error_message IS NOT NULL THEN 'error'
                ELSE ul.action
            END
    END as activity_type
FROM usage_logs ul
LEFT JOIN licenses l ON ul.license_id = l.id
ORDER BY ul.created_at DESC;

COMMENT ON VIEW activity_logs IS '活動記錄視圖 (前端儀表板使用)';

-- =====================================================
-- 函數 - 清理過期記錄
-- =====================================================
CREATE OR REPLACE FUNCTION cleanup_old_logs(days_to_keep INTEGER DEFAULT 90)
RETURNS INTEGER AS $$
DECLARE
    deleted_count INTEGER;
BEGIN
    DELETE FROM usage_logs 
    WHERE created_at < NOW() - (days_to_keep || ' days')::INTERVAL;
    
    GET DIAGNOSTICS deleted_count = ROW_COUNT;
    RETURN deleted_count;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION cleanup_old_logs IS '清理指定天數前的使用記錄';

-- =====================================================
-- 函數 - 生成授權統計報告
-- =====================================================
CREATE OR REPLACE FUNCTION generate_license_report(
    start_date DATE DEFAULT CURRENT_DATE - INTERVAL '30 days',
    end_date DATE DEFAULT CURRENT_DATE
)
RETURNS TABLE (
    date DATE,
    new_licenses INTEGER,
    activations INTEGER,
    validations INTEGER,
    errors INTEGER
) AS $$
BEGIN
    RETURN QUERY
    SELECT 
        d.date,
        COALESCE(l.new_licenses, 0) as new_licenses,
        COALESCE(ul_activate.activations, 0) as activations,
        COALESCE(ul_validate.validations, 0) as validations,
        COALESCE(ul_error.errors, 0) as errors
    FROM (
        SELECT generate_series(start_date, end_date, '1 day'::interval)::date as date
    ) d
    LEFT JOIN (
        SELECT created_at::date as date, COUNT(*) as new_licenses
        FROM licenses 
        WHERE created_at::date BETWEEN start_date AND end_date
        GROUP BY created_at::date
    ) l ON d.date = l.date
    LEFT JOIN (
        SELECT created_at::date as date, COUNT(*) as activations
        FROM usage_logs 
        WHERE action = 'activate' AND created_at::date BETWEEN start_date AND end_date
        GROUP BY created_at::date
    ) ul_activate ON d.date = ul_activate.date
    LEFT JOIN (
        SELECT created_at::date as date, COUNT(*) as validations
        FROM usage_logs 
        WHERE action = 'validate' AND created_at::date BETWEEN start_date AND end_date
        GROUP BY created_at::date
    ) ul_validate ON d.date = ul_validate.date
    LEFT JOIN (
        SELECT created_at::date as date, COUNT(*) as errors
        FROM usage_logs 
        WHERE error_message IS NOT NULL AND created_at::date BETWEEN start_date AND end_date
        GROUP BY created_at::date
    ) ul_error ON d.date = ul_error.date
    ORDER BY d.date;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION generate_license_report IS '生成指定日期範圍的授權統計報告';

-- =====================================================
-- 示例資料 (可選)
-- =====================================================

-- 插入測試用授權 (啟用來測試系統) - 統一使用台灣時間00:00計算（-8hr）
INSERT INTO licenses (license_code, user_name, user_email, expires_at, hardware_id, machine_name, is_active, activated_at) VALUES
-- 已啟用用戶 (正常使用中)
('A1B2C3D4E5F6G7H8I9J0K1L2M3N4O5P6', '王小明', 'ming@company.com.tw', DATE_TRUNC('day', NOW()) - INTERVAL '8 hours' + INTERVAL '90 days' - INTERVAL '1 second', 'CPU123ABC456DEF789GHI012JKL345MN', 'DESKTOP-MING-PC', true, NOW() - INTERVAL '5 days'),
('X7Y8Z9W0Q1R2S3T4U5V6A7B8C9D0E1F2', '李美華', 'li.meihua@design.studio', DATE_TRUNC('day', NOW()) - INTERVAL '8 hours' + INTERVAL '365 days' - INTERVAL '1 second', 'CPU789XYZ012ABC345DEF678GHI901JK', 'DESIGN-WORKSTATION-02', true, NOW() - INTERVAL '30 days'),

-- 試用用戶 (7天試用)
('B9C8D7E6F5G4H3I2J1K0L9M8N7O6P5Q4', '張工程師', 'engineer.zhang@tech.com', DATE_TRUNC('day', NOW()) - INTERVAL '8 hours' + INTERVAL '7 days' - INTERVAL '1 second', 'CPUABC123XYZ456QWE789RTY012UIU34', 'ENGINEER-LAPTOP-01', true, NOW() - INTERVAL '2 days'),

-- 更多正常用戶
('M5N6O7P8Q9R0S1T2U3V4W5X6Y7Z8A9B0', '陳建築師', 'chen@architect.firm', DATE_TRUNC('day', NOW()) - INTERVAL '8 hours' + INTERVAL '30 days' - INTERVAL '1 second', 'CPUARCH456BUILD789DESIGN012PROJ34', 'ARCHITECT-STUDIO-PC', true, NOW() - INTERVAL '1 day'),
('F3G4H5I6J7K8L9M0N1O2P3Q4R5S6T7U8', '劉設計師', 'liu.designer@studio.tw', DATE_TRUNC('day', NOW()) - INTERVAL '8 hours' + INTERVAL '60 days' - INTERVAL '1 second', 'CPUDESIGN123CREATIVE456ART789STUD', 'CREATIVE-WORKSTATION', true, NOW() - INTERVAL '7 days'),

-- 過期授權 (已過期15天)
('Z0Y9X8W7V6U5T4S3R2Q1P0O9N8M7L6K5', '周老闆', 'boss.zhou@construction.co', DATE_TRUNC('day', NOW()) - INTERVAL '8 hours' - INTERVAL '15 days' - INTERVAL '1 second', 'CPU456DEF789ABC012GHI345JKL678MN', 'BOSS-OFFICE-PC', true, NOW() - INTERVAL '45 days'),

-- 已停用授權 (管理員手動停用)
('Q2W3E4R5T6Y7U8I9O0P1A2S3D4F5G6H7', '黃違規用戶', 'huang@suspended.user', DATE_TRUNC('day', NOW()) - INTERVAL '8 hours' + INTERVAL '20 days' - INTERVAL '1 second', 'CPUXYZ789ABC012DEF345GHI678JKL90', 'SUSPENDED-PC', false, NOW() - INTERVAL '10 days'),

-- 永久授權 (企業用戶) - 50年
('K1L2M3N4O5P6Q7R8S9T0U1V2W3X4Y5Z6', 'KSTools管理員', 'admin@kstools.com.tw', DATE_TRUNC('day', NOW()) - INTERVAL '8 hours' + INTERVAL '18250 days' - INTERVAL '1 second', 'CPUADMIN123MASTER456CONTROL789AB', 'ADMIN-MASTER-SERVER', true, NOW() - INTERVAL '1 day'),

-- 即將過期 (今天結束就過期)
('V8W9X0Y1Z2A3B4C5D6E7F8G9H0I1J2K3', '趙用戶', 'zhao@expires.soon', DATE_TRUNC('day', NOW()) - INTERVAL '8 hours' + INTERVAL '1 day' - INTERVAL '1 second', 'CPU999END888SOON777EXPIRE666ABC', 'EXPIRING-PC-TODAY', true, NOW() - INTERVAL '28 days')
ON CONFLICT (license_code) DO NOTHING;

-- 插入測試使用記錄
INSERT INTO usage_logs (license_id, action, ip_address, hardware_info, machine_name) VALUES
-- 正常用戶的使用記錄
((SELECT id FROM licenses WHERE license_code = 'A1B2C3D4E5F6G7H8I9J0K1L2M3N4O5P6'), 'activate', '192.168.1.100', 'CPU123ABC456DEF789GHI012JKL345MN', 'DESKTOP-MING-PC'),
((SELECT id FROM licenses WHERE license_code = 'A1B2C3D4E5F6G7H8I9J0K1L2M3N4O5P6'), 'validate', '192.168.1.100', 'CPU123ABC456DEF789GHI012JKL345MN', 'DESKTOP-MING-PC'),
((SELECT id FROM licenses WHERE license_code = 'A1B2C3D4E5F6G7H8I9J0K1L2M3N4O5P6'), 'validate', '192.168.1.100', 'CPU123ABC456DEF789GHI012JKL345MN', 'DESKTOP-MING-PC'),

-- 企業用戶的使用記錄
((SELECT id FROM licenses WHERE license_code = 'X7Y8Z9W0Q1R2S3T4U5V6A7B8C9D0E1F2'), 'activate', '10.0.1.50', 'CPU789XYZ012ABC345DEF678GHI901JK', 'DESIGN-WORKSTATION-02'),
((SELECT id FROM licenses WHERE license_code = 'X7Y8Z9W0Q1R2S3T4U5V6A7B8C9D0E1F2'), 'validate', '10.0.1.50', 'CPU789XYZ012ABC345DEF678GHI901JK', 'DESIGN-WORKSTATION-02'),

-- 其他正常用戶記錄
((SELECT id FROM licenses WHERE license_code = 'M5N6O7P8Q9R0S1T2U3V4W5X6Y7Z8A9B0'), 'activate', '192.168.50.20', 'CPUARCH456BUILD789DESIGN012PROJ34', 'ARCHITECT-STUDIO-PC'),
((SELECT id FROM licenses WHERE license_code = 'M5N6O7P8Q9R0S1T2U3V4W5X6Y7Z8A9B0'), 'validate', '192.168.50.20', 'CPUARCH456BUILD789DESIGN012PROJ34', 'ARCHITECT-STUDIO-PC'),

((SELECT id FROM licenses WHERE license_code = 'F3G4H5I6J7K8L9M0N1O2P3Q4R5S6T7U8'), 'activate', '172.20.10.15', 'CPUDESIGN123CREATIVE456ART789STUD', 'CREATIVE-WORKSTATION'),
((SELECT id FROM licenses WHERE license_code = 'F3G4H5I6J7K8L9M0N1O2P3Q4R5S6T7U8'), 'validate', '172.20.10.15', 'CPUDESIGN123CREATIVE456ART789STUD', 'CREATIVE-WORKSTATION'),

-- 試用用戶的記錄
((SELECT id FROM licenses WHERE license_code = 'B9C8D7E6F5G4H3I2J1K0L9M8N7O6P5Q4'), 'activate', '203.74.123.45', 'CPUABC123XYZ456QWE789RTY012UIU34', 'ENGINEER-LAPTOP-01'),
((SELECT id FROM licenses WHERE license_code = 'B9C8D7E6F5G4H3I2J1K0L9M8N7O6P5Q4'), 'validate', '203.74.123.45', 'CPUABC123XYZ456QWE789RTY012UIU34', 'ENGINEER-LAPTOP-01'),

-- 過期用戶的歷史記錄
((SELECT id FROM licenses WHERE license_code = 'Z0Y9X8W7V6U5T4S3R2Q1P0O9N8M7L6K5'), 'activate', '172.16.0.10', 'CPU456DEF789ABC012GHI345JKL678MN', 'BOSS-OFFICE-PC'),
((SELECT id FROM licenses WHERE license_code = 'Z0Y9X8W7V6U5T4S3R2Q1P0O9N8M7L6K5'), 'validate', '172.16.0.10', 'CPU456DEF789ABC012GHI345JKL678MN', 'BOSS-OFFICE-PC'),

-- 管理員測試記錄
((SELECT id FROM licenses WHERE license_code = 'K1L2M3N4O5P6Q7R8S9T0U1V2W3X4Y5Z6'), 'activate', '127.0.0.1', 'CPUADMIN123MASTER456CONTROL789AB', 'ADMIN-MASTER-SERVER'),
((SELECT id FROM licenses WHERE license_code = 'K1L2M3N4O5P6Q7R8S9T0U1V2W3X4Y5Z6'), 'validate', '127.0.0.1', 'CPUADMIN123MASTER456CONTROL789AB', 'ADMIN-MASTER-SERVER'),

-- 即將過期用戶的記錄
((SELECT id FROM licenses WHERE license_code = 'V8W9X0Y1Z2A3B4C5D6E7F8G9H0I1J2K3'), 'activate', '118.163.88.99', 'CPU999END888SOON777EXPIRE666ABC', 'EXPIRING-PC-TODAY'),
((SELECT id FROM licenses WHERE license_code = 'V8W9X0Y1Z2A3B4C5D6E7F8G9H0I1J2K3'), 'validate', '118.163.88.99', 'CPU999END888SOON777EXPIRE666ABC', 'EXPIRING-PC-TODAY');

-- =====================================================
-- 權限設定 (建議)
-- =====================================================

-- 如果需要建立專用的應用程式角色
-- CREATE ROLE kstools_app_role;
-- GRANT SELECT, INSERT, UPDATE, DELETE ON licenses TO kstools_app_role;
-- GRANT SELECT, INSERT ON usage_logs TO kstools_app_role;
-- GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO kstools_app_role;

-- =====================================================
-- 完成設定
-- =====================================================

-- 驗證資料表建立
SELECT 
    schemaname,
    tablename,
    tableowner
FROM pg_tables 
WHERE tablename IN ('licenses', 'usage_logs')
ORDER BY tablename;

-- 驗證索引建立
SELECT 
    indexname,
    tablename,
    indexdef
FROM pg_indexes 
WHERE tablename IN ('licenses', 'usage_logs')
ORDER BY tablename, indexname;