FunctionsProceduresTriggers: 01_databaseFunctions.sql

File 01_databaseFunctions.sql, 7.1 KB (added by 231184, 9 days ago)

databaseFunctions

Line 
1-- =====================================================
2-- FUNCTIONS FOR REZERVO PROJECT
3-- =====================================================
4
5
6-- =====================================================
7-- 1. Get appointments for one customer
8-- =====================================================
9
10CREATE OR REPLACE FUNCTION get_customer_appointments(
11 p_customer_id INT
12)
13 RETURNS TABLE (
14 appointment_id INT,
15 status TEXT,
16 created_at TIMESTAMP,
17 customer_id INT,
18 customer_name TEXT,
19 business_id INT,
20 business_name TEXT,
21 service_id INT,
22 service_name TEXT,
23 employee_id INT,
24 employee_name TEXT,
25 appointment_date DATE,
26 start_time TIME,
27 end_time TIME,
28 city TEXT
29 )
30 LANGUAGE plpgsql
31AS $$
32BEGIN
33 RETURN QUERY
34 SELECT
35 cav.appointment_id,
36 cav.status::TEXT,
37 cav.created_at,
38 cav.customer_id,
39 cav.customer_name::TEXT,
40 cav.business_id,
41 cav.business_name::TEXT,
42 cav.service_id,
43 cav.service_name::TEXT,
44 cav.employee_id,
45 cav.employee_name::TEXT,
46 cav.date,
47 cav.start_time,
48 cav.end_time,
49 cav.city::TEXT
50 FROM customer_appointments_view cav
51 WHERE cav.customer_id = p_customer_id;
52END;
53$$;
54
55
56-- Example:
57-- SELECT * FROM get_customer_appointments(100);
58
59
60
61-- =====================================================
62-- 2. Get available slots for business and date
63-- =====================================================
64
65CREATE OR REPLACE FUNCTION get_available_slots(
66 p_business_id INT,
67 p_date_from DATE,
68 p_date_to DATE
69)
70 RETURNS TABLE (
71 slot_id INT,
72 slot_date DATE,
73 start_time TIME,
74 end_time TIME,
75 business_id INT,
76 business_name TEXT,
77 employee_id INT,
78 employee_name TEXT,
79 service_id INT,
80 service_name TEXT,
81 price NUMERIC,
82 duration_minutes INT
83 )
84 LANGUAGE plpgsql
85AS $$
86BEGIN
87 RETURN QUERY
88 SELECT
89 av.slot_id,
90 av.date,
91 av.start_time,
92 av.end_time,
93 av.business_id,
94 av.business_name::TEXT,
95 av.employee_id,
96 av.employee_name::TEXT,
97 av.service_id,
98 av.service_name::TEXT,
99 av.price,
100 av.duration_minutes
101 FROM available_slots av
102 WHERE av.business_id = p_business_id
103 AND av.date >= p_date_from
104 AND av.date <= p_date_to;
105END;
106$$;
107--
108-- EXAMPLE
109SELECT *
110FROM get_available_slots(1, '2026-05-01', '2026-05-31');
111
112
113
114-- =====================================================
115-- 3. Get review summary for one business
116-- =====================================================
117
118CREATE OR REPLACE FUNCTION get_business_review_summary(
119 p_business_id INT
120)
121 RETURNS TABLE (
122 business_id INT,
123 business_name TEXT,
124 avg_rating NUMERIC,
125 total_reviews BIGINT
126 )
127 LANGUAGE plpgsql
128AS $$
129BEGIN
130 RETURN QUERY
131 SELECT
132 rs.business_id,
133 rs.business_name::TEXT,
134 rs.avg_rating,
135 rs.total_reviews
136 FROM review_summary rs
137 WHERE rs.business_id = p_business_id;
138END;
139$$;
140
141
142-- Example:
143-- SELECT * FROM get_business_review_summary(1);
144
145
146
147-- =====================================================
148-- 4. Get reviews by rating
149-- =====================================================
150
151-- CREATE OR REPLACE FUNCTION get_reviews_by_rating(
152-- p_rating INT
153-- )
154-- RETURNS TABLE (
155-- review_id INT,
156-- business_name TEXT,
157-- rating INT,
158-- comment TEXT,
159-- created_at TIMESTAMP
160-- )
161-- LANGUAGE plpgsql
162-- AS $$
163-- BEGIN
164-- RETURN QUERY
165-- SELECT
166-- rd.review_id,
167-- rd.business_name::TEXT,
168-- rd.rating,
169-- rd.comment::TEXT,
170-- rd.created_at
171-- FROM review_details rd
172-- WHERE rd.rating = p_rating;
173-- END;
174-- $$;
175
176
177-- Example:
178-- SELECT * FROM get_reviews_by_rating(5);
179
180
181
182-- =====================================================
183-- 5. Get customer profile
184-- =====================================================
185
186-- CREATE OR REPLACE FUNCTION get_customer_profile(
187-- p_customer_id INT
188-- )
189-- RETURNS TABLE (
190-- user_id INT,
191-- email TEXT,
192-- customer_id INT,
193-- first_name TEXT,
194-- last_name TEXT,
195-- phone TEXT,
196-- total_appointments BIGINT,
197-- businesses_visited BIGINT,
198-- avg_given_rating NUMERIC,
199-- total_reviews_written BIGINT,
200-- last_appointment_date TIMESTAMP
201-- )
202-- LANGUAGE plpgsql
203-- AS $$
204-- BEGIN
205-- RETURN QUERY
206-- SELECT
207-- cpv.user_id,
208-- cpv.email::TEXT,
209-- cpv.customer_id,
210-- cpv.first_name::TEXT,
211-- cpv.last_name::TEXT,
212-- cpv.phone::TEXT,
213-- cpv.total_appointments,
214-- cpv.businesses_visited,
215-- cpv.avg_given_rating,
216-- cpv.total_reviews_written,
217-- cpv.last_appointment_date
218-- FROM customer_profile_view cpv
219-- WHERE cpv.customer_id = p_customer_id;
220-- END;
221-- $$;
222
223
224-- Example:
225-- SELECT * FROM get_customer_profile(100);
226
227
228
229-- =====================================================
230-- 6. Get reschedule requests by status
231-- =====================================================
232
233-- CREATE OR REPLACE FUNCTION get_reschedule_requests_by_status(
234-- p_status TEXT
235-- )
236-- RETURNS TABLE (
237-- request_id INT,
238-- status TEXT,
239-- reason TEXT,
240-- old_date DATE,
241-- old_time TIME,
242-- new_date DATE,
243-- new_time TIME
244-- )
245-- LANGUAGE plpgsql
246-- AS $$
247-- BEGIN
248-- RETURN QUERY
249-- SELECT
250-- ro.request_id,
251-- ro.status::TEXT,
252-- ro.reason::TEXT,
253-- ro.old_date,
254-- ro.old_time,
255-- ro.new_date,
256-- ro.new_time
257-- FROM reschedule_overview ro
258-- WHERE ro.status::TEXT = p_status;
259-- END;
260-- $$;
261
262
263-- Example:
264-- SELECT * FROM get_reschedule_requests_by_status('pending');