CREATE TABLE Venues (
    venue_id INT AUTO_INCREMENT PRIMARY KEY,
    venue_name VARCHAR(255) NOT NULL,
    location VARCHAR(255) NOT NULL,
    capacity INT NOT NULL
);

CREATE TABLE Organizers (
    organizer_id INT AUTO_INCREMENT PRIMARY KEY,
    organizer_name VARCHAR(255) NOT NULL,
    contact_email VARCHAR(255) NOT NULL UNIQUE,
    contact_phone VARCHAR(15)
);

CREATE TABLE Events (
    event_id INT AUTO_INCREMENT PRIMARY KEY,
    event_name VARCHAR(255) NOT NULL,
    event_date DATE NOT NULL,
    venue_id INT NOT NULL,
    organizer_id INT NOT NULL,
    description TEXT,
    FOREIGN KEY (venue_id) REFERENCES Venues(venue_id),
    FOREIGN KEY (organizer_id) REFERENCES Organizers(organizer_id)
);

CREATE TABLE Participants (
    participant_id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) NOT NULL,
    email VARCHAR(255) NOT NULL UNIQUE,
    phone_number VARCHAR(15) NOT NULL
);

CREATE TABLE Registrations (
    registration_id INT AUTO_INCREMENT PRIMARY KEY,
    event_id INT NOT NULL,
    participant_id INT NOT NULL,
    registration_date DATE NOT NULL,
    status ENUM('Confirmed','Pending','Cancelled') DEFAULT 'Pending',
    FOREIGN KEY (event_id) REFERENCES Events(event_id),
    FOREIGN KEY (participant_id) REFERENCES Participants(participant_id)
);

INSERT INTO Venues (venue_id, venue_name, location, capacity) VALUES
    (1, 'Grand Hall', '123 Main Street, Cityville', 500),
    (2, 'Open Plaza', '456 Market Lane, Cityville', 300),
    (3, 'Tech Conference Center', '789 Innovation Blvd, TechTown', 1000),
    (4, 'Community Center', '321 Oak Avenue, Suburbia', 200),
    (5, 'Downtown Auditorium', '654 Center Street, Metropolis', 800);

INSERT INTO Organizers (organizer_id, organizer_name, contact_email, contact_phone) VALUES
    (1, 'Event Pros', 'info@eventpros.com', '555-1234'),
    (2, 'Celebration Experts', 'team@celebrationexperts.com', '555-5678'),
    (3, 'Tech Events Co.', 'contact@techevents.com', '555-8765'),
    (4, 'Community Fun', 'hello@communityfun.org', '555-4321'),
    (5, 'Citywide Planners', 'support@citywideplanners.com', '555-9999');

INSERT INTO Events (event_id,event_name, event_date, venue_id, organizer_id, description) VALUES
    (1, 'Tech Expo 2025', '2025-03-15', 3, 3, 'A large expo showcasing the latest technology.'),
    (2, 'Community Fair', '2025-04-10', 4, 4, 'A day of fun activities for all ages.'),
    (3, 'Music Fest', '2025-05-20', 2, 1, 'A festival featuring live music performances.'),
    (4, 'Startup Pitch Night', '2025-03-30', 3, 3, 'Pitch event for startups to showcase ideas.'),
    (5, 'City Marathon', '2025-06-05', 5, 5, 'Annual marathon event.'),
    (6, 'Art Exhibition', '2025-02-25', 1, 2, 'An exhibition featuring local artists.'),
    (7, 'Charity Gala', '2025-04-15', 1, 5, 'A formal gala for fundraising.'),
    (8, 'Book Fair', '2025-05-15', 4, 2, 'A fair featuring books and author signings.'),
    (9, 'Film Screening', '2025-03-20', 5, 1, 'Screening of an award-winning film.'),
    (10, 'Robotics Workshop', '2025-04-05', 3, 3, 'Hands-on robotics building workshop.');

INSERT INTO Participants (participant_id,first_name,last_name,email,phone_number) VALUES
    (1,'John','Doe','john.doe@example.com','555-1010'),
    (2,'Jane','Smith','jane.smith@example.com','555-2020'),
    (3,'Alice','Johnson','alice.johnson@example.com','555-3030'),
    (4,'Bob','Brown','bob.brown@example.com','555-4040'),
    (5,'Charlie','Davis','charlie.davis@example.com','555-5050'),
    (6,'Diana','Evans','diana.evans@example.com','555-6060'),
    (7,'Eve','Foster','eve.foster@example.com','555-7070'),
    (8,'Frank','Green','frank.green@example.com','555-8080'),
    (9,'Grace','Harris','grace.harris@example.com','555-9090'),
    (10,'Hank','Irwin','hank.irwin@example.com','555-1111'),
    (11,'Ivy','Jackson','ivy.jackson@example.com','555-2222'),
    (12,'Jack','Knight','jack.knight@example.com','555-3333'),
    (13,'Karen','Lewis','karen.lewis@example.com','555-4444'),
    (14,'Leo','Moore','leo.moore@example.com','555-5555'),
    (15,'Mona','Nelson','mona.nelson@example.com','555-6666'),
    (16,'Oscar','Perry','oscar.perry@example.com','555-7777'),
    (17,'Paula','Quinn','paula.quinn@example.com','555-8888'),
    (18,'Quincy','Reed','quincy.reed@example.com','555-9999'),
    (19,'Rita','Stone','rita.stone@example.com','555-0000'),
    (20,'Sam','Taylor','sam.taylor@example.com','555-1212');

INSERT INTO Registrations (registration_id,event_id,participant_id,registration_date,status) VALUES
    (1,1,1,'2025-02-01','Confirmed'),
    (2,1,2,'2025-02-02','Pending'),
    (3,2,3,'2025-02-03','Confirmed'),
    (4,2,4,'2025-02-04','Cancelled'),
    (5,3,5,'2025-02-05','Confirmed'),
    (6,3,6,'2025-02-06','Pending'),
    (7,4,7,'2025-02-07','Confirmed'),
    (8,4,8,'2025-02-08','Confirmed'),
    (9,5,9,'2025-02-09','Pending'),
    (10,5,10,'2025-02-10','Confirmed'),
    (11,6,11,'2025-02-11','Cancelled'),
    (12,6,12,'2025-02-12','Confirmed'),
    (13,7,13,'2025-02-13','Pending'),
    (14,7,14,'2025-02-14','Confirmed'),
    (15,8,15,'2025-02-15','Confirmed'),
    (16,8,16,'2025-02-16','Pending'),
    (17,9,17,'2025-02-17','Cancelled'),
    (18,9,18,'2025-02-18','Confirmed'),
    (19,10,19,'2025-02-19','Confirmed'),
    (20,10,20,'2025-02-20','Pending'),
    (21,1,3,'2025-02-21','Confirmed'),
    (22,1,4,'2025-02-22','Cancelled'),
    (23,2,5,'2025-02-23','Pending'),
    (24,2,6,'2025-02-24','Confirmed'),
    (25,3,7,'2025-02-25','Confirmed'),
    (26,3,8,'2025-02-26','Pending'),
    (27,4,9,'2025-02-27','Confirmed'),
    (28,4,10,'2025-02-28','Pending'),
    (29,5,11,'2025-03-01','Confirmed'),
    (30,5,12,'2025-03-02','Cancelled'),
    (31,6,13,'2025-03-03','Confirmed'),
    (32,6,14,'2025-03-04','Pending'),
    (33,7,15,'2025-03-05','Confirmed'),
    (34,7,16,'2025-03-06','Pending'),
    (35,8,17,'2025-03-07','Cancelled'),
    (36,8,18,'2025-03-08','Confirmed'),
    (37,9,19,'2025-03-09','Confirmed'),
    (38,9,20,'2025-03-10','Pending'),
    (39,10,1,'2025-03-11','Confirmed'),
    (40,10,2,'2025-03-12','Pending');

-- SELECT event_name, event_date
-- FROM Events
-- JOIN Venues ON Venues.venue_id=Events.venue_id
-- WHERE Venues.venue_name='Tech Conference Center'

-- SELECT first_name, last_name
-- FROM Participants
-- JOIN Registrations ON Registrations.participant_id=Participants.participant_id
-- WHERE Registrations.status='Pending'

-- SELECT event_name, event_date
-- FROM Events
-- JOIN Organizers ON Organizers.organizer_id=Events.organizer_id
-- WHERE Organizers.organizer_name='Event Pros'

-- SELECT Events.event_name, COUNT(Registrations.registration_id) AS total_registration
-- FROM Events
-- JOIN Registrations ON Registrations.event_id=Events.event_id
-- GROUP BY Events.event_name

-- SELECT Events.event_name, COUNT(Registrations.registration_id) AS total_registration, Venues.capacity
-- FROM Events
-- JOIN Registrations ON Registrations.event_id=Events.event_id
-- JOIN Venues ON Venues.venue_id=Events.venue_id
-- GROUP BY Events.event_name, Venues.capacity

-- SELECT first_name, last_name, email
-- FROM Participants
-- JOIN Registrations ON Participants.participant_id=Registrations.participant_id
-- JOIN Events ON Registrations.event_id=Events.event_id
-- WHERE Events.event_name='Tech Expo 2025'

-- SELECT Organizers.organizer_name, COUNT(Events.event_id) AS total_events
-- FROM Organizers
-- JOIN Events ON Events.organizer_id=Organizers.organizer_id
-- GROUP BY Organizers.organizer_name
-- ORDER BY total_events DESC

-- SELECT Participants.first_name, Participants.last_name, Participants.email, COUNT(Registrations.participant_id) AS event_count
-- FROM Participants
-- JOIN Registrations ON Participants.participant_id=Registrations.participant_id
-- GROUP BY Participants.first_name, Participants.last_name, Participants.email

-- SELECT venue_name, location
-- FROM Venues
-- JOIN Events ON Events.venue_id=Venues.venue_id
-- WHERE Events.event_date='2025-03-20'

-- SELECT first_name, last_name, email
-- FROM Participants
-- JOIN Registrations ON Registrations.participant_id=Participants.participant_id
-- JOIN Events ON Events.event_id=Registrations.event_id
-- WHERE Events.event_name='Community Fair'

SELECT Events.event_name, COUNT(Registrations.registration_id) AS total_registration, SUM(Registrations.status='Confirmed') AS confirmed_count, 
    ROUND(100.0 * SUM(Registrations.status = 'Confirmed') / COUNT(Registrations.registration_id), 2) AS conversion_rate_percentage 
FROM Events
JOIN Registrations ON Registrations.event_id=Events.event_id
GROUP BY Events.event_name