CREATE OR REPLACE PROCEDURE 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'
)
LANGUAGE plpgsql
AS $$
BEGIN
    -- Check customer exists
    IF NOT EXISTS (
        SELECT 1
        FROM customers
        WHERE customer_id = p_customer_id
    ) THEN
        RAISE EXCEPTION 'Customer % does not exist.', p_customer_id;
    END IF;

    -- Check employee exists and is active
    IF NOT EXISTS (
        SELECT 1
        FROM 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;

    -- Insert new support ticket
    INSERT INTO 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
    );

    RAISE NOTICE 'Support ticket created successfully.';
END;
$$;