-- ============================================================
-- Scottish Handball League Database
-- For use with DataGrip (MySQL via Laragon)
-- N5 Computing Science — Unit 3: Database Design & Development
-- James Gillespie's High School
-- ============================================================

SET FOREIGN_KEY_CHECKS = 0;

CREATE DATABASE IF NOT EXISTS handball;
USE handball;

DROP TABLE IF EXISTS Player;
DROP TABLE IF EXISTS Team;

SET FOREIGN_KEY_CHECKS = 1;

-- ============================================================
-- TABLE: Team
-- ============================================================
CREATE TABLE Team (
    teamName        VARCHAR(100) PRIMARY KEY,
    town            VARCHAR(100) NOT NULL,
    leagueDivision  INT          NOT NULL,
    district        VARCHAR(50)  NOT NULL,
    trainingVenue   VARCHAR(255) NOT NULL,
    contactPerson   VARCHAR(100) NOT NULL,
    emailAddress    VARCHAR(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLE: Player
-- ============================================================
CREATE TABLE Player (
    playerID    VARCHAR(10)  PRIMARY KEY,
    firstname   VARCHAR(100) NOT NULL,
    surname     VARCHAR(100) NOT NULL,
    dateOfBirth VARCHAR(10)  NOT NULL,
    position    VARCHAR(50)  NOT NULL,
    goalsScored INT          NOT NULL,
    teamName    VARCHAR(100) NOT NULL,
    FOREIGN KEY (teamName) REFERENCES Team(teamName)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- DATA: Team (8 teams across 4 districts, 2 divisions)
-- ============================================================
INSERT INTO Team VALUES ('Clyde Flyers',    'Greenock',  1, 'West',  'Burnside Sports Centre',   'Chris Black',    'clydeflyers@handball.mail.uk');
INSERT INTO Team VALUES ('Paisley Panthers','Paisley',   1, 'West',  'Paisley Leisure Centre',   'Sandra Reid',    'panthers@paisleyhandball.co.uk');
INSERT INTO Team VALUES ('Airdrie Lions',   'Airdrie',   2, 'West',  'Airdrie Sports Hub',       'Lynne Jack',     'airdrie@lionshandball.com');
INSERT INTO Team VALUES ('Harris Hurricanes','Stornoway', 1, 'North', 'An Lanntair Sports Hall',  'Fiona MacLeod',  'hurricanes@harrishandball.co.uk');
INSERT INTO Team VALUES ('Inverness Ice',   'Inverness', 2, 'North', 'Inverness Leisure',        'Donald Fraser',  'ice@invernesshandball.co.uk');
INSERT INTO Team VALUES ('Edinburgh Eagles','Edinburgh', 1, 'East',  'Meadowbank Sports Centre', 'Priya Sharma',   'eagles@edinhandball.co.uk');
INSERT INTO Team VALUES ('Dundee Dynamos',  'Dundee',    2, 'East',  'Dundee International Sports Centre', 'Paul McLaughlin', 'dynamos@dundeehandball.gmail.com');
INSERT INTO Team VALUES ('Borders Bandits', 'Galashiels',2, 'South', 'Galashiels Academy Hall',  'Ewan Douglas',   'bandits@bordershandball.co.uk');

-- ============================================================
-- DATA: Player (~30 players across all teams)
-- Positions used: Left back, Right back, Centre back,
--                 Left wing, Right wing, Goalkeeper, Pivot
-- goalsScored varies 0-42 to make WHERE/ORDER BY interesting
-- ============================================================

-- Clyde Flyers (West, Division 1)
INSERT INTO Player VALUES ('810JE', 'Jack',    'Edwards',   '03/05/1994', 'Left back',   37, 'Clyde Flyers');
INSERT INTO Player VALUES ('811AM', 'Aisha',   'Munro',     '14/08/1998', 'Right wing',  22, 'Clyde Flyers');
INSERT INTO Player VALUES ('812TB', 'Tom',     'Baxter',    '27/11/2001', 'Goalkeeper',   3, 'Clyde Flyers');
INSERT INTO Player VALUES ('813RK', 'Rona',    'Kennedy',   '05/03/1996', 'Centre back', 18, 'Clyde Flyers');

-- Paisley Panthers (West, Division 1)
INSERT INTO Player VALUES ('820LM', 'Lewis',   'MacDonald', '19/07/1999', 'Right back',  29, 'Paisley Panthers');
INSERT INTO Player VALUES ('821SP', 'Sienna',  'Park',      '02/12/2000', 'Left wing',   41, 'Paisley Panthers');
INSERT INTO Player VALUES ('822DH', 'Dylan',   'Hughes',    '30/04/1997', 'Pivot',       15, 'Paisley Panthers');

-- Airdrie Lions (West, Division 2)
INSERT INTO Player VALUES ('830GW', 'Grace',   'Wilson',    '11/09/2002', 'Left back',   8,  'Airdrie Lions');
INSERT INTO Player VALUES ('831CB', 'Callum',  'Burns',     '23/06/1995', 'Right wing',  19, 'Airdrie Lions');
INSERT INTO Player VALUES ('832NF', 'Niamh',   'Forsyth',   '07/01/2003', 'Goalkeeper',   0, 'Airdrie Lions');

-- Harris Hurricanes (North, Division 1)
INSERT INTO Player VALUES ('840FK', 'Fionnuala','Kelly',    '15/05/1997', 'Centre back', 33, 'Harris Hurricanes');
INSERT INTO Player VALUES ('841AC', 'Anatol',  'Czaja',     '28/02/2000', 'Left back',   11, 'Harris Hurricanes');
INSERT INTO Player VALUES ('842SM', 'Stuart',  'Morrison',  '09/10/1993', 'Right back',  27, 'Harris Hurricanes');
INSERT INTO Player VALUES ('843EB', 'Eilidh',  'Buchanan',  '18/08/2001', 'Right wing',  42, 'Harris Hurricanes');

-- Inverness Ice (North, Division 2)
INSERT INTO Player VALUES ('850RF', 'Rory',    'Fraser',    '04/04/1999', 'Left wing',   16, 'Inverness Ice');
INSERT INTO Player VALUES ('851HC', 'Hannah',  'Campbell',  '22/11/2002', 'Pivot',       7,  'Inverness Ice');
INSERT INTO Player VALUES ('852JR', 'Jamie',   'Ross',      '31/07/1996', 'Left back',   23, 'Inverness Ice');

-- Edinburgh Eagles (East, Division 1)
INSERT INTO Player VALUES ('860PS', 'Priya',   'Sharma',    '13/03/1998', 'Centre back', 31, 'Edinburgh Eagles');
INSERT INTO Player VALUES ('861OT', 'Oliver',  'Thomson',   '25/06/2001', 'Right wing',  14, 'Edinburgh Eagles');
INSERT INTO Player VALUES ('862ZA', 'Zara',    'Ahmed',     '08/09/1997', 'Goalkeeper',   2, 'Edinburgh Eagles');
INSERT INTO Player VALUES ('863LB', 'Luca',    'Benedetti', '17/01/2000', 'Left back',   38, 'Edinburgh Eagles');
INSERT INTO Player VALUES ('864MO', 'Megan',   'Ogilvie',   '29/05/2003', 'Right back',  9,  'Edinburgh Eagles');

-- Dundee Dynamos (East, Division 2)
INSERT INTO Player VALUES ('870KD', 'Kyle',    'Duncan',    '06/12/1994', 'Pivot',       21, 'Dundee Dynamos');
INSERT INTO Player VALUES ('871IS', 'Isla',    'Stewart',   '14/03/2002', 'Left wing',   13, 'Dundee Dynamos');
INSERT INTO Player VALUES ('872BM', 'Blair',   'Mackenzie', '20/08/1999', 'Right back',  6,  'Dundee Dynamos');

-- Borders Bandits (South, Division 2)
INSERT INTO Player VALUES ('880ED', 'Emma',    'Douglas',   '03/07/2001', 'Centre back', 17, 'Borders Bandits');
INSERT INTO Player VALUES ('881RP', 'Ryan',    'Paterson',  '16/11/1998', 'Left back',   25, 'Borders Bandits');
INSERT INTO Player VALUES ('882CS', 'Chloe',   'Scott',     '11/04/2000', 'Right wing',  36, 'Borders Bandits');
INSERT INTO Player VALUES ('883JW', 'Josh',    'Wallace',   '27/02/1997', 'Goalkeeper',   1, 'Borders Bandits');

-- ============================================================
-- END OF FILE
-- Database: handball
-- Tables: Team (8 rows), Player (30 rows)
-- ============================================================
