DatabaseProgramming: 03 Support ticket creation.sql

DROP PROCEDURE IF EXISTS public.proc_create_support_ticket(bigint, bigint, bigint, bigint, text, text, text, text);
DROP PROCEDURE IF EXISTS public.proc_create_support_ticket(bigint, bigint, bigint, bigint, text, text, text, text, text);

CREATE PROCEDURE public.proc_create_support_ticket(
    p_customer_id bigint,
    p_account_id bigint,
    p_subscription_id bigint,
    p_employee_id bigint,
    p_ticket_type text,
    p_subject text,
    p_description text,
    p_priority text DEFAULT 'medium',
    p_channel text DEFAULT 'phone'
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_ticket_id bigint;
BEGIN
    IF NOT EXISTS (
        SELECT 1 FROM public.accounts
        WHERE account_id = p_account_id AND customer_id = p_customer_id
    ) THEN
        RAISE EXCEPTION 'Account % does not belong to customer %.', p_account_id, p_customer_id;
    END IF;
    IF p_subscription_id IS NOT NULL AND NOT EXISTS (
        SELECT 1 FROM public.subscriptions
        WHERE subscription_id = p_subscription_id AND account_id = p_account_id
    ) THEN
        RAISE EXCEPTION 'Subscription % does not belong to account %.', p_subscription_id, p_account_id;
    END IF;
    IF NOT EXISTS (
        SELECT 1 FROM public.employees
        WHERE employee_id = p_employee_id AND employment_status = 'active'
    ) THEN
        RAISE EXCEPTION 'Employee % does not exist or is not active.', p_employee_id;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM public.crm_ticket_types WHERE code = p_ticket_type AND is_active) THEN
        RAISE EXCEPTION 'Ticket type % is not valid.', p_ticket_type;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM public.ticket_priorities WHERE code = p_priority AND is_active) THEN
        RAISE EXCEPTION 'Priority % is not valid.', p_priority;
    END IF;
    IF NOT EXISTS (SELECT 1 FROM public.interaction_channels WHERE code = p_channel AND is_active) THEN
        RAISE EXCEPTION 'Interaction channel % is not valid.', p_channel;
    END IF;

    INSERT INTO public.crm_tickets (
        customer_id, account_id, subscription_id, assigned_employee_id,
        ticket_type, subject, description, priority, status, created_at
    ) VALUES (
        p_customer_id, p_account_id, p_subscription_id, p_employee_id,
        p_ticket_type, p_subject, p_description, p_priority, 'open', CURRENT_TIMESTAMP
    ) RETURNING ticket_id INTO v_ticket_id;

    INSERT INTO public.crm_interactions (
        ticket_id, employee_id, interaction_type, channel, interaction_time, notes
    ) VALUES (
        v_ticket_id, p_employee_id, 'customer_contact', p_channel, CURRENT_TIMESTAMP, 'Initial customer contact'
    );

    INSERT INTO public.employee_assignments (
        employee_id, ticket_id, assignment_type, start_time, status
    ) VALUES (
        p_employee_id, v_ticket_id, 'ticket_owner', CURRENT_TIMESTAMP, 'assigned'
    );

    RAISE NOTICE 'Support ticket % created and assigned to employee %.', v_ticket_id, p_employee_id;
END;
$$;