-- /***************************************************************************
-- copied from shoptext/brue_shop/App.js VVVVVVVVVVVVV
--    Ver 1.1.2 : Bruce on 2024-03-07
--       ADD: add_on text to product table
-- 
--    Ver 1.1.1; 2024-01-05
--       - should refer foreign key constraint to avoid foreign key constraint failure during import
--  * https://blog.logrocket.com/create-radio-buttons-react-native/
--    Ver 1.1.0 : Bruce on 2023-12-28
--       REN: column datetime to order_dttm of orders table
--       ADD: prod_count in pkg_detail_in_cart for individual items of the package in shopping cart
-- 
--   Ver 1.0.9 : Bruce on 2023-07-29
--       ADD : cart-id in orders table
--       ADD : pkg_master_id in order_items table
--       ADD : order_items field in orders table to store ordered items (the same as msg_text to shorten text in go-text)
--             e.g. hk-rice *1, Package-A *1, ... etc
-- 
-- 
--    Ver 1.0.8 : Bruce on 2023-07-03
--       ADD: new column prod_num in pkg_detail_in_cart in order to display in better sorting order of package in shopping cart and order items
--       UPD: prod_num could be null in product and pkg_detail_in_cart for some restaurant
-- 
--    Ver 1.0.7 : Bruce on 2023-06-27
--       ADD: new column cart_id in pkg_master_in_cart and pkg_detail_in_cart
-- 
--    Ver 1.0.6 : Bruce on 2023-06-22
--       UPD: use pkg_master_id as unique key instead of pacakge_name in pkg_master_in_cart table
-- 
--    Ver 1.0.5 : Bruce on 2023-06-06
--       ADD: 1 field : pkg_price for pkg_master table
-- 
--    Ver 1.0.4 : Bruce on 2023-06-04
--       ADD : a new field service_charge in orders table
-- 
--    Ver 1.0.3 : Bruce on 2023-05-19
--       REN : table name - order to orders
-- 
--    Ver 1.0.2 : Bruce on 2023-05-16
--       REN: rename id field to product_id of product table by alter table statement
-- 
--    Ver 1.0.1 : Bruce on 2023-05-13
--       ADD : new field category number cat_num for the category table e.g. rice 001
--       REN: rename name field to cat_name of category table
-- 
--       ADD : new field product number prod_num for the product table e.g. lemon tea is 5001
--       REN: rename product field name to prod_name of product table
-- ****************************************************************************/

-- sqlite to mysql : 
-- AUTOINCREMENT to AUTO_INCREMENT
-- order is reserved word => `order` or orders

create table if not exists  user (
                id      INTEGER    PRIMARY KEY AUTO_INCREMENT,
                name    VARCHAR (20)  NOT NULL,
                mobile  VARCHAR (15)  NOT NULL,
                email   VARCHAR (30)  NOT NULL,
                address VARCHAR (120) NOT NULL
);

create table if not exists orders (
                  id INTEGER PRIMARY KEY AUTO_INCREMENT NOT NULL,
                  -- user_id INTEGER NOT NULL REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE,
                  user_id INTEGER NOT NULL ,
                  status VARCHAR (10) NOT NULL, 
                  order_dttm DATETIME NOT NULL,
                  discount_type VARCHAR(10) NOT NULL DEFAULT 'FIXED' ,
                  discount_amt INTEGER,
                  service_charge INTEGER,
                  remarks TEXT ,
                  order_items TEXT,
                  mobile VARCHAR (16) NULL, 
                  cart_id INTEGER,
                  total INTEGER NOT NULL
); 

create table if not exists  category (
          id      INTEGER    PRIMARY KEY AUTO_INCREMENT,
          cat_name    VARCHAR (20)  NOT NULL,
          cat_num  VARCHAR (6)  NOT NULL,
          CONSTRAINT category_name_uk UNIQUE (cat_name)
);

create table if not exists  product (
          product_id      INTEGER    PRIMARY KEY AUTO_INCREMENT,
          prod_name    VARCHAR (20)  NOT NULL,
          prod_num  VARCHAR (6) ,
          description  VARCHAR (200)  NOT NULL,
          saleflag VARCHAR(10) NULL,
          price   INTEGER  NOT NULL,
          add_on text,
          category_id INTEGER NOT NULL,
          FOREIGN KEY(category_id) REFERENCES category(id)
);

create table if not exists pkg_master (
          pkg_master_id INTEGER PRIMARY KEY AUTO_INCREMENT,
          pkg_name VARCHAR(20) NOT NULL,
          pkg_price   INTEGER  NOT NULL,
          created_at DATETIME NOT NULL,
          CONSTRAINT package_name_uk UNIQUE (pkg_name)
);

create table if not exists pkg_detail (
          pkg_detail_id INTEGER PRIMARY KEY AUTO_INCREMENT,
          pkg_master_id INTEGER NOT NULL,
          pkg_desc TEXT NULL,
          category_id      INTEGER    ,
          product_id      INTEGER    ,
          created_at DATETIME NULL,
          FOREIGN KEY (pkg_master_id) REFERENCES pkg_master(pkg_master_id) ON DELETE CASCADE          
);

create table if not exists pkg_master_in_cart (
          cart_id INTEGER PRIMARY KEY AUTO_INCREMENT,
          pkg_master_id INTEGER ,
          pkg_name VARCHAR(20) NOT NULL,
          pkg_price   INTEGER  NOT NULL,
          created_at DATETIME NOT NULL
);

create table if not exists pkg_detail_in_cart (
          cart_id INTEGER ,
          pkg_detail_id INTEGER ,
          pkg_master_id INTEGER NOT NULL,
          pkg_desc TEXT NULL,
          category_id      INTEGER    ,
          product_id      INTEGER    ,
          prod_num  VARCHAR (6),
          prod_count  INTEGER,
          created_at DATETIME NULL,
          FOREIGN KEY (cart_id) REFERENCES pkg_master_in_cart(cart_id) ON DELETE CASCADE          
);

          
-- WRONG syntax: order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
create table if not exists order_items (
          -- order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE ON UPDATE CASCADE,
          order_id INTEGER NOT NULL,
          product_id INTEGER ,  
          pkg_master_id INTEGER,
          cart_type VARCHAR(10) NOT NULL,
          created_at DATETIME NOT NULL,
          count SMALLINT NOT NULL
);

