//------------- Product.kt ----------------------

class Product {
    var id: Int = 0
    var productName: String? = null
    var quantity: Int = 0

    constructor(id: Int, productName: String, quantity: Int) {
        this.id = id
        this.productName = productName
        this.quantity = quantity
    }

    constructor(productName: String, quantity: Int) {
        this.productName = productName
        this.quantity = quantity
    }
}

//---------------- DBHandler.kt -------------------

import android.content.ContentValues
import android.content.Context
import android.database.sqlite.SQLiteDatabase
import android.database.sqlite.SQLiteOpenHelper
import androidx.annotation.IntegerRes

class DBHandler (context: Context): SQLiteOpenHelper(context, DATABASE_NAME, null, DATABASE_VERSION){

    var context: Context? = null

    init {
        this.context = context
    }

    companion object {
        private val DATABASE_VERSION = 1
        private val DATABASE_NAME = "productDB.db"
        val TABLE_PRODUCTS = "products"

        // Declaring columns name
        val COLUMN_ID = "_id"
        val COLUMN_PRODUCTNAME = "productname"
        val COLUMN_QUANTITY = "quantity"

        val CREATE_PRODUCTS_TABLE = "CREATE TABLE IF NOT EXISTS $TABLE_PRODUCTS ($COLUMN_ID INTEGER PRIMARY KEY, " +
                "$COLUMN_PRODUCTNAME TEXT, $COLUMN_QUANTITY INTEGER)"
        val SELECT_PRODUCT = "SELECT * FROM $TABLE_PRODUCTS"
    }
    override fun onCreate(db: SQLiteDatabase) {
        db.execSQL(CREATE_PRODUCTS_TABLE)
    }

    override fun onUpgrade(db: SQLiteDatabase?, oldVersion: Int, newVersion: Int) {

    }

    fun addProduct(product: Product) {
        val values = ContentValues()
        values.put(COLUMN_PRODUCTNAME, product.productName)
        values.put(COLUMN_QUANTITY, product.quantity)

        val db = this.writableDatabase
        db.insert(TABLE_PRODUCTS, null, values)
        db.close()
    }

    fun findProduct(productName: String): Product? {
        val query = "$SELECT_PRODUCT WHERE $COLUMN_PRODUCTNAME = \"$productName\""
        val db = this.readableDatabase
        val cursor = db.rawQuery(query, null)
        var product: Product? = null

        if(cursor.moveToFirst()) {
            cursor.moveToFirst()
            val id = Integer.parseInt(cursor.getString(0))
            val name = cursor.getString(1)
            val quantity = Integer.parseInt(cursor.getString(2))

            product = Product(id, name, quantity)
            cursor.close()
        }
        db.close()
        return product
    }

    fun getAllProducts(): ArrayList<Product> {
        var products: ArrayList<Product> = ArrayList()
        var cursor = readableDatabase.rawQuery(SELECT_PRODUCT, null)
        if(cursor.moveToFirst()) {
            while(!cursor.isAfterLast) {
                val product = Product(
                    Integer.parseInt(cursor.getString(0)),
                    cursor.getString(1),
                    Integer.parseInt(cursor.getString(2))
                )
                products.add(product)
                cursor.moveToNext()
            }
        }
        cursor.close()
        return products
    }

    fun deleteProduct(productName: String): Boolean {
        var result = false
        val query = "$SELECT_PRODUCT WHERE $COLUMN_PRODUCTNAME = \"$productName\""
        val db = this.writableDatabase
        val cursor = db.rawQuery(query, null)

        if(cursor.moveToFirst()) {
            //cursor.moveToFirst()
            val id = Integer.parseInt(cursor.getString(0))
            db.delete(TABLE_PRODUCTS, "$COLUMN_ID = ?", arrayOf(id.toString()))
            cursor.close()
            result = true
        }
        db.close()
        return result
    }

    fun deleteAll() {
        val db = this.writableDatabase
        var cursor = db.rawQuery(SELECT_PRODUCT, null)
        if(cursor.moveToFirst()) {
            while(!cursor.isAfterLast) {
                val id = Integer.parseInt(cursor.getString(0))
                db.delete(TABLE_PRODUCTS, "$COLUMN_ID = ?", arrayOf(id.toString()))
                cursor.moveToNext()
            }
        }
        cursor.close()
    }
}

//-------------------- MainActivity.kt ----------------------

import androidx.appcompat.app.AppCompatActivity
import android.os.Bundle
import android.widget.ArrayAdapter
import kotlinx.android.synthetic.main.activity_main.*

class MainActivity : AppCompatActivity() {

    val dbHandler: DBHandler = DBHandler(this)

    lateinit var stringsList: ArrayList<String>

    override fun onCreate(savedInstanceState: Bundle?) {
        super.onCreate(savedInstanceState)
        setContentView(R.layout.activity_main)
        setPointer()
    }

    private fun setPointer() {
        addProductsToDB()
        setAdapter()
    }

    private fun setAdapter() {
        val products = dbHandler.getAllProducts()
        stringsList = ArrayList()
        for (product in products) {
            stringsList.add("${product.id}   ${product.productName}   ${product.quantity}")
        }
        products_list.adapter = ArrayAdapter(this, android.R.layout.simple_list_item_1, stringsList)
    }

    private fun addProductsToDB() {
        dbHandler.deleteAll()
        dbHandler.addProduct(Product(0, "table", 5))
        dbHandler.addProduct(Product(1, "Chair", 20))
        dbHandler.addProduct(Product(2, "Tablespoon", 200))
        dbHandler.addProduct((Product(3, "Fork", 230)))
        dbHandler.addProduct(Product(4, "Knife", 180))
    }
}

//------------------ activity_main.xml ---------------------

<?xml version="1.0" encoding="utf-8"?>
<androidx.constraintlayout.widget.ConstraintLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:app="http://schemas.android.com/apk/res-auto"
    xmlns:tools="http://schemas.android.com/tools"
    android:id="@+id/productsList"
    android:layout_width="match_parent"
    android:layout_height="match_parent"
    tools:context=".MainActivity">

    <TextView
        android:id="@+id/textView"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="Products"
        android:textSize="24sp"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintLeft_toLeftOf="parent"
        app:layout_constraintRight_toRightOf="parent"
        app:layout_constraintTop_toTopOf="parent"
        app:layout_constraintVertical_bias="0.095" />

    <ListView
        android:id="@+id/products_list"
        android:layout_width="359dp"
        android:layout_height="431dp"
        android:layout_marginStart="20dp"
        android:layout_marginTop="30dp"
        android:layout_marginEnd="20dp"
        android:layout_marginBottom="30dp"
        app:layout_constraintBottom_toBottomOf="parent"
        app:layout_constraintEnd_toEndOf="parent"
        app:layout_constraintStart_toStartOf="parent"
        app:layout_constraintTop_toBottomOf="@+id/textView" />

</androidx.constraintlayout.widget.ConstraintLayout>