xtablo-source/supabase/migrations_backup/06_sample_data_and_queries.sql

-- =====================================================
-- SAMPLE DATA FOR TABLOS SYSTEM
-- =====================================================

-- Create tablos table
CREATE TABLE IF NOT EXISTS tablos (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    description TEXT,
    color VARCHAR(50) DEFAULT 'bg-blue-500',
    owner_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
    is_public BOOLEAN DEFAULT false,
    position INTEGER DEFAULT 0,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP WITH TIME ZONE
);

-- Enable RLS
ALTER TABLE tablos ENABLE ROW LEVEL SECURITY;

-- Create policies for tablos
CREATE POLICY "Users can view their own tablos" ON tablos
    FOR SELECT USING (auth.uid() = owner_id);

CREATE POLICY "Users can view public tablos" ON tablos
    FOR SELECT USING (is_public = true);

CREATE POLICY "Users can insert their own tablos" ON tablos
    FOR INSERT WITH CHECK (auth.uid() = owner_id);

CREATE POLICY "Users can update their own tablos" ON tablos
    FOR UPDATE USING (auth.uid() = owner_id);

CREATE POLICY "Users can delete their own tablos" ON tablos
    FOR DELETE USING (auth.uid() = owner_id);


-- Sample tablos data
INSERT INTO tablos (name, description, color, owner_id, is_public) VALUES
('Projet Alpha', 'Développement de la nouvelle application mobile', 'bg-blue-500', auth.uid(), false),
('Marketing Q4', 'Campagnes marketing pour le quatrième trimestre 2024', 'bg-green-500', auth.uid(), true),
('Équipe Dev', 'Coordination et suivi de l''équipe de développement', 'bg-purple-500', auth.uid(), false),
('Budget 2024', 'Planification et suivi budgétaire pour l''année 2024', 'bg-red-500', auth.uid(), false),
('Roadmap Produit', 'Feuille de route et évolution du produit', 'bg-yellow-500', auth.uid(), true),
('Support Client', 'Gestion et suivi du support client', 'bg-indigo-500', auth.uid(), false);

-- =====================================================
-- USEFUL QUERIES FOR TABLOS SYSTEM
-- =====================================================

-- 1. Get all tablos for a user (owned or member of)
/*
SELECT DISTINCT t.*, tm.role, tm.permissions
FROM tablos t
LEFT JOIN tablo_members tm ON t.id = tm.tablo_id AND tm.user_id = auth.uid()
WHERE t.owner_id = auth.uid() 
   OR tm.user_id = auth.uid() 
   OR t.is_public = true
ORDER BY t.updated_at DESC;
*/

-- 2. Get tablo with all its boards and lists
/*
SELECT 
    t.name as tablo_name,
    t.description as tablo_description,
    b.name as board_name,
    b.type as board_type,
    l.name as list_name,
    l.position as list_position
FROM tablos t
LEFT JOIN tablo_boards b ON t.id = b.tablo_id
LEFT JOIN tablo_lists l ON b.id = l.board_id
WHERE t.id = 'your-tablo-id'
ORDER BY b.position, l.position;
*/

-- 3. Get cards with assignees for a specific board
/*
SELECT 
    c.title,
    c.description,
    c.priority,
    c.due_date,
    l.name as list_name,
    c.assignees,
    c.labels
FROM tablo_cards c
JOIN tablo_lists l ON c.list_id = l.id
JOIN tablo_boards b ON l.board_id = b.id
WHERE b.id = 'your-board-id'
ORDER BY l.position, c.position;
*/

-- 4. Get recent activity for a tablo
/*
SELECT 
    ta.action,
    ta.entity_type,
    ta.details,
    ta.created_at,
    p.full_name as user_name
FROM tablo_activities ta
JOIN profiles p ON ta.user_id = p.id
WHERE ta.tablo_id = 'your-tablo-id'
ORDER BY ta.created_at DESC
LIMIT 20;
*/

-- 5. Get chat messages for a channel with user info
/*
SELECT 
    tcm.content,
    tcm.message_type,
    tcm.created_at,
    p.full_name as sender_name,
    p.avatar_url
FROM tablo_chat_messages tcm
JOIN profiles p ON tcm.user_id = p.id
WHERE tcm.channel_id = 'your-channel-id'
ORDER BY tcm.created_at ASC;
*/

-- 6. Get overdue cards across all user's tablos
/*
SELECT 
    c.title,
    c.due_date,
    c.priority,
    t.name as tablo_name,
    b.name as board_name,
    l.name as list_name
FROM tablo_cards c
JOIN tablo_lists l ON c.list_id = l.id
JOIN tablo_boards b ON l.board_id = b.id
JOIN tablos t ON b.tablo_id = t.id
LEFT JOIN tablo_members tm ON t.id = tm.tablo_id
WHERE (t.owner_id = auth.uid() OR tm.user_id = auth.uid())
  AND c.due_date < NOW()
  AND c.due_date IS NOT NULL
ORDER BY c.due_date ASC;
*/

-- 7. Get member statistics for a tablo
/*
SELECT 
    COUNT(*) as total_members,
    COUNT(CASE WHEN tm.role = 'owner' THEN 1 END) as owners,
    COUNT(CASE WHEN tm.role = 'admin' THEN 1 END) as admins,
    COUNT(CASE WHEN tm.role = 'member' THEN 1 END) as members,
    COUNT(CASE WHEN tm.role = 'viewer' THEN 1 END) as viewers
FROM tablo_members tm
WHERE tm.tablo_id = 'your-tablo-id';
*/

-- 8. Search cards by content
/*
SELECT 
    c.title,
    c.description,
    t.name as tablo_name,
    b.name as board_name,
    l.name as list_name,
    ts_rank(to_tsvector('french', c.title || ' ' || COALESCE(c.description, '')), 
            plainto_tsquery('french', 'search-term')) as rank
FROM tablo_cards c
JOIN tablo_lists l ON c.list_id = l.id
JOIN tablo_boards b ON l.board_id = b.id
JOIN tablos t ON b.tablo_id = t.id
LEFT JOIN tablo_members tm ON t.id = tm.tablo_id
WHERE (t.owner_id = auth.uid() OR tm.user_id = auth.uid())
  AND to_tsvector('french', c.title || ' ' || COALESCE(c.description, '')) 
      @@ plainto_tsquery('french', 'search-term')
ORDER BY rank DESC;
*/

-- =====================================================
-- VIEWS FOR COMMON QUERIES
-- =====================================================

-- View for user's tablos with member info
CREATE VIEW user_tablos AS
SELECT DISTINCT 
    t.*,
    COALESCE(tm.role, 'owner') as user_role,
    COALESCE(tm.permissions, '{"read": true, "write": true, "admin": true}'::jsonb) as user_permissions,
    (SELECT COUNT(*) FROM tablo_members WHERE tablo_id = t.id) as member_count
FROM tablos t
LEFT JOIN tablo_members tm ON t.id = tm.tablo_id AND tm.user_id = auth.uid()
WHERE t.owner_id = auth.uid() 
   OR tm.user_id = auth.uid() 
   OR t.is_public = true;

-- View for tablo structure (boards, lists, cards count)
CREATE VIEW tablo_structure AS
SELECT 
    t.id as tablo_id,
    t.name as tablo_name,
    COUNT(DISTINCT b.id) as boards_count,
    COUNT(DISTINCT l.id) as lists_count,
    COUNT(DISTINCT c.id) as cards_count
FROM tablos t
LEFT JOIN tablo_boards b ON t.id = b.tablo_id
LEFT JOIN tablo_lists l ON b.id = l.board_id
LEFT JOIN tablo_cards c ON l.id = c.list_id
GROUP BY t.id, t.name;

-- View for recent activities across all user tablos
CREATE VIEW user_recent_activities AS
SELECT 
    ta.*,
    t.name as tablo_name,
    p.full_name as user_name
FROM tablo_activities ta
JOIN tablos t ON ta.tablo_id = t.id
JOIN profiles p ON ta.user_id = p.id
LEFT JOIN tablo_members tm ON t.id = tm.tablo_id AND tm.user_id = auth.uid()
WHERE t.owner_id = auth.uid() OR tm.user_id = auth.uid()
ORDER BY ta.created_at DESC;