-- ============================================================================
-- Phase 5: Client Portal - Group-Based Approach (SIMPLIFIED)
-- ============================================================================
-- Creates "Clients" group and client-specific modules
-- Clients login as regular users - no magic tokens needed!
-- Date: 2025-10-16

BEGIN;

-- ============================================================================
-- 1. CREATE "CLIENTS" GROUP FOR EACH ORG
-- ============================================================================

INSERT INTO groups (name, description, org_id)
SELECT 
    'Clients',
    'Client Portal Users - View quotations, invoices, and make payments',
    org_id
FROM orgs
WHERE NOT EXISTS (
    SELECT 1 FROM groups WHERE name = 'Clients' AND groups.org_id = orgs.org_id
);

-- ============================================================================
-- 2. CREATE CLIENT-FACING MODULES (Declarative - Phase 4 Pattern)
-- ============================================================================

-- Module 1: My Quotations
INSERT INTO modules (name, icon, description, config, org_id)
SELECT 
    'My Quotations',
    'fa-file-invoice',
    'View and manage your quotations',
    jsonb_build_object(
        'type', 'list-detail',
        'table', 'quotations',
        'primaryKey', 'quotation_id',
        'dataFilter', 'client_id IN (SELECT client_id FROM clients WHERE user_id = {current_user_id})',
        'list', jsonb_build_object(
            'title', 'My Quotations',
            'columns', jsonb_build_array(
                jsonb_build_object('field', 'quotation_number', 'label', 'Number', 'sortable', true),
                jsonb_build_object('field', 'date', 'label', 'Date', 'type', 'date', 'sortable', true),
                jsonb_build_object('field', 'expires_at', 'label', 'Valid Until', 'type', 'date', 'sortable', true),
                jsonb_build_object('field', 'total_amount', 'label', 'Amount', 'type', 'currency', 'sortable', true),
                jsonb_build_object('field', 'status', 'label', 'Status', 'type', 'badge', 'sortable', true)
            ),
            'defaultSort', jsonb_build_object('field', 'date', 'direction', 'desc'),
            'rowActions', jsonb_build_array(
                jsonb_build_object(
                    'label', 'View',
                    'icon', 'fa-eye',
                    'action', 'openModal',
                    'modalId', 'quotation-detail'
                ),
                jsonb_build_object(
                    'label', 'Accept',
                    'icon', 'fa-check',
                    'action', 'quotations:accept',
                    'condition', 'status == "sent"',
                    'confirm', 'Accept this quotation?',
                    'class', 'btn-success btn-sm'
                ),
                jsonb_build_object(
                    'label', 'Reject',
                    'icon', 'fa-times',
                    'action', 'quotations:reject',
                    'condition', 'status == "sent"',
                    'class', 'btn-error btn-sm'
                ),
                jsonb_build_object(
                    'label', 'Download PDF',
                    'icon', 'fa-file-pdf',
                    'action', 'quotations:pdf',
                    'class', 'btn-ghost btn-sm'
                )
            )
        ),
        'detail', jsonb_build_object(
            'modalId', 'quotation-detail',
            'title', 'Quotation Details',
            'size', 'large',
            'readonly', true,
            'sections', jsonb_build_array(
                jsonb_build_object(
                    'title', 'Quotation Information',
                    'fields', jsonb_build_array(
                        jsonb_build_object('name', 'quotation_number', 'label', 'Quotation #', 'type', 'text', 'readonly', true),
                        jsonb_build_object('name', 'date', 'label', 'Date', 'type', 'date', 'readonly', true),
                        jsonb_build_object('name', 'expires_at', 'label', 'Valid Until', 'type', 'date', 'readonly', true),
                        jsonb_build_object('name', 'status', 'label', 'Status', 'type', 'text', 'readonly', true),
                        jsonb_build_object('name', 'note', 'label', 'Notes', 'type', 'textarea', 'readonly', true)
                    )
                ),
                jsonb_build_object(
                    'title', 'Amount',
                    'fields', jsonb_build_array(
                        jsonb_build_object('name', 'total_amount', 'label', 'Total Amount', 'type', 'currency', 'readonly', true)
                    )
                )
            )
        )
    ),
    org_id
FROM orgs
WHERE NOT EXISTS (
    SELECT 1 FROM modules WHERE name = 'My Quotations' AND modules.org_id = orgs.org_id
);

-- Module 2: My Invoices
INSERT INTO modules (name, icon, description, config, org_id)
SELECT 
    'My Invoices',
    'fa-file-invoice-dollar',
    'View and pay your invoices',
    jsonb_build_object(
        'type', 'list-detail',
        'table', 'invoices',
        'primaryKey', 'invoice_id',
        'dataFilter', 'client_id IN (SELECT client_id FROM clients WHERE user_id = {current_user_id})',
        'list', jsonb_build_object(
            'title', 'My Invoices',
            'columns', jsonb_build_array(
                jsonb_build_object('field', 'invoice_number', 'label', 'Number', 'sortable', true),
                jsonb_build_object('field', 'date', 'label', 'Date', 'type', 'date', 'sortable', true),
                jsonb_build_object('field', 'due_date', 'label', 'Due Date', 'type', 'date', 'sortable', true),
                jsonb_build_object('field', 'amount', 'label', 'Amount', 'type', 'currency', 'sortable', true),
                jsonb_build_object('field', 'amount_paid', 'label', 'Paid', 'type', 'currency', 'sortable', true),
                jsonb_build_object('field', 'status', 'label', 'Status', 'type', 'badge', 'sortable', true)
            ),
            'defaultSort', jsonb_build_object('field', 'date', 'direction', 'desc'),
            'rowActions', jsonb_build_array(
                jsonb_build_object(
                    'label', 'View',
                    'icon', 'fa-eye',
                    'action', 'openModal',
                    'modalId', 'invoice-detail'
                ),
                jsonb_build_object(
                    'label', 'Pay Now',
                    'icon', 'fa-credit-card',
                    'action', 'payments:initiate',
                    'condition', 'amount_paid < amount',
                    'class', 'btn-primary btn-sm'
                ),
                jsonb_build_object(
                    'label', 'Download PDF',
                    'icon', 'fa-file-pdf',
                    'action', 'invoices:pdf',
                    'class', 'btn-ghost btn-sm'
                )
            )
        ),
        'detail', jsonb_build_object(
            'modalId', 'invoice-detail',
            'title', 'Invoice Details',
            'size', 'large',
            'readonly', true,
            'sections', jsonb_build_array(
                jsonb_build_object(
                    'title', 'Invoice Information',
                    'fields', jsonb_build_array(
                        jsonb_build_object('name', 'invoice_number', 'label', 'Invoice #', 'type', 'text', 'readonly', true),
                        jsonb_build_object('name', 'date', 'label', 'Date', 'type', 'date', 'readonly', true),
                        jsonb_build_object('name', 'due_date', 'label', 'Due Date', 'type', 'date', 'readonly', true),
                        jsonb_build_object('name', 'status', 'label', 'Status', 'type', 'text', 'readonly', true)
                    )
                ),
                jsonb_build_object(
                    'title', 'Payment Details',
                    'fields', jsonb_build_array(
                        jsonb_build_object('name', 'amount', 'label', 'Total Amount', 'type', 'currency', 'readonly', true),
                        jsonb_build_object('name', 'amount_paid', 'label', 'Amount Paid', 'type', 'currency', 'readonly', true)
                    )
                )
            )
        )
    ),
    org_id
FROM orgs
WHERE NOT EXISTS (
    SELECT 1 FROM modules WHERE name = 'My Invoices' AND modules.org_id = orgs.org_id
);

-- Module 3: My Account
INSERT INTO modules (name, icon, description, config, org_id)
SELECT 
    'My Account',
    'fa-user-circle',
    'View and update your account information',
    jsonb_build_object(
        'type', 'form',
        'table', 'clients',
        'primaryKey', 'client_id',
        'dataFilter', 'user_id = {current_user_id}',
        'sections', jsonb_build_array(
            jsonb_build_object(
                'title', 'Contact Information',
                'fields', jsonb_build_array(
                    jsonb_build_object('name', 'name', 'label', 'Name', 'type', 'text', 'required', true),
                    jsonb_build_object('name', 'email', 'label', 'Email', 'type', 'email', 'required', true),
                    jsonb_build_object('name', 'phone', 'label', 'Phone', 'type', 'tel'),
                    jsonb_build_object('name', 'address', 'label', 'Address', 'type', 'textarea', 'rows', 3),
                    jsonb_build_object('name', 'city', 'label', 'City', 'type', 'text'),
                    jsonb_build_object('name', 'country', 'label', 'Country', 'type', 'text')
                )
            )
        ),
        'actions', jsonb_build_object(
            'edit', jsonb_build_array(
                jsonb_build_object('label', 'Save', 'action', 'save', 'class', 'btn-primary'),
                jsonb_build_object('label', 'Cancel', 'action', 'cancel')
            )
        )
    ),
    org_id
FROM orgs
WHERE NOT EXISTS (
    SELECT 1 FROM modules WHERE name = 'My Account' AND modules.org_id = orgs.org_id
);

-- ============================================================================
-- 3. ASSIGN MODULES TO CLIENTS GROUP
-- ============================================================================

INSERT INTO group_module_permissions (group_id, module_id, action)
SELECT g.group_id, m.module_id, 'allow'
FROM groups g
CROSS JOIN modules m
WHERE g.name = 'Clients'
  AND m.name IN ('My Quotations', 'My Invoices', 'My Account')
  AND g.org_id = m.org_id
  AND NOT EXISTS (
    SELECT 1 FROM group_module_permissions gmp
    WHERE gmp.group_id = g.group_id AND gmp.module_id = m.module_id
  );

-- ============================================================================
-- 4. AUTO-ASSIGN EXISTING CLIENTS TO CLIENTS GROUP
-- ============================================================================

-- For clients that already have user accounts, assign them to Clients group
INSERT INTO user_group_memberships (user_id, group_id, role, joined_at)
SELECT c.user_id, g.group_id, 'member', NOW()
FROM clients c
JOIN groups g ON g.org_id = c.org_id AND g.name = 'Clients'
WHERE c.user_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM user_group_memberships ugm
    WHERE ugm.user_id = c.user_id AND ugm.group_id = g.group_id
  );

COMMIT;

-- ============================================================================
-- VERIFICATION QUERIES
-- ============================================================================

-- Show created groups
SELECT 
    g.name as group_name,
    g.description,
    o.name as org_name,
    COUNT(ugm.user_id) as member_count
FROM groups g
JOIN orgs o ON g.org_id = o.org_id
LEFT JOIN user_group_memberships ugm ON ugm.group_id = g.group_id
WHERE g.name = 'Clients'
GROUP BY g.group_id, g.name, g.description, o.name
ORDER BY o.name;

-- Show created modules
SELECT 
    m.name as module_name,
    m.icon,
    m.description,
    o.name as org_name
FROM modules m
JOIN orgs o ON m.org_id = o.org_id
WHERE m.name IN ('My Quotations', 'My Invoices', 'My Account')
ORDER BY o.name, m.name;

-- Show group permissions
SELECT 
    g.name as group_name,
    m.name as module_name,
    gmp.action,
    o.name as org_name
FROM group_module_permissions gmp
JOIN groups g ON gmp.group_id = g.group_id
JOIN modules m ON gmp.module_id = m.module_id
JOIN orgs o ON g.org_id = o.org_id
WHERE g.name = 'Clients'
ORDER BY o.name, m.name;

-- Show client user assignments
SELECT 
    u.full_name,
    ue.email,
    c.name as client_name,
    g.name as group_name,
    o.name as org_name
FROM user_group_memberships ugm
JOIN users u ON ugm.user_id = u.user_id
JOIN user_emails ue ON ue.user_id = u.user_id AND ue.is_primary = true
JOIN groups g ON ugm.group_id = g.group_id
JOIN clients c ON c.user_id = u.user_id
JOIN orgs o ON g.org_id = o.org_id
WHERE g.name = 'Clients'
ORDER BY o.name, u.full_name;
