import {ISPC_DB_NAME} from '@src/constants/Constants';
import {RW3AlternativePriceReason} from '@src/services/models/RW3AlternativePriceReason';
import React, {useContext, useEffect, useState} from 'react';
import SQLite from 'react-native-sqlite-storage';

export const DatabaseContext = React.createContext<
  SQLite.SQLiteDatabase | undefined
>(undefined);

export const DatabaseProvider: React.FC = ({children}) => {
  const [db, setDb] = useState<SQLite.SQLiteDatabase>();

  useEffect(() => {
    const initDatabase = async () => {
      try {
        const database = await SQLite.openDatabase({
          name: ISPC_DB_NAME,
          location: 'default',
          version: 1,
        } as any);
        console.log('Database opened successfully');

        const createAllTables = `
          CREATE TABLE IF NOT EXISTS AlternativePriceReason (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            activeDefault INTEGER , name TEXT , tertiaryDefault INTEGER
          );

          CREATE TABLE IF NOT EXISTS Brand (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
             name TEXT
          );

          CREATE TABLE IF NOT EXISTS Category (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            expectedDirectedItemsCount INTEGER , expectedUndirectedItemsCount INTEGER , fullCheck BOOLEAN , isAssigned BOOLEAN , localNotFoundDirectedItemsCount INTEGER , localScannedDirectedItemsCount INTEGER , localScannedUndirectedItemsCount INTEGER , name TEXT , priceCheckType INTEGER , serverNotFoundDirectedItemsCount INTEGER , serverScannedDirectedItemsCount INTEGER , serverScannedUndirectedItemsCount INTEGER
          );

          CREATE TABLE IF NOT EXISTS DataCollectionField (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
             name TEXT , tabOrder INTEGER
          );

          CREATE TABLE IF NOT EXISTS FeatureUsage (
            continuousScanMode BOOLEAN , linePriceMode BOOLEAN , shortcutKeyUsed BOOLEAN
          );

          CREATE TABLE IF NOT EXISTS Item (
            activeCount INTEGER , activePrice DOUBLE , shortcutKeyUsed BOOLEAN , anomalyIDs INTEGER , brand TEXT , brandID INTEGER , changed BOOLEAN , collected BOOLEAN , comment TEXT , isActivePastPrice BOOLEAN , isDirected BOOLEAN , isEditMode BOOLEAN , isNewItem BOOLEAN , isOriginal BOOLEAN , isPrivateLabel BOOLEAN , isRegularPastPrice BOOLEAN , isTertiaryPastPrice BOOLEAN , itemDescriptionChanged  BOOLEAN , name TEXT , notFound  BOOLEAN , pack INTEGER , pastActiveCount INTEGER , pastActivePrice DOUBLE , pastPricePerLb BOOLEAN , pastRegularCount INTEGER , pastRegularPrice DOUBLE , pastScanDate DATE , pastTertiaryCount INTEGER ,
            pastTertiaryPrice DOUBLE , pricePerLb  BOOLEAN , regularCount INTEGER , regularPrice DOUBLE , scanDate DATE , scanned BOOLEAN , size DECIMAL , sortOrder INTEGER , symbologyID INTEGER , tertiaryCount INTEGER , tertiaryPrice DOUBLE , uomID INTEGER , upcDisplayed TEXT , upcPadded TEXT , upcUserInputted TEXT
          );

          CREATE TABLE IF NOT EXISTS ItemDescription (
            brand TEXT , brandID INTEGER , isPrivateLabel BOOLEAN , name TEXT , pack INTEGER , size DECIMAL , uomID INTEGER , upc TEXT
          );

          CREATE TABLE IF NOT EXISTS ItemDescriptionsMeta (
            timestamp DATE
          );

          CREATE TABLE IF NOT EXISTS ItemScan (
            peopleID INTEGER , priceCheckID INTEGER , scanDate DATE , storeID INTEGER , subcategoryID  INTEGER , symbologyID INTEGER , upcPadded TEXT , upcUserInputted TEXT
          );

          CREATE TABLE IF NOT EXISTS Note (
            changed BOOLEAN , text TEXT
          );

          CREATE TABLE IF NOT EXISTS PastPriceItem (
            activeCount INTEGER , activePrice DOUBLE , pricePerLb BOOLEAN , regularCount INTEGER , regularPrice DOUBLE , scanDate DATE , tertiaryCount  INTEGER , tertiaryPrice DOUBLE , upcPadded TEXT
          );

          CREATE TABLE IF NOT EXISTS Photo (
            changed BOOLEAN , timestamp DATE , image BLOB , thumbnail BLOB
          );

          CREATE TABLE IF NOT EXISTS PriceCheck (
            id INTEGER PRIMARY KEY AUTOINCREMENT ,  autoSetNewItem BOOLEAN , categoriesNeedsRefreshed BOOLEAN , disableActivePrice BOOLEAN , disableTertiaryPrice BOOLEAN , endDate DATE , hasScrapeData BOOLEAN , maxPriceInput DOUBLE , message TEXT , name TEXT , showPastPrice BOOLEAN , showPrivateLabelFlagInApp BOOLEAN , startDate DATE , subcategoriesNeedsRefreshed BOOLEAN , type INTEGER
          );

          CREATE TABLE IF NOT EXISTS PriceCheckAttachment (
            modifiedDate DATE , url TEXT
          );

          CREATE TABLE IF NOT EXISTS Settings (
            helpText TEXT , inactiveTimeout DOUBLE , linePricingDisabled BOOLEAN , logLevel INTEGER , maxPriceInput DOUBLE , regularActiveVariancePercentage DOUBLE , regularTertiaryVariancePercentage DOUBLE , storesCount INTEGER
          );

          CREATE TABLE IF NOT EXISTS SpentTimeRecord (
            categoryID DOUBLE , enterDate DATE , leaveDate DATE , pageID TEXT , storeID DOUBLE , subcategoryID DOUBLE
          );

          CREATE TABLE IF NOT EXISTS Store (
            id INTEGER PRIMARY KEY AUTOINCREMENT, city TEXT , latitude DOUBLE , longitude DOUBLE , name TEXT , phone TEXT , postalCode TEXT , state TEXT , street TEXT , unrestrictedScanAccount  BOOLEAN
          );

          CREATE TABLE IF NOT EXISTS Subcategory (
            id INTEGER PRIMARY KEY AUTOINCREMENT, acceptableRangePriceMax DOUBLE , acceptableRangePriceMin DOUBLE , expectedDirectedItemsCount INTEGER , expectedUndirectedItemsCount INTEGER , fullCheck BOOLEAN ,
            isAssigned BOOLEAN , isUndirectedPriceCheckCompleted BOOLEAN , isUploading BOOLEAN , localNotFoundDirectedItemsCount INTEGER , localScannedDirectedItemsCount INTEGER , localScannedUndirectedItemsCount INTEGER ,
            name TEXT , newItemPhotoPromptExclusion BOOLEAN , pastPriceVariancePercent DOUBLE , priceCheckType INTEGER , serverNotFoundDirectedItemsCount INTEGER , serverScannedDirectedItemsCount INTEGER , serverScannedUndirectedItemsCount INTEGER , shortcutKey1Text TEXT , shortcutKey2Text TEXT , shortcutKey3Text TEXT
          );

          CREATE TABLE IF NOT EXISTS UnitOfMeasure (
            id INTEGER PRIMARY KEY AUTOINCREMENT, abbreviation TEXT , name TEXT
          );

          CREATE TABLE IF NOT EXISTS ValidationPrompt (
            userAction TEXT , validationText TEXT
          );




        `;
        await database.transaction(tx => tx.executeSql(createAllTables));
        console.log('Tables created successfully');

        setDb(database);
      } catch (error) {
        console.log(error);
      }
    };

    initDatabase();
  }, []);

  const insertAlternativePriceReason = async (
    alternativePriceReason: RW3AlternativePriceReason,
  ) => {
    try {
      const result = await db.executeSql(
        'INSERT INTO AlternativePriceReason (id, name, activeDefault, tertiaryDefault) VALUES (?, ?, ?, ?)',
        [
          alternativePriceReason.id,
          alternativePriceReason.name,
          alternativePriceReason.activeDefault,
          alternativePriceReason.tertiaryDefault,
        ],
      );
      console.log('Alternative Price Reason inserted successfully', result);
    } catch (error) {
      console.log('Error:', error);
    }
  };

  return (
    <DatabaseContext.Provider value={db}>{children}</DatabaseContext.Provider>
  );
};