-- Migration: Generic Messages System
-- Date: October 16, 2025
-- Description: Create generic message threads system that can be hooked into any module

BEGIN;

-- Create message_threads table
CREATE TABLE IF NOT EXISTS message_threads (
    thread_id SERIAL PRIMARY KEY,
    reference_table VARCHAR(100) NOT NULL,
    reference_id VARCHAR(100) NOT NULL,
    subject VARCHAR(500) NOT NULL,
    context VARCHAR(50) NOT NULL DEFAULT 'general',
    status VARCHAR(20) NOT NULL DEFAULT 'open',
    created_by UUID REFERENCES users(user_id),
    created_at TIMESTAMP DEFAULT NOW(),
    resolved_at TIMESTAMP,
    resolved_by UUID REFERENCES users(user_id),
    org_id UUID REFERENCES orgs(org_id)
);

-- Create indexes
CREATE INDEX IF NOT EXISTS idx_thread_reference ON message_threads (reference_table, reference_id);
CREATE INDEX IF NOT EXISTS idx_thread_context ON message_threads (context);
CREATE INDEX IF NOT EXISTS idx_thread_status ON message_threads (status);
CREATE INDEX IF NOT EXISTS idx_thread_org ON message_threads (org_id);

COMMENT ON TABLE message_threads IS 'Generic message threads that can be attached to any entity';
COMMENT ON COLUMN message_threads.reference_table IS 'Table name (invoices, quotations, rfqs, vendors, etc.)';
COMMENT ON COLUMN message_threads.reference_id IS 'ID of the referenced record';
COMMENT ON COLUMN message_threads.context IS 'Context: client_portal, procurement, rfq, support, hr, projects, etc.';
COMMENT ON COLUMN message_threads.status IS 'Status: open, responded, resolved, closed';

-- Create messages table
CREATE TABLE IF NOT EXISTS messages (
    message_id SERIAL PRIMARY KEY,
    thread_id INT NOT NULL REFERENCES message_threads(thread_id) ON DELETE CASCADE,
    message TEXT NOT NULL,
    sender_id UUID NOT NULL REFERENCES users(user_id),
    sender_type VARCHAR(20) NOT NULL DEFAULT 'staff',
    sent_at TIMESTAMP DEFAULT NOW(),
    read_at TIMESTAMP,
    read_by UUID REFERENCES users(user_id),
    org_id UUID REFERENCES orgs(org_id),
    
    -- Attachments (optional JSON array)
    attachments JSONB
);

-- Create indexes for messages
CREATE INDEX IF NOT EXISTS idx_message_thread ON messages (thread_id);
CREATE INDEX IF NOT EXISTS idx_message_sender ON messages (sender_id);
CREATE INDEX IF NOT EXISTS idx_message_sent ON messages (sent_at);
CREATE INDEX IF NOT EXISTS idx_message_org ON messages (org_id);

COMMENT ON TABLE messages IS 'Individual messages within threads';
COMMENT ON COLUMN messages.sender_type IS 'Sender type: client, vendor, staff, support_staff, procurement_staff, etc.';
COMMENT ON COLUMN messages.attachments IS 'JSON array of attachment URLs/IDs';

-- Migrate existing clarification_requests to message_threads
-- (if clarification tables exist)
DO $$
BEGIN
    IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'clarification_requests') THEN
        -- Migrate clarification requests to threads
        INSERT INTO message_threads (
            reference_table,
            reference_id,
            subject,
            context,
            status,
            created_by,
            created_at,
            resolved_at,
            org_id
        )
        SELECT 
            CASE 
                WHEN invoice_id IS NOT NULL THEN 'invoices'
                WHEN quotation_id IS NOT NULL THEN 'quotations'
                ELSE 'general'
            END as reference_table,
            COALESCE(invoice_id::text, quotation_id::text, 'unknown') as reference_id,
            subject,
            'client_portal' as context,
            status,
            client_user_id as created_by,
            created_at,
            resolved_at,
            org_id
        FROM clarification_requests
        WHERE NOT EXISTS (
            SELECT 1 FROM message_threads mt 
            WHERE mt.reference_table = CASE 
                    WHEN clarification_requests.invoice_id IS NOT NULL THEN 'invoices'
                    WHEN clarification_requests.quotation_id IS NOT NULL THEN 'quotations'
                    ELSE 'general'
                END
            AND mt.reference_id = COALESCE(
                clarification_requests.invoice_id::text,
                clarification_requests.quotation_id::text,
                'unknown'
            )
            AND mt.created_by = clarification_requests.client_user_id
        );

        -- Migrate initial messages
        INSERT INTO messages (
            thread_id,
            message,
            sender_id,
            sender_type,
            sent_at,
            org_id
        )
        SELECT 
            mt.thread_id,
            cr.message,
            cr.client_user_id,
            'client' as sender_type,
            cr.created_at,
            cr.org_id
        FROM clarification_requests cr
        JOIN message_threads mt ON (
            mt.reference_table = CASE 
                WHEN cr.invoice_id IS NOT NULL THEN 'invoices'
                WHEN cr.quotation_id IS NOT NULL THEN 'quotations'
                ELSE 'general'
            END
            AND mt.reference_id = COALESCE(cr.invoice_id::text, cr.quotation_id::text, 'unknown')
            AND mt.created_by = cr.client_user_id
        )
        WHERE NOT EXISTS (
            SELECT 1 FROM messages m 
            WHERE m.thread_id = mt.thread_id 
            AND m.sender_id = cr.client_user_id
        );

        -- Migrate responses
        IF EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'clarification_responses') THEN
            INSERT INTO messages (
                thread_id,
                message,
                sender_id,
                sender_type,
                sent_at,
                org_id
            )
            SELECT 
                mt.thread_id,
                cr_resp.response_text,
                cr_resp.responded_by,
                'staff' as sender_type,
                cr_resp.responded_at,
                mt.org_id
            FROM clarification_responses cr_resp
            JOIN clarification_requests cr ON cr.request_id = cr_resp.request_id
            JOIN message_threads mt ON (
                mt.reference_table = CASE 
                    WHEN cr.invoice_id IS NOT NULL THEN 'invoices'
                    WHEN cr.quotation_id IS NOT NULL THEN 'quotations'
                    ELSE 'general'
                END
                AND mt.reference_id = COALESCE(cr.invoice_id::text, cr.quotation_id::text, 'unknown')
                AND mt.created_by = cr.client_user_id
            )
            WHERE NOT EXISTS (
                SELECT 1 FROM messages m 
                WHERE m.thread_id = mt.thread_id 
                AND m.sender_id = cr_resp.responded_by
                AND m.sent_at = cr_resp.responded_at
            );
        END IF;

        RAISE NOTICE 'Migrated clarification_requests to message_threads';
    END IF;
END $$;

-- Update modules table to replace "My Clarifications" with "My Messages"
UPDATE modules 
SET 
    name = 'My Messages',
    icon = 'fa-comments',
    description = 'View and manage your messages and inquiries',
    config = jsonb_set(
        config,
        '{table}',
        '"message_threads"'
    )
WHERE name = 'My Clarifications'
  AND EXISTS (SELECT 1 FROM information_schema.tables WHERE table_name = 'modules');

-- Create Messages module for staff (if doesn't exist)
INSERT INTO modules (name, icon, description, config, org_id)
SELECT 
    'Messages',
    'fa-comments',
    'Manage message threads across all contexts',
    jsonb_build_object(
        'type', 'list',
        'table', 'message_threads',
        'list', jsonb_build_object(
            'columns', jsonb_build_array(
                jsonb_build_object('field', 'subject', 'label', 'Subject'),
                jsonb_build_object('field', 'context', 'label', 'Context'),
                jsonb_build_object('field', 'status', 'label', 'Status'),
                jsonb_build_object('field', 'created_at', 'label', 'Created', 'type', 'datetime')
            ),
            'rowActions', jsonb_build_array(
                jsonb_build_object('label', 'View', 'action', 'messages:view'),
                jsonb_build_object('label', 'Reply', 'action', 'messages:reply'),
                jsonb_build_object('label', 'Close', 'action', 'messages:close', 'condition', 'status == "open"')
            )
        )
    ),
    org_id
FROM orgs
WHERE NOT EXISTS (
    SELECT 1 FROM modules 
    WHERE name = 'Messages' 
    AND modules.org_id = orgs.org_id
);

-- Grant permissions to appropriate groups
-- Clients can access My Messages
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 = 'My Messages'
  AND NOT EXISTS (
      SELECT 1 FROM group_module_permissions gmp 
      WHERE gmp.group_id = g.group_id AND gmp.module_id = m.module_id
  );

-- Staff groups can access Messages module
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 IN ('Admins', 'Managers', 'Support', 'Procurement')
  AND m.name = 'Messages'
  AND NOT EXISTS (
      SELECT 1 FROM group_module_permissions gmp 
      WHERE gmp.group_id = g.group_id AND gmp.module_id = m.module_id
  );

-- Create view for easy querying of threads with message counts
CREATE OR REPLACE VIEW message_threads_summary AS
SELECT 
    mt.thread_id,
    mt.reference_table,
    mt.reference_id,
    mt.subject,
    mt.context,
    mt.status,
    mt.created_by,
    mt.created_at,
    mt.resolved_at,
    mt.resolved_by,
    mt.org_id,
    u_creator.full_name as created_by_name,
    u_resolver.full_name as resolved_by_name,
    COUNT(m.message_id) as message_count,
    MAX(m.sent_at) as last_message_at
FROM message_threads mt
LEFT JOIN users u_creator ON u_creator.user_id = mt.created_by
LEFT JOIN users u_resolver ON u_resolver.user_id = mt.resolved_by
LEFT JOIN messages m ON m.thread_id = mt.thread_id
GROUP BY 
    mt.thread_id, mt.reference_table, mt.reference_id, mt.subject, 
    mt.context, mt.status, mt.created_by, mt.created_at, mt.resolved_at, 
    mt.resolved_by, mt.org_id, u_creator.full_name, u_resolver.full_name;

COMMENT ON VIEW message_threads_summary IS 'Summary view of threads with message counts and user names';

-- Success message
DO $$
BEGIN
    RAISE NOTICE '✅ Generic messages system created successfully';
    RAISE NOTICE '✅ Message threads and messages tables created';
    RAISE NOTICE '✅ Migrated existing clarification_requests (if present)';
    RAISE NOTICE '✅ Updated modules and permissions';
    RAISE NOTICE '';
    RAISE NOTICE 'Usage contexts:';
    RAISE NOTICE '  - client_portal: Client inquiries on invoices/quotations';
    RAISE NOTICE '  - procurement: Vendor communications';
    RAISE NOTICE '  - rfq: RFQ discussions';
    RAISE NOTICE '  - support: Help desk tickets';
    RAISE NOTICE '  - hr: Employee grievances';
    RAISE NOTICE '  - projects: Task comments';
END $$;

COMMIT;
