-------insert user date----------------------
CREATE proc sp_user_register
@name VARCHAR(20),
@pass  VARCHAR(MAX),
@mail  VARCHAR(140)
AS
BEGIN
INSERT INTO [tbl_user] ([username],[password],[email])   VALUES  (@name,@pass,@mail);
END
GO
sp_user_register 'Microsoft', 'micromicromicro', 'user@hotmail.com';
GO
sp_user_register 'google', 'gogogoole11ss', 'developer@google.com';
GO
sp_user_register 'mozilla', 'mozmoz115599', 'dev@mozilla.com';
GO
sp_user_register 'bebo_uwk', 'uwk910723', 'uwkbebo@hotmail.com';
GO
sp_user_register 'johnBA', 'jj_zz_2211', 'jjzz@gmail.com';
GO
sp_user_register 'Sofy_ZZZ', 'Sofy_ZZZ_!1!1', 'sof91@test.com';
GO
sp_user_register 'Jack', '8597145222', 'jck@ymail.com';
GO
sp_user_register 'Sallam', '11113333', 'sallam@null.com';
GO
sp_user_register 'Saed', 'n123n123', 'smsm@hotmail.com';
GO
sp_user_register 'Mike', '12345678', 'mike@gmail.com';

GO
-------update user date----------------------
CREATE proc sp_user_update
@name VARCHAR(20),
@newpass  VARCHAR(MAX),
@newemail  VARCHAR(140)
AS
BEGIN
UPDATE [tbl_user]
SET [password] = @newpass,
    email = @newemail
WHERE [username] = @name
END
GO
sp_user_update 'Mike', '1234567', 'mike@gmail.com'


------------------insert developer data-----------------
GO
CREATE proc sp_developer_register
@x INT,
@website text ,
@phone_number VARCHAR(20),
@address text
AS
BEGIN
INSERT INTO  tbl_developer (developer_id,website,phone_number,[address])
VALUES  (@x,@website,@phone_number,@address);
END
GO
sp_developer_register '10', 'microsoft.com', 'Silicon Valley, California, USA', '+1800-555-999'
GO
sp_developer_register '9', 'google.com', 'Silicon Valley, California, USA', '+1800-444-666'
GO
sp_developer_register '8', 'mozilla.com', 'USA', ''
GO
sp_developer_register '3', 'tanta.ga', '', ''
GO
sp_developer_register '1', '', 'Egypt', ''
GO

---------------------update developer data-----------
GO
CREATE proc sp_developer_update
@x INT,-- the value of developer_id
@newwebsite text ,
@newphone_number VARCHAR(20),
@newaddress text
AS
BEGIN
UPDATE tbl_developer
SET website = @newwebsite,
    phone_number = @newphone_number,
    [address] = @newaddress
WHERE developer_id = @x
END
GO
sp_developer_update '10', 'microsoft.com', 'Silicon Valley, California, USA', '+1800-555-999'

---------------insert app data---------------
GO
CREATE proc sp_app_submit
@app_name  VARCHAR(50),
@category   INT,
@version   VARCHAR(20),
@discription  text,
@icon text,
@download_link text,
@size_mb FLOAT
AS
BEGIN
INSERT INTO [tbl_apps] ([app_name],category,[version],[description],icon,download_link,[size_mb],[download_count],[rating],[submit_date])
VALUES(@app_name,@category,@version,@discription,@icon,@download_link,@size_mb,0,0,getdate())
END
GO
sp_app_submit 'Firefox', '2', '43-stable', 'Open Source Web Broswer','http://test.mozilla.com/firefox-icon', 'http://firefox.com/download/43-stable',3.5;

GO
------------------update app data-----------
CREATE proc sp_app_update
@x INT,--the value of app_id
@newapp_name  VARCHAR(50),
@newcategory   INT,
@newversion   VARCHAR(20),
@newdiscription  text,
@newicon text,
@newdownload_link text
AS
BEGIN
UPDATE [tbl_apps]
SET [app_name] = @newapp_name,
    [category] = @newcategory,
    [version] = @newversion,
    [description] = @newdiscription,
    [icon] = @newicon,
    [download_link] = @newdownload_link,
    [submit_date] = getdate()
WHERE app_id = @x
END
GO
sp_app_update 7,'Firefox77', 2, '44-stable', ' Web Broswer','http://test.mozilla.com/firefox-icon', 'http://firefox.com/download/43-stable';
GO
-------------------insert category data--------------
CREATE proc sp_category_insert
@category_name  VARCHAR(20)
AS
BEGIN
INSERT INTO tbl_category (category_name) VALUES (@category_name)
END
GO
sp_category_insert 'Games';
GO
sp_category_insert 'Internet';
GO
sp_category_insert 'Productivity';
GO
sp_category_insert 'Network';
GO
sp_category_insert 'Security';
GO
sp_category_insert 'Programming';
GO
sp_category_insert 'Design';
GO
sp_category_insert 'Photography';
GO
sp_category_insert 'Scientific';
GO
sp_category_insert 'Engineering';
GO
sp_category_insert 'Medical';

GO

------------------------update category data--------------------
CREATE proc sp_category_update
@x INT , --the value of the category
@category_name  VARCHAR(20)
AS
BEGIN
UPDATE tbl_category
SET category_name= @category_name
WHERE category_id= @x
END
GO
sp_category_update  1 , 'games'
GO

------------------------list app for developer  x  ----------
CREATE proc sp_apps_list_developer
@x INT --developer id
AS
BEGIN
SELECT [app_name] ,[category],[rating],[icon], developer FROM tbl_apps  a
JOIN tbl_developer d
ON a.developer = d.developer_id
WHERE developer_id = @x
END
GO
sp_apps_list_developer 1
GO

----------------------list app for category   x  ------------------
CREATE proc sp_apps_list_category
@x INT ---category id
AS
BEGIN
SELECT [app_name] ,[rating],[icon], developer,[category] FROM tbl_apps  a
JOIN tbl_category c
ON a.[category] = c.category_id
WHERE category_id = @x

END
GO
sp_apps_list_category 2
GO

------------------------------list app for os  x ----------------------
CREATE proc sp_apps_for_os
@x INT --os id
AS
BEGIN

SELECT [app_name] ,[rating],[icon], developer,[category] ,oa.os_id , oa.app_id FROM tbl_os_app  oa
JOIN tbl_apps a
ON oa.app_id = a.app_id
WHERE os_id = @x

END
GO
sp_apps_for_os 1