-- ============================================================================
-- Phase 5: Client Portal Implementation (Group-Based) - SIMPLIFIED
-- ============================================================================
-- Creates "Clients" group and client-specific modules using Phase 4 patterns
-- No custom JavaScript needed - all declarative!

BEGIN;

-- ============================================================================
-- 1. Create Clients Group
-- ============================================================================

INSERT INTO groups (name, description, org_id)
SELECT 'Clients', 'Client Portal Access', org_id
FROM orgs
WHERE NOT EXISTS (
    SELECT 1 FROM groups 
    WHERE name = 'Clients' AND groups.org_id = orgs.org_id
);

-- ============================================================================
-- 3. Create Clarification Tables (if they don't exist)
-- ============================================================================

CREATE TABLE IF NOT EXISTS clarification_requests (
    clarification_id SERIAL PRIMARY KEY,
    reference_table VARCHAR(50), -- 'quotations', 'invoices', 'general'
    reference_id INTEGER,
    client_user_id INTEGER REFERENCES users(user_id),
    org_id INTEGER REFERENCES orgs(org_id),
    subject VARCHAR(255) NOT NULL,
    message TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'open', -- 'open', 'responded', 'resolved', 'closed'
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS clarification_responses (
    response_id SERIAL PRIMARY KEY,
    clarification_id INTEGER REFERENCES clarification_requests(clarification_id) ON DELETE CASCADE,
    user_id INTEGER REFERENCES users(user_id),
    message TEXT NOT NULL,
    is_internal_note BOOLEAN DEFAULT FALSE, -- true = staff note, false = visible to client
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX IF NOT EXISTS idx_clarifications_client_status 
ON clarification_requests(client_user_id, status);

-- ============================================================================
-- 4. Create Client-Specific Modules
-- ============================================================================

-- 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',
        'dataSource', 'quotations',
        'filter', 'client_id = {current_user_client_id}',
        'columns', jsonb_build_array(
            jsonb_build_object('field', 'quotation_number', 'label', 'Number', 'sortable', true),
            jsonb_build_object('field', 'quotation_date', 'label', 'Date', 'type', 'date', 'sortable', true),
            jsonb_build_object('field', 'valid_until', 'label', 'Valid Until', 'type', 'date', 'sortable', true),
            jsonb_build_object('field', 'amount', 'label', 'Amount', 'type', 'currency', 'sortable', true),
            jsonb_build_object('field', 'status', 'label', 'Status', 'type', 'badge', 'sortable', true)
        ),
        'defaultSort', jsonb_build_object('field', 'quotation_date', 'direction', 'desc'),
        'rowActions', jsonb_build_array(
            jsonb_build_object(
                'label', 'View',
                'icon', 'fa-eye',
                'action', 'openDetail'
            ),
            jsonb_build_object(
                'label', 'Accept',
                'icon', 'fa-check',
                'action', 'quotations:accept',
                'condition', 'status == "sent"',
                'confirm', 'Accept this quotation?',
                'class', 'btn-success'
            ),
            jsonb_build_object(
                'label', 'Reject',
                'icon', 'fa-times',
                'action', 'quotations:reject',
                'condition', 'status == "sent"',
                'confirm', 'Are you sure you want to reject this quotation?',
                'class', 'btn-error'
            ),
            jsonb_build_object(
                'label', 'Download PDF',
                'icon', 'fa-file-pdf',
                'action', 'quotations:pdf',
                'class', 'btn-ghost'
            )
        )
    ),
    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',
        'dataSource', 'invoices',
        'filter', 'client_id = {current_user_client_id}',
        'columns', jsonb_build_array(
            jsonb_build_object('field', 'invoice_number', 'label', 'Number', 'sortable', true),
            jsonb_build_object('field', 'invoice_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', 'invoice_date', 'direction', 'desc'),
        'rowActions', jsonb_build_array(
            jsonb_build_object(
                'label', 'View',
                'icon', 'fa-eye',
                'action', 'openDetail'
            ),
            jsonb_build_object(
                'label', 'Download PDF',
                'icon', 'fa-file-pdf',
                'action', 'invoice:pdf',
                'class', 'btn-ghost'
            ),
            jsonb_build_object(
                'label', 'Pay Now',
                'icon', 'fa-credit-card',
                'action', 'invoice:pay',
                'condition', 'status == "sent" || status == "overdue"',
                'class', 'btn-primary'
            )
        )
    ),
    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 Clarifications
INSERT INTO modules (name, icon, description, config, org_id)
SELECT 
    'My Clarifications',
    'fa-comments',
    'Request clarifications and view responses',
    jsonb_build_object(
        'type', 'list',
        'dataSource', 'clarification_requests',
        'filter', 'client_user_id = {current_user_id}',
        'columns', jsonb_build_array(
            jsonb_build_object('field', 'created_at', 'label', 'Date', 'type', 'datetime', 'sortable', true),
            jsonb_build_object('field', 'reference_table', 'label', 'Regarding', 'sortable', true),
            jsonb_build_object('field', 'subject', 'label', 'Subject', 'sortable', true),
            jsonb_build_object('field', 'status', 'label', 'Status', 'type', 'badge', 'sortable', true)
        ),
        'defaultSort', jsonb_build_object('field', 'created_at', 'direction', 'desc'),
        'rowActions', jsonb_build_array(
            jsonb_build_object(
                'label', 'View',
                'icon', 'fa-eye',
                'action', 'openDetail'
            )
        ),
        'globalActions', jsonb_build_array(
            jsonb_build_object(
                'label', 'New Clarification',
                'icon', 'fa-plus',
                'action', 'clarification:create',
                'class', 'btn-primary'
            )
        )
    ),
    org_id
FROM orgs
WHERE NOT EXISTS (
    SELECT 1 FROM modules 
    WHERE name = 'My Clarifications' AND modules.org_id = orgs.org_id
);

-- ============================================================================
-- 5. 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 Clarifications')
  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
  );

-- ============================================================================
-- 6. Auto-assign Existing Clients to Clients Group
-- ============================================================================

-- Link clients table to users and assign 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;
