BEGIN;

TRUNCATE TABLE
    review,
  appointmentpackageusage,
  payment,
  appointmentservice,
  appointment,
  userpackagepurchase,
  packageservice,
  package,
  service,
  category,
  userrole,
  loyaltycard,
  "User",
  role,
  status,
  availability
RESTART IDENTITY CASCADE;

INSERT INTO role (role_id, name) VALUES
                                     (1, 'ADMIN'),
                                     (2, 'CUSTOMER'),
                                     (3, 'STYLIST'),
                                     (4, 'MANAGER');

INSERT INTO "User" (user_id, full_name, email, phone, created_at, password_hash) VALUES
                                                                                     (1, 'admin', 'admin@venusbeauty.com', '1213124244', '2026-02-09 12:40:41.265433', '$2b$10$1.f1NlqdSPtv8QC5s7RJM.XNfVfYAhuNPF5jAxz2PCt7M5JTYqSGS'),
                                                                                     (2, 'Ana Petrova', 'ana@venusbeauty.com', '070100200', '2026-02-10 09:10:00', '$2b$12$ruD94UIabEn0k89s7E.p.uU6jpyZ0vZBYR7toQkL1nOMFERbiV2d2'),
                                                                                     (3, 'Marko Ivanov', 'marko@venusbeauty.com', '071300400', '2026-02-10 09:20:00', '$2b$12$ruD94UIabEn0k89s7E.p.uU6jpyZ0vZBYR7toQkL1nOMFERbiV2d2'),
                                                                                     (4, 'Ivana Stojanova', 'ivana@venusbeauty.com', '072500600', '2026-02-10 09:30:00', '$2b$12$ruD94UIabEn0k89s7E.p.uU6jpyZ0vZBYR7toQkL1nOMFERbiV2d2'),
                                                                                     (5, 'Emily Johnson', 'emily@venusbeauty.com', '073700800', '2026-02-10 09:40:00', '$2b$12$ruD94UIabEn0k89s7E.p.uU6jpyZ0vZBYR7toQkL1nOMFERbiV2d2');

INSERT INTO userrole (user_id, role_id)
SELECT 1, role_id
FROM role
WHERE name = 'ADMIN';

INSERT INTO userrole (user_id, role_id) VALUES
                                            (2, 2),
                                            (3, 2),
                                            (4, 2),
                                            (5, 3);

INSERT INTO category (category_id, name) VALUES
                                             (1, 'Hair'),
                                             (2, 'Nails'),
                                             (3, 'Skincare');

INSERT INTO service (service_id, name, price, duration_minutes, category_id) VALUES
                                                                                 (1, 'Haircut', 50, 45, 1),
                                                                                 (2, 'Blow Dry', 30, 30, 1),
                                                                                 (3, 'Manicure', 55, 45, 2),
                                                                                 (4, 'Gel Nails', 70, 60, 2),
                                                                                 (5, 'Express Facial', 90, 60, 3),
                                                                                 (6, 'Deep Facial', 120, 75, 3);

INSERT INTO package (package_id, name, max_usage, total_price) VALUES
                                                                   (1, 'Hair Essentials', 5, 180),
                                                                   (2, 'Nail Care Bundle', 4, 220),
                                                                   (3, 'Glow Skin Pack', 3, 300);


INSERT INTO packageservice (package_id, service_id, discounted_price) VALUES
                                                                          (1, 1, 40),
                                                                          (1, 2, 25),
                                                                          (2, 3, 45),
                                                                          (2, 4, 60),
                                                                          (3, 5, 70),
                                                                          (3, 6, 95);


INSERT INTO status (status_id, name) VALUES
                                         (1, 'SCHEDULED'),
                                         (2, 'COMPLETED'),
                                         (3, 'CANCELLED'),
                                         (4, 'NO_SHOW');


INSERT INTO appointment (
    appointment_id, appointment_time, end_time, notes, type, total_price, user_id, status_id, points_awarded
) VALUES
      (1, '2026-03-15 10:00:00', '2026-03-15 11:15:00', 'Hair combo', 'pre-booked', 80, 2, 1, false),
      (2, '2026-02-10 13:00:00', '2026-02-10 14:15:00', 'Facial session', 'pre-booked', 120, 3, 2, true),
      (3, '2026-02-11 09:00:00', '2026-02-11 09:45:00', 'Package-covered haircut', 'pre-booked', 0, 2, 2, false),
      (4, '2026-03-20 15:00:00', '2026-03-20 15:45:00', NULL, 'walk-in', 55, 4, 1, false),
      (5, '2026-03-05 12:00:00', '2026-03-05 12:30:00', 'Cancelled in advance', 'pre-booked', 30, 2, 3, false);


INSERT INTO appointmentservice (appointment_id, service_id) VALUES
                                                                (1, 1),
                                                                (1, 2),
                                                                (2, 6),
                                                                (3, 1),
                                                                (4, 3),
                                                                (5, 2);


INSERT INTO loyaltycard (card_id, points, user_id) VALUES
                                                       (1, 0, 1),
                                                       (2, 120, 2),
                                                       (3, 80, 3),
                                                       (4, 20, 4),
                                                       (5, 0, 5);


INSERT INTO userpackagepurchase (
    purchase_id, user_id, package_id, purchased_at, total_uses, remaining_uses, status, expires_at
) VALUES
      (1, 2, 1, '2026-02-09 13:00:00', 5, 4, 'ACTIVE', '2026-12-31'),
      (2, 3, 3, '2026-02-10 15:00:00', 3, 3, 'ACTIVE', '2026-11-30'),
      (3, 4, 2, '2026-02-12 11:00:00', 4, 0, 'EXPIRED', '2026-06-30');


INSERT INTO appointmentpackageusage (
    appointment_id, purchase_id, service_id, used_units, finalized_at
) VALUES
    (3, 1, 1, 1, '2026-02-11 10:00:00');


INSERT INTO availability (
    availability_id, date, start_time, end_time, is_closed
) VALUES
      (1, '2026-03-15', '09:00', '17:00', false),
      (2, '2026-03-16', '09:00', '17:00', false),
      (3, '2026-03-20', '10:00', '18:00', false),
      (4, '2026-03-21', '09:00', '17:00', true),
      (5, '2026-03-22', '09:00', '13:00', false);


INSERT INTO payment (
    payment_id, amount, method, "timestamp", status, appointment_id, points_used, package_purchase_id
) VALUES
      (1, 80,  'CARD',    '2026-03-14 18:00:00', 'PENDING',  1, 0, NULL),
      (2, 120, 'CARD',    '2026-02-10 14:30:00', 'PAID',     2, 0, NULL),
      (3, 180, 'CARD',    '2026-02-09 13:10:00', 'PAID',  NULL, 0, 1),
      (4, 0,   'PACKAGE', '2026-02-11 09:50:00', 'PAID',     3, 0, 1),
      (5, 55,  'CASH',    '2026-03-20 14:50:00', 'FAILED',   4, 0, NULL),
      (6, 30,  'CARD',    '2026-03-05 10:00:00', 'REFUNDED', 5, 0, NULL),
      (7, 300, 'CARD',    '2026-02-10 15:05:00', 'PENDING', NULL, 0, 2);

INSERT INTO review (
    review_id, rating, comment, created_at, payment_id
) VALUES
      (1, 5, 'Excellent facial service.', '2026-02-10 16:00:00', 2),
      (2, 4, 'Package appointment was smooth.', '2026-02-11 12:00:00', 4);

SELECT setval(pg_get_serial_sequence('"User"', 'user_id'),
              COALESCE((SELECT MAX(user_id) FROM "User"), 1), true);

SELECT setval(pg_get_serial_sequence('role', 'role_id'),
              COALESCE((SELECT MAX(role_id) FROM role), 1), true);

SELECT setval(pg_get_serial_sequence('category', 'category_id'),
              COALESCE((SELECT MAX(category_id) FROM category), 1), true);

SELECT setval(pg_get_serial_sequence('service', 'service_id'),
              COALESCE((SELECT MAX(service_id) FROM service), 1), true);

SELECT setval(pg_get_serial_sequence('package', 'package_id'),
              COALESCE((SELECT MAX(package_id) FROM package), 1), true);

SELECT setval(pg_get_serial_sequence('status', 'status_id'),
              COALESCE((SELECT MAX(status_id) FROM status), 1), true);

SELECT setval(pg_get_serial_sequence('appointment', 'appointment_id'),
              COALESCE((SELECT MAX(appointment_id) FROM appointment), 1), true);

SELECT setval(pg_get_serial_sequence('loyaltycard', 'card_id'),
              COALESCE((SELECT MAX(card_id) FROM loyaltycard), 1), true);

SELECT setval(pg_get_serial_sequence('payment', 'payment_id'),
              COALESCE((SELECT MAX(payment_id) FROM payment), 1), true);

SELECT setval(pg_get_serial_sequence('review', 'review_id'),
              COALESCE((SELECT MAX(review_id) FROM review), 1), true);

SELECT setval(pg_get_serial_sequence('userpackagepurchase', 'purchase_id'),
              COALESCE((SELECT MAX(purchase_id) FROM userpackagepurchase), 1), true);

SELECT setval(pg_get_serial_sequence('availability', 'availability_id'),
              COALESCE((SELECT MAX(availability_id) FROM availability), 1), true);

COMMIT;
