schemas.sql

/*
  Install mysql  -> https://dev.mysql.com/doc/mysql-osx-excerpt/5.5/en/osx-installation-pkg.html
  run the server 
  log in with 'root' user
  log in with  mysql -u root -p;

  create a super user like (https://dev.mysql.com/doc/refman/5.5/en/adding-users.html)
  in another termilal log in with the user info. 'sara'
  mysql -u sara -p;
  
  create a database --> nehal


  in another temilal nanigate to the folder where this file is and run this file

  mysql -u sara -p nehal < schemas.sql;



Now your database is populated 
*/


DROP TABLE IF EXISTS product_family;

CREATE TABLE product_family (
  ID_PRODUCT_FAMILY SERIAL PRIMARY KEY,
  PRODUCT_FAMILY_HANDLE VARCHAR(45) UNIQUE
);


DROP TABLE IF EXISTS product;

CREATE TABLE product (
  ID_PRODUCT SERIAL PRIMARY KEY,
  PRODUCT_SKU VARCHAR(255) UNIQUE,
  NUMBER_OF_RECIPES INT,
  NUMBER_OF_MEALS   INT,
  FK_PRODUCT_FAMILY INT REFERENCES product_family (ID_PRODUCT_FAMILY)
);

DROP TABLE IF EXISTS customer;

CREATE TABLE customer (
  ID_CUSTOMER SERIAL PRIMARY KEY,
  FIRST_NAME VARCHAR(255),
  LAST_NAME VARCHAR(255),
  EMAIL VARCHAR(255),
  PASSWORD VARCHAR(255),
  CREATED_AT DATETIME,
  UPDATED_AT DATETIME
);


DROP TABLE IF EXISTS subscription;

CREATE TABLE subscription (
  ID_SUBSCRIPTION SERIAL PRIMARY KEY,
  STATUS VARCHAR(255),
  CREATED_AT DATETIME,
  UPDATED_AT DATETIME,
  FK_CUSTOMER INT REFERENCES customer (ID_CUSTOMER),
  FK_PRODUCT_SUBSCRIBED_TO INT REFERENCES product (ID_PRODUCT)
);



/*
you can add more items in the tables like the following
note: that you have adjust the reference keys yourself


resource:
install: https://vladster.net/en/instructions/install-mysql-mac/

basic: https://www.digitalocean.com/community/tutorials/a-basic-mysql-tutorial


*/
INSERT INTO product_family (PRODUCT_FAMILY_HANDLE) VALUES ('bags');

INSERT INTO product (PRODUCT_SKU, NUMBER_OF_RECIPES, NUMBER_OF_MEALS, FK_PRODUCT_FAMILY) VALUES ('product_SKU_someUnqueVale', 5, 4, 1);


INSERT INTO customer (FIRST_NAME, LAST_NAME, EMAIL, PASSWORD,CREATED_AT,UPDATED_AT ) VALUES ('Sara', 'Daqiq', 'sadaqiq@gmail.com', '12345', current_timestamp, current_timestamp);

INSERT INTO subscription (STATUS, CREATED_AT,UPDATED_AT, FK_CUSTOMER, FK_PRODUCT_SUBSCRIBED_TO ) VALUES ('Active', current_timestamp, current_timestamp, 1, 1);