-- Migration: Phase 5 - Client Portal (Group-Based Approach)
-- Date: 2025-10-15
-- Description: Create "Clients" group and client-facing modules using existing infrastructure
-- Version: phase5_client_portal

BEGIN;

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

-- Create Clients group for each existing organization
-- This group will have limited module access (only client-facing modules)
INSERT INTO groups (name, description, org_id)
SELECT 
    'Clients',
    'Client Portal Users - Limited access to quotations, invoices, and clarifications',
    org_id
FROM orgs
WHERE NOT EXISTS (
    SELECT 1 FROM groups WHERE name = 'Clients' AND groups.org_id = orgs.org_id
);

COMMENT ON TABLE groups IS 'User groups for permission management. Clients group provides portal access.';

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

-- For clients that already have user accounts, assign them to the Clients group
INSERT INTO user_group_memberships (user_id, group_id, role)
SELECT 
    c.user_id,
    g.group_id,
    'member'
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 
    WHERE user_id = c.user_id AND group_id = g.group_id
  );

-- ============================================================================
-- 3. CREATE CLIENT-FACING MODULES
-- ============================================================================

-- Module 1: My Quotations
-- Clients can view, accept, reject quotations and request clarifications
INSERT INTO modules (name, config, icon, description) VALUES (
    'My Quotations',
    '{
        "table": "quotations",
        "primaryKey": "quotation_id",
        "clientFilter": "client_id",
        "list": {
            "title": "My Quotations",
            "description": "View and manage your quotations",
            "dataSource": "/api/quotations/my-quotations",
            "autoRefresh": 60000,
            "emptyMessage": "You have no quotations yet",
            "columns": [
                {
                    "field": "quotation_number",
                    "label": "Quote #",
                    "sortable": true,
                    "searchable": true
                },
                {
                    "field": "date",
                    "label": "Date",
                    "render": "date",
                    "sortable": true
                },
                {
                    "field": "total_amount",
                    "label": "Amount",
                    "render": "currency",
                    "sortable": true
                },
                {
                    "field": "status",
                    "label": "Status",
                    "render": "badge",
                    "sortable": true
                },
                {
                    "field": "expires_at",
                    "label": "Expires",
                    "render": "date",
                    "sortable": true
                }
            ],
            "filters": [
                {
                    "field": "status",
                    "label": "Status",
                    "type": "select",
                    "options": [
                        {"value": "all", "label": "All Quotations"},
                        {"value": "sent", "label": "Pending"},
                        {"value": "accepted", "label": "Accepted"},
                        {"value": "rejected", "label": "Rejected"},
                        {"value": "expired", "label": "Expired"}
                    ]
                }
            ],
            "rowActions": [
                {
                    "label": "View Details",
                    "icon": "fa-eye",
                    "action": "openModal",
                    "modalId": "my-quotation-detail",
                    "class": "btn-sm"
                },
                {
                    "label": "Accept",
                    "icon": "fa-check",
                    "action": "quotations:accept",
                    "condition": "status == \"sent\"",
                    "confirm": "Are you sure you want to accept this quotation?",
                    "class": "btn-success btn-sm"
                },
                {
                    "label": "Reject",
                    "icon": "fa-times",
                    "action": "quotations:reject",
                    "condition": "status == \"sent\"",
                    "class": "btn-error btn-sm"
                },
                {
                    "label": "Request Changes",
                    "icon": "fa-comment",
                    "action": "clarification:request",
                    "condition": "status == \"sent\"",
                    "class": "btn-warning btn-sm"
                },
                {
                    "label": "Download PDF",
                    "icon": "fa-file-pdf",
                    "action": "quotations:pdf",
                    "class": "btn-ghost btn-sm"
                }
            ]
        },
        "detail": {
            "modalId": "my-quotation-detail",
            "title": "Quotation Details",
            "size": "xl",
            "readonly": true,
            "tabs": [
                {
                    "id": "details",
                    "label": "Details",
                    "icon": "fa-info-circle",
                    "sections": [
                        {
                            "title": "Quotation Information",
                            "fields": [
                                {"name": "quotation_number", "label": "Quote Number", "type": "text", "readonly": true},
                                {"name": "date", "label": "Date", "type": "date", "readonly": true},
                                {"name": "expires_at", "label": "Valid Until", "type": "date", "readonly": true},
                                {"name": "status", "label": "Status", "type": "text", "readonly": true}
                            ]
                        },
                        {
                            "title": "Your Information",
                            "fields": [
                                {"name": "customer_name", "label": "Customer Name", "type": "text", "readonly": true},
                                {"name": "customer_email", "label": "Email", "type": "email", "readonly": true},
                                {"name": "customer_phone", "label": "Phone", "type": "text", "readonly": true},
                                {"name": "customer_address", "label": "Address", "type": "textarea", "readonly": true}
                            ]
                        }
                    ]
                },
                {
                    "id": "items",
                    "label": "Line Items",
                    "icon": "fa-list",
                    "component": "LineItemsView",
                    "componentConfig": {
                        "table": "quotation_items",
                        "foreignKey": "quotation_id",
                        "readonly": true,
                        "showTotals": true
                    }
                },
                {
                    "id": "notes",
                    "label": "Notes & Terms",
                    "icon": "fa-file-alt",
                    "sections": [
                        {
                            "title": "Additional Information",
                            "fields": [
                                {"name": "note", "label": "Notes", "type": "textarea", "readonly": true, "rows": 4},
                                {"name": "terms", "label": "Terms & Conditions", "type": "textarea", "readonly": true, "rows": 6}
                            ]
                        }
                    ]
                }
            ],
            "actions": {
                "view": [
                    {
                        "label": "Accept Quotation",
                        "icon": "fa-check",
                        "action": "quotations:accept",
                        "condition": "status == \"sent\"",
                        "confirm": "Are you sure you want to accept this quotation? This will create an invoice.",
                        "class": "btn-success"
                    },
                    {
                        "label": "Reject Quotation",
                        "icon": "fa-times",
                        "action": "quotations:reject",
                        "condition": "status == \"sent\"",
                        "class": "btn-error"
                    },
                    {
                        "label": "Request Changes",
                        "icon": "fa-comment",
                        "action": "clarification:request",
                        "condition": "status == \"sent\"",
                        "class": "btn-warning"
                    },
                    {
                        "label": "Download PDF",
                        "icon": "fa-file-pdf",
                        "action": "quotations:pdf",
                        "class": "btn-ghost"
                    },
                    {
                        "label": "Close",
                        "action": "cancel"
                    }
                ]
            }
        }
    }'::jsonb,
    'fa-file-invoice',
    'Client portal - View and manage quotations'
)
ON CONFLICT DO NOTHING;

-- Module 2: My Invoices
-- Clients can view invoices, make payments, and download receipts
INSERT INTO modules (name, config, icon, description) VALUES (
    'My Invoices',
    '{
        "table": "invoices",
        "primaryKey": "invoice_id",
        "clientFilter": "client_id",
        "list": {
            "title": "My Invoices",
            "description": "View and pay your invoices",
            "dataSource": "/api/invoices/my-invoices",
            "autoRefresh": 60000,
            "emptyMessage": "You have no invoices yet",
            "columns": [
                {
                    "field": "invoice_number",
                    "label": "Invoice #",
                    "sortable": true,
                    "searchable": true
                },
                {
                    "field": "invoice_date",
                    "label": "Date",
                    "render": "date",
                    "sortable": true
                },
                {
                    "field": "due_date",
                    "label": "Due Date",
                    "render": "date",
                    "sortable": true
                },
                {
                    "field": "amount",
                    "label": "Amount",
                    "render": "currency",
                    "sortable": true
                },
                {
                    "field": "amount_paid",
                    "label": "Paid",
                    "render": "currency"
                },
                {
                    "field": "balance_due",
                    "label": "Balance",
                    "render": "currency",
                    "computed": "amount - amount_paid"
                },
                {
                    "field": "status",
                    "label": "Status",
                    "render": "badge",
                    "sortable": true
                }
            ],
            "filters": [
                {
                    "field": "status",
                    "label": "Status",
                    "type": "select",
                    "options": [
                        {"value": "all", "label": "All Invoices"},
                        {"value": "draft", "label": "Draft"},
                        {"value": "pending", "label": "Unpaid"},
                        {"value": "paid", "label": "Paid"},
                        {"value": "overdue", "label": "Overdue"}
                    ]
                }
            ],
            "rowActions": [
                {
                    "label": "View Details",
                    "icon": "fa-eye",
                    "action": "openModal",
                    "modalId": "my-invoice-detail",
                    "class": "btn-sm"
                },
                {
                    "label": "Pay Now",
                    "icon": "fa-credit-card",
                    "action": "payments:initiate",
                    "condition": "status != \"paid\" && status != \"draft\"",
                    "class": "btn-primary btn-sm"
                },
                {
                    "label": "Download Invoice",
                    "icon": "fa-file-pdf",
                    "action": "invoices:pdf",
                    "class": "btn-ghost btn-sm"
                },
                {
                    "label": "Download Receipt",
                    "icon": "fa-receipt",
                    "action": "invoices:taxcore-receipt",
                    "condition": "taxcore_submission_id != null",
                    "class": "btn-ghost btn-sm"
                }
            ]
        },
        "detail": {
            "modalId": "my-invoice-detail",
            "title": "Invoice Details",
            "size": "xl",
            "readonly": true,
            "tabs": [
                {
                    "id": "details",
                    "label": "Details",
                    "icon": "fa-info-circle",
                    "sections": [
                        {
                            "title": "Invoice Information",
                            "fields": [
                                {"name": "invoice_number", "label": "Invoice Number", "type": "text", "readonly": true},
                                {"name": "invoice_date", "label": "Invoice Date", "type": "date", "readonly": true},
                                {"name": "due_date", "label": "Due Date", "type": "date", "readonly": true},
                                {"name": "status", "label": "Status", "type": "text", "readonly": true}
                            ]
                        },
                        {
                            "title": "Payment Information",
                            "fields": [
                                {"name": "amount", "label": "Total Amount", "type": "currency", "readonly": true},
                                {"name": "amount_paid", "label": "Amount Paid", "type": "currency", "readonly": true},
                                {"name": "balance_due", "label": "Balance Due", "type": "currency", "readonly": true, "computed": "amount - amount_paid"}
                            ]
                        }
                    ]
                },
                {
                    "id": "items",
                    "label": "Line Items",
                    "icon": "fa-list",
                    "component": "LineItemsView",
                    "componentConfig": {
                        "table": "invoice_items",
                        "foreignKey": "invoice_id",
                        "readonly": true,
                        "showTotals": true
                    }
                },
                {
                    "id": "payments",
                    "label": "Payment History",
                    "icon": "fa-money-bill",
                    "component": "PaymentHistoryView",
                    "componentConfig": {
                        "table": "payments",
                        "foreignKey": "invoice_id",
                        "readonly": true
                    }
                }
            ],
            "actions": {
                "view": [
                    {
                        "label": "Pay Now",
                        "icon": "fa-credit-card",
                        "action": "payments:initiate",
                        "condition": "status != \"paid\" && status != \"draft\"",
                        "class": "btn-primary"
                    },
                    {
                        "label": "Download Invoice",
                        "icon": "fa-file-pdf",
                        "action": "invoices:pdf",
                        "class": "btn-ghost"
                    },
                    {
                        "label": "Download Receipt",
                        "icon": "fa-receipt",
                        "action": "invoices:taxcore-receipt",
                        "condition": "taxcore_submission_id != null",
                        "class": "btn-ghost"
                    },
                    {
                        "label": "Close",
                        "action": "cancel"
                    }
                ]
            }
        }
    }'::jsonb,
    'fa-file-invoice-dollar',
    'Client portal - View and pay invoices'
)
ON CONFLICT DO NOTHING;

-- Module 3: My Clarifications
-- Clients can create and view clarification requests
INSERT INTO modules (name, config, icon, description) VALUES (
    'My Clarifications',
    '{
        "table": "clarification_requests",
        "primaryKey": "clarification_id",
        "clientFilter": "client_user_id",
        "list": {
            "title": "My Clarification Requests",
            "description": "Request clarifications or changes to quotations and invoices",
            "dataSource": "/api/clarifications/my-clarifications",
            "autoRefresh": 30000,
            "emptyMessage": "You have no clarification requests",
            "columns": [
                {
                    "field": "subject",
                    "label": "Subject",
                    "sortable": true,
                    "searchable": true
                },
                {
                    "field": "reference_table",
                    "label": "Regarding",
                    "render": "capitalize",
                    "sortable": true
                },
                {
                    "field": "reference_number",
                    "label": "Number",
                    "computed": true
                },
                {
                    "field": "status",
                    "label": "Status",
                    "render": "badge",
                    "sortable": true
                },
                {
                    "field": "priority",
                    "label": "Priority",
                    "render": "badge"
                },
                {
                    "field": "created_at",
                    "label": "Created",
                    "render": "datetime",
                    "sortable": true
                }
            ],
            "filters": [
                {
                    "field": "status",
                    "label": "Status",
                    "type": "select",
                    "options": [
                        {"value": "all", "label": "All Requests"},
                        {"value": "open", "label": "Open"},
                        {"value": "responded", "label": "Responded"},
                        {"value": "resolved", "label": "Resolved"},
                        {"value": "closed", "label": "Closed"}
                    ]
                }
            ],
            "actions": [
                {
                    "label": "New Request",
                    "icon": "fa-plus",
                    "action": "openModal",
                    "modalId": "clarification-create",
                    "class": "btn-primary"
                }
            ],
            "rowActions": [
                {
                    "label": "View Conversation",
                    "icon": "fa-comments",
                    "action": "openModal",
                    "modalId": "clarification-detail",
                    "class": "btn-sm"
                }
            ]
        },
        "detail": {
            "modalId": "clarification-detail",
            "title": "Clarification Request",
            "size": "large",
            "readonly": false,
            "sections": [
                {
                    "title": "Request Details",
                    "fields": [
                        {"name": "subject", "label": "Subject", "type": "text", "readonly": true},
                        {"name": "reference_table", "label": "Regarding", "type": "text", "readonly": true},
                        {"name": "status", "label": "Status", "type": "text", "readonly": true},
                        {"name": "created_at", "label": "Created", "type": "datetime", "readonly": true}
                    ]
                },
                {
                    "title": "Conversation",
                    "component": "ConversationThread",
                    "componentConfig": {
                        "table": "clarification_responses",
                        "foreignKey": "clarification_id",
                        "allowReply": true,
                        "realtimeUpdates": true
                    }
                }
            ],
            "actions": {
                "view": [
                    {
                        "label": "Close",
                        "action": "cancel"
                    }
                ]
            }
        },
        "create": {
            "modalId": "clarification-create",
            "title": "New Clarification Request",
            "size": "medium",
            "sections": [
                {
                    "title": "Request Information",
                    "fields": [
                        {
                            "name": "reference_table",
                            "label": "Regarding",
                            "type": "select",
                            "required": true,
                            "options": [
                                {"value": "quotations", "label": "Quotation"},
                                {"value": "invoices", "label": "Invoice"}
                            ]
                        },
                        {
                            "name": "reference_id",
                            "label": "Select Document",
                            "type": "select",
                            "required": true,
                            "dataSource": "/api/clarifications/my-documents",
                            "dependsOn": "reference_table"
                        },
                        {
                            "name": "subject",
                            "label": "Subject",
                            "type": "text",
                            "required": true,
                            "placeholder": "Brief description of your request"
                        },
                        {
                            "name": "message",
                            "label": "Message",
                            "type": "textarea",
                            "required": true,
                            "rows": 6,
                            "placeholder": "Please describe your clarification request in detail..."
                        },
                        {
                            "name": "priority",
                            "label": "Priority",
                            "type": "select",
                            "default": "normal",
                            "options": [
                                {"value": "normal", "label": "Normal"},
                                {"value": "high", "label": "High"}
                            ]
                        }
                    ]
                }
            ],
            "actions": {
                "create": [
                    {
                        "label": "Submit Request",
                        "action": "save",
                        "class": "btn-primary"
                    },
                    {
                        "label": "Cancel",
                        "action": "cancel"
                    }
                ]
            }
        }
    }'::jsonb,
    'fa-question-circle',
    'Client portal - Request clarifications'
)
ON CONFLICT DO NOTHING;

-- Module 4: My Account
-- Client profile and preferences
INSERT INTO modules (name, config, icon, description) VALUES (
    'My Account',
    '{
        "table": "users",
        "primaryKey": "user_id",
        "clientFilter": "user_id",
        "detail": {
            "title": "My Account",
            "size": "medium",
            "sections": [
                {
                    "title": "Personal Information",
                    "fields": [
                        {"name": "full_name", "label": "Full Name", "type": "text", "readonly": false},
                        {"name": "email", "label": "Email", "type": "email", "readonly": true},
                        {"name": "phone", "label": "Phone", "type": "text", "readonly": false}
                    ]
                },
                {
                    "title": "Business Information",
                    "fields": [
                        {"name": "company_name", "label": "Company Name", "type": "text", "readonly": false},
                        {"name": "address", "label": "Address", "type": "textarea", "readonly": false, "rows": 3}
                    ]
                },
                {
                    "title": "Security",
                    "fields": [
                        {"name": "change_password", "label": "Change Password", "type": "password_change"}
                    ]
                }
            ],
            "actions": {
                "edit": [
                    {
                        "label": "Save Changes",
                        "action": "save",
                        "class": "btn-primary"
                    },
                    {
                        "label": "Cancel",
                        "action": "cancel"
                    }
                ]
            }
        }
    }'::jsonb,
    'fa-user-circle',
    'Client portal - Manage account settings'
)
ON CONFLICT DO NOTHING;

-- ============================================================================
-- 4. ASSIGN CLIENT MODULES TO CLIENTS GROUP
-- ============================================================================

-- Give Clients group access to ONLY client-facing modules
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 Clarifications', 'My Account')
  AND NOT EXISTS (
    SELECT 1 FROM group_module_permissions gmp
    WHERE gmp.group_id = g.group_id AND gmp.module_id = m.module_id
  );

-- ============================================================================
-- 5. TABLE-LEVEL PERMISSIONS FOR CLIENTS GROUP
-- ============================================================================

-- Clients can read quotations (filtered by client_id)
INSERT INTO group_table_permissions (group_id, table_name, access_mode)
SELECT g.group_id, 'quotations', 'readonly'
FROM groups g
WHERE g.name = 'Clients'
  AND NOT EXISTS (
    SELECT 1 FROM group_table_permissions 
    WHERE group_id = g.group_id AND table_name = 'quotations'
  );

-- Clients can read invoices (filtered by client_id)
INSERT INTO group_table_permissions (group_id, table_name, access_mode)
SELECT g.group_id, 'invoices', 'readonly'
FROM groups g
WHERE g.name = 'Clients'
  AND NOT EXISTS (
    SELECT 1 FROM group_table_permissions 
    WHERE group_id = g.group_id AND table_name = 'invoices'
  );

-- Clients can read/write clarification requests (their own)
INSERT INTO group_table_permissions (group_id, table_name, access_mode)
SELECT g.group_id, 'clarification_requests', 'readwrite'
FROM groups g
WHERE g.name = 'Clients'
  AND NOT EXISTS (
    SELECT 1 FROM group_table_permissions 
    WHERE group_id = g.group_id AND table_name = 'clarification_requests'
  );

-- Clients can read clarification responses
INSERT INTO group_table_permissions (group_id, table_name, access_mode)
SELECT g.group_id, 'clarification_responses', 'readonly'
FROM groups g
WHERE g.name = 'Clients'
  AND NOT EXISTS (
    SELECT 1 FROM group_table_permissions 
    WHERE group_id = g.group_id AND table_name = 'clarification_responses'
  );

-- Clients can read payments (their own invoices)
INSERT INTO group_table_permissions (group_id, table_name, access_mode)
SELECT g.group_id, 'payments', 'readonly'
FROM groups g
WHERE g.name = 'Clients'
  AND NOT EXISTS (
    SELECT 1 FROM group_table_permissions 
    WHERE group_id = g.group_id AND table_name = 'payments'
  );

-- Clients can update their own user profile
INSERT INTO group_table_permissions (group_id, table_name, access_mode)
SELECT g.group_id, 'users', 'readwrite'
FROM groups g
WHERE g.name = 'Clients'
  AND NOT EXISTS (
    SELECT 1 FROM group_table_permissions 
    WHERE group_id = g.group_id AND table_name = 'users'
  );

-- ============================================================================
-- 6. HELPER FUNCTION: AUTO-ASSIGN NEW CLIENTS TO CLIENTS GROUP
-- ============================================================================

-- Function to automatically assign new clients to the Clients group
CREATE OR REPLACE FUNCTION auto_assign_client_to_group()
RETURNS TRIGGER AS $$
BEGIN
    -- When a client gets a user_id assigned, add them to Clients group
    IF NEW.user_id IS NOT NULL AND (OLD.user_id IS NULL OR OLD.user_id IS DISTINCT FROM NEW.user_id) THEN
        INSERT INTO user_group_memberships (user_id, group_id, role)
        SELECT 
            NEW.user_id,
            g.group_id,
            'member'
        FROM groups g
        WHERE g.org_id = NEW.org_id 
          AND g.name = 'Clients'
          AND NOT EXISTS (
            SELECT 1 FROM user_group_memberships 
            WHERE user_id = NEW.user_id AND group_id = g.group_id
          );
    END IF;
    
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
DROP TRIGGER IF EXISTS trg_auto_assign_client_group ON clients;
CREATE TRIGGER trg_auto_assign_client_group
    AFTER INSERT OR UPDATE OF user_id ON clients
    FOR EACH ROW
    EXECUTE FUNCTION auto_assign_client_to_group();

COMMENT ON FUNCTION auto_assign_client_to_group() IS 'Automatically assigns clients to Clients group when user_id is set';

-- ============================================================================
-- 7. ADD INDEXES FOR PERFORMANCE
-- ============================================================================

-- Index for client portal token lookups
CREATE INDEX IF NOT EXISTS idx_portal_tokens_active 
ON client_portal_tokens(token_hash, expires_at) 
WHERE used_at IS NULL;

-- Index for clarification requests by client
CREATE INDEX IF NOT EXISTS idx_clarifications_client_status 
ON clarification_requests(client_user_id, status);

-- Index for quotations by client and status
CREATE INDEX IF NOT EXISTS idx_quotations_client_status 
ON quotations(client_id, status) 
WHERE isDeleted = FALSE;

-- Index for invoices by client and status
CREATE INDEX IF NOT EXISTS idx_invoices_client_status 
ON invoices(client_id, status) 
WHERE isDeleted = FALSE;

-- ============================================================================
-- 8. ADD HELPFUL VIEWS FOR CLIENT DATA
-- ============================================================================

-- View: Client's unpaid invoices with balance
CREATE OR REPLACE VIEW client_unpaid_invoices AS
SELECT 
    i.invoice_id,
    i.invoice_number,
    i.client_id,
    i.invoice_date,
    i.due_date,
    i.amount,
    COALESCE(i.amount_paid, 0) as amount_paid,
    (i.amount - COALESCE(i.amount_paid, 0)) as balance_due,
    i.status,
    CASE 
        WHEN i.due_date < CURRENT_DATE AND i.status != 'paid' THEN true
        ELSE false
    END as is_overdue,
    (CURRENT_DATE - i.due_date) as days_overdue
FROM invoices i
WHERE i.status != 'paid' 
  AND i.status != 'draft'
  AND i.isDeleted = FALSE;

COMMENT ON VIEW client_unpaid_invoices IS 'Client unpaid invoices with balance due and overdue status';

-- View: Client's pending quotations
CREATE OR REPLACE VIEW client_pending_quotations AS
SELECT 
    q.quotation_id,
    q.quotation_number,
    q.client_id,
    q.date,
    q.expires_at,
    q.total_amount,
    q.status,
    CASE 
        WHEN q.expires_at < CURRENT_TIMESTAMP AND q.status = 'sent' THEN true
        ELSE false
    END as is_expired,
    (q.expires_at - CURRENT_TIMESTAMP) as time_until_expiry
FROM quotations q
WHERE q.status IN ('sent', 'draft')
  AND q.isDeleted = FALSE;

COMMENT ON VIEW client_pending_quotations IS 'Client pending quotations with expiry information';

-- ============================================================================
-- 9. VERIFICATION QUERIES
-- ============================================================================

-- To verify installation, run these queries:

-- Check Clients groups created
-- SELECT g.name, g.org_id, o.name as org_name
-- FROM groups g
-- JOIN orgs o ON o.org_id = g.org_id
-- WHERE g.name = 'Clients';

-- Check client modules created
-- SELECT name, icon, description 
-- FROM modules 
-- WHERE name LIKE 'My %';

-- Check module permissions
-- SELECT g.name as group_name, m.name as module_name, gmp.action
-- FROM group_module_permissions gmp
-- JOIN groups g ON g.group_id = gmp.group_id
-- JOIN modules m ON m.module_id = gmp.module_id
-- WHERE g.name = 'Clients';

-- Check client group memberships
-- SELECT u.full_name, g.name as group_name, c.name as client_name
-- FROM user_group_memberships ugm
-- JOIN users u ON u.user_id = ugm.user_id
-- JOIN groups g ON g.group_id = ugm.group_id
-- JOIN clients c ON c.user_id = u.user_id
-- WHERE g.name = 'Clients';

COMMIT;

-- ============================================================================
-- ROLLBACK (IF NEEDED)
-- ============================================================================

-- To rollback this migration:
/*
BEGIN;

-- Remove triggers
DROP TRIGGER IF EXISTS trg_auto_assign_client_group ON clients;
DROP FUNCTION IF EXISTS auto_assign_client_to_group();

-- Remove views
DROP VIEW IF EXISTS client_unpaid_invoices;
DROP VIEW IF EXISTS client_pending_quotations;

-- Remove permissions
DELETE FROM group_module_permissions 
WHERE group_id IN (SELECT group_id FROM groups WHERE name = 'Clients');

DELETE FROM group_table_permissions 
WHERE group_id IN (SELECT group_id FROM groups WHERE name = 'Clients');

-- Remove client modules
DELETE FROM modules WHERE name IN ('My Quotations', 'My Invoices', 'My Clarifications', 'My Account');

-- Remove group memberships
DELETE FROM user_group_memberships 
WHERE group_id IN (SELECT group_id FROM groups WHERE name = 'Clients');

-- Remove Clients groups
DELETE FROM groups WHERE name = 'Clients';

COMMIT;
*/
