
create database orders;

use database orders;



create table customer ( 
 customer_id int NOT NULL
, customer_name VARCHAR (300) NOT NULL
, start_date DATE 
, PRIMARY KEY (Customer_Id)
);

insert into customer ( 
 customer_id 
, customer_name 
)
values (1, 'Fortis Hospital') 
	,(2, 'Manipal Hospital')
	,(3, 'Apollo Hospital')
	;

create table tenant ( 
 tenant_id int NOT NULL
, customer_id int NOT NULL
, tenant_name VARCHAR (300) NOT NULL
, begin_date DATE 
, min_order_quantity int DEFAULT 0
, customization_required int
, cust_microsvc_type VARCHAR (200) NOT NULL
, customization_service VARCHAR (200) NOT NULL
, customization_payload VARCHAR (50) NOT NULL
, PRIMARY KEY (tenant_id)
);

insert into tenant ( 
 tenant_id
, customer_id 
, tenant_name 
, begin_date
, min_order_quantity
, customization_required 
, cust_microsvc_type
, customization_service 
, customization_payload 
)
values (1,1, 'Fortis - Mumbai Branch', CURRENT_TIMESTAMP(), 1, 0, '', '', '') 
	,(2,1, 'Fortis - Pune Branch', CURRENT_TIMESTAMP(), 1, 0, '', '', '')
	,(3,2, 'Manipal - Pune Branch', CURRENT_TIMESTAMP(), 10, 1, 'synchronous-webclient', 'tenant-2-validation-service', 'order_data')
	,(4,3, 'Apollo - Mumbai Branch', CURRENT_TIMESTAMP(), 1, 1, 'asynchronous-event', 'tenant-3-shipping-service', 'order_data')
	;

CREATE TABLE orders (
  order_id int NOT NULL,
  customer_id int NOT NULL,
  tenant_id int NOT NULL,
  product_id int NOT NULL,
  order_date DATE NULL,
  order_quantity int DEFAULT 0,
  order_status varchar(50) NULL,
  created TIMESTAMP NULL,
  updated TIMESTAMP NULL,
  PRIMARY KEY (order_id)
);

INSERT INTO orders
(
  order_id ,
  customer_id ,
  tenant_id ,
  product_id ,
  order_date ,
  order_quantity ,
  order_status ,
  created ,
  updated 
)
VALUES (1,1,1,1,CURRENT_TIMESTAMP(),10,'Created',CURRENT_TIMESTAMP(),CURRENT_TIMESTAMP())
;

CREATE TABLE product (
  product_id int NOT NULL,
  product_name varchar(50) NOT NULL,
  product_category varchar(20) DEFAULT '',
  primary_supplier varchar(30) DEFAULT '',
  PRIMARY KEY (product_id)
);

INSERT INTO product 
(
  product_id ,
  product_name ,
  product_category ,
  primary_supplier 
)
VALUES (1,'Amoxicillin','Antibiotics','Lupin'),
(2,'Azithromycin','Antibiotics','Abbot'),
(3,'Erythromycin Stearate','Antibiotics','Cipla')
;

CREATE TABLE inventory (
  product_id int NOT NULL,
  inventory_date DATE NULL,
  inventory_qty int DEFAULT 0,
  PRIMARY KEY (product_id)
);

INSERT INTO inventory 
(
  product_id ,
  inventory_date ,
  inventory_qty 
)
SELECT 
  product_id ,
  current_date - 31, 
  product_id * 300 
FROM product
;

CREATE TABLE shipping (
  shipping_id int NOT NULL,
  order_id int NOT NULL,
  customer_id int NOT NULL,
  tenant_id int NOT NULL,
  product_id int NOT NULL,
  shipping_date DATE NULL,
  shipping_quantity int DEFAULT 0,
  shipping_status varchar(50) NULL,
  created TIMESTAMP NULL,
  updated TIMESTAMP NULL,
  PRIMARY KEY (shipping_id)
);

