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


              `create table if not exists  "orders" (
                  id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
                  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 (20) NULL, 
                  cart_id INTEGER,
                  total INTEGER NOT NULL);` 
                  ,[]
            );


        `create table if not exists order_items (
          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,
FOREIGN KEY(category_id) REFERENCES category(id));`
        


        `create table if not exists  category (
          id      INTEGER  PRIMARY KEY AUTOINCREMENT ,
          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 AUTOINCREMENT,
          prod_name    VARCHAR (20)  NOT NULL,
          prod_num  VARCHAR (6) ,
          description  VARCHAR (200)  NOT NULL,
          saleflag VARCHAR(10) NULL,
          price   INTEGER  NOT NULL,
          category_id INTEGER NOT NULL
          );`
        
        `create table if not exists pkg_master (
          pkg_master_id INTEGER PRIMARY KEY AUTOINCREMENT,
          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 AUTOINCREMENT,
          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 AUTOINCREMENT,
          pkg_master_id INTEGER ,
          pkg_name VARCHAR(20) NOT NULL,
          pkg_price   INTEGER  NOT NULL,
          created_at DATETIME NOT NULL,
FOREIGN KEY (cart_id) REFERENCES pkg_master_in_cart(cart_id) ON DELETE CASCADE          
          );`

        `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
          );`