-- Populate the CLYSTMS DB of CSCI 4333

INSERT INTO Coach(CoachId, FName, LName, Phone, EMail) VALUES
    (1,'Joe', 'Smith', '713-222-9413', 'joesmile_1061@gmail.com'),
    (2,'Jane', 'Smith', '713-222-9414', 'janesmile_1061@gmail.com'),
    (3,'Paul', 'Lam', '713-486-2011', 'paulkkk@hotmail.com'),
    (4,'Paulina', 'Hall', '832-486-1997', 'paulinathenice@yahoo.com'),
    (5,'Katrina', 'Bajaj', '832-117-2435', 'KatrinaBajaj@gmail.com');
     
INSERT INTO Caretaker(CT_Id, FName, LName, Phone, EMail) VALUES
    (1,'Azalea', 'Khan', '832-116-2992', 'theAKhan@gmail.com'),
    (2,'Joseph', 'Khan', '832-116-2993', 'theJKhan@gmail.com'),
    (3,'Jim', 'Khan', '832-116-2994', 'theJKhan2@gmail.com'),
    (4,'Katie', 'Johnson', '713-014-0090', 'KatieJohnson1010@yahoo.com'),
    (5,'Elizabeth', 'Johnson', '713-014-2090', 'EJohnson5111@yahoo.com'),
    (6,'Benjamin', 'Smith', '281-290-1929', 'BSmith_honeywell@gmail.com');

INSERT INTO Level (LevelId, Level, Description) VALUES
    (1, 'Green', 'First Level'),
    (2, 'Blue', 'Second level'),
    (3, 'Yellow', 'Third level'),
    (4, 'Pink', 'Fourth level'),
    (5, 'Orange', 'Fifth level'),
    (6, 'Lime', 'Six level'),
    (7, 'Purple', 'Seventh level'),
    (8, 'Red', 'Eigth level'),
    (9, 'Brown', 'Ninth level'),
    (10, 'Black', 'Tenth level');

INSERT INTO Venue(VenueId, Name, Address, City, State, ZipCode, Phone) VALUES
    (1,'UHCL', '2700 Bay Area Boulevard', 'Houston','Texas', 
        '77058', '281-283-3700'),
    (2,'CLHS', '3300 Bay Area Boulevard', 'Houston', 'Texas',
        '77059', '713-126-4544'),
    (3,'BAHEP', '1260 Gemini Way Boulevard', 'Webster', 'Texas',
        '77047', '713-789-2999');
       
INSERT INTO Swimmer(SwimmerId, FName, LName, Phone, EMail, JoinTime, 
    CurrentLevelId, Main_CT_Id, Main_CT_Relationship, Main_CT_Since) VALUES
    (1,'Bobby', 'Khan', '832-116-2992', 'theBKhan1@gmail.com',
        '2014-2-12', 3, 1, 'Mother', '2014-2-12'),
    (2,'Billy', 'Khan', '832-116-2992', 'theBKhan2@gmail.com',
        '2015-12-12', 2, 1, 'Mother', '2015-12-12'),
    (3,'Nina', 'Khan', '832-116-2992', 'theNinaKhan@gmail.com',
        '2016-5-12', 2, 2, 'Father', '2017-5-12'),
    (4,'Clara', 'Johnson', '713-222-1010', 'ClaraJohnson_11@yahoo.com',
        '2013-5-12', 3, 4, 'Mother', '2015-5-12'),
    (5,'Philip', 'Johnson', '713-222-1010', 'PhilipJohnson_108@yahoo.com',
        '2015-5-15', 2, 5, 'Mother', '2015-5-15'),
    (6, 'Joe', 'Fen', '271-909-2733', 'JoeFenTheGreat@gmail.com',
        '2018-3-13', 5, 4, 'Aunt', '2019-1-1');
   
INSERT INTO OtherCareTaker(SwimmerId, CT_Id, Relationship, Since) VALUES
    (1,2,'Uncle','2014-2-12'),
    (1,3,'Uncle','2014-2-12'),
    (2,3,'Uncle','2016-1-3'),
    (3,1,'Aunt','2016-5-12'),
    (3,3,'Adult Friend','2016-5-12'),
    (4,5,'Aunt','2016-1-12');  

INSERT INTO LevelHistory(SwimmerId, LevelId, StartDate) VALUES
    (1,2,'2014-7-15'),
    (2,1,'2015-12-12'),
    (2,2,'2016-4-15'),
    (3,1,'2016-5-12'),
    (4,2,'2014-6-1'),
    (4,3,'2015-10-2'),
    (5,1,'2015-5-15'),
    (5,2,'2016-3-15'),
    (6,5,'2018-2-14');
    
INSERT INTO LevelHistory(SwimmerId, LevelId, StartDate, Comment) VALUES
    (1,1,'2014-2-12','Good spirit'),
    (1,3,'2016-1-19','Fast time'),
    (3,2,'2016-7-12', 'Fast advance'),
    (4,1,'2013-5-12','Freestyle best'),
    (6,4,'2018-1-1','Club record in Freestyle50');
     
INSERT INTO Meet(MeetId, Title, Date, StartTime, EndTime, VenueId, CoachId) VALUES
    (1,'UHCL Open', '2016-3-3', '09:00:00', '16:00:00', 1, 1),
    (2,'Shell Trial', '2016-8-4', '08:00:00', '11:00:00', 2, 1),
    (3,'Clear Lake Contest', '2019-7-4', '10:00:00', '15:00:00', 2, 2);
 
INSERT INTO Event(EventId,Title, StartTime, EndTime, MeetId, LevelId) VALUES
    (1,'50M Butterfly', '09:10:00', '09:30:00', 1, 2),
    (2,'100M Freestyle', '09:40:00', '09:50:00', 1, 2),
    (3,'100M Butterfly', '10:10:00', '10:30:00', 1, 3),
    (4,'200M Freestyle', '10:40:00', '10:50:00', 1, 3),
    (5,'50M Butterfly', '09:15:00', '09:35:00', 2, 2),
    (6,'100M Freestyle', '09:45:00', '09:55:00', 2, 2),
    (7,'100M Butterfly', '10:15:00', '10:35:00', 2, 3),
    (8,'200M Freestyle', '10:45:00', '10:55:00', 2, 3),
    (9,'100M Breaststroke', '11:15:00', '10:35:00', 2, 3),
    (10,'200M Backstroke', '10:15:00', '10:55:00', 3, 5),
    (11,'50M Breaststroke', '11:15:00', '10:35:00', 3, 3);

INSERT INTO Participation(SwimmerId, EventId) VALUES(2,1);
INSERT INTO Participation(SwimmerId, EventId, Committed)
    VALUES(3,1,1);
INSERT INTO Participation(SwimmerId, EventId, Committed, CommitTime, 
        Participated, Result, Comment, CommentCoachId)
    VALUES(5,1,1,'2016-2-20 10:00:00', 1, 'Winner', 'Good!', 2);
INSERT INTO Participation(SwimmerId, EventId)
    VALUES(3,2);
INSERT INTO Participation(SwimmerId, EventId, Committed)
    VALUES(2,2,1);
INSERT INTO Participation(SwimmerId, EventId, Committed, CommitTime, 
        Participated, Result, Comment, CommentCoachId)
    VALUES(5,2,1,'2016-2-20 10:00:00', 1, 'Winner', 'Second winner', 2);
INSERT INTO Participation(SwimmerId, EventId)
    VALUES(1,3);
INSERT INTO Participation(SwimmerId, EventId, Committed)
    VALUES(4,3,1);
INSERT INTO Participation(SwimmerId, EventId)
    VALUES(1,4);
INSERT INTO Participation(SwimmerId, EventId, Committed, CommitTime, 
        Participated, Result)
    VALUES(4,5,1,'2016-1-15 12:00:00', 1, 'Runner up');
INSERT INTO Participation(SwimmerId, EventId)
    VALUES(2,5);
INSERT INTO Participation(SwimmerId, EventId, Committed)
    VALUES(3,5,1);
INSERT INTO Participation(SwimmerId, EventId, Committed, CommitTime, 
        Participated, Result, Comment, CommentCoachId)
    VALUES(5,5,1,'2016-3-20 10:00:00', 1, '1:12:20', 'Good!', 1);
INSERT INTO Participation(SwimmerId, EventId, Committed, CommitTime, 
        Participated, Result, Comment, CommentCoachId)
    VALUES(2,6,1,'2016-3-9 10:00:00', 1, 'Good', 'Need to focus', 3);
INSERT INTO Participation(SwimmerId, EventId)
    VALUES(3,6);
INSERT INTO Participation(SwimmerId, EventId, Committed)
    VALUES(5,6,1);
INSERT INTO Participation(SwimmerId, EventId)
    VALUES(1,7);
INSERT INTO Participation(SwimmerId, EventId, Committed)
    VALUES(4,7,1);
INSERT INTO Participation(SwimmerId, EventId, Committed, CommitTime, 
        Participated, Result)
    VALUES(1,8,1,'2016-1-21 12:00:00', 1, 'winner');
INSERT INTO Participation(SwimmerId, EventId)
    VALUES(4,8);
INSERT INTO Participation(SwimmerId, EventId)
    VALUES(1,9),(3,9),(6,10),(1,11);

INSERT INTO V_TaskList(VTL_Id, MeetId, Required, Description)    
    VALUES(1,1,0,'UHCL meet volunteer tasks');
INSERT INTO V_TaskList(VTL_Id, MeetId, Required, Description, Penalty, PenaltyAmt)    
    VALUES(2,2,1,'Shell Trial meet volunteer tasks','3 credits', 0);
INSERT INTO V_TaskList(VTL_Id, MeetId, Required, Description)    
    VALUES(3,3,0,'Clear Lake Contest task lists');
    
INSERT INTO V_Task(VT_Id, VTL_Id, Name, Comment, Num_V) 
    VALUES(1,1,'Officiating','Must be trained',2);
INSERT INTO V_Task(VT_Id, VTL_Id, Name, Num_V)
    VALUES(2,1,'Recording',3);
INSERT INTO V_Task(VT_Id, VTL_Id, Name)
    VALUES(3,1,'Diecting traffic');
INSERT INTO V_Task(VT_Id, VTL_Id, Name, Comment, Num_V)
    VALUES(4,2,'Officiating','Must be trained',3);
INSERT INTO V_Task(VT_Id, VTL_Id, Name, Num_V)
    VALUES(5,2,'Recording',2);
INSERT INTO V_Task(VT_Id, VTL_Id, Name)
    VALUES(6,2,'Diecting traffic');
    
INSERT INTO Commitment(CT_Id, VT_Id, CommitTime) VALUES
    (1,1,'2015-10-10 11:11:12'),
    (4,1,'2015-10-11 12:11:20'),
    (2,2,'2016-1-10 11:05:12'),
    (3,2,'2016-1-11 12:12:20'),
    (5,2,'2016-1-17 09:19:25'),
    (1,3,'2015-10-10 11:11:12');
    
INSERT INTO Commitment(CT_Id, VT_Id, CommitTime,CarriedOut,
        Comment, CommentCoachId) VALUES
    (1,4,'2016-05-11 11:47:22',1,'Very dependable',1),
    (4,4,'2016-05-12 06:37:12',0,'Sick and called',1),
    (5,4,'2016-05-12 06:37:12',1,'Brought fruit',2);
INSERT INTO Commitment(CT_Id, VT_Id, CommitTime,Rescinded) VALUES
    (2,5,'2016-03-11 11:33:12',1),
    (4,5,'2016-03-17 12:35:14',0);
INSERT INTO Commitment(CT_Id, VT_Id, CommitTime) VALUES
    (1,5,'2016-05-11 11:47:22'),
    (5,6,'2016-06-11 09:15:22');