//usage
// Initialize the SQLite wrapper
$dbPath = 'your_database.db';
$db = new SQLiteWrapper($dbPath);

// Select from a table
$result = $db->select('your_table', '*', 'id = :id', 'name ASC');
$result = $db->select('your_table', '*', 'id = :id', 'name ASC', [':id' => $id]);

// Insert into a table
$insertData = ['name' => 'John', 'age' => 30];
$db->insert('your_table', $insertData);

// Update records in a table
$updateData = ['age' => 31];
$db->update('your_table', $updateData, 'name = :name');

// Delete records from a table
$db->delete('your_table', 'age < :age');

// Close the database connection
$db->close();



//post

// Assuming you have a form like this in your HTML
// <form method="POST" action="process_form.php">
//     <input type="text" name="name">
//     <input type="text" name="age">
//     <input type="submit" name="submit">
// </form>

if ($_SERVER['REQUEST_METHOD'] === 'POST') {
    // Remove the "submit" field from the POST data
    unset($_POST['submit']);
    
    // Initialize the SQLite wrapper
    $dbPath = 'your_database.db';
    $db = new SQLiteWrapper($dbPath);

    // Insert into a table from the POST data
    $insertData = $_POST;
    $db->insert('your_table', $insertData);

    // Update records in a table from the POST data
    $updateData = $_POST;
    $db->update('your_table', $updateData, 'name = :name');

    // Close the database connection
    $db->close();
}
class SQLiteWrapper
{
    private $pdo;

    public function __construct($dbPath)
    {
        try {
            $this->pdo = new PDO('sqlite:' . $dbPath);
            $this->pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        } catch (PDOException $e) {
            die("Error connecting to the database: " . $e->getMessage());
        }

        // Chama a função para criar as tabelas ao inicializar
        $this->createTables();
    }

    public function select($table, $columns = '*', $where = '', $orderBy = '', $params = [])
    {
        $query = "SELECT $columns FROM $table";
        if (!empty($where)) {
            $query .= " WHERE $where";
        }
        if (!empty($orderBy)) {
            $query .= " ORDER BY $orderBy";
        }

        $stmt = $this->pdo->prepare($query);
        $stmt->execute($params);
        return $stmt->fetchAll(PDO::FETCH_ASSOC);
    }

    public function insert($table, $data)
    {
        $columns = implode(", ", array_keys($data));
        $placeholders = ":" . implode(", :", array_keys($data));
        $query = "INSERT INTO $table ($columns) VALUES ($placeholders)";

        $stmt = $this->pdo->prepare($query);
        $stmt->execute($data);
    }

    public function update($table, $data, $where, $params = [])
    {
        $set = "";
        foreach ($data as $column => $value) {
            $set .= "$column = :$column, ";
        }
        $set = rtrim($set, ", ");

        $query = "UPDATE $table SET $set WHERE $where";

        $stmt = $this->pdo->prepare($query);
        $stmt->execute(array_merge($data, $params));
    }

    public function delete($table, $where, $params = [])
    {
        $query = "DELETE FROM $table WHERE $where";

        $stmt = $this->pdo->prepare($query);
        $stmt->execute($params);
    }

    // Função para executar comandos SQL diretos
    public function execute($query)
    {
        $this->pdo->exec($query);
    }

    // Função para criar as tabelas necessárias no banco de dados
    private function createTables()
    {
        // Criação da tabela DNS
        $createDnsTable = "
            CREATE TABLE IF NOT EXISTS dns (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                url TEXT NOT NULL
            );
        ";

        // Criação da tabela Playlist
        $createPlaylistTable = "
            CREATE TABLE IF NOT EXISTS playlist (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                username TEXT NOT NULL,
                password TEXT NOT NULL,
                mac_address TEXT NOT NULL,
                pin TEXT NOT NULL,
                dns_id INTEGER NOT NULL,
                FOREIGN KEY (dns_id) REFERENCES dns(id)
            );
        ";

        // Executa os comandos para criar as tabelas
        $this->execute($createDnsTable);
        $this->execute($createPlaylistTable);
    }

    public function close()
    {
        $this->pdo = null;
    }
}
