-- SQLINES DEMO *** sktop version to convert large SQL scripts,
-- SQLINES DEMO *** ny issues with Online conversion

-- SQLINES DEMO *** act us at [email protected]
-- create a table
-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE animal (
  product_id varchar(50) PRIMARY KEY ,
  name LONGTEXT NOT NULL,
  animal_name LONGTEXT NOT NULL
);
-- insert some values
INSERT INTO animal VALUES ('FI-SW-01', 'Angelfish', 'fish');
INSERT INTO animal VALUES ('FI-SW-02', 'Tiger Shark', 'fish');
INSERT INTO animal VALUES ('FI-FW-01', 'Koi', 'fish');

-- SQLINES LICENSE FOR EVALUATION USE ONLY
SELECT * FROM animal;

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE item_list(item_id VARCHAR(50) primary KEY,
                      product_id VARCHAR(50) references  animal(product_id);,
                      description text TEXT NOT NULL,
                      list_price float NOT NULL,
                      in_stock bool NOT NULL
                     );
                     
INSERT INTO item_list VALUES ('KEY-1','FI-SW-01', 'Large Angelfish', 16.50,'false');






 -- create a table
-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE user_login(
  id varchar(1)  PRIMARY KEY,
  password LONGTEXT NOT NULL
  
  );

INSERT INTO user_login VALUES ('saik', 'saikk');  
  

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE user_account(
  
  user_id varchar(50) references user_login(id); PRIMARY KEY,
  first_name TEXT NOT NULL,
  last_name TEXT NOT NULL,
  email varchar NOT NULL,
  phone int8 NOT NULL, 
  address1 varchar NOT NULL,
  address2 varchar,
  city text,
  state text,
  zip int4,
  country text
);

INSERT INTO user_account VALUES ('saik', 'abdf', 'ads','fgfdg', 89879, 'fgs', 'gfgfd','gdfgd','hfghdf',4345,'nd'); 

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE user_profile(
  
  user_id varchar(50) references  user_login(id); PRIMARY KEY,
  language_preference text NOT NULL,

  favourite_category text NOT NULL,
  enable_my_list bool NOT NULL,
  enable_my_banner bool NOT NULL
  
);
INSERT INTO user_profile VALUES ('saik', 'tel','*****','true','true'); 

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE orders(
  user_id varchar(50) references  user_login(id);,
  order_id INT IDENTITY(1,1) PRIMARY KEY,
  date  datetime, 
  total_price float ,
  item_id varchar references item_list(item_id)

);


INSERT INTO orders(user_id,date,total_price,item_id )VALUES ('saik','1000-01-01 00:00:00',100,'KEY-1'); 

-- SQLINES LICENSE FOR EVALUATION USE ONLY
CREATE TABLE payment_details(
  user_id varchar(50) references  user_account(user_id); PRIMARY KEY,
  card_type TEXT NOT NULL,
  card_no  INT,
  Expiry_date datetime,
  item_id varchar references item_list(item_id)

); 

INSERT INTO payment_details VALUES ('saik', 'tel','456','1000-01-01 00:00:00','KEY-1'); 

-- SQLINES LICENSE FOR EVALUATION USE ONLY
SELECT payment_details.card_type, payment_details.Expiry_date, payment_details.card_no,
user_account.first_name,
user_account.last_name,user_account.email,user_account.phone,
user_account.address1,user_account.address2,user_account.city,
user_account.state,user_account.zip,user_account.country
FROM payment_details JOIN user_account 
ON user_account.user_id = payment_details.user_id where user_account.user_id = 'saik' ;



-- SQLINES DEMO ***     After selecting link/image suppose it's fish
-- SQLINES LICENSE FOR EVALUATION USE ONLY
SELECT product_id,name FROM animal WHERE animal_name = 'fish';

-- SQLINES DEMO ***     After selecting product id link suppose product_id= FI-SW-01
-- SQLINES LICENSE FOR EVALUATION USE ONLY
SELECT item_id, product_id, description, list_price FROM item_list  WHERE  product_id= 'FI-SW-01';

-- SQLINES DEMO *** er selecting item_id  link suppose item_id= 'KEY-1'
-- SQLINES LICENSE FOR EVALUATION USE ONLY
SELECT item_list.item_id, item_list.description,animal.name,item_list.list_price
FROM item_list join animal ON item_list.product_id = animal.product_id WHERE item_list.item_id= 'KEY-1';

-- SQLINES DEMO *** nt       after selecting my account SUPPOSE user_id ='saiK'
-- SQLINES LICENSE FOR EVALUATION USE ONLY
SELECT user_login.id, user_login.password,user_account.first_name,
user_account.last_name,user_account.email,user_account.phone,
user_account.address1,user_account.address2,user_account.city,
user_account.state,user_account.zip,user_account.country,
user_profile.favourite_category,user_profile.enable_my_list,
user_profile.enable_my_banner from user_login JOIN user_account
ON user_login.id = user_account.user_id  JOIN user_profile
ON user_login.id = user_profile.user_id
where user_login.id = 'saiK' ;


-- SQLINES DEMO *** y account page 
update user_account set first_name = 'saikumarr'  where user_id ='saiK';

-- SQLINES DEMO *** ing my order page 
-- SQLINES LICENSE FOR EVALUATION USE ONLY
SELECT order_id, date, total_price from orders where user_id  ='saiK';

--   deleting a data
DELETE FROM animal WHERE name = 'Koi';