-- Create Database
CREATE DATABASE [DB_AppStore];
GO
-- select default database
USE [DB_AppStore];

-- Craete user table
CREATE TABLE [tbl_user](
    [user_id]       INT IDENTITY PRIMARY KEY,
    [username]      VARCHAR(20) NOT NULL UNIQUE,
    [password]      VARCHAR(MAX) NOT NULL,
    [email]         VARCHAR(140) NOT NULL
);

-- Create developer table
CREATE TABLE [tbl_developer](
    [developer_id]      INT FOREIGN KEY REFERENCES [tbl_user]([user_id]),
    [website]       text,
    [address]       text,
    [phone_number]      VARCHAR(20),
    PRIMARY KEY ([developer_id])
);

-- Create Category table
CREATE TABLE [tbl_category](
    [category_id]       INT IDENTITY PRIMARY KEY,
    [category_name]     VARCHAR(20) NOT NULL
);

-- Create apps table
CREATE TABLE [tbl_apps](
    [app_id]        INT IDENTITY PRIMARY KEY,
    [app_name]      VARCHAR(50) NOT NULL,
    [category]      INT FOREIGN KEY REFERENCES [tbl_category]([category_id]),
    [version]       VARCHAR(20) NOT NULL,
    [rating]        FLOAT NOT NULL,
    [submit_date]       DATE NOT NULL,
    [developer]     INT FOREIGN KEY REFERENCES [tbl_developer]([developer_id]),
    [description]       text NOT NULL,
    [icon]          text NOT NULL,
    [size_mb]       FLOAT NOT NULL,
    [download_link]     text NOT NULL,
    [download_count]    INT NOT NULL
);

-- Create screanshots table
CREATE TABLE [tbl_screenshots](
    [app_id]        INT FOREIGN KEY REFERENCES [tbl_apps]([app_id]),
    [link]          text NOT NULL
);

-- Create rating table
CREATE TABLE [tbl_rating](
    [app_id]        INT FOREIGN KEY REFERENCES [tbl_apps]([app_id]),
    [user_id]       INT FOREIGN KEY REFERENCES [tbl_user]([user_id]),
    [rating_value]      FLOAT NOT NULL CHECK ([rating_value] BETWEEN 1 AND 5),
    [comment]       text
    PRIMARY KEY([app_id],[user_id])
);

-- Create os table
CREATE TABLE [tbl_os](
    [os_id]         INT IDENTITY PRIMARY KEY,
    [os_name]       VARCHAR(20) NOT NULL
);

-- Create os_app table
CREATE TABLE [tbl_os_app](
    [app_id]        INT FOREIGN KEY REFERENCES [tbl_apps]([app_id]),
    [os_id]         INT FOREIGN KEY REFERENCES [tbl_os]([os_id]),
    PRIMARY KEY([app_id],[os_id])
);

------------------------------------------------------------------------------------------------
----------------------------------insert  Data--------------------------------------------------
------------------------------------------------------------------------------------------------
INSERT INTO [tbl_os] VALUES('android')
INSERT INTO [tbl_os] VALUES('windows')
INSERT INTO [tbl_os] VALUES('linux')
INSERT INTO [tbl_os] VALUES('windows_phone')
INSERT INTO [tbl_os] VALUES('symbion')
INSERT INTO [tbl_os] VALUES('blackbery')

INSERT INTO [tbl_os_app] VALUES (6,1)
INSERT INTO [tbl_os_app] VALUES (7,1)
INSERT INTO [tbl_os_app] VALUES (8,1)
INSERT INTO [tbl_os_app] VALUES (7,2)

INSERT INTO [tbl_user] VALUES
('Mike', '12345678', 'mike@gmail.com'),
('Saed', 'n123n123', 'smsm@hotmail.com'),
('Sallam', '11113333', 'sallam@null.com'),
('Jack', '8597145222', 'jck@ymail.com'),
('Sofy_ZZZ', 'Sofy_ZZZ_!1!1', 'sof91@test.com'),
('johnBA', 'jj_zz_2211', 'jjzz@gmail.com'),
('bebo_uwk', 'uwk910723', 'uwkbebo@hotmail.com'),
('mozilla', 'mozmoz115599', 'dev@mozilla.com'),
('google', 'gogogoole11ss', 'developer@google.com'),
('Microsoft', 'micromicromicro', 'user@hotmail.com');

INSERT INTO [tbl_developer] VALUES
('10', 'microsoft.com', 'Silicon Valley, California, USA', '+1800-555-999'),
('9', 'google.com', 'Silicon Valley, California, USA', '+1800-444-666'),
('8', 'mozilla.com', 'USA', ''),
('3', 'tanta.ga', '', ''),
('1', '', 'Egypt', '');

INSERT INTO [tbl_category] VALUES
('Games'),
('Internet'),
('Productivity'),
('Network'),
('Security'),
('Programming'),
('Design'),
('Photography'),
('Scientific'),
('Engineering'),
('Medical'),
('Simulation');

INSERT INTO [tbl_apps] VALUES
('Firefox', '2', '43-stable', '4.5', '11-10-2015', '8', 'Open Source Web Broswer',
'http://test.mozilla.com/firefox-icon', '43.3', 'http://firefox.com/download/43-stable', '430156'),
('Thunderbird', '2', '40-beta', '4.9', '15-10-2015', '8', 'Open Source Mail Client',
'http://test.mozilla.com/thunderbird-icon', '20.3', 'http://firefox.com/download/40-beta', '21536'),
('Photoshop', '7', '15.5-64bit-pro', '4.7', '1-5-2014', '1', 'Best photo editing software',
'http://download.adobe.com/ps15.5-icon', '2113.88', 'http://download.adobe.com/ps15.5-64bit-pro.download', '14156'),
('NeedForSpeed MW', '1', '2012-most-wanted', '3.9', '1-5-2012', '1', 'Enjoy a premium racing game',
'http://download.ea.com/nfsmw2012-icon', '14555.88', 'http://download.ea.com/nfsmw2012-setup.iso', '9021'),
('Office 2013', '3', '2013-sp1', '4.2', '12-11-2012', '10', 'Best office suite ever',
'http://download.microsoft.com/office2013-icon', '2115.12', 'http://download.microsoft.com/office2013-setup.iso', '10254'),
('Office 2016', '3', '2016-32-bit', '4.5', '12-11-2015', '10', 'Best office suite ever, with new features',
'http://download.microsoft.com/office2016-icon', '3251.89', 'http://download.microsoft.com/office201632-setup.iso', '1258'),
('chrome', '2', '47', '4.4', '12-12-2015', '9', 'Number 1 web broswer',
'http://download.google.com/chrome-icon', '40.1', 'http://download.google.com/chrome.exe', '4410254'),
('one drive', '2', '47', '4.1', '12-10-2015', '9', 'Best online office suite',
'http://download.google.com/onedrive-icon', '40.1', 'http://download.google.com/onedrive.exe', '410254'),