dbInitialization.js

import SQLite from 'react-native-sqlite-storage';
console.log('in dbInitialization.js');
// Open or create the SQLite database
const db = SQLite.openDatabase(
  {name: 'mydatabase.db', location: 'default'},
  () => {
    console.log('Database opened successfully');
  },
  error => {
    console.error('Error opening database', error);
  },
);

const initializeDatabase = async () => {
  console.log('in initializeDatabase');
  console.log('creating tables');
  // Drop the existing users table before updating it. might be better to have a migrations file
  db.transaction(tx => {
    // Drop the existing users table
    tx.executeSql(
      `DROP TABLE IF EXISTS users`,
      [],
      () => {
        console.log('Old users table dropped successfully.');
      },
      (_, error) => {
        console.error('Error dropping table', error);
      },
    );
  });
    // Drop the existing curriculum table before updating it. might be better to have a migrations file
    db.transaction(tx => {
      // Drop the existing curriculum table
      tx.executeSql(
        `DROP TABLE IF EXISTS curriculum`,
        [],
        () => {
          console.log('Old curriculum table dropped successfully.');
        },
        (_, error) => {
          console.error('Error dropping table', error);
        },
      );
    });

//     tx.executeSql(
//       `CREATE TABLE IF NOT EXISTS UserSettingsv3 (
//         id INTEGER PRIMARY KEY AUTOINCREMENT,
//         userId INTEGER,
//         featureA INTEGER DEFAULT 1 CHECK(featureA BETWEEN 0 AND 1),
//         featureB INTEGER DEFAULT 1 CHECK(featureB BETWEEN 0 AND 1),
//         featureC INTEGER DEFAULT 1 CHECK(featureC BETWEEN 0 AND 1),
//         featureD INTEGER DEFAULT 1 CHECK(featureD BETWEEN 0 AND 1),
//         featureE INTEGER DEFAULT 1 CHECK(featureE BETWEEN 0 AND 1),
//         featureF INTEGER DEFAULT 1 CHECK(featureF BETWEEN 0 AND 1),
//         featureG INTEGER DEFAULT 1 CHECK(featureG BETWEEN 0 AND 1),
//         featureH INTEGER DEFAULT 1 CHECK(featureH BETWEEN 0 AND 1),
//         FOREIGN KEY (userId) REFERENCES Users(id)


//       )`,
//       [],
//       () => {
//         console.log('usersettingsv3 Table created successfully - in dbInitialization.');
//       },

//       (_, error) => {
//         console.error('Error creating UserSettingsv3 table or exists or or or or or', error);
//       },
//     );

//     tx.executeSql(`
//     CREATE TRIGGER IF NOT EXISTS create_default_settings
//     AFTER INSERT ON Users
//     BEGIN
//         INSERT INTO UserSettingsv3 (userId)
//         VALUES (NEW.id);

//     END;
// `,
//       [],
//       () => {
//         console.log('users trigger created successfuly - in dbInitialization.');
//       },
//       (_, error) => {
//         console.error('Error creating trigger', error);
//       },
// );
// });

    // Drop the existing achievements table before updating it. might be better to have a migrations file
    db.transaction(tx => {
      // Drop the existing achievements table
      tx.executeSql(
        `DROP TABLE IF EXISTS achievements`,
        [],
        () => {
          console.log('Old achievements table dropped successfully.');
        },
        (_, error) => {
          console.error('Error dropping table', error);
        }
      );
    });

    // function to create tables
  const createTable = (query, tableName) => {
    return new Promise((resolve, reject) => {
      db.transaction(tx => {
        tx.executeSql(
          query,
          [],
          () => {
            console.log(`${tableName} table created successfully.`);
            resolve();
          },
          (tx, error) => {
            console.error(`Error creating ${tableName} table`, error);
            reject(error);
          },
        );
      });
    });
  };

  const addColumnIfTableExists = (tableName, columnName, columnDefinition) => {
    return new Promise((resolve, reject) => {
      console.log(
        `Checking if column '${columnName}' exists in '${tableName}' table...`,
      );
      db.transaction(tx => {
        // Query the table schema to check if the column exists
        tx.executeSql(
          `PRAGMA table_info(${tableName});`, // This returns info about the columns in the table
          [],
          (_, result) => {
            // Check if the column exists
            const columnExists = Array.from({length: result.rows.length}).some(
              (_, i) => result.rows.item(i).name === columnName,
            );

            if (columnExists) {
              console.log(
                `Column '${columnName}' already exists in '${tableName}' table. No changes needed.`,
              );
              resolve(); // Resolve as no need to add the column
            } else {
              console.log(
                `Column '${columnName}' does not exist in '${tableName}' table. Adding column...`,
              );
              // Add the column if it doesn't exist
              tx.executeSql(
                `ALTER TABLE ${tableName} ADD COLUMN ${columnDefinition};`,
                [],
                () => {
                  console.log(
                    `Column '${columnName}' added to '${tableName}' table successfully.`,
                  );
                  resolve(); // Resolve after successfully adding the column
                },
                (tx, error) => {
                  // Handle any errors that occur while adding the column
                  console.error(
                    `Error adding column '${columnName}' to '${tableName}' table:`,
                    error?.message || 'Unknown error',
                  );
                  reject(error);
                },
              );
            }
          },
          (tx, error) => {
            // Handle any errors that occur while querying the table schema
            console.error(
              `Error querying table info for '${tableName}':`,
              error?.message || 'Unknown error',
            );
            reject(error);
          },
        );
      });
    });
  };

  // Create the tables if they don't exist
  const imgdpTableQuery = `
    CREATE TABLE IF NOT EXISTS imgdp (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      b64str TEXT,
      input INTEGER,
      output INTEGER
    )`;

  const curriculumTableQuery = `
    CREATE TABLE IF NOT EXISTS curriculum (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      input_output INTEGER,
      sequence INTEGER,
      content TEXT,
      completed INTEGER DEFAULT 0,
      score INTEGER DEFAULT 0,
      correct_answer TEXT,
      difficulty TEXT DEFAULT 'medium',
      choices TEXT
    )`;

  const curriculumImagesTableQuery = `
      CREATE TABLE IF NOT EXISTS curriculumImages (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        base64 TEXT NOT NULL
    )`;

  const usersTableQuery = `
    CREATE TABLE IF NOT EXISTS users (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT,
      picture TEXT,
      estimatedAttentionSpan INTEGER,
      settingsChoices TEXT,
      progressInCurriculum INTEGER,
      averageAccuracy INTEGER,
      description TEXT,
      necessaryBreakTime INTEGER
    )`;

  const achievementsTableQuery = `
    CREATE TABLE IF NOT EXISTS achievements (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT,
      description TEXT,
      picture TEXT,
      points INTEGER,
      user_id INTEGER,
      FOREIGN KEY (user_id) REFERENCES users(id)
    )`;

  const userSettingsTableQuery = `
    CREATE TABLE IF NOT EXISTS UserSettingsv3 (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      userId INTEGER,
      featureA INTEGER DEFAULT 1 CHECK(featureA BETWEEN 0 AND 1),
      featureB INTEGER DEFAULT 1 CHECK(featureB BETWEEN 0 AND 1),
      featureC INTEGER DEFAULT 1 CHECK(featureC BETWEEN 0 AND 1),
      featureD INTEGER DEFAULT 1 CHECK(featureD BETWEEN 0 AND 1),
      featureE INTEGER DEFAULT 1 CHECK(featureE BETWEEN 0 AND 1),
      featureF INTEGER DEFAULT 1 CHECK(featureF BETWEEN 0 AND 1),
      featureG INTEGER DEFAULT 1 CHECK(featureG BETWEEN 0 AND 1),
      featureH INTEGER DEFAULT 1 CHECK(featureH BETWEEN 0 AND 1),
      FOREIGN KEY (userId) REFERENCES users(id)
    )`;

    // note: the emotionalStateDuringResponse is a string representation of an object with the emotional state of the user during the response as the key and the confidence level (response accuracy) as the value.
    const responseTableQuery = `
    CREATE TABLE IF NOT EXISTS response (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      user_id INTEGER,
      curriculum_id INTEGER,
      responseTime DATETIME,
      emotionalStateDuringResponse TEXT,
      FOREIGN KEY (user_id) REFERENCES users(id),
      FOREIGN KEY (curriculum_id) REFERENCES curriculum(id)
    )`;

  // Create the tables using the queries above and the createTable function
  return Promise.all([
    createTable(imgdpTableQuery, 'imgdp'),
    createTable(curriculumTableQuery, 'curriculum'),
    createTable(curriculumImagesTableQuery, 'curriculumImages'),
    createTable(usersTableQuery, 'users'),
    createTable(achievementsTableQuery, 'achievements'),
    createTable(userSettingsTableQuery, 'UserSettingsv3'),
    createTable(responseTableQuery, 'response'),
  ])
    .then(() => {
      console.log('All tables created successfully.');
      return addColumnIfTableExists(
        'curriculum',
        'completed',
        'completed INTEGER DEFAULT 0',
      );
      //    return addColumnIfTableExists('curriculum', 'score', 'score INTEGER DEFAULT 0');
    })
    .catch(error => {
      console.error('Error initializing database', error);
      throw error;
    });
};

function dropTrigger(triggerName) {
  db.transaction(tx => {
      tx.executeSql(
          `DROP TRIGGER IF EXISTS ${triggerName};`,
          [],
          () => {
              console.log(`Trigger '${triggerName}' dropped successfully.`);
          },
          error => {
              console.error(`Error dropping trigger '${triggerName}':`, error);
          }
      );
  });
}
// CRUD operations for the imgdp table
// Insert a new row into the imgdp table
const insertImageData = async (b64str, input, output) => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'INSERT INTO imgdp (b64str, input, output) VALUES (?, ?, ?)',
        [b64str, input, output],
        (_, result) => {
          console.log(`A row has been inserted with rowid ${result.insertId}`);
          resolve(result);
        },
        (tx, error) => {
          console.error('Error inserting data', error);
          reject(error);
        },
      );
    });
  });
};

// Retrieve all rows from the imgdp table
const getImageData = () => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'SELECT * FROM imgdp',
        [],
        (_, result) => {
          resolve(result.rows.raw());
        },
        (tx, error) => {
          reject(error);
        },
      );
    });
  });
};

// Update a row in the imgdp table
const updateImageData = (b64str, id) => {
  db.transaction(tx => {
    tx.executeSql(
      'UPDATE imgdp SET b64str = ? WHERE id = ?',
      [b64str, id],
      (_, result) => {
        console.log(`Row(s) updated: ${result.rowsAffected}`);
      },
      (tx, error) => {
        console.error('Error updating data', error);
      },
    );
  });
};

// Delete a row from the imgdp table
const deleteImageData = id => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'DELETE FROM imgdp WHERE id = ?',
        [id],
        (_, result) => {
          console.log(`Row(s) deleted: ${result.rowsAffected}`);
          resolve(result);
        },
        (tx, error) => {
          console.error('Error deleting data', error);
          reject(error);
        },
      );
    });
  });
};
// Function to print the first row of the imgdp table
const printFirstRow = () => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'SELECT * FROM imgdp LIMIT 1',
        [],
        (_, {rows}) => {
          if (rows.length > 0) {
            console.log('First row data:', rows.item(0));
            resolve(rows.item(0));
          } else {
            console.log('No data found.');
            resolve(null);
          }
        },
        (tx, error) => {
          console.error('Error querying data', error);
          reject(error);
        },
      );
    });
  });
};


//Insert a new row into the curriculum table
const insertCurriculumData = (input_output, sequence, content) => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'INSERT INTO curriculum (input_output, sequence, content) VALUES (?, ?, ?)',
        [input_output, sequence, content],
        (_, result) => {
          console.log(`A row has been inserted with rowid ${result.insertId}`);
          resolve(result);
        },
        (tx, error) => {
          console.error('Error inserting data', error);
          reject(error);
        },
      );
    });
  });
};
//Insert a new row into answers table
const insertAnswerData = (user_id, prompt, writing_response) => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'INSERT INTO answers (user_id, prompt, writing_response) VALUES(?, ?, ?)',
        [user_id, prompt, writing_response],
        (_, result) => {
          console.log(`A row has been inserted with rowid ${result.insertId}`);
          resolve(result);
        },
        (tx, error) => {
          console.error('Error inserting data', error);
          reject(error);
        },
      );
    });
  });
};

const insertCurriculumDataWithImage = async (input_output, sequence, content) => {
  try {
    let uriContent = content;

    // Ensure content is an object and check for base64 image
    if (typeof content === 'object' && content.image && content.image.startsWith('data:image/')) {
      const base64Image = content.image;  // Extract the base64 image string
      const imageId = await createCurriculumImage(base64Image);  // Store image and get its ID
      uriContent = { ...content, image: `image://${imageId}` };  // Create URI and update content object
      console.log("uriContent", uriContent);
    }

    // Serialize the content object to a JSON string
    const serializedContent = JSON.stringify(uriContent);

    const result = await insertCurriculumData(input_output, sequence, serializedContent);
    console.log(`Inserted curriculum with row ID ${result.insertId}`);
    return result;
  } catch (error) {
    console.error('Error inserting curriculum data with image', error);
    throw error;
  }
};

// Retrieve all rows from the curriculum table for testing
const getAllCurriculumData = () => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'SELECT * FROM curriculum',
        [],
        (_, result) => {
          resolve(result.rows.raw());
        },
        (tx, error) => {
          console.error('Error fetching all curriculum data:', error);
          reject(error);
        },
      );
    });
  });
};

const printCurriculumFirstRow = () => {
  return new Promise((resolve, reject) => {

  db.transaction(tx => {
    tx.executeSql(
      'SELECT * FROM curriculum LIMIT 1',
      [],
      (_, { rows }) => {
        if (rows.length > 0) {
          console.log('First row data:', rows.item(0));
          resolve(rows.item(0));
        } else {
          console.log('No data found.');
          resolve(null);
        }
      },
      (tx, error) => { console.error('Error querying data', error);
        reject(error);
      }
    );
  });
});
};

//  CRUD operations for the curriculumImages table
// Create function to store base64 data in curriculumImages
function createCurriculumImage(base64String) {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'INSERT INTO curriculumImages (base64) VALUES (?);',
        [base64String],
        (_, { insertId }) => resolve(insertId),  // Return the new image's ID
        (_, error) => reject(error)
      );
    });
  });
}

// Read function to retrieve base64 data by ID
const getCurriculumImageById = (id) =>{
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'SELECT base64 FROM curriculumImages WHERE id = ?;',
        [id],
        (_, { rows }) => {
          if (rows.length > 0) {
            const base64 = rows.item(0).base64;
            console.log(`Base64 string for ID ${id}: ${base64}`);
            resolve(base64);
          } else {
            console.log(`No image found for ID ${id}`);
            resolve(null);
          }
        },
        (_, error) => {
          console.error(`Error retrieving image for ID ${id}`, error);
          reject(error);
        }
      );
    });
  });
}

// Function to retrieve a curriculum image by ID
const retrieveCurriculumImageFromUri = (uri) => {
  console.log('URI:', uri);

  const imageId = uri.split('://')[1];
  console.log('Image ID:', imageId);
  return getCurriculumImageById(imageId);
}

// CRUD operations for the users table
  // Insert a new row into the users table
  const insertUser = (
    name,
    picture,
    estimatedAttentionSpan,
    settingsChoices,
    progressInCurriculum,
    averageAccuracy,
    description,
    necessaryBreakTime,
  ) => {
    return new Promise((resolve, reject) => {
      db.transaction(
        tx => {
          tx.executeSql(
            'INSERT INTO users (name, picture, estimatedAttentionSpan, settingsChoices, progressInCurriculum, averageAccuracy, description, necessaryBreakTime) VALUES (?, ?, ?, ?, ?, ?, ?, ?)',
            [
              name,
              picture,
              estimatedAttentionSpan,
              settingsChoices,
              progressInCurriculum,
              averageAccuracy,
              description,
              necessaryBreakTime,
            ],
            (_, result) => {
              console.log(
                `A row has been inserted in the user table with rowid ${result.insertId} and name ${name}`,
              );
              resolve(result);
            },
            (_, error) => {
              console.error('Error inserting user data', error.message);
              reject(error);
            },
          );
        },
      );
    },
    error => {
      console.error('Transaction error:', error.message); //
    },
    () => {
      console.log('Transaction completed successfully');
    },
  );
};


// Retrieve all rows from the users table
const getUsers = () => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        `SELECT * FROM Users`,
        [],
        (_, result) => {
          const users = result.rows.raw();
          resolve(users);
        },
        (_, error) => { reject(error); }
      );
    });
  });
};

//Create Settings Tablee in db
// const createSettingsTable = () => {
//   db.transaction(tx => {
//     tx.executeSql(
//       `CREATE TABLE IF NOT EXISTS UserSettings (
//         id INTEGER PRIMARY KEY AUTOINCREMENT,
//         featureA NUMBER DEFAULT 1,
//         featureB NUMBER DEFUALT 1,
//         featureC NUMBER DEFUALT 1,
//         featureD NUMBER DEFAULT 1,
//         featureE NUMBER DEFAULT 1,
//         featureF NUMBER DEFAULT 1,
//         featureG NUMBER DEFAULT 1,
//         featureH NUMBER DEFAULT 1,

//       )`,
//       [],
//       () => {
//         console.log('Settings Table created successfully - in dbInitialization.');
//       },

//       (_, error) => {
//         console.error('Error creating table or exists', error);
//       },
//     );
//   });
// };

// Function to update user settings
const dropTable = (tableName) => {
  db.transaction(tx => {
    tx.executeSql(
      `DROP TABLE IF EXISTS ${tableName}`,
      [],
      (tx, results) => {
        console.log(`Table ${tableName} dropped successfully.`);
      },
      (tx, error) => {
        console.error(`Error dropping table ${tableName}:`, error);
      }
    );
  });
};

// Call the function with the table name you want to drop
// dropTable('UserSettings');

// is this chaning the true and false into 1 and zero

const updateUserSettings = (userId, settings) => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        `UPDATE UserSettingsv3 SET
        featureA = ?,
        featureB = ?,
        featureC = ?,
        featureD = ?,
        featureE = ?,
        featureF = ?,
        featureG = ?,
        featureH = ?
        WHERE userId = ?`,
        [
          settings.featureA ? 1 : 0,
          settings.featureB ? 1 : 0,
          settings.featureC ? 1 : 0,
          settings.featureD ? 1 : 0,
          settings.featureE ? 1 : 0,
          settings.featureF ? 1 : 0,
          settings.featureG ? 1 : 0,
          settings.featureH ? 1 : 0,
          userId
        ],
        (_, result) => {
          resolve(result);
        },
        (_, error) => {
          reject(error);
        }
      );
    });
  });
};

const updateUserSettings2 = (userId, settings) => {
  const booleanToInteger = value => (value === true ? 1 : 0);
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(`
        INSERT INTO UserSettingsv3 (userId, featureA, featureB, featureC, featureD, featureE, featureF, featureG, featureH)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
        ON CONFLICT(userId) DO UPDATE SET
          featureA=excluded.featureA,
          featureB=excluded.featureB,
          featureC=excluded.featureC,
          featureD=excluded.featureD,
          featureE=excluded.featureE,
          featureF=excluded.featureF,
          featureG=excluded.featureG,
          featureH=excluded.featureH
      `, [
        userId,
        booleanToInteger(settings.featureA),
        booleanToInteger(settings.featureB),
        booleanToInteger(settings.featureC),
        booleanToInteger(settings.featureD),
        booleanToInteger(settings.featureE),
        booleanToInteger(settings.featureF),
        booleanToInteger(settings.featureG),
        booleanToInteger(settings.featureH)
      ], (tx, results) => {
        resolve(results);
      }, (tx, error) => {
        reject(error);
      });
    });
  });
};


// Function to retrieve user settings
const getUserSettings=(userId)=> {
  return new Promise((resolve, reject) => {
      db.transaction(tx => {
          tx.executeSql(
              `SELECT * FROM UserSettingsv3 WHERE userId = ?;`,
              [userId],
              (tx, results) => {
                  const rows = results.rows;
                  let userSettings = [];
                  for (let i = 0; i < rows.length; i++) {
                      userSettings.push(rows.item(i));
                  }
                  resolve(userSettings); // Resolve the promise with the user settings
              },
              error => {
                  reject(error); // Reject the promise with an error
              }
          );
      });
  });
}

const getAllUserSettings = () => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        `SELECT * FROM UserSettingsv3`,
        [],
        (_, result) => { resolve(result.rows.raw()); },

        (_, error) => { reject(error); }
      );
    });
  });
};

const getOneUser = (id) => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'SELECT * FROM users WHERE id = ?',
        [id],
        (_, result) => {
          const user = result.rows.raw();
          console.log('User:', user);
          resolve(user);
        },
        (_, error) => { reject(error); }
      );
    });
  });
};

// Update a row in the users table
const updateUser = (id, updates) => {
  const fields = [];
  const values = [];

  if (updates.name !== undefined) {
    fields.push('name = ?');
    values.push(updates.name);
  }
  if (updates.picture !== undefined) {
    fields.push('picture = ?');
    values.push(updates.picture);
  }
  if (updates.estimatedAttentionSpan !== undefined) {
    fields.push('estimatedAttentionSpan = ?');
    values.push(updates.estimatedAttentionSpan);
  }
  if (updates.settingsChoices !== undefined) {
    fields.push('settingsChoices = ?');
    values.push(updates.settingsChoices);
  }
  if (updates.progressInCurriculum !== undefined) {
    fields.push('progressInCurriculum = ?');
    values.push(updates.progressInCurriculum);
  }
  if (updates.averageAccuracy !== undefined) {
    fields.push('averageAccuracy = ?');
    values.push(updates.averageAccuracy);
  }
  if (updates.description !== undefined) {
    fields.push('description = ?');
    values.push(updates.description);
  }
  if (updates.necessaryBreakTime !== undefined) {
    fields.push('necessaryBreakTime = ?');
    values.push(updates.necessaryBreakTime);
  }

  if (fields.length === 0) {
    console.log('No fields to update');
    return;
  }

  values.push(id);

  const query = `UPDATE users SET ${fields.join(', ')} WHERE id = ?`;

  db.transaction(tx => {
    tx.executeSql(
      query,
      values,
      (_, result) => { console.log(`Row(s) updated: ${result.rowsAffected}`); },
      (_, error) => { console.error('Error updating data', error); }
    );
  });
};

  // delete a row from the users table
  const deleteUser = (id) => {
    db.transaction(tx => {
      tx.executeSql(
        'DELETE FROM users WHERE id = ?',
        [id],
        (_, result) => { console.log(`Row(s) deleted: ${result.rowsAffected}`); },
        (_, error) => { console.error('Error deleting data', error); }
      );
    });
  };

  //  RL tables
  export const setupRLTables = async (db) => {
    return new Promise((resolve, reject) => {
      db.transaction(tx => {
        // DROP OLD UCB TABLE IF EXISTS
        tx.executeSql(
          `DROP TABLE IF EXISTS Combos`,
          [],
          () => console.log("Dropped old Combos table"),
          (_, err) => {
            console.error("Failed to drop Combos table:", err);
            return true;
          }
        );
  
        // CREATE RL COMBOS TABLE
        tx.executeSql(
          `CREATE TABLE IF NOT EXISTS Combos (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id INTEGER NOT NULL,
            state TEXT NOT NULL,
            action TEXT NOT NULL,
            q_value REAL DEFAULT 0,
            last_updated DATETIME DEFAULT CURRENT_TIMESTAMP,
            UNIQUE(student_id, state, action)
          )`,
          [],
          () => console.log("✅ RL Combos table created"),
          (_, err) => {
            console.error("Combos table create error:", err);
            return true;
          }
        );
  
        // CREATE INTERACTION LOGS TABLE
        tx.executeSql(
          `CREATE TABLE IF NOT EXISTS InteractionLogs (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            student_id INTEGER NOT NULL,
            state TEXT NOT NULL,
            action TEXT NOT NULL,
            reward INTEGER NOT NULL,
            is_correct BOOLEAN,
            time_taken REAL,
            emotion TEXT,
            hints_used INTEGER,
            timestamp DATETIME DEFAULT CURRENT_TIMESTAMP
          )`,
          [],
          () => console.log("✅ InteractionLogs table created"),
          (_, err) => {
            console.error("Logs table create error:", err);
            return true;
          }
        );
      }, reject, resolve);
    });
  };
  

// CRUD operations for the achievements table
// Insert a new row into the achievements table
const insertAchievement = (name, description, points, user_id) => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'INSERT INTO achievements (name, description, points, user_id) VALUES (?, ?, ?, ?)',
        [name, description, points, user_id],
        (_, result) => {
          console.log(`A row has been inserted with rowid ${result.insertId}`);
          resolve(result);
        },
        (_, error) => {
          console.error('Error inserting data', error);
          reject(error);
        },
      );
    });
  });
};

// update a row in the achievements table
const updateAchievement = (name, description, points, user_id) => {
  db.transaction(tx => {
    tx.executeSql(
      'UPDATE achievements SET name = ?, description = ?, points = ? WHERE user_id = ?',
      [name, description, points, user_id],
      (_, result) => {
        console.log(`Row(s) updated: ${result.rowsAffected}`);
      },
      (_, error) => {
        console.error('Error updating data', error);
      },
    );
  });
};

// Retrieve all rows from the achievements table
const allUserAchievements = (id) => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'SELECT * FROM achievements WHERE user_id = ?',
        [id],
        (_, result) => {
          resolve(result.rows.raw());
        },
        (_, error) => {
          reject(error);
        },
      );
    });
  });
};

// delete a row from the achievements table
const deleteAchievement = (id) => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'DELETE FROM achievements WHERE id = ?',
        [id],
        (_, result) => {
          console.log(`Row(s) deleted: ${result.rowsAffected}`);
          resolve(result);
        },
        (_, error) => {
          console.error('Error deleting data', error);
          reject(error);
        },
      );
    });
  });
};

// CRUD operations for the response table
// Insert a new row into the response table
const insertResponse = (user_id, curriculum_id, responseTime, emotionalStateDuringResponse) => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'INSERT INTO response (user_id, curriculum_id, responseTime, emotionalStateDuringResponse) VALUES (?, ?, ?, ?)',
        [user_id, curriculum_id, responseTime.toISOString(), JSON.stringify(emotionalStateDuringResponse)],
        (_, result) => {
          console.log(`A row has been inserted into the response table with rowid ${result.insertId}`);
          resolve(result);
        },
        (_, error) => {
          console.error('Error inserting data', error);
          reject(error);
        },
      );
    });
  });
};

// Retrieve all rows from the response table
const getResponses = () => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'SELECT * FROM response',
        [],
        (_, result) => {
          resolve(result.rows.raw());
        },
        (_, error) => {
          reject(error);
        }
      );
    });
  });
};

// Retrieve a single row from the response table
const getOneResponse = (user_id, curriculum_id) => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'SELECT * FROM response WHERE user_id = ? AND curriculum_id = ?',
        [user_id, curriculum_id],
        (_, result) => {
          resolve(result.rows.raw());
        },
        (_, error) => {
          reject(error);
        }
      );
    });
  });
};

// Update a row in the response table
const updateResponse = (user_id, curriculum_id, responseTime, emotionalStateDuringResponse) => {
  db.transaction(tx => {
    tx.executeSql(
      'UPDATE response SET responseTime = ?, emotionalStateDuringResponse = ? WHERE user_id = ? AND curriculum_id = ?',
      [responseTime.toISOString(), JSON.stringify(emotionalStateDuringResponse), user_id, curriculum_id],
      (_, result) => {
        console.log(`Row(s) updated: ${result.rowsAffected}`);
      },
      (_, error) => {
        console.error('Error updating data', error);
      },
    );
  });
};

// Delete a row from the response table
const deleteResponse = (user_id, curriculum_id) => {
  return new Promise((resolve, reject) => {
    db.transaction(tx => {
      tx.executeSql(
        'DELETE FROM response WHERE user_id = ? AND curriculum_id = ?',
        [user_id, curriculum_id],
        (_, result) => {
          console.log(`Row(s) deleted: ${result.rowsAffected}`);
          resolve(result);
        },
        (_, error) => {
          console.error('Error deleting data', error);
          reject(error);
        },
      );
    });
  });
};



// test the functions above
const testDb = async () => {
  console.log('running testDb');
  const allUsers = await getUsers();
  console.log('top user', allUsers[0]);
  console.log('second user', allUsers[1]);

  await insertImageData('test_base64_string', 1, 2);
  await printFirstRow();
  await insertCurriculumData(0, 5, 'Testing curriculum');
  await insertCurriculumDataWithImage(0, 6, { text: 'Testing curriculum with image', image: 'data:image/png;base64,base64_string' });
  await printCurriculumFirstRow();

  // User tests
  // Example call to insertUser with test data
  const testUser = {
    name: 'John Test',
    picture: 'path/to/picture.jpg',
    estimatedAttentionSpan: 30,
    settingsChoices: '{"sound": "low", "brightness": "medium"}',
    progressInCurriculum: 0,
    averageAccuracy: 75.5,
    description: 'A brief description of John Doe.',
    necessaryBreakTime: 15,
  };

  const testUser2 = {
    name: 'Jane Test',
    picture: 'path/to/picture.jpg',
    estimatedAttentionSpan: 30,
    settingsChoices: '{"sound": "low", "brightness": "medium"}',
    progressInCurriculum: 0,
    averageAccuracy: 75.5,
    description: 'A brief description of Jane Doe.',
    necessaryBreakTime: 15,
  };

  console.log('Inserting user:', testUser);
  insertUser(
    testUser.name,
    testUser.picture,
    testUser.estimatedAttentionSpan,
    testUser.settingsChoices,
    testUser.progressInCurriculum,
    testUser.averageAccuracy,
    testUser.description,
    testUser.necessaryBreakTime,
  );

  console.log('Inserting user:', testUser2);
  insertUser(
    testUser2.name,
    testUser2.picture,
    testUser2.estimatedAttentionSpan,
    testUser2.settingsChoices,
    testUser2.progressInCurriculum,
    testUser2.averageAccuracy,
    testUser2.description,
    testUser2.necessaryBreakTime,
  );
  // USER TESTS
  // console.log('Getting one user:');
  // const oneUser = await getOneUser(3);
  // console.log('One user:', oneUser);

  // const allData = await getImageData();
  // console.log('All data:', allData[0]);

  // if (allData.length > 0) {
  //   updateImageData('updated_base64_string', allData[0].id);
  // }
  //  const user1 = await getOneUser(1);
  //   console.log('user1:', user1);
  //   updateUser(1, { name:'Billy Bob' });
  //   console.log('user1 after update:', user1);
  //   const user2 = await getOneUser(2);
  //   updateUser(2, { progressInCurriculum: 20, averageAccuracy: 20, necessaryBreakTime: 20 });
  //   console.log('user2 after update:', user2);
  // other tests
  // delete a user test
  // const userToDelete = await getOneUser(3);
  // console.log('userToDelete:', userToDelete);
  // deleteUser(3);
  // console.log(getOneUser(3), "should be null");

  // INSERTING AN ACHIEVEMENT TO THE ACHIEVEMENTS TABLE TO DISPLAY
  // See logic in Achievements directory for displaying achievements.

  await insertAchievement('Streak', 'day streak', 4, 1);
  await insertAchievement('Hours', 'hours of learning', 20, 1);
  await insertAchievement('Tasks', 'tasks completed', 12, 1);
  console.log('All achievements:', await allUserAchievements(1));

  // curriculumImage tests
// Create a new image
console.log("create curriculum Image");
const newImageId = await createCurriculumImage(
  'iVBORw0KGgoAAAANSUhEUgAAAAoAAAAKCAIAAAACUFjqAAAADUlEQVR4nGNgGAWkAwABNgABVtF/yAAAAABJRU5ErkJggg=='
);
console.log(`Created new image with ID: ${newImageId}`);

 // Retrieve a curriculumImage by ID
console.log("getting curriculum image by ID");
try {
  const base64ById = await getCurriculumImageById(newImageId);
  if (base64ById) {
    console.log(`Image data by ID: ${base64ById}`);
  } else {
    console.log('No image found with the given ID.');
  }
} catch (error) {
  console.error('Error retrieving image by ID:', error);
}
// Retrieve a curriculumImage from a URI
console.log("getting curriculum image from URI");
try {
  const base64ByUri = await retrieveCurriculumImageFromUri(`image://${newImageId}`);
  if (base64ByUri) {
    console.log(`Image data by URI: ${base64ByUri}`);
  } else {
    console.log('No image found with the given URI.');
  }
} catch (error) {
  console.error('Error retrieving image by URI:', error);
}

  // Insert curriculum data with image
console.log("inserting curriculum data with image");
await insertCurriculumDataWithImage(0, 7, {
  text: 'Testing curriculum with image',
  image: 'data:image/png;base64,iVBORw0KGgoAAAANSUhEUgAAAAoAAAAKCAIAAAACUFjqAAAADUlEQVR4nGNgGAWkAwABNgABVtF/yAAAAABJRU5ErkJggg=='
});

// Retrieve all curriculum data
console.log("getting all curriculum data");
const allCurriculum = await getAllCurriculumData();
const lastCurriculum = allCurriculum[allCurriculum.length - 1];

// Parse the content JSON string back into an object
if (lastCurriculum && lastCurriculum.content) {
  lastCurriculum.content = JSON.parse(lastCurriculum.content);
  console.log('Last curriculum data parsed:', lastCurriculum);

  // Retrieve the actual image using the URI from the content field
  try {
    const imageBase64 = await retrieveCurriculumImageFromUri(lastCurriculum.content.image);
    console.log('Retrieved image base64 data:', imageBase64);
  } catch (error) {
    console.error('Error retrieving image from parsed URI:', error);
  }
} else {
  console.log('No curriculum data found.');
}

// response table tests
// Insert a response
console.log("inserting response");
const currentUserId = 1;
const currentCurriculumId = 1;
const currentResponseTime = new Date();
const outputFromModel = { anger:  0.8567321, disgust: 0.1456789, fear: 0.0020342, happy: 0.0000000, neutral: 0.0000000, sad: 0.0000000, surprise: 0.0000000 };
const emotionalStateDuringResponse = JSON.stringify(outputFromModel);
await insertResponse(currentUserId, currentCurriculumId, currentResponseTime, emotionalStateDuringResponse);

// Retrieve all responses
console.log("getting all responses");
const allResponses = await getResponses();
console.log('All responses:', allResponses);

// Retrieve a single response
console.log("getting one response");
const oneResponse = await getOneResponse(currentUserId, currentCurriculumId);
console.log('One response:', oneResponse);

// Update a response
console.log("updating response");
const newResponseTime = new Date();
const newOutputFromModel = { anger: 0.6566674, disgust: 0.0000000, fear: 0.0000000, happy: 0.0000000, neutral: 0.0000000, sad: 0.0000000, surprise: 1.0000000 };

const newEmotionalStateDuringResponse = JSON.stringify(newOutputFromModel);
updateResponse(currentUserId, currentCurriculumId, newResponseTime, newEmotionalStateDuringResponse);

// Delete a response
console.log("deleting response");
deleteResponse(currentUserId, currentCurriculumId);

};

// uncomment to run tests
testDb();

// Export functions
export {
  // createSettingsTable,
  dropTable,
  updateUserSettings,
  getUserSettings,
  getAllUserSettings,
  // setupRLTables, // Removed duplicate export to avoid parsing error
  initializeDatabase,
  insertImageData,
  insertAnswerData,
  getImageData,
  updateImageData,
  deleteImageData,
  printFirstRow,
  insertUser,
  getUsers,
  getOneUser,
  updateUser,
  deleteUser,
  printCurriculumFirstRow,
  getAllCurriculumData,
  insertCurriculumData,
  dropTrigger,
  insertAchievement,
  updateAchievement,
  allUserAchievements,
  deleteAchievement,
  createCurriculumImage,
  getCurriculumImageById,
  retrieveCurriculumImageFromUri,
  insertCurriculumDataWithImage,
  insertResponse,
  getResponses,
  getOneResponse,
  updateResponse,
  deleteResponse,
  testDb,
};