Логички и физички дизајн - Креирање база податоци (со SQL DDL): Polnenje2.sql

truncate table users cascade;
truncate table business cascade;
truncate table client cascade;
truncate table feedback cascade;
truncate table competition cascade;
truncate table guest_pay cascade;
truncate table "subscription" cascade;
truncate table reservation cascade;

INSERT INTO users (name, email, "password", "location")
VALUES
  ('Park', 'alice@email.com', 'pass123', 'New York'),
  ('TCC', 'bob@email.com', 'pass456', 'London'),
  ('Marriott', 'charlie@email.com', 'pass789', 'Paris'),
  ('David Miller', 'david@email.com', 'passabc', 'Tokyo'),
  ('Eva Davis', 'eva@email.com', 'passdef', 'Sydney'),
  ('Frank White', 'frank@email.com', 'passghi', 'Berlin');

INSERT INTO business (user_id, address)
SELECT id, 
       CASE 
          WHEN email = 'alice@email.com' THEN '123 Main Street'
          WHEN email = 'bob@email.com' THEN '456 Oak Avenue'
          WHEN email = 'charlie@email.com' THEN '789 Elm Street'
          ELSE null
       END AS address
FROM users
WHERE email IN ('alice@email.com', 'bob@email.com', 'charlie@email.com');

INSERT INTO client (user_id, date_of_birth)
SELECT id, 
       CASE 
          WHEN email = 'david@email.com' THEN TO_DATE ('1994-06-08', 'YYYY-MM-DD')
          WHEN email = 'eva@email.com' THEN TO_DATE ('2000-06-02', 'YYYY-MM-DD')
          WHEN email = 'frank@email.com' THEN TO_DATE ('1982-03-01', 'YYYY-MM-DD')
          ELSE null
       END AS date_of_birth
FROM users
WHERE email IN ('david@email.com', 'eva@email.com', 'frank@email.com');

 //
INSERT INTO feedback (client_id, business_id, "comment", rating)
VALUES
  (1, 1, 'Great service from Business', 5),
  (2, 1, 'Excellent experience with Busines', 4),
  (1, 3, 'Could be better for Business', 3),
  (3, 2, 'Fantastic support from Business', 5),
  (3, 1, 'Average service from Business', 2);
 
INSERT INTO competition (client_id, business_id, "name", eligibility)
VALUES
  (1, 1, 'Summer Contest', 1),
  (2, 1, 'Holiday Giveaway', 2),
  (3, 3, 'Spring Sweepstakes', 1),
  (1, 2, 'Winter Challenge', 3),
  (3, 3, 'Fall Festival', 2);

INSERT INTO "subscription" (client_id, "type", price)
VALUES
  (1, 'Small', 10),
  (2, 'Medium', 20),
  (3, 'Large', 30);
 
INSERT INTO guest_pay (client_id, "type", amount)
VALUES
  (1, 'Credit Card', 50),
  (2, 'PayPal', 30);
 
INSERT INTO reservation (client_id, business_id, "time", "date")
VALUES
  (1, 1, '14:00:00', '2023-05-20'),
  (2, 1, '18:30:00', '2023-06-10'),
  (3, 1, '12:15:00', '2023-07-05'),
  (1, 2, '20:00:00', '2023-08-15'),
  (3, 3, '16:45:00', '2023-09-01'),
  (2, 3, '19:30:00', '2023-10-12'); //
  
-- Create client
INSERT INTO users (name, email, password, location) values ('John Smith', 'john@email.com', 'passcode123', 'Skopje');
INSERT INTO client (user_id, date_of_birth) select id, '1994-06-08' from users where email='john@email.com';


-- Create business
INSERT INTO users (name, email, password, location) values ('Park', 'park@email.com', 'passcode567', 'Skopje');
INSERT INTO business (user_id, address) select id, '365 West Street' from users where email='park@email.com';

insert  into reservation  (client_id, business_id, "time", "date")
values (
  (select client.id from client join users on users.id = client.user_id where email = 'john@email.com'), 
  (select business.id from business join users on business.user_id = users.id where email = 'park@email.com'), 
  '14:00', 
  '2000-12-01'
  )
  -- Leave feedback
  insert  into feedback  (client_id, business_id, "comment", rating)
values (
  (select client.id from client join users on users.id = client.user_id where email = 'john@email.com'), 
  (select business.id from business join users on business.user_id = users.id where email = 'park@email.com'), 
  'Odlicno', 4
  
)