sec1_gr11_database.sql

DROP DATABASE IF EXISTS lsdb;
CREATE DATABASE lsdb;

USE lsdb;

CREATE TABLE IF NOT EXISTS adm_information (
    uid 			VARCHAR(50) PRIMARY KEY,
    first_name 		VARCHAR(50) NOT NULL,
    last_name 		VARCHAR(50) NOT NULL,
    age 			INT,
    gender			CHAR(1)	 	 NOT NULL,
    phone			VARCHAR(255),
    email 			VARCHAR(255) NOT NULL,
    CONSTRAINT check_uid CHECK (uid REGEXP '^[a][A-Za-z0-9_]*$')
);

CREATE TABLE IF NOT EXISTS adm_login (
    login_id 		VARCHAR(50) NOT NULL PRIMARY KEY,
    pass 			VARCHAR(50) NOT NULL,
    login_role 		ENUM('Boss', 'Product Manager','Tester') NOT NULL,
    
    CONSTRAINT check_login_id CHECK (login_id REGEXP '^[a][A-Za-z0-9_]*$'),
    CONSTRAINT fk_id  FOREIGN KEY (login_id) REFERENCES adm_information(uid)
);

CREATE TABLE IF NOT EXISTS country (
	c_id 		CHAR(2) PRIMARY KEY,
    c_name 		VARCHAR(50) NOT NULL
);

CREATE TABLE IF NOT EXISTS address (
    address_id 		INT PRIMARY KEY,
    street 			VARCHAR(255) NOT NULL,
    city 			VARCHAR(255) NOT NULL,
    state 			VARCHAR(255) NOT NULL,
    add_country 	CHAR(2) NOT NULL,
    zip_code 		VARCHAR(10) NOT NULL,
    add_uid			VARCHAR(50) NOT NULL,
    
    CONSTRAINT fk_add_uid FOREIGN KEY (add_uid) REFERENCES adm_information(uid),
    CONSTRAINT fk_country  FOREIGN KEY (add_country) REFERENCES country(c_id)
);

CREATE TABLE IF NOT EXISTS report(
	rep_id			INT PRIMARY KEY
);

CREATE TABLE IF NOT EXISTS product (
	p_id			INT PRIMARY KEY,
    p_name			VARCHAR(255) NOT NULL,
    p_des			VARCHAR(255) ,
    rating			CHAR(7) NOT NULL,
    p_type			VARCHAR(255) NOT NULL,
    p_color			VARCHAR(255) NOT NULL,
    p_inches		VARCHAR(255) NOT NULL,
    p_memory		VARCHAR(255) NOT NULL,
    p_internet		VARCHAR(255) NOT NULL,
    p_price			DOUBLE NOT NULL,
    p_reportid		INT NOT NULL,
    
    CONSTRAINT fk_p_repid FOREIGN KEY (p_reportid) REFERENCES report(rep_id)
);


INSERT INTO adm_information (uid, first_name, last_name, age, gender, phone, email) VALUES 
("a0001", "John", "Doe", 35, "M", "1234567890", "johndoe@gmail.com"),
("a0002", "Jane", "Doe", 28, "F", "2345678901", "janedoe@outlook.com"),
("a07203", "Robert", "Smith", 42, "M", NULL, "robertsmith@gmail.com"),
("a58204", "Samantha", "Jones", 31, "F", "3456789012", "samanthajones@student.abc.edu"),
("a855", "William", "Lee", 27, "M", "4567890123", "williamlee@student.ac.th"),
("a1236", "Emily", "Brown", 24, "F", "09514269", "emilybrown@outlook.com"),
("a14587", "David", "Johnson", 38, "M", NULL, "davidjohnson@gmail.com"),
("a741569", "Melissa", "Davis", 29, "F", "8901234567", "melissadavis@mail.com");

INSERT INTO adm_login (login_id, pass, login_role) VALUES
('a0001', '123456', 'Boss'),
('a0002', 'sdsd1512', 'Product Manager'),
('a07203', 'Ssdaa132', 'Tester'),
('a58204', 'asdjhggh', 'Product Manager'),
('a855', 'hgjdf82gh6', 'Tester'),
('a1236', 'ghdfghb442', 'Tester'),
('a14587', '123abc', 'Tester'),
('a741569', '20000920abc', 'Tester');

INSERT INTO country (c_id,c_name) VALUES
('AF', 'Afghanistan'),
('AX', 'Aland Islands'),
('AL', 'Albania'),
('DZ', 'Algeria'),
('AS', 'American Samoa'),
('AD', 'Andorra'),
('AO', 'Angola'),
('AI', 'Anguilla'),
('AQ', 'Antarctica'),
('AG', 'Antigua and Barbuda'),
('AR', 'Argentina'),
('AM', 'Armenia'),
('AW', 'Aruba'),
('AU', 'Australia'),
('AT', 'Austria'),
('AZ', 'Azerbaijan'),
('BS', 'Bahamas'),
('BH', 'Bahrain'),
('BD', 'Bangladesh'),
('BB', 'Barbados'),
('BY', 'Belarus'),
('BE', 'Belgium'),
('BZ', 'Belize'),
('BJ', 'Benin'),
('BM', 'Bermuda'),
('BT', 'Bhutan'),
('BO', 'Bolivia'),
('BQ', 'Bonaire, Sint Eustatius and Saba'),
('BA', 'Bosnia and Herzegovina'),
('BW', 'Botswana'),
('BV', 'Bouvet Island'),
('BR', 'Brazil'),
('IO', 'British Indian Ocean Territory'),
('BN', 'Brunei Darussalam'),
('BG', 'Bulgaria'),
('BF', 'Burkina Faso'),
('BI', 'Burundi'),
('KH', 'Cambodia'),
('CM', 'Cameroon'),
('CA', 'Canada'),
('CV', 'Cape Verde'),
('KY', 'Cayman Islands'),
('CF', 'Central African Republic'),
('TD', 'Chad'),
('CL', 'Chile'),
('CN', 'China'),
('CX', 'Christmas Island'),
('CC', 'Cocos (Keeling) Islands'),
('CO', 'Colombia'),
('KM', 'Comoros'),
('CG', 'Congo'),
('CD', 'Congo, Democratic Republic of the Congo'),
('CK', 'Cook Islands'),
('CR', 'Costa Rica'),
('CI', 'Cote D\'Ivoire'),
('HR', 'Croatia'),
('CU', 'Cuba'),
('CW', 'Curacao'),
('CY', 'Cyprus'),
('CZ', 'Czech Republic'),
('DK', 'Denmark'),
('DJ', 'Djibouti'),
('DM', 'Dominica'),
('DO', 'Dominican Republic'),
('EC', 'Ecuador'),
('EG', 'Egypt'),
('SV', 'El Salvador'),
('GQ', 'Equatorial Guinea'),
('ER', 'Eritrea'),
('EE', 'Estonia'),
('ET', 'Ethiopia'),
('FK', 'Falkland Islands (Malvinas)'),
('FO', 'Faroe Islands'),
('FJ', 'Fiji'),
('FI', 'Finland'),
('FR', 'France'),
('GF', 'French Guiana'),
('PF', 'French Polynesia'),
('TF', 'French Southern Territories'),
('GA', 'Gabon'),
('GM', 'Gambia'),
('GE', 'Georgia'),
('DE', 'Germany'),
('GH', 'Ghana'),
('GI', 'Gibraltar'),
('GR', 'Greece'),
('GL', 'Greenland'),
('GD', 'Grenada'),
('GP', 'Guadeloupe'),
('GU', 'Guam'),
('GT', 'Guatemala'),
('GG', 'Guernsey'),
('GN', 'Guinea'),
('GW', 'Guinea-Bissau'),
('GY', 'Guyana'),
('HT', 'Haiti'),
('HM', 'Heard Island and Mcdonald Islands'),
('VA', 'Holy See (Vatican City State)'),
('HN', 'Honduras'),
('HK', 'Hong Kong'),
('HU', 'Hungary'),
('IS', 'Iceland'),
('IN', 'India'),
('ID', 'Indonesia'),
('IR', 'Iran, Islamic Republic of'),
('IQ', 'Iraq'),
('IE', 'Ireland'),
('IM', 'Isle of Man'),
('IL', 'Israel'),
('IT', 'Italy'),
('JM', 'Jamaica'),
('JP', 'Japan'),
('JE', 'Jersey'),
('JO', 'Jordan'),
('KZ', 'Kazakhstan'),
('KE', 'Kenya'),
('KI', 'Kiribati'),
('KP', 'Korea, Democratic People\'s Republic of'),
('KR', 'Korea, Republic of'),
('XK', 'Kosovo'),
('KW', 'Kuwait'),
('KG', 'Kyrgyzstan'),
('LA', 'Lao People\'s Democratic Republic'),
('LV', 'Latvia'),
('LB', 'Lebanon'),
('LS', 'Lesotho'),
('LR', 'Liberia'),
('LY', 'Libyan Arab Jamahiriya'),
('LI', 'Liechtenstein'),
('LT', 'Lithuania'),
('LU', 'Luxembourg'),
('MO', 'Macao'),
('MK', 'Macedonia, the Former Yugoslav Republic of'),
('MG', 'Madagascar'),
('MW', 'Malawi'),
('MY', 'Malaysia'),
('MV', 'Maldives'),
('ML', 'Mali'),
('MT', 'Malta'),
('MH', 'Marshall Islands'),
('MQ', 'Martinique'),
('MR', 'Mauritania'),
('MU', 'Mauritius'),
('YT', 'Mayotte'),
('MX', 'Mexico'),
('FM', 'Micronesia, Federated States of'),
('MD', 'Moldova, Republic of'),
('MC', 'Monaco'),
('MN', 'Mongolia'),
('ME', 'Montenegro'),
('MS', 'Montserrat'),
('MA', 'Morocco'),
('MZ', 'Mozambique'),
('MM', 'Myanmar'),
('NA', 'Namibia'),
('NR', 'Nauru'),
('NP', 'Nepal'),
('NL', 'Netherlands'),
('AN', 'Netherlands Antilles'),
('NC', 'New Caledonia'),
('NZ', 'New Zealand'),
('NI', 'Nicaragua'),
('NE', 'Niger'),
('NG', 'Nigeria'),
('NU', 'Niue'),
('NF', 'Norfolk Island'),
('MP', 'Northern Mariana Islands'),
('NO', 'Norway'),
('OM', 'Oman'),
('PK', 'Pakistan'),
('PW', 'Palau'),
('PS', 'Palestinian Territory, Occupied'),
('PA', 'Panama'),
('PG', 'Papua New Guinea'),
('PY', 'Paraguay'),
('PE', 'Peru'),
('PH', 'Philippines'),
('PN', 'Pitcairn'),
('PL', 'Poland'),
('PT', 'Portugal'),
('PR', 'Puerto Rico'),
('QA', 'Qatar'),
('RE', 'Reunion'),
('RO', 'Romania'),
('RU', 'Russian Federation'),
('RW', 'Rwanda'),
('BL', 'Saint Barthelemy'),
('SH', 'Saint Helena'),
('KN', 'Saint Kitts and Nevis'),
('LC', 'Saint Lucia'),
('MF', 'Saint Martin'),
('PM', 'Saint Pierre and Miquelon'),
('VC', 'Saint Vincent and the Grenadines'),
('WS', 'Samoa'),
('SM', 'San Marino'),
('ST', 'Sao Tome and Principe'),
('SA', 'Saudi Arabia'),
('SN', 'Senegal'),
('RS', 'Serbia'),
('CS', 'Serbia and Montenegro'),
('SC', 'Seychelles'),
('SL', 'Sierra Leone'),
('SG', 'Singapore'),
('SX', 'Sint Maarten'),
('SK', 'Slovakia'),
('SI', 'Slovenia'),
('SB', 'Solomon Islands'),
('SO', 'Somalia'),
('ZA', 'South Africa'),
('GS', 'South Georgia and the South Sandwich Islands'),
('SS', 'South Sudan'),
('ES', 'Spain'),
('LK', 'Sri Lanka'),
('SD', 'Sudan'),
('SR', 'Suriname'),
('SJ', 'Svalbard and Jan Mayen'),
('SZ', 'Swaziland'),
('SE', 'Sweden'),
('CH', 'Switzerland'),
('SY', 'Syrian Arab Republic'),
('TW', 'Taiwan, Province of China'),
('TJ', 'Tajikistan'),
('TZ', 'Tanzania, United Republic of'),
('TH', 'Thailand'),
('TL', 'Timor-Leste'),
('TG', 'Togo'),
('TK', 'Tokelau'),
('TO', 'Tonga'),
('TT', 'Trinidad and Tobago'),
('TN', 'Tunisia'),
('TR', 'Turkey'),
('TM', 'Turkmenistan'),
('TC', 'Turks and Caicos Islands'),
('TV', 'Tuvalu'),
('UG', 'Uganda'),
('UA', 'Ukraine'),
('UK', 'United Kingdom'),
('AE', 'United Arab Emirates'),
('GB', 'United Kingdom'),
('US', 'United States'),
('UM', 'United States Minor Outlying Islands'),
('UY', 'Uruguay'),
('UZ', 'Uzbekistan'),
('VU', 'Vanuatu'),
('VE', 'Venezuela'),
('VN', 'Viet Nam'),
('VG', 'Virgin Islands, British'),
('VI', 'Virgin Islands, U.s.'),
('WF', 'Wallis and Futuna'),
('EH', 'Western Sahara'),
('YE', 'Yemen'),
('ZM', 'Zambia'),
('ZW', 'Zimbabwe');

INSERT INTO address (address_id, street, city, state, add_country, zip_code, add_uid) VALUES
(1, '123 Main St', 'Mar Lee', 'Colorado', 'US', '12345', 'a0001'),
(2, '456 Oak Ave', 'Taipei', 'Taipei', 'TW', '23456', 'a0002'),
(3, '789 Central Park Ln', 'New York', 'New York', 'US', '34567', 'a07203'),
(4, 'Soi Samsen 6', 'Bangkok', 'Bangkok', 'TH', '45678', 'a58204'),
(5, '555 Elm St', 'Salaya', 'Nakhon Pathom', 'TH', '56789', 'a855'),
(6, 'Cuihu St', 'Kunming', 'Yunnan', 'CN', '67890', 'a1236'),
(7, '777 Broadway', 'Manchester', 'Manchester', 'UK', '78901', 'a14587'),
(8, '999 Church St', 'Albuquerque', 'New Mexico', 'US', '89012', 'a741569');

INSERT INTO report (rep_id) VALUES
(1),
(2),
(3),
(4),
(5),
(6),
(7),
(8),
(9),
(10);

-- In p_id the first number mean the product type 
-- Phone: 1
-- Pad: 2
-- Wearable: 3
INSERT INTO product (p_id, p_name, p_des, rating, p_type,p_color, p_inches, p_memory, p_internet, p_price, p_reportid) VALUES
(100001,"iPhone 14 ProMax","Almost New Phone","99% New","iPhone","Night Purple","6.69 inches","256G","5G","31000.00",1),
(100002,"iPhone 13","Small Scratches","80% New","iPhone","Gold","6.06 inches","64G","5G","12000.00",2),
(100003,"iPhone 13 Pro","Cost-effective Choice","85% New","iPhone","Black","6.06 inches","128G","5G","19000.00",3),
(100004, "iPhone SE", "Brand New Phone", "99% New", "iPhone","White","4.7 inches","64G","4G","5800.00", 4),
(100005, "iPhone XR", "Good Condition Phone", "90% New", "iPhone","White","6.06 inches","64G","4G","7490.00", 5),

(200001,"iPad 7","Small Scratches","90% New	","iPad","Space Gray","10.2 inches","64G","WiFi","6900.00",6),
(200002, "iPad Air", "Slight Wear and Tear", "85% New", "iPad","Black","10.86 inches","64G","4G","2700.00",7),
(200003, "iPad Pro 12.9-inch", "Like New iPad", "98% New", "iPad","Black","12.9 inches","256G","WiFi","23500.00",8),

(300001,"Apple Watch SE","Almost New Watch","99% New","Apple Watch","White","44 mm","32G","GPS","2900.00",9),
(300002, "Apple Watch Series 7", "Like New Watch", "98% New", "Apple Watch","White","45 mm","32G","GPS","7800.00",10);