/***************************************************************************
 * https://blog.logrocket.com/create-radio-buttons-react-native/
   Ver 1.1.2 : Bruce on 2024-01-10
      UPD: remove foreign constraints for RestoreDB 
                        //   Error inserting row into table category: [Error: UNIQUE constraint failed: category.id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)]
                        //   WARN  Possible Unhandled Promise Rejection (id: 0):
                        //  Error: UNIQUE constraint failed: category.id (code 1555 SQLITE_CONSTRAINT_PRIMARYKEY)                          
 
   Ver 1.1.1 : Bruce on 2024-01-10
      UPD: column mobile as char(20) to be consistent with server DB

   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
****************************************************************************/

import { StatusBar } from 'expo-status-bar';
import { StyleSheet, Text, View,Alert, Button } from 'react-native';
import { NavigationContainer } from '@react-navigation/native';
import { createNativeStackNavigator } from '@react-navigation/native-stack';
import { createBottomTabNavigator } from '@react-navigation/bottom-tabs';
import Ionicons from '@expo/vector-icons/Ionicons';
import AsyncStorage from '@react-native-async-storage/async-storage';
import React,{useEffect,useState,useContext,useReducer,createContext} from 'react'

import Home from './pages/Home';
import HomeDetail from './pages/HomeDetail';
import Signup from './pages/Signup';
import Login from './pages/Login';
import Cart from './pages/Cart';
import OrderByProdNum from './pages/OrderByProdNum';
import OrderByPackage from './pages/OrderByPackage';
import OrderByPkgDetail from './pages/OrderByPkgDetail';
import Order from './pages/Order';
import Setting from './pages/Setting';
import AddProduct from './pages/AddProduct';
import AddPackage from './pages/AddPackage';
import ListAllTables from './pages/ListAllTables';
import BackupDB from './pages/BackupDB';
import RestoreDB from './pages/RestoreDB';
import EditAllTables from './pages/EditAllTables';
import EditTableDetail from './pages/EditTableDetail';
import AddType from './pages/AddType';
import EditType from './pages/EditType';
import EditProduct from './pages/EditProduct';
import EditPackage from './pages/EditPackage';
import {reducer,initState}  from './store/reducer';
// import ContextProvider from './store/provider';
import Scanner from './pages/qr_code';   // added by bruce

import {DatabaseConnection}  from './sqlite/db';

import * as FileSystem from 'expo-file-system'
import * as Sharing from 'expo-sharing';
import { MyContext } from './store/context';


const db = DatabaseConnection.getConnection();

const Stack = createNativeStackNavigator();

const Tab = createBottomTabNavigator();
// export const MyContext = createContext();



function HomeTab() {

  // const [state, dispatch] = useReducer(reducer, initState)
  const { state, dispatch } = useContext(MyContext);

  // console.log(state)

  return (

      <Tab.Navigator
      initialRouteName="Home"
      screenOptions={({ route }) => ({
        tabBarIcon: ({ focused, color, size }) => {
          let iconName;

          if (route.name === "Home") {
            iconName = focused
              ? 'newspaper'
              : 'newspaper';
            size = focused?21:20;
          } else if (route.name === "Setting") {
            // iconName = focused ? 'logo-ionic' : 'logo-ionic';
            // iconName = focused ? 'setting' : 'setting'; // NOT work
            iconName = focused ? 'ios-settings' : 'ios-settings'; // NOT work
            size = focused?21:20;
          }else if (route.name === "Cart") {
            iconName = focused ? 'cart' : 'cart';
            size = focused?21:20;
          }else if (route.name === "OrderByProdNum") {    // LATERRRRRRRRRR
            iconName = focused ? 'newspaper' : 'newspaper';
            size = focused?21:20;
          }else if (route.name === "OrderByPackage") {
            iconName = focused ? 'newspaper' : 'newspaper';
            // iconName = focused ? 'ios-menu' : 'ios-menu';
            size = focused?21:20;
          }else if (route.name === "Order") {
            // iconName = focused ? 'newspaper' : 'newspaper';
            iconName = focused ? 'ios-checkmark' : 'ios-checkmark';
            size = focused?21:20;
          }

          return <Ionicons name={iconName} size={size} color={color} />
        },
        // tabBarLabel: ({focused, color, size}) => (
        //   <Text style={{color: focused ? 'red' : color}}>{route.name}</Text>
        // ),
        // tabBarActiveTintColor: '#6495ED',
        tabBarActiveTintColor: 'tomato',
        tabBarInactiveTintColor: 'gray',


      })}>
        
        <Tab.Screen name="Home" component={Home} options={{ title: '單點', headerStyle: {backgroundColor: 'white'}}}/>
        <Tab.Screen name="OrderByPackage" component={OrderByPackage} options={{ title: '套餐點餐',headerStyle: {backgroundColor: 'white'}}}/>
        <Tab.Screen name="OrderByProdNum" component={OrderByProdNum} options={{ title: '編號點餐',headerStyle: {backgroundColor: 'white'}}}/>
        <Tab.Screen name="Cart" component={Cart} options={{ title: '購物車',tabBarBadge: (state.cart_shown && state.count>0)? state.count:null , tabBarBadgeStyle: { backgroundColor: 'tomato',color:'white' }}} />
        <Tab.Screen name="Order" component={Order}  options={{ title: '訂單' }} />
        <Tab.Screen name="Setting" component={Setting} options={{ title: '設定' }}/>

      </Tab.Navigator>

// LATERRRRRRRRRRRRRRRRRR

  );
}


export default function App() {

  const [state, dispatch] = useReducer(reducer, initState)

  useEffect(() => {

// speciallllllllllllllllllllll

db.transaction((tx) => {
      // Fetch products from the database and set them to state variables
      console.log('DEBUG: inside App.js - special display')
      tx.executeSql('select * from "orders"', []);  // dummy select

      // test for restora data
      tx.executeSql("UPDATE product set prod_name='福建炒飯VVV' where prod_name='福建炒飯' ; ", []);
      
      // tx.executeSql('DROP TABLE IF EXISTS "order"', []);
      // tx.executeSql('DROP TABLE IF EXISTS "temp_table"', []);

      // tx.executeSql("UPDATE pkg_master set pkg_price=99 where pkg_master_id<16; ", []);
      // tx.executeSql("UPDATE pkg_detail_in_cart set prod_count=2 ;", []);

      // tx.executeSql("DELETE FROM pkg_master_in_cart where cart_id='4' or cart_id='5' or cart_id='6';", []);

      // tx.executeSql("UPDATE pkg_master_in_cart set cart_id=11 where cart_id=3; ", []);
      // tx.executeSql("UPDATE pkg_detail_in_cart set cart_id=11 where cart_id=3; ", []);

      // tx.executeSql('alter table pkg_detail_in_cart add column prod_count INTEGER', []);
      // console.log('alter table pkg_detail_in_cart add column prod_count INTEGER ');

      // tx.executeSql("UPDATE orders set cart_id=1 where id=2; ", []);
      // tx.executeSql("UPDATE orders set cart_id=2 where id=3; ", []);
      // tx.executeSql('DROP TABLE IF EXISTS "orders"', []);
      // tx.executeSql('DROP TABLE IF EXISTS "order_items"', []);
      // tx.executeSql('DROP TABLE IF EXISTS "pkg_master_in_cart"', []);
      // tx.executeSql('DROP TABLE IF EXISTS "pkg_detail_in_cart"', []);

      // tx.executeSql('alter table pkg_master add column pkg_price INTEGER', []);
      // console.log('alter table pkg_master add column pkg_price INTEGER ');

      // tx.executeSql('DELETE FROM "order_items" where order_id=6', []);
      // console.log('DELETE FROM order_itemsSSSS ');
      // tx.executeSql('DELETE FROM "pkg_master_in_cart" ;', []);
      // tx.executeSql('DELETE FROM "pkg_detail_in_cart" ;', []);


      // tx.executeSql(
      //   // `alter table pkg_detail add column product_id INTEGER; `,
      //   `alter table pkg_detail add column category_id INTEGER; `,
      //     [],
      //   (_, { rows: { _array } }) => {
      //     // display each row data
      //     _array.forEach((row) => {
      //       console.log('DEBUG::: App.js - inside selected OK pkg_name=[' + row.pkg_name + ']');
      //       console.log('DEBUG::: App.js - inside selected OK product_id=[' + row.product_id + ']');
      //       console.log('DEBUG::: App.js - inside selected OK category_id=[' + row.category_id + ']');
      //     });            
      //   }  // end row
      // );  // end tx.executeSql



      // tx.executeSql(
      //   `alter table "orders" add column discount_type VARCHAR(10) NOT NULL DEFAULT 'FIXED';`,
      //   `alter table "orders" add column discount_amt INTEGER;`,
      //   `alter table "orders" add column service_charge INTEGER;`,
      //   `alter table "orders" add column remarks TEXT`,
      //     [],
      //   (_, { rows: { _array } }) => {
      //     // setProductArr(_array);
      
      //     // display each row data
      //     _array.forEach((row) => {
      //       console.log('DEBUG::: App.js - inside selected OK status=[' + row.status + ']');
      //       console.log('DEBUG::: App.js - inside selected OK discount_type=[' + row.discount_type + ']');
      //       console.log('DEBUG::: App.js - inside selected OK discount_amt=[' + row.discount_amt + ']');
      //       console.log('DEBUG::: App.js - inside selected OK service_charge=[' + row.service_charge + ']');
      //       console.log('DEBUG::: App.js - inside selected OK remarks=[' + row.remarks + ']');
      //     });            
      //   }  // end row
      // );  // end tx.executeSql


    //   tx.executeSql('DROP TABLE IF EXISTS "orders"', []);
    //   tx.executeSql(
    //     `CREATE TABLE orders AS SELECT * FROM "order"; `,
    //     [],
    //     (_, { rows: { _array } }) => {
    //       // display each row data
    //       _array.forEach((row) => {
    //         console.log('DEBUG::: App.js - inside selected OK status=[' + row.status + ']');
    //         console.log('DEBUG::: App.js - inside selected OK discount_type=[' + row.discount_type + ']');
    //         if (Array.isArray(row.discount_amt)) {
    //           console.log('DEBUG::: App.js - inside selected OK discount_amt=[' + row.discount_amt.join(',') + ']');
    //         } else {
    //           console.log('DEBUG::: App.js - inside selected OK discount_amt=[' + row.discount_amt + ']');
    //         }
    //               console.log('DEBUG::: App.js - inside selected OK remarks=[' + row.remarks + ']');
    //       });            
    //     }  // end row
    //   );  // end tx.executeSql
    // }, (error) => {
    //   console.log(`create table new_table as select from old_table error: ${error}`);


  });  // end db.transaction
// speciallllllllllllllllllllll

    db.transaction(function (txn) {
          // txn.executeSql('DROP TABLE IF EXISTS "orders" ', []);

      txn.executeSql(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='user'",
        [],
        function (txn, res) {
          console.log('item:', res.rows.length);
          if (res.rows.length == 0) {
            txn.executeSql('DROP TABLE IF EXISTS user', []);
            console.log('drop table user if exists');
            txn.executeSql(
              `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
              );` ,[]
            );
            console.log('create table user if NOT exist');
          }
        }
      );  // end txn.executeSql



      txn.executeSql(
        "SELECT name FROM sqlite_master WHERE type='table' AND name='orders'",
        [],
        function (txn, res) {
          console.log('table orders exists in sqlite_master table');
          // txn.executeSql('.schema "orders";', []);
          console.log('item:', res.rows.length);

          // txn.executeSql('DROP TABLE IF EXISTS "orders" ', []);
          // txn.executeSql('delete from orders;', [],()=>{console.log('orders deleted')},   (_,err)=>{console.log(err)});
          // txn.executeSql('CREATE TABLE orders AS SELECT * FROM "order";', []);
          // txn.executeSql('CREATE TABLE "orders" AS SELECT * FROM "order";', []);
          // txn.executeSql('CREATE TABLE "order" AS SELECT * FROM "orders";', []);

            // 2024-01-06 BEGIN  - rename column datetime to order_dttm
            // txn.executeSql(
            //   "ALTER TABLE orders RENAME COLUMN datetime TO order_dttm;",
            //   [],
            //   (tx, res) => {
            //     console.log("Column renamed successfully!");
            //   },
            //   (_, err) => {
            //     console.log("Error renaming column in orders table:", err);
            //   }
            // );
          // 2024-01-06 END

          // 2023-12-28 BEGIN -- somehow not working
          // txn.executeSql("ALTER TABLE `orders` RENAME COLUMN `datetime` TO `order_dttm`;",
          //   [],
          //   (tx,res) => {
          //     console.log('Table orders RENAME COLUMN successfully!');
          //   },
          //   (_, err) => {
          //     console.log('Error RENAME COLUMN for table orders:', err);
          //     // do NOT know how to fix : Error RENAME COLUMN for table orders: [Error: near "COLUMN": syntax error (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE product RENAME COLUMN id TO product_id;]
          //   }
          // );  // end txn.executeSql
          // 2023-12-28 END


          if (res.rows.length == 0) {
            txn.executeSql('DROP TABLE IF EXISTS orders', []);
            console.log('drop table orders if exists');
            // user_id INTEGER NOT NULL REFERENCES user (id) ON DELETE CASCADE ON UPDATE CASCADE,
            txn.executeSql(
              `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);` 
                  ,[]
            );
            console.log('create table orders if NOT exist');
          }
        }
      );  // end txn.executeSql

      // category - 產品類型
txn.executeSql(
  "SELECT name FROM sqlite_master WHERE type='table' AND name='category'",
  [],
  (txn, res) => {
    console.log(res);
    console.log('item:', res.rows.length);
    // txn.executeSql('DROP TABLE IF EXISTS category', []);

    if (res.rows.length == 0) {
      console.log('Table category does not exist. Creating...');
    txn.executeSql('DROP TABLE IF EXISTS category', []);
    // id      INTEGER    PRIMARY KEY AUTOINCREMENT,
    // CONSTRAINT category_name_uk UNIQUE (cat_name)
    txn.executeSql(
        `create table if not exists  category (
          id      INTEGER  PRIMARY KEY AUTOINCREMENT ,
          cat_name    VARCHAR (20)  NOT NULL,
          cat_num  VARCHAR (6)  NOT NULL
          );`,
        [],
        () => {
          console.log('Table category created successfully!');
        },
        (_, err) => {
          console.log('Error creating for table category:', err);
        }
      );
    } else {
      console.log('Table category already exists.');
    }  // end else len not 0
  },   // end tx,res
  (_, err) => {
    console.log('Error checking for table category:', err);
  }
);  // end txn.executeSql
// summmmmmmmmmmmmmmmmmmmmmmm

// summmmmmmmmmmmmmmmmmmmmmmm
// product - 產品名稱
txn.executeSql(
  "SELECT name FROM sqlite_master WHERE type='table' AND name='product'",
  [],
  (txn, res) => {
    console.log(res);
    console.log('item:', res.rows.length);
    // txn.executeSql('DROP TABLE IF EXISTS product', []);

    // 2023-05-16 BEGIN
    // Windows 10 sqlite3 works ALTER TABLE product RENAME COLUMN, BUT not here
    // console.log('ALTER TABLE product RENAME id TO product_id. Work in progress...');

    // txn.executeSql("ALTER TABLE `product` RENAME COLUMN `id` TO `product_id`;",
    //   [],
    //   (tx,res) => {
    //     console.log('Table product RENAME COLUMN successfully!');
    //   },
    //   (_, err) => {
    //     console.log('Error RENAME COLUMN for table product:', err);
    //     // do NOT know how to fix : Error RENAME COLUMN for table product: [Error: near "COLUMN": syntax error (code 1 SQLITE_ERROR): , while compiling: ALTER TABLE product RENAME COLUMN id TO product_id;]
    //   }
    // );  // end txn.executeSql
    // // 2023-05-16 END

    if (res.rows.length == 0) {
      console.log('Table product does not exist. Creating...');
      // FOREIGN KEY(category_id) REFERENCES category(id)
      txn.executeSql('DROP TABLE IF EXISTS product', []);
      txn.executeSql(
        `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
          );`,
        [],
        () => {
          console.log('Table product created successfully!');
        },
        (_, err) => {
          console.log('Error creating for table product:', err);
        }
      );
    } else {
      console.log('Table product already exists.');
    }  // end else len not 0
  },   // end tx,res
  (_, err) => {
    console.log('Error checking for table product:', err);
  }
);  // end txn.executeSql


// Package master and detail
txn.executeSql (
  "SELECT name FROM sqlite_master WHERE type='table' AND name='pkg_master'",
  [],
  (txn, res) => {
    console.log(res);
    console.log('item:', res.rows.length);
    // txn.executeSql('DROP TABLE IF EXISTS pkg_master', []);

    if (res.rows.length == 0) {
      txn.executeSql('DROP TABLE IF EXISTS pkg_master', []);
      console.log('drop table pkg_master if exists');
      console.log('Table pkg_master does not exist. Creating...');
      txn.executeSql(
        `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)
          );`,
        [],
        () => {
          console.log('Table pkg_master created successfully!');
        },
        (_, err) => {
          console.log('Error creating for table pkg_master:', err);
        }
      );
    } else {
      console.log('Table pkg_master already exists.');
    }  // end else len not 0
  },   // end tx,res
  (_, err) => {
    console.log('Error checking for table pkg_master:', err);
  }
);  // end txn.executeSql

txn.executeSql (
  "SELECT name FROM sqlite_master WHERE type='table' AND name='pkg_detail'",
  [],
  (txn, res) => {
    console.log(res);
    console.log('item:', res.rows.length);
    // txn.executeSql('DROP TABLE IF EXISTS pkg_detail', []);

    if (res.rows.length == 0) {
      txn.executeSql('DROP TABLE IF EXISTS pkg_detail', []);
      console.log('drop table pkg_detail if exists');
      console.log('Table pkg_detail does not exist. Creating...');
      // FOREIGN KEY (pkg_master_id) REFERENCES pkg_master(pkg_master_id) ON DELETE CASCADE          );`,
      txn.executeSql(
        `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
          );`,
        [],
        () => {
          console.log('Table pkg_detail created successfully!');
        },
        (_, err) => {
          console.log('Error creating table pkg_detail:', err);
        }
      );
    } else {
      console.log('Table pkg_detail already exists.');
    }
  },
  (_, err) => {
    console.log('Error checking for table pkg_detail:', err);
  }
);
// });

txn.executeSql (
  "SELECT name FROM sqlite_master WHERE type='table' AND name='pkg_master_in_cart'",
  [],
  (txn, res) => {
    console.log(res);
    console.log('item:', res.rows.length);
    // txn.executeSql('DROP TABLE IF EXISTS pkg_master_in_cart', []);


    if (res.rows.length == 0) {
    txn.executeSql('DROP TABLE IF EXISTS pkg_master_in_cart', []);
    console.log('Table pkg_master_in_cart does not exist. Creating...');
      txn.executeSql(
        `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
          );`,
        [],
        () => {
          console.log('Table pkg_master_in_cart created successfully!');
        },
        (_, err) => {
          console.log('Error creating for table pkg_master_in_cart:', err);
        }
      );
    } else {
      console.log('Table pkg_master_in_cart already exists.');
    }  // end else len not 0
  },   // end tx,res
  (_, err) => {
    console.log('Error checking for table pkg_master_in_cart:', err);
  }
);  // end txn.executeSql

txn.executeSql (
  "SELECT name FROM sqlite_master WHERE type='table' AND name='pkg_detail_in_cart'",
  [],
  (txn, res) => {
    console.log(res);
    console.log('item:', res.rows.length);
    // txn.executeSql('DROP TABLE IF EXISTS pkg_detail_in_cart', []);

    // XXX old table FOREIGN KEY (pkg_master_id) REFERENCES pkg_master_in_cart(pkg_master_id) ON DELETE CASCADE          );`,
    if (res.rows.length == 0) {
    txn.executeSql('DROP TABLE IF EXISTS pkg_detail_in_cart', []);
    console.log('Table pkg_detail_in_cart does not exist. Creating...');
    // FOREIGN KEY (cart_id) REFERENCES pkg_master_in_cart(cart_id) ON DELETE CASCADE          );`,
    txn.executeSql(
        `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
          );`,
        [],
        () => {
          console.log('Table pkg_detail_in_cart created successfully!');
        },
        (_, err) => {
          console.log('Error creating table pkg_detail_in_cart:', err);
        }
      );
    } else {
      console.log('Table pkg_detail_in_cart already exists.');
    }
  },
  (_, err) => {
    console.log('Error checking for table pkg_detail_in_cart:', err);
  }
);
// }); end txn.db

txn.executeSql (
  "SELECT name FROM sqlite_master WHERE type='table' AND name='order_items'",
  [],
  (txn, res) => {
    console.log(res);
    console.log('item:', res.rows.length);
    // txn.executeSql('DROP TABLE IF EXISTS order_items', []);
    // txn.executeSql('delete from order_items;', [],()=>{console.log('order_items deleted')},   (_,err)=>{console.log(err)});

    if (res.rows.length == 0) {
      txn.executeSql('DROP TABLE IF EXISTS order_items', []);
      console.log('drop table order_items if exists');
      console.log('Table order_items does not exist. Creating...');
      // order_id INTEGER REFERENCES "orders" (id) ON DELETE CASCADE ON UPDATE CASCADE NOT NULL,
      txn.executeSql(
        `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);`,
        [],
        () => {
          console.log('Table order_items created successfully!');
        },
        (_, err) => {
          console.log('Error creating for table order_items:', err);
        }
      );
    } else {
      console.log('Table order_items already exists.');
    }  // end else len not 0
  },   // end tx,res
  (_, err) => {
    console.log('Error checking for table order_items:', err);
  }
);  // end txn.executeSql


});  // end db.transaction

  }, []);   // end useEffect

  async function view_file(){
    let a =await FileSystem.readDirectoryAsync(FileSystem.documentDirectory + 'SQLite')
    console.log(a)

    // try {
    //   const response = await FileSystem.uploadAsync(`./`, FileSystem.documentDirectory + 'SQLite/bruce_shop_v3.db', {
    //     fieldName: 'file',
    //     httpMethod: 'PATCH',
    //     uploadType: FileSystem.FileSystemUploadType.BINARY_CONTENT,
    //   });
    //   console.log(JSON.stringify(response, null, 4));
    // } catch (error) {
    //   console.log(error);
    // }


  }  // end func view_file

  return (
    <NavigationContainer>
      <MyContext.Provider value={{state, dispatch}}>
          <Stack.Navigator initialRouteName="Home"  >
          <Stack.Screen name="HomeTab" component={HomeTab}   options={{headerShown: false}}/>
          <Stack.Screen name="HomeDetail" component={HomeDetail}   options={{headerShown: true,title: '單點' }}/>
          <Stack.Screen name="AddProduct" component={AddProduct}   options={{headerShown: true,title: '新增產品' }}/>
          <Stack.Screen name="AddPackage" component={AddPackage}   options={{headerShown: true,title: '新增套餐' }}/>
          <Stack.Screen name="ListAllTables" component={ListAllTables}   options={{headerShown: true,title: 'List All Tables' }}/>
          <Stack.Screen name="BackupDB" component={BackupDB}   options={{headerShown: true,title: 'Backup The Data' }}/>
          <Stack.Screen name="RestoreDB" component={RestoreDB}   options={{headerShown: true,title: 'Restore All Tables' }}/>
          <Stack.Screen name="EditAllTables" component={EditAllTables}   options={{headerShown: true,title: 'Edit Table Data' }}/>
          <Stack.Screen name="EditTableDetail" component={EditTableDetail}   options={{headerShown: true,title: 'Edit All Tables' }}/>
          <Stack.Screen name="OrderByPackage" component={OrderByPackage}   options={{headerShown: false}}/>
          <Stack.Screen name="OrderByPkgDetail" component={OrderByPkgDetail}   options={{headerShown: true,title: '套餐點餐' }}/>
          <Stack.Screen name="nn_Scanner" component={Scanner}   options={{headerShown: true,title: '掃描 GO Text 二維碼/QR Code' }}/>
          <Stack.Screen name="AddType" component={AddType}   options={{headerShown: true,title: '新增類型',headerStyle: {
            // backgroundColor: 'tomato',
            color:'white'
            }}}/>
          <Stack.Screen name="EditType" component={EditType}   options={{headerShown: true,title: '編輯類型' }}/>
          <Stack.Screen name="EditProduct" component={EditProduct}   options={{headerShown: true,title: '編輯產品' }}/>
          <Stack.Screen name="EditPackage" component={EditPackage}   options={{headerShown: true,title: '編輯套餐' }}/>
          <Stack.Screen name="Signup" component={Signup}   options={{headerShown: true}}/>
          <Stack.Screen name="Login" component={Login}   options={{headerShown: true}}/>
        </Stack.Navigator>
      </MyContext.Provider>

    </NavigationContainer>
  ); // end return
} // end func App

const styles = StyleSheet.create({
  container: {
    flex: 1,
    backgroundColor: '#fff',
    alignItems: 'center',
    justifyContent: 'center',
  },
});  // end const styles