# v1-SQL Schema

## Member

-- 會員表
CREATE TABLE members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    --* 看有沒有支援 ENUM、確定系統角色是否可重複，另外統一 org 或是 com
    member_type VARCHAR(20) NOT NULL DEFAULT 'general', -- 'general', 'company_user', 'company_admin'
    is_verified BOOLEAN DEFAULT FALSE,
    is_active BOOLEAN DEFAULT TRUE,
    avatar_url VARCHAR(255),
    bio TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    last_login_at TIMESTAMP WITH TIME ZONE,
    last_active_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 會員驗證表
CREATE TABLE member_verifications (
    --* 可能不需要 id, 可以直接用 member_id 當 PK
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    member_id UUID NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    verification_type VARCHAR(20) NOT NULL, -- 'email', 'password_reset'
    verification_code VARCHAR(100) NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    is_used BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 會員會話表
--* 要跟宇衡確定 JWT refresh token 的 session 要存那哪些欄位
CREATE TABLE member_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    member_id UUID NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    ip_address INET,
    user_agent TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 會員通知設定表 (對應個人設定頁面)
CREATE TABLE member_notification_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    member_id UUID NOT NULL REFERENCES members(id) ON DELETE CASCADE,
    email_notifications BOOLEAN DEFAULT TRUE, -- Email Notifications
    activity_updates BOOLEAN DEFAULT TRUE,    -- Activity Updates  
    marketing_emails BOOLEAN DEFAULT FALSE,   -- Marketing Emails
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

--* 可能需要多一張表、存 org-member 的對應提升效能
--* 例如列出某個 org 底下的所有 member 時，若 member DB 中有紀錄 org-member，可以直接 SQL 一次撈
--* 但如果只有 org DB 中有 member-org，需要一筆一筆跟 member svc 拿 member data

-- 索引
CREATE INDEX idx_members_email ON members(email);
CREATE INDEX idx_members_username ON members(username);
CREATE INDEX idx_members_type ON members(member_type);
CREATE INDEX idx_members_active ON members(is_active);
CREATE INDEX idx_members_last_active ON members(last_active_at);
CREATE INDEX idx_member_verifications_code ON member_verifications(verification_code);
CREATE INDEX idx_member_sessions_token ON member_sessions(session_token);
CREATE INDEX idx_member_sessions_member_id ON member_sessions(member_id);

## Organization

-- 企業組織表 (對應Company Profile頁面)
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(100) NOT NULL,                    -- Company Name
    industry VARCHAR(50),                          -- Industry
    website VARCHAR(255),                          -- Company Website  
    description TEXT,                              -- Company Description
    admin_member_id UUID NOT NULL,                 -- 企業後台管理者
    --* 看能不能 ENUM、或是用 true/false
    subscription_plan VARCHAR(20) DEFAULT 'free',  -- 'free', 'premium' 
    is_active BOOLEAN DEFAULT TRUE,
    logo_url VARCHAR(255),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 組織成員表 (對應Team Members和User Management頁面)
--* 若 member 也存 org-member，可以斟酌這邊完全保留、或是只留設定的部分
CREATE TABLE organization_members (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    member_id UUID NOT NULL, -- 參考 Member Service 的 member.id
    role VARCHAR(20) NOT NULL DEFAULT 'member', -- 'admin', 'member'
    
    -- 權限設定 (對應Permissions欄位)
    can_post BOOLEAN DEFAULT FALSE,              -- Can post
    can_moderate BOOLEAN DEFAULT FALSE,          -- Can moderate  
    can_invite BOOLEAN DEFAULT FALSE,            -- Can invite
    
    status VARCHAR(20) DEFAULT 'pending',        -- 'pending', 'active', 'inactive' (對應Status欄位)
    joined_at TIMESTAMP WITH TIME ZONE,
    invited_by UUID,                             -- 邀請者ID
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(organization_id, member_id)
);

-- 成員邀請表
--* 同樣的、可以討論 member DB 是否需要一份 replica
CREATE TABLE member_invitations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    inviter_member_id UUID NOT NULL, -- 邀請者
    invitee_email VARCHAR(100) NOT NULL,
    invitation_token VARCHAR(255) UNIQUE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'accepted', 'rejected', 'expired'
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 組織設定表
CREATE TABLE organization_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    
    -- 安全設定 (對應Account Settings頁面)
    two_factor_authentication BOOLEAN DEFAULT FALSE,
    api_access BOOLEAN DEFAULT FALSE,
    email_notifications BOOLEAN DEFAULT TRUE,
    
    settings JSONB NOT NULL DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_organizations_admin ON organizations(admin_member_id);
CREATE INDEX idx_org_members_org_id ON organization_members(organization_id);
CREATE INDEX idx_org_members_member_id ON organization_members(member_id);
CREATE INDEX idx_org_members_status ON organization_members(status);
CREATE INDEX idx_invitations_token ON member_invitations(invitation_token);
CREATE INDEX idx_invitations_email ON member_invitations(invitee_email);

## Admin

-- 系統管理員表
CREATE TABLE system_admins (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    role VARCHAR(20) DEFAULT 'admin', -- 'super_admin', 'admin', 'moderator'
    permissions JSONB,
    is_active BOOLEAN DEFAULT TRUE,
    last_login_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 內容審核表 (支援Q&A的Approved/Pending Review狀態)
CREATE TABLE content_reviews (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content_type VARCHAR(20) NOT NULL, -- 'experience_post', 'qa_discussion'
    content_id UUID NOT NULL,
    content_service VARCHAR(50) NOT NULL, -- 來源服務名稱
    reviewer_id UUID REFERENCES system_admins(id),
    
    -- AI檢測結果
    ai_score DECIMAL(3,2), -- AI評分 0-1
    ai_analysis JSONB, -- AI分析結果
    is_sustainability_related BOOLEAN DEFAULT FALSE,
    
    -- 審核狀態
    manual_review_required BOOLEAN DEFAULT FALSE,
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'approved', 'rejected', 'flagged'
    review_notes TEXT,
    
    reviewed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 系統設定表
CREATE TABLE system_settings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value JSONB NOT NULL,
    description TEXT,
    is_public BOOLEAN DEFAULT FALSE,
    updated_by UUID REFERENCES system_admins(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 系統日誌表
--! 改用 ES，直接 log 出來 EFK 收
CREATE TABLE system_logs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    service_name VARCHAR(50) NOT NULL,
    log_level VARCHAR(10) NOT NULL, -- 'info', 'warning', 'error', 'critical'
    message TEXT NOT NULL,
    metadata JSONB,
    member_id UUID, -- 關聯的會員ID
    organization_id UUID, -- 關聯的組織ID
    ip_address INET,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 企業審核表
CREATE TABLE organization_reviews (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,
    reviewer_id UUID REFERENCES system_admins(id),
    review_type VARCHAR(20) NOT NULL, -- 'registration', 'subscription_upgrade'
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'approved', 'rejected'
    review_notes TEXT,
    reviewed_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_content_reviews_content ON content_reviews(content_type, content_id);
CREATE INDEX idx_content_reviews_status ON content_reviews(status);
CREATE INDEX idx_content_reviews_reviewer ON content_reviews(reviewer_id);
CREATE INDEX idx_system_logs_service ON system_logs(service_name);
CREATE INDEX idx_system_logs_level ON system_logs(log_level);
CREATE INDEX idx_system_logs_created ON system_logs(created_at);
CREATE INDEX idx_org_reviews_org_id ON organization_reviews(organization_id);
CREATE INDEX idx_org_reviews_status ON organization_reviews(status);

## Content

-- 標籤表 (對應hashtag功能)
CREATE TABLE tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL,
    usage_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

--* 有沒有機會 ES/OpenSearch 或 Mongo 全文搜尋更快 還能 RAG (x
-- 經驗分享文章表 (對應Experience Sharing頁面)
CREATE TABLE experience_posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    author_member_id UUID NOT NULL, -- 參考 Member Service
    organization_id UUID,           -- 參考 Organization Service (發文者所屬組織)
    title VARCHAR(200),
    content TEXT NOT NULL,
    media_urls JSONB,               -- 存儲圖片和影片URL
    status VARCHAR(20) DEFAULT 'published', -- 'draft', 'published', 'archived'
    
    -- 互動統計
    view_count INTEGER DEFAULT 0,
    like_count INTEGER DEFAULT 0,
    comment_count INTEGER DEFAULT 0,
    share_count INTEGER DEFAULT 0,
    
    -- AI檢測
    is_sdg_related BOOLEAN DEFAULT FALSE,
    ai_analysis JSONB,
    
    published_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 文章標籤關聯表 (支援hashtag功能)
CREATE TABLE post_tag_relations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id UUID NOT NULL REFERENCES experience_posts(id) ON DELETE CASCADE,
    tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(post_id, tag_id)
);

--* 這感覺也能用 no-SQL
-- 文章留言表 (對應Comments功能)
CREATE TABLE post_comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    post_id UUID NOT NULL REFERENCES experience_posts(id) ON DELETE CASCADE,
    author_member_id UUID NOT NULL, -- 參考 Member Service
    content TEXT NOT NULL CHECK (LENGTH(content) <= 100), -- 100字限制
    like_count INTEGER DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Q&A討論表 (對應Q&A Zone頁面)
CREATE TABLE qa_discussions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    author_member_id UUID NOT NULL, -- 參考 Member Service
    organization_id UUID,           -- 參考 Organization Service
    title VARCHAR(200) NOT NULL,
    content TEXT NOT NULL,
    
    -- 標籤 (如 #sustainability, #manufacturing)
    tags JSONB,
    
    -- 狀態管理
    status VARCHAR(20) DEFAULT 'published', -- 'pending', 'published', 'approved', 'rejected'
    approval_status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'approved' (對應Approved標籤)
    
    -- 統計
    view_count INTEGER DEFAULT 0,
    answer_count INTEGER DEFAULT 0,
    like_count INTEGER DEFAULT 0,
    
    is_resolved BOOLEAN DEFAULT FALSE,
    is_sustainability_related BOOLEAN DEFAULT FALSE, -- AI檢測結果
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- Q&A回覆表 (對應Responses功能)
CREATE TABLE qa_answers (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    discussion_id UUID NOT NULL REFERENCES qa_discussions(id) ON DELETE CASCADE,
    author_member_id UUID NOT NULL, -- 參考 Member Service
    content TEXT NOT NULL CHECK (LENGTH(content) <= 100), -- 100字限制
    like_count INTEGER DEFAULT 0,
    is_best_answer BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 會員互動表 (點讚、分享等)
CREATE TABLE member_interactions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    member_id UUID NOT NULL, -- 參考 Member Service
    target_type VARCHAR(20) NOT NULL, -- 'post', 'comment', 'qa_discussion', 'qa_answer'
    target_id UUID NOT NULL,
    interaction_type VARCHAR(20) NOT NULL, -- 'like', 'share', 'bookmark'
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(member_id, target_type, target_id, interaction_type)
);

-- 索引
CREATE INDEX idx_experience_posts_author ON experience_posts(author_member_id);
CREATE INDEX idx_experience_posts_org ON experience_posts(organization_id);
CREATE INDEX idx_experience_posts_status ON experience_posts(status);
CREATE INDEX idx_experience_posts_published ON experience_posts(published_at);
CREATE INDEX idx_post_comments_post_id ON post_comments(post_id);
CREATE INDEX idx_post_comments_author ON post_comments(author_member_id);
CREATE INDEX idx_qa_discussions_author ON qa_discussions(author_member_id);
CREATE INDEX idx_qa_discussions_status ON qa_discussions(status);
CREATE INDEX idx_qa_discussions_approval ON qa_discussions(approval_status);
CREATE INDEX idx_qa_answers_discussion ON qa_answers(discussion_id);
CREATE INDEX idx_qa_answers_author ON qa_answers(author_member_id);
CREATE INDEX idx_member_interactions_member ON member_interactions(member_id);

-- 全文搜索索引 (支援搜索功能)
CREATE INDEX idx_experience_posts_search ON experience_posts USING gin(to_tsvector('english', COALESCE(title, '') || ' ' || content));
CREATE INDEX idx_qa_discussions_search ON qa_discussions USING gin(to_tsvector('english', title || ' ' || content));

## Exchange

-- 會員點數表
-- * 可以考慮放 member (視後續 API 功能決定)
CREATE TABLE member_points (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    member_id UUID NOT NULL, -- 參考 Member Service
    total_points INTEGER DEFAULT 0,
    available_points INTEGER DEFAULT 0,
    used_points INTEGER DEFAULT 0,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 會員點數歷史記錄
CREATE TABLE member_point_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    member_id UUID NOT NULL, -- 參考 Member Service
    point_change INTEGER NOT NULL,
    point_type VARCHAR(20) NOT NULL, -- 'earned', 'used', 'expired'
    source_type VARCHAR(50), -- 'event_participation', 'exchange', 'manual_adjustment'
    source_id UUID, -- 關聯的活動或兌換記錄ID
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 兌換商品分類表
CREATE TABLE exchange_categories (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 兌換商品表
CREATE TABLE exchange_items (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID, -- 參考 Organization Service (提供商品的組織)
    category_id UUID REFERENCES exchange_categories(id),
    name VARCHAR(100) NOT NULL,
    description TEXT NOT NULL,
    points_required INTEGER NOT NULL,
    stock_quantity INTEGER DEFAULT 0,
    unlimited_stock BOOLEAN DEFAULT FALSE,
    image_urls JSONB,
    terms_and_conditions TEXT,
    status VARCHAR(20) DEFAULT 'active', -- 'active', 'inactive', 'out_of_stock'
    valid_until TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 兌換記錄表
CREATE TABLE exchange_orders (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    member_id UUID NOT NULL, -- 參考 Member Service
    item_id UUID NOT NULL REFERENCES exchange_items(id),
    organization_id UUID, -- 參考 Organization Service
    points_used INTEGER NOT NULL,
    quantity INTEGER DEFAULT 1,
    status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'confirmed', 'shipped', 'completed', 'cancelled'
    delivery_info JSONB, -- 配送資訊
    tracking_number VARCHAR(100),
    notes TEXT,
    ordered_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 兌換記錄狀態歷史
CREATE TABLE exchange_order_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    order_id UUID NOT NULL REFERENCES exchange_orders(id) ON DELETE CASCADE,
    previous_status VARCHAR(20),
    new_status VARCHAR(20) NOT NULL,
    changed_by UUID, -- 操作者ID
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 庫存變動記錄
CREATE TABLE exchange_stock_movements (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    item_id UUID NOT NULL REFERENCES exchange_items(id) ON DELETE CASCADE,
    movement_type VARCHAR(20) NOT NULL, -- 'add', 'remove', 'exchange', 'adjustment'
    quantity_change INTEGER NOT NULL,
    reference_id UUID, -- 關聯的訂單或其他記錄ID
    notes TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 索引
CREATE INDEX idx_member_points_member_id ON member_points(member_id);
CREATE INDEX idx_member_point_history_member_id ON member_point_history(member_id);
CREATE INDEX idx_member_point_history_type ON member_point_history(point_type);
CREATE INDEX idx_exchange_items_org_id ON exchange_items(organization_id);
CREATE INDEX idx_exchange_items_category ON exchange_items(category_id);
CREATE INDEX idx_exchange_items_status ON exchange_items(status);
CREATE INDEX idx_exchange_orders_member ON exchange_orders(member_id);
CREATE INDEX idx_exchange_orders_item ON exchange_orders(item_id);
CREATE INDEX idx_exchange_orders_status ON exchange_orders(status);
CREATE INDEX idx_exchange_orders_org ON exchange_orders(organization_id);
CREATE INDEX idx_exchange_order_history_order ON exchange_order_history(order_id);
CREATE INDEX idx_stock_movements_item ON exchange_stock_movements(item_id);

## Analytics

-- 首頁統計數據表 (對應首頁儀表板數據)
CREATE TABLE dashboard_stats (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    date DATE NOT NULL,
    organization_id UUID, -- 可選，組織級別統計
    
    -- 對應首頁顯示的數據
    experience_shared_count INTEGER DEFAULT 0,    -- Experience Shared: 245
    questions_asked_count INTEGER DEFAULT 0,      -- Questions Asked: 128  
    active_users_count INTEGER DEFAULT 0,         -- Active Users: 1,024
    
    -- 其他統計
    total_members INTEGER DEFAULT 0,
    new_members_today INTEGER DEFAULT 0,
    total_posts INTEGER DEFAULT 0,
    total_comments INTEGER DEFAULT 0,
    total_likes INTEGER DEFAULT 0,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(date, organization_id)
);

-- 會員活動統計表
CREATE TABLE member_activity_stats (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    member_id UUID NOT NULL, -- 參考 Member Service
    date DATE NOT NULL,
    
    -- 活動統計
    posts_created INTEGER DEFAULT 0,
    comments_made INTEGER DEFAULT 0,
    qa_questions_asked INTEGER DEFAULT 0,
    qa_answers_given INTEGER DEFAULT 0,
    likes_given INTEGER DEFAULT 0,
    shares_made INTEGER DEFAULT 0,
    login_count INTEGER DEFAULT 0,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(member_id, date)
);

-- 內容統計表 (支援Featured Content)
CREATE TABLE content_stats (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    date DATE NOT NULL,
    organization_id UUID, -- 可選
    
    -- 內容統計
    experience_posts_created INTEGER DEFAULT 0,
    qa_discussions_created INTEGER DEFAULT 0,
    comments_created INTEGER DEFAULT 0,
    qa_answers_created INTEGER DEFAULT 0,
    
    -- 互動統計
    total_views INTEGER DEFAULT 0,
    total_likes INTEGER DEFAULT 0,
    total_shares INTEGER DEFAULT 0,
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    UNIQUE(date, organization_id)
);

-- 搜索統計表 (支援搜索功能分析)
CREATE TABLE search_stats (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    search_query VARCHAR(255) NOT NULL,
    search_type VARCHAR(20) NOT NULL, -- 'post', 'qa', 'tag', 'hashtag'
    result_count INTEGER DEFAULT 0,
    member_id UUID, -- 參考 Member Service
    organization_id UUID, -- 參考 Organization Service
    created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

-- 熱門內容表 (支援Community Highlights)
CREATE TABLE trending_content (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    content_type VARCHAR(20) NOT NULL, -- 'experience_post', 'qa_discussion'
    content_id UUID NOT NULL,
    organization_id UUID, -- 參考 Organization Service
    trend_score DECIMAL(10,4) DEFAULT 0, -- 熱門分數
    trend_period VARCHAR(20) NOT NULL, -- 'daily', 'weekly', 'monthly'
    
    -- 計算熱門度的因子
    view_count INTEGER DEFAULT 0,
    like_count INTEGER DEFAULT 0,
    comment_count INTEGER DEFAULT 0,
    share_count INTEGER DEFAULT 0,
    
    calculated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    valid_until TIMESTAMP WITH TIME ZONE NOT NULL,
    
    UNIQUE(content_type, content_id, trend_period)
);

-- 索引
CREATE INDEX idx_dashboard_stats_date ON dashboard_stats(date);
CREATE INDEX idx_dashboard_stats_org ON dashboard_stats(organization_id);
CREATE INDEX idx_member_activity_member ON member_activity_stats(member_id);
CREATE INDEX idx_member_activity_date ON member_activity_stats(date);
CREATE INDEX idx_content_stats_date ON content_stats(date);
CREATE INDEX idx_content_stats_org ON content_stats(organization_id);
CREATE INDEX idx_search_stats_query ON search_stats(search_query);
CREATE INDEX idx_search_stats_type ON search_stats(search_type);
CREATE INDEX idx_search_stats_created ON search_stats(created_at);
CREATE INDEX idx_trending_content_type ON trending_content(content_type);
CREATE INDEX idx_trending_content_score ON trending_content(trend_score);
CREATE INDEX idx_trending_content_period ON trending_content(trend_period);

# 問題統整

## Member DB 問題

1. **`--* 看有沒有支援 ENUM、確定系統角色是否可重複，另外統一 org 或是 com`**
- 位置：members 表的 member_type 欄位
- 問題：需要確認資料庫是否支援 ENUM 類型、系統角色重複性、命名統一性

2. **`--* 可能不需要 id, 可以直接用 member_id 當 PK`**
- 位置：member_verifications 表
- 問題：考慮是否移除獨立的 id 欄位，直接使用 member_id 作為主鍵

3. **`--* 要跟宇衡確定 JWT refresh token 的 session 要存那哪些欄位`**
- 位置：member_sessions 表
- 問題：需要與宇衡確認 JWT refresh token 機制所需的欄位

4. **`--* 可能需要多一張表、存 org-member 的對應提升效能`**
- 位置：索引區塊前
- 問題：考慮是否需要額外的組織-會員對應表來提升查詢效能

5. **`--* 例如列出某個 org 底下的所有 member 時，若 member DB 中有紀錄 org-member，可以直接 SQL 一次撈`**
- 位置：同上
- 問題：說明效能考量的具體場景

6. **`--* 但如果只有 org DB 中有 member-org，需要一筆一筆跟 member svc 拿 member data`**
- 位置：同上
- 問題：說明現有架構可能的效能瓶頸

## Organization DB 問題

7. **`--* 看能不能 ENUM、或是用 true/false`**
- 位置：organizations 表的 subscription_plan 欄位
- 問題：考慮使用 ENUM 或布林值來替代 VARCHAR

8. **`--* 若 member 也存 org-member，可以斟酌這邊完全保留、或是只留設定的部分`**
- 位置：organization_members 表
- 問題：如果 member DB 也存儲組織關聯，需要考慮資料重複性

9. **`--* 同樣的、可以討論 member DB 是否需要一份 replica`**
- 位置：member_invitations 表
- 問題：考慮在 member DB 中是否需要邀請資料的副本

## Content DB 問題

10. **`--* 有沒有機會 ES/OpenSearch 或 Mongo 全文搜尋更快 還能 RAG (x`**
- 位置：tags 表註解
- 問題：考慮使用 Elasticsearch/OpenSearch 或 MongoDB 來提升搜尋效能和支援 RAG

11. **`--* 這感覺也能用 no-SQL`**
- 位置：post_comments 表註解
- 問題：考慮使用 NoSQL 資料庫來存儲留言資料

## Exchange DB 問題

12. **`--* 可以考慮放 member (視後續 API 功能決定)`**
- 位置：member_points 表註解
- 問題：考慮是否將點數資料放在 member service 中

## Admin DB 問題

13. **`--! 改用 ES，直接 log 出來 EFK 收`**
- 位置：system_logs 表註解
- 問題：建議改用 Elasticsearch，通過 EFK (Elasticsearch, Fluentd, Kibana) 來收集日誌

# 改進
- ENUM 類型 + 複合索引
- 移除不必要的 UUID 欄位
- 本地存儲組織關聯
- 專用的 token 索引

| 服務 | 原始問題 | 修正項目 | 修正效果 |
|------|----------|----------|----------|
| **Member Service** | `--* 看有沒有支援 ENUM、確定系統角色是否可重複，另外統一 org 或是 com` | 使用 `member_type_enum` 替代 VARCHAR | 數據一致性提升、查詢效能提升 |
| Member Service | `--* 可能不需要 id, 可以直接用 member_id 當 PK` | `member_verifications` 使用複合主鍵 `(member_id, verification_type)` | 節省空間、避免無意義ID |
| Member Service | `--* 要跟宇衡確定 JWT refresh token 的 session 要存那哪些欄位` | 新增 `refresh_token`, `refresh_expires_at`, `token_type` 欄位 | 支援完整JWT流程 |
| Member Service | `--* 可能需要多一張表、存 org-member 的對應提升效能` | 新增 `member_organizations` 表 | 避免跨服務查詢、提升效能 |
| **Organization Service** | `--* 看能不能 ENUM、或是用 true/false` | 使用 `subscription_plan_enum` 或 `is_premium BOOLEAN` | 訂閱狀態管理標準化 |
| Organization Service | `--* 若 member 也存 org-member，可以斟酌這邊完全保留、或是只留設定的部分` | 保留完整 `organization_members` 表，負責詳細權限管理 | 職責分離、避免數據重複 |
| Organization Service | `--* 同樣的、可以討論 member DB 是否需要一份 replica` | 建議不在 Member DB 複製邀請資料 | 維持單一資料源 |
| **Admin Service** | 字串比較效能問題 | 所有狀態欄位使用 ENUM 類型 | 狀態管理標準化、查詢效能提升 |
| Admin Service | `--! 改用 ES，直接 log 出來 EFK 收` | 移除 `system_logs` 表 | 符合 ELK Stack 架構 |
| **Content Service** | `--* 有沒有機會 ES/OpenSearch 或 Mongo 全文搜尋更快 還能 RAG (x` | 保持 PostgreSQL GIN 索引，建議搭配 ES | 搜尋效能提升、支援未來RAG |
| Content Service | `--* 這感覺也能用 no-SQL` | 保持 PostgreSQL 結構 | 維持ACID特性、暫不遷移 |
| Content Service | 標籤關聯表效能 | `post_tag_relations` 使用複合主鍵 `(post_id, tag_id)` | 提升效能、節省空間 |
| **Exchange Service** | `-- * 可以考慮放 member (視後續 API 功能決定)` | `member_points` 直接用 `member_id` 當主鍵，保留在 Exchange Service | 避免跨服務查詢複雜度 |
| Exchange Service | 狀態管理混亂 | 所有狀態欄位使用 ENUM 類型 | 狀態流程標準化 |
| **Analytics Service** | 字串比較效能 | `search_type`, `content_type`, `trend_period` 使用 ENUM | 查詢效能提升 |

---
# v2-SQL Schema

> 接下來以國展想看的 demo 為主，我提議依序搞定 admin、member、organization

## Activity Service Database

-- 動態表單系統擴展

-- 表單欄位類型枚舉
CREATE TYPE field_type_enum AS ENUM (
    'short_text',      -- 短文字 (單行輸入)
    'long_text',       -- 長文字 (多行輸入)
    'number',          -- 數字
    'email',           -- 電子郵件
    'phone',           -- 電話
    'date',            -- 日期
    'time',            -- 時間
    'datetime',        -- 日期時間
    'single_choice',   -- 單選 (radio)
    'multiple_choice', -- 多選 (checkbox)
    'dropdown',        -- 下拉選單
    'file_upload',     -- 檔案上傳
    'rating',          -- 評分
    'yes_no',          -- 是/否
    'url',             -- 網址
    'address'          -- 地址
);

-- 表單狀態枚舉
CREATE TYPE form_status_enum AS ENUM (
    'draft',           -- 草稿
    'active',          -- 啟用中
    'closed',          -- 已關閉
    'archived'         -- 已封存
);

-- 表單模板表 (定義表單結構)
CREATE TABLE form_templates (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL,          -- 所屬組織
    creator_user_id UUID NOT NULL,          -- 創建者
    activity_id UUID REFERENCES activities(id) ON DELETE SET NULL, -- 可選：關聯到特定活動
    
    title VARCHAR(255) NOT NULL,            -- 表單標題
    description TEXT,                       -- 表單說明
    status form_status_enum DEFAULT 'draft', -- 表單狀態
    
    -- 進階設定
    is_public BOOLEAN DEFAULT false,        -- 是否公開 (可匿名填寫)
    allow_multiple_submissions BOOLEAN DEFAULT false, -- 是否允許重複提交
    require_login BOOLEAN DEFAULT true,     -- 是否需要登入才能填寫
    show_progress_bar BOOLEAN DEFAULT false, -- 是否顯示進度條
    submit_button_text VARCHAR(50) DEFAULT '提交', -- 提交按鈕文字
    success_message TEXT DEFAULT '感謝您的提交！', -- 提交成功訊息
    
    -- 時間設定
    open_time TIMESTAMP WITH TIME ZONE,     -- 開放填寫時間
    close_time TIMESTAMP WITH TIME ZONE,    -- 關閉填寫時間
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 表單欄位表 (定義每個欄位的屬性)
CREATE TABLE form_fields (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    form_template_id UUID NOT NULL REFERENCES form_templates(id) ON DELETE CASCADE,
    
    field_type field_type_enum NOT NULL,    -- 欄位類型
    label VARCHAR(255) NOT NULL,            -- 欄位標籤/問題
    description TEXT,                       -- 欄位說明/提示文字
    placeholder VARCHAR(255),               -- 佔位符文字
    
    -- 驗證規則
    is_required BOOLEAN DEFAULT false,      -- 是否必填
    min_length INTEGER,                     -- 最小長度 (文字欄位)
    max_length INTEGER,                     -- 最大長度 (文字欄位)
    min_value NUMERIC(15, 2),              -- 最小值 (數字欄位)
    max_value NUMERIC(15, 2),              -- 最大值 (數字欄位)
    validation_regex TEXT,                  -- 自訂驗證正則表達式
    validation_message TEXT,                -- 驗證失敗訊息
    
    -- 排序與顯示
    display_order INTEGER NOT NULL,         -- 顯示順序
    is_visible BOOLEAN DEFAULT true,        -- 是否顯示
    
    -- 條件邏輯 (進階功能)
    conditional_logic JSONB,                -- 條件顯示邏輯 (JSON格式)
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 表單欄位選項表 (用於單選、多選、下拉選單等)
CREATE TABLE form_field_options (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    form_field_id UUID NOT NULL REFERENCES form_fields(id) ON DELETE CASCADE,
    
    option_value TEXT NOT NULL,             -- 選項值
    option_label VARCHAR(255) NOT NULL,     -- 選項顯示文字
    display_order INTEGER NOT NULL,         -- 顯示順序
    is_default BOOLEAN DEFAULT false,       -- 是否為預設選項
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 表單提交記錄表 (儲存每次提交)
CREATE TABLE form_submissions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    form_template_id UUID NOT NULL REFERENCES form_templates(id) ON DELETE CASCADE,
    
    submitter_user_id UUID,                 -- 提交者 (如果已登入)
    submitter_ip VARCHAR(45),               -- 提交者 IP
    submitter_email VARCHAR(255),           -- 提交者 Email (如果收集)
    
    is_completed BOOLEAN DEFAULT false,     -- 是否完成提交 (支援草稿功能)
    submission_data JSONB NOT NULL,         -- 提交的表單資料 (JSON格式，靈活儲存)
    
    submitted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 表單提交回應表 (詳細儲存每個欄位的回應)
CREATE TABLE form_field_responses (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    form_submission_id UUID NOT NULL REFERENCES form_submissions(id) ON DELETE CASCADE,
    form_field_id UUID NOT NULL REFERENCES form_fields(id) ON DELETE CASCADE,
    
    response_value TEXT,                    -- 回應值 (文字形式)
    response_data JSONB,                    -- 回應資料 (JSON格式，支援複雜資料)
    
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 表單檔案上傳表 (如果欄位包含檔案上傳)
CREATE TABLE form_file_uploads (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    form_field_response_id UUID NOT NULL REFERENCES form_field_responses(id) ON DELETE CASCADE,
    
    file_name VARCHAR(255) NOT NULL,        -- 檔案名稱
    file_size BIGINT NOT NULL,              -- 檔案大小 (bytes)
    file_type VARCHAR(100),                 -- 檔案類型 (MIME type)
    file_url TEXT NOT NULL,                 -- 檔案儲存路徑/URL
    
    uploaded_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 表單統計表 (快取表單統計資料)
CREATE TABLE form_statistics (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    form_template_id UUID NOT NULL REFERENCES form_templates(id) ON DELETE CASCADE,
    
    total_views INTEGER DEFAULT 0,          -- 總瀏覽次數
    total_submissions INTEGER DEFAULT 0,    -- 總提交次數
    completion_rate NUMERIC(5, 2),          -- 完成率
    average_completion_time INTEGER,        -- 平均完成時間 (秒)
    
    last_calculated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 索引優化
CREATE INDEX idx_form_templates_org_id ON form_templates(organization_id);
CREATE INDEX idx_form_templates_activity_id ON form_templates(activity_id);
CREATE INDEX idx_form_templates_status ON form_templates(status);
CREATE INDEX idx_form_fields_template_id ON form_fields(form_template_id);
CREATE INDEX idx_form_fields_display_order ON form_fields(form_template_id, display_order);
CREATE INDEX idx_form_field_options_field_id ON form_field_options(form_field_id);
CREATE INDEX idx_form_submissions_template_id ON form_submissions(form_template_id);
CREATE INDEX idx_form_submissions_user_id ON form_submissions(submitter_user_id);
CREATE INDEX idx_form_submissions_submitted_at ON form_submissions(submitted_at);
CREATE INDEX idx_form_field_responses_submission_id ON form_field_responses(form_submission_id);
CREATE INDEX idx_form_file_uploads_response_id ON form_file_uploads(form_field_response_id);

-- JSONB 索引 (加速 JSON 查詢)
CREATE INDEX idx_form_submissions_data ON form_submissions USING gin(submission_data);
CREATE INDEX idx_form_field_responses_data ON form_field_responses USING gin(response_data);

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

-- 為需要的表創建觸發器
CREATE TRIGGER update_form_templates_updated_at BEFORE UPDATE ON form_templates
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_form_fields_updated_at BEFORE UPDATE ON form_fields
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_form_submissions_updated_at BEFORE UPDATE ON form_submissions
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

## Admin Service Database

-- Admin Service Database

-- 平台管理員操作日誌
CREATE TABLE platform_admin_audit_log (
    id BIGSERIAL PRIMARY KEY,
    admin_user_id UUID NOT NULL, -- 執行操作的平台管理員 User ID (來自 Member Service)
    action_type VARCHAR(100) NOT NULL, -- 例如: 'approve_organization', 'reject_organization', 'suspend_user', 'update_system_setting'
    target_entity_type VARCHAR(50), -- 例如: 'organization', 'user', 'article'
    target_entity_id VARCHAR(36),   -- 被操作的實體 ID (應該是 UUID)
    details JSONB,                  -- 操作細節
    ip_address INET,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 系統設定表 (可選，若有全域設定需求)
CREATE TABLE system_settings (
    setting_key VARCHAR(100) PRIMARY KEY,
    setting_value TEXT,
    description TEXT,
    updated_by_admin_user_id UUID,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- (可選) 審核佇列表 (如果需要一個集中的地方管理各類審核任務)
-- 實際上審核狀態更多是分散在各自服務的實體中 (如 Organization 的 status, Article 的 status)
-- 此表更像是一個給平台管理員看的「待辦事項」摘要
-- CREATE TABLE admin_review_queue (
--     id BIGSERIAL PRIMARY KEY,
--     item_type VARCHAR(50) NOT NULL, -- 'organization_application', 'article_flagged', 'question_flagged'
--     item_id UUID NOT NULL,          -- 對應到具體服務中的實體 ID
--     reason_for_review TEXT,
--     assigned_to_admin_user_id UUID,
--     status VARCHAR(20) DEFAULT 'pending', -- 'pending', 'in_progress', 'resolved'
--     created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
--     updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
-- );

-- 索引
CREATE INDEX idx_platform_admin_audit_log_admin_user_id ON platform_admin_audit_log(admin_user_id);
CREATE INDEX idx_platform_admin_audit_log_target_entity ON platform_admin_audit_log(target_entity_type, target_entity_id);
-- CREATE INDEX idx_admin_review_queue_status ON admin_review_queue(status);

## Analytics Service Database

-- Analytics Service Database

-- 每日使用者活動快照 (範例)
CREATE TABLE daily_user_activity_summary (
    summary_date DATE PRIMARY KEY,
    active_users_count INT,          -- 活躍使用者數量
    new_registrations_count INT,
    -- ... 其他使用者相關指標
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 每日內容互動快照 (範例)
CREATE TABLE daily_content_summary (
    summary_date DATE PRIMARY KEY,
    articles_published_count INT,
    questions_asked_count INT,
    comments_made_count INT,
    answers_given_count INT,
    -- 其他指標
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 企業活動概覽
CREATE TABLE organization_activity_overview (
    organization_id UUID,
    summary_date DATE,
    active_users_in_org_count INT,
    articles_posted_by_org_users_count INT,
    activities_created_count INT,
    PRIMARY KEY (organization_id, summary_date),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 儀表板用：最近的文章/問題 (這更像是一個快取或投影，資料源是 Content Service)
CREATE TABLE recent_dashboard_items (
    item_id UUID PRIMARY KEY, -- article_id 或 question_id
    item_type VARCHAR(20) NOT NULL, -- 'article', 'question'
    title VARCHAR(255),
    author_display_name VARCHAR(100), -- 冗餘顯示用
    organization_display_name VARCHAR(200), -- 冗餘顯示用
    created_or_published_at TIMESTAMP WITH TIME ZONE,
    -- 用於排序和快速提取
    display_priority INT DEFAULT 0,
    data_payload JSONB, -- 原始項目摘要，方便前端直接使用
    refreshed_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 索引 (針對查詢優化)
CREATE INDEX idx_daily_user_activity_summary_date ON daily_user_activity_summary(summary_date);
CREATE INDEX idx_daily_content_summary_date ON daily_content_summary(summary_date);
CREATE INDEX idx_organization_activity_overview_org_date ON organization_activity_overview(organization_id, summary_date);
CREATE INDEX idx_recent_dashboard_items_type_date ON recent_dashboard_items(item_type, created_or_published_at DESC);

## Content Service Database

-- Content Service Database

-- 內容審核狀態枚舉
CREATE TYPE content_status_enum AS ENUM (
    'draft',            -- 草稿 (若有此功能)
    'pending_review',   -- 等待審核 (SDGs/ESG 相關性)
    'published',        -- 已發布
    'rejected',         -- 審核未通過
    'archived'          -- 已封存/下架
);

-- 文章表 (經驗分享文章)
CREATE TABLE articles (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL, -- 主要文字內容

    -- 作者資訊 (從 Member Service 獲取)
    author_user_id UUID NOT NULL, -- 必須是 'organization_user'
    author_organization_id UUID,  -- 作者所屬企業 ID (方便查詢)

    status content_status_enum NOT NULL DEFAULT 'pending_review',
    rejection_reason TEXT, -- 審核未通過原因

    -- 圖片與影片連結 (使用 JSONB 儲存陣列，或另開表)
    -- 範例：[{ "type": "image", "url": "...", "caption": "..." }, { "type": "video", "url": "...", "provider": "youtube" }]
    media_attachments JSONB,

    view_count INT DEFAULT 0,
    like_count INT DEFAULT 0, -- 若有按讚功能

    published_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- tags
CREATE TABLE tags (
    id SERIAL PRIMARY KEY, -- 使用 SERIAL 或 UUID 皆可
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 文章與標籤關聯表 (多對多)
CREATE TABLE article_tags (
    article_id UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    tag_id INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (article_id, tag_id)
);

-- 文章留言表
CREATE TABLE article_comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    article_id UUID NOT NULL REFERENCES articles(id) ON DELETE CASCADE,
    commenter_user_id UUID NOT NULL, -- 任何已登入使用者皆可留言
    parent_comment_id UUID REFERENCES article_comments(id) ON DELETE CASCADE, -- 用於巢狀留言
    content VARCHAR(100) NOT NULL, -- 文字上限 100 字

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 問題討論串表 (發問區)
CREATE TABLE questions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(255) NOT NULL, -- 問題標題/簡述
    details TEXT,                -- 問題詳細描述 (純文字)

    asker_user_id UUID NOT NULL, -- 任何已登入使用者皆可提問
    asker_organization_id UUID,  -- 提問者所屬企業ID (若適用)

    status content_status_enum NOT NULL DEFAULT 'pending_review', -- 問題也需審核 SDGs/ESG 相關性
    rejection_reason TEXT,

    view_count INT DEFAULT 0,
    answer_count INT DEFAULT 0, -- 回答數量

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_activity_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP -- 最後回答或編輯時間
);

-- 問題回答表
CREATE TABLE Youtubes ( -- *** 原 Youtubes 表已更名 ***
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    question_id UUID NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
    answerer_user_id UUID NOT NULL, -- 任何已登入使用者皆可回答
    content TEXT NOT NULL,          -- 純文字回答

    is_accepted_answer BOOLEAN DEFAULT FALSE, -- 若有最佳答案功能

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 索引
CREATE INDEX idx_articles_author_user_id ON articles(author_user_id);
CREATE INDEX idx_articles_status ON articles(status);
CREATE INDEX idx_article_tags_tag_id ON article_tags(tag_id);
CREATE INDEX idx_article_comments_article_id ON article_comments(article_id);
CREATE INDEX idx_questions_asker_user_id ON questions(asker_user_id);
CREATE INDEX idx_questions_status ON questions(status);
CREATE INDEX idx_Youtubes_question_id ON Youtubes(question_id); -- *** 索引名稱與對應表已更新 ***

-- 全文搜索相關的索引 (依賴具體資料庫，例如 PostgreSQL 的 GIN/GIST 索引)
-- CREATE INDEX idx_articles_content_search ON articles USING gin(to_tsvector('chinese', title || ' ' || content));
-- CREATE INDEX idx_questions_content_search ON questions USING gin(to_tsvector('chinese', title || ' ' || details));
-- CREATE INDEX idx_tags_name_search ON tags USING gin(to_tsvector('chinese', name));

## Member Service Database

-- Member Service Database

-- 使用者身份枚舉
CREATE TYPE member_role_enum AS ENUM (
    'platform_admin',  -- 平台管理者
    'general_user',    -- 一般使用者
    'organization_admin', -- 企業後臺管理者
    'organization_user'   -- 企業使用者
);

-- email驗證枚舉
CREATE TYPE email_verification_enum AS ENUM (
    'email_verification',
    'password_reset',
);

-- 使用者帳號表
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username VARCHAR(50) UNIQUE NOT NULL, -- 使用者帳號名稱
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),              -- 真實姓名或暱稱
    avatar_url VARCHAR(255),             -- 頭像圖片URL
    bio TEXT,                            -- 個人簡介

    role member_role_enum NOT NULL DEFAULT 'general_user', -- 使用者身份

    -- 關聯到 Organization Service 的 organization_id
    -- 對於 platform_admin 和 general_user，此欄位為 NULL
    -- 對於 organization_admin 和 organization_user，此欄位必要
    organization_id UUID,
    -- 冗餘企業名稱，方便 Member Service 內部快速查詢，但主要來源是 Organization Service
    organization_name VARCHAR(200),

    is_email_verified BOOLEAN DEFAULT FALSE, -- Email 認證了沒
    is_active BOOLEAN DEFAULT TRUE,          -- 帳號是否啟用 (用於軟刪除或禁用)
    status_reason TEXT,                      -- 帳號狀態變更原因（要？）

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_login_at TIMESTAMP WITH TIME ZONE
);

-- 使用者 Email 驗證碼/密碼重設 Token 錶
CREATE TABLE user_verifications (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    verification_type VARCHAR(20) NOT NULL, -- 'email_verification', 'password_reset'
    token VARCHAR(100) UNIQUE NOT NULL,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    is_used BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 使用者會話表 (用於管理登入狀態，可選，取決於認證方案)
CREATE TABLE user_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    session_token VARCHAR(255) UNIQUE NOT NULL,
    refresh_token VARCHAR(255) UNIQUE,
    ip_address INET,
    user_agent TEXT,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 使用者通知設定表
CREATE TABLE user_notification_settings (
    user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    email_notifications_enabled BOOLEAN DEFAULT TRUE,    -- 不累嘎總開關：是否接收郵件通知
    -- 可以再細分更多通知類型，例如：
    -- new_post_in_followed_topic BOOLEAN DEFAULT TRUE,
    -- new_reply_to_my_comment BOOLEAN DEFAULT TRUE,
    -- activity_updates BOOLEAN DEFAULT TRUE, -- 來自活動模組的更新
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 索引
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_role ON users(role);
CREATE INDEX idx_users_organization_id ON users(organization_id) WHERE organization_id IS NOT NULL;
CREATE INDEX idx_user_verifications_token ON user_verifications(token);
CREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id);

## Organization Service Database

-- Organization Service Database

-- 企業帳號申請狀態枚舉
CREATE TYPE organization_status_enum AS ENUM (
    'pending_approval', -- 等待平台管理者審核
    'approved',         -- 已核准
    'rejected',         -- 已拒絕
    'active',           -- 活躍 (核准後)
    'suspended'         -- 已暫停
);

-- 系統版本枚舉 (免費版/付費版)
CREATE TYPE system_version_enum AS ENUM (
    'free',  -- 免費版
    'paid'   -- 付費版
);

-- 企業表
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(200) UNIQUE NOT NULL, -- 企業名稱
    -- 企業後台管理者 (對應到 Member Service 的 user_id)
    -- 此 user_id 的 role 應為 'organization_admin'
    admin_user_id UUID UNIQUE, 
    status organization_status_enum NOT NULL DEFAULT 'pending_approval',
    status_reason TEXT,                      -- 狀態變更原因

    -- 新增：企業基本資訊欄位
    industry VARCHAR(100),                   -- 行業別
    website VARCHAR(255),                    -- 企業網站
    description TEXT,                        -- 企業描述

    current_version system_version_enum NOT NULL DEFAULT 'free',
    subscription_start_date TIMESTAMP WITH TIME ZONE,
    subscription_end_date TIMESTAMP WITH TIME ZONE, -- 付費版的到期日

    -- 其他企業相關資訊 (可擴充)
    -- address TEXT,
    -- contact_phone VARCHAR(30),

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    approved_by UUID, -- 平台管理者 User ID (如果需要記錄是誰核准的)
    approved_at TIMESTAMP WITH TIME ZONE
);

-- 企業成員表 (用於管理企業使用者與企業的關係)
CREATE TABLE organization_members (
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    -- 對應到 Member Service 的 user_id
    -- 此 user_id 的 role 應為 'organization_user' 或 'organization_admin'
    user_id UUID NOT NULL,
    member_status organization_status_enum NOT NULL DEFAULT 'pending_approval', -- 企業使用者加入企業的審核狀態
    status_reason TEXT, -- 審核不過原因等

    -- 企業內部細粒度權限 (範例，可擴展)
    can_post_articles BOOLEAN DEFAULT FALSE,   -- 是否能發布經驗分享文章 (由企業後台管理者控制)
    can_edit_activities BOOLEAN DEFAULT FALSE, -- 是否能編輯活動資料 (由企業後台管理者控制)

    -- 移除 can_ask_questions 欄位，因為提問應該是所有用戶的基本權限
    -- can_manage_specific_activity_id UUID, -- 管理特定活動的權限

    joined_at TIMESTAMP WITH TIME ZONE, -- 審核通過加入時間
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- 申請時間
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (organization_id, user_id)
);

-- 索引
CREATE INDEX idx_organizations_name ON organizations(name);
CREATE INDEX idx_organizations_admin_user_id ON organizations(admin_user_id);
CREATE INDEX idx_organizations_status ON organizations(status);
CREATE INDEX idx_organizations_industry ON organizations(industry);
CREATE INDEX idx_organization_members_user_id ON organization_members(user_id);

## 怎麼起在本地
路徑要改
### docker-compose.yml

version: '3.8'

services:
  postgres_db:
    image: postgres:15
    container_name: impact_copilot_postgres_demo
    environment:
      POSTGRES_USER: ricky_user
      POSTGRES_PASSWORD: ricky_secret
      POSTGRES_DB: main_db # 這個資料庫會被自動建立，init-script.sh 會用它來連線並建立其他DB
    ports:
      - "5433:5432"
    volumes:
      - pg_data_demo:/var/lib/postgresql/data
      - ../db-v1:/docker-entrypoint-initdb.d/schemas
      - ./init-db/init-script.sh:/docker-entrypoint-initdb.d/init-script.sh
    restart: unless-stopped
    healthcheck: 
      test: ["CMD-SHELL", "pg_isready -U ricky_user -d main_db"]
      interval: 10s
      timeout: 5s
      retries: 5

  pgadmin:
    image: dpage/pgadmin4
    container_name: impact_copilot_pgadmin
    environment:
      PGADMIN_DEFAULT_EMAIL: [e-mail] # pgAdmin 登入 Email
      PGADMIN_DEFAULT_PASSWORD: admin          # pgAdmin 登入密碼
    ports:
      - "5050:80" # pgAdmin on http://localhost:5050
    volumes:
      - pgadmin_data:/var/lib/pgadmin 
    depends_on:
      postgres_db: 
        condition: service_healthy 
    restart: unless-stopped

volumes:
  pg_data_demo:
  pgadmin_data:

### demo-api/init-db/init-script.sh

#!/bin/bash
set -e 


psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "$POSTGRES_DB" <<-EOSQL
    CREATE DATABASE member_service_db;
    CREATE DATABASE organization_service_db;
    CREATE DATABASE content_service_db;
    CREATE DATABASE activity_service_db;
EOSQL

echo "資料庫 member_service_db, organization_service_db, content_service_db, activity_service_db 已建立。"

# 匯入 Schema
echo "Member Service Schema..."
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "member_service_db" < /docker-entrypoint-initdb.d/schemas/Member-Service-Database.sql

echo " Organization Service Schema..."
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "organization_service_db" < /docker-entrypoint-initdb.d/schemas/Organization-Service-Database.sql

echo " Content Service Schema..."
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "content_service_db" < /docker-entrypoint-initdb.d/schemas/Content-Service-Database.sql

echo " Activity Service Schema..."
psql -v ON_ERROR_STOP=1 --username "$POSTGRES_USER" --dbname "activity_service_db" < /docker-entrypoint-initdb.d/schemas/Activity-Service-Database.sql

echo "應該成了"