create table service
(
    service_name VARCHAR(30) NOT NULL,
    service_description TEXT NOT NULL,
    duration_minutes INT NOT NULL,
    price INT NOT NULL
);

create table product
(
    product_name VARCHAR(30) NOT NULL,
    product_description TEXT NOT NULL,
    quantity INT NOT NULL,
    cost INT NOT NULL
);

create table customer
(
    customer_name VARCHAR(30) NOT NULL,
    phone TEXT NOT NULL,
    email TEXT NOT NULL,
    address TEXT NOT NULL
);

create table appointment
(
    customer VARCHAR(30) NOT NULL,
    service TEXT NOT NULL,
    date DATE NOT NULL,
    time TIME NOT NULL,
    status ENUM('Active', 'Done') NOT NULL
);

create table payment
(
    paying_customer VARCHAR(30) NOT NULL,
    appointment INT NOT NULL,
    amount INT NOT NULL,
    payment_method ENUM('Cash', 'Card') NOT NULL,
    payment_date DATE NOT NULL
);

create table employee
(
    employee_name VARCHAR(30) NOT NULL,
    employee_phone TEXT NOT NULL,
    employee_email TEXT NOT NULL,
    position TEXT NOT NULL,
    salary INT NOT NULL
);

INSERT INTO service (service_name, service_description, duration_minutes, price) VALUES
('Haircut', 'Hair care', 40, 400),
('Haircut, 2', 'Full haircut', 120, 800),
('Haircut, 3', 'New hairstyle', 300, 1500),
('Manicure', 'Doing the nails', 200, 1000),
('Pedicure', 'Doing the other nails', 200, 1000);

INSERT INTO product (product_name, product_description, quantity, cost) VALUES
('Hair gel', 'SYOSS', 13, 300),
('Shampoo', 'Hair shampoo', 37, 300),
('Cond.', 'Conditioner', 24, 300),
('Soap', 'Liquid soap, good for hands', 12, 150),
('Cream', 'Hand cream', 45, 100);

INSERT INTO customer (customer_name, phone, email, address) VALUES
('Emily Brown', '070-456-0976', 'emilybrown@gmail.com', '152 Old St., Oldcity'),
('Jane Davis', '040-674-3759', 'janedavis@gmail.com', '637 New St., Newcity'),
('Bob Johnson', '060-234-2349', 'bobjohnson@gmail.com', '847 Pine St., Classiccity'),
('Alice Smith', '234-547-2358', 'alicesmith@gmail.com', '346 Oak St., Countrycity'),
('Jane Doe', '312-436-1234', 'janedoe@gmail.com', '346 New St., Oldcity');

INSERT INTO appointment (customer, service, date, time, status) VALUES
('Emily Brown', 'Haircut', '2024-02-09','13:00:00', 'Active'),
('Jane Davis', 'Haircut, 2', '2024-02-10', '08:05:00', 'Done'),
('Bob Johnson', 'Manicure', '2024-02-10','12:30:00', 'Active'),
('Alice Smith', 'Pedicure', '2024-02-10', '16:00:00', 'Active'),
('Jane Doe', 'Haircut, 3', '2024-02-10','17:00:00', 'Active');

INSERT INTO payment (paying_customer, appointment, amount, payment_method, payment_date) VALUES
('Emily Brown', 1, 400, 'Card', '2024-02-09'),
('Jane Davis', 2, 500, 'Cash', '2024-02-10'),
('Bob Johnson', 3, 600, 'Card', '2024-02-10'),
('Alice Smith', 4, 700, 'Cash', '2024-02-10'),
('Jane Doe', 5, 800, 'Card', '2024-02-10');

INSERT INTO employee (employee_name, employee_phone, employee_email, position, salary) VALUES
('Sarah Jones', '555-111-2222', 'sarah.jones@example.com', 'Hair Stylist', 3000),
('Mike Smith', '555-333-4444', 'mike.smith@example.com', 'Nail Technician', 2500),
('Jessica Brown', '555-555-6666', 'jessica.brown@example.com', 'Esthetician', 2800),
('David Johnson', '555-777-8888', 'david.johnson@example.com', 'Manager', 3500),
('Emma Davis', '555-999-0000', 'emma.davis@example.com', 'Receptionist', 2000);

SELECT * FROM appointment ORDER BY date, time;
SELECT paying_customer, SUM(amount) AS total_amount FROM payment GROUP BY paying_customer HAVING total_amount > 600;
SELECT * FROM appointment WHERE status = 'Active' LIMIT 3;