package none.treego;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.widget.ArrayAdapter;
import android.widget.Toast;

import java.util.ArrayList;
import java.util.List;

import none.treego.AddressManager;

public class dbOfflineExtension extends SQLiteOpenHelper{
    public static final String DB_Name = "TreeGO.db";

    //Estados
    public static final String Table_cState = "Estados";
    public static final String Column_cStateID = "ID_Estado";
    public static final String Column_cStateName = "Nome_Estado";
    public static final String Column_cStateInitials= "Siglas";

    //Cidades
    public static final String Table_City = "Cidades";
    public static final String Column_CityID = "ID_Cidade";
    public static final String Column_CityName = "Nome_Cidade";
    public static final String Column_CityStateID = "ID_Estado";

    //Bairros
    public static final String Table_Neighborhood = "Bairros";
    public static final String Column_NeighborhoodID = "ID_Bairro";
    public static final String Column_NeighborhoodName = "Nome_Bairro";
    public static final String Column_NeighborhoodCityID = "ID_Cidade";

    //Ruas
    public static final String Table_Street = "Ruas";
    public static final String Column_StreetID = "ID_Rua";
    public static final String Column_StreetName = "Nome_Rua";
    public static final String Column_StreetNeighborhoodID = "ID_Bairro";
    public static final String Column_StreetCityID = "ID_Cidade";

    Context ctx;
    public dbOfflineExtension(Context ct){
        super(ct, DB_Name, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String SQLCmd;
        SQLCmd = "CREATE TABLE IF NOT EXISTS Estados (" +
                "  ID_Estado INTEGER PRIMARY KEY AUTOINCREMENT," +
                "  Nome_Estado TEXT," +
                "  Siglas TEXT" +
                ");";
        db.execSQL(SQLCmd);
        SQLCmd ="CREATE TABLE IF NOT EXISTS Cidades (" +
                "  ID_Cidade INTEGER PRIMARY KEY AUTOINCREMENT," +
                "  Nome_Cidade TEXT," +
                "  ID_Estado INTEGER" +
                ");";
        db.execSQL(SQLCmd);
        SQLCmd ="CREATE TABLE IF NOT EXISTS Bairros (" +
                "  ID_Bairro INTEGER PRIMARY KEY AUTOINCREMENT," +
                "  Nome_Bairro TEXT," +
                "  ID_Cidade INTEGER" +
                ");";
        db.execSQL(SQLCmd);
        SQLCmd ="CREATE TABLE IF NOT EXISTS Ruas (" +
                "  ID_Rua INTEGER PRIMARY KEY AUTOINCREMENT," +
                "  Nome_Rua TEXT," +
                "  ID_Bairro INTEGER," +
                "  ID_Cidade INTEGER" +
                ");";
        db.execSQL(SQLCmd);
        ContentValues contentValues = new ContentValues();
        AddressManager addressManager = new AddressManager();

        addressManager.addAllcStates();
        SQLCmd = "SELECT * FROM Estados";
        Cursor res = db.rawQuery(SQLCmd, null);
        if (!res.move(1)) {
            for (int i = 0; i < addressManager.getQuantityOf("State"); i++) {
                contentValues.put("Nome_Estado", addressManager.Estados[i].cStateName);
                contentValues.put("Siglas", addressManager.Estados[i].cStateInit);
                db.insert("Estados", null, contentValues);
                contentValues.clear();
            }
        }

        addressManager.addAllCities();
        SQLCmd = "SELECT * FROM Cidades";
        res = db.rawQuery(SQLCmd, null);
        if (!res.move(1)) {
            for (int i = 0; i < addressManager.getQuantityOf("City"); i++) {
                contentValues.put("Nome_Cidade", addressManager.Cidades[i].CityName);
                contentValues.put("ID_Estado", addressManager.Cidades[i].StateID);
                db.insert("Cidades", null, contentValues);
                contentValues.clear();
            }
        }

        addressManager.addAllNeighborhoods();
        SQLCmd = "SELECT * FROM Bairros";
        res = db.rawQuery(SQLCmd, null);
        if (!res.move(1)) {
            for (int i = 0; i < addressManager.getQuantityOf("Neighborhood"); i++) {
                contentValues.put("Nome_Bairro", addressManager.Bairros[i].NeighborhoodName);
                contentValues.put("ID_Cidade", addressManager.Bairros[i].NeighborhoodCityID);
                db.insert("Bairros", null, contentValues);
                contentValues.clear();
            }
        }

        addressManager.addAllStreets();
        SQLCmd = "SELECT * FROM Ruas";
        res = db.rawQuery(SQLCmd, null);
        if (!res.move(1)) {
            for (int i = 0; i < addressManager.getQuantityOf("Street"); i++) {
                contentValues.put("Nome_Rua", addressManager.Ruas[i].StreetName);
                contentValues.put("ID_Bairro", addressManager.Ruas[i].NeighborhoodID);
                contentValues.put("ID_Cidade", addressManager.Ruas[i].CityID);
                db.insert("Ruas", null, contentValues);
                contentValues.clear();
            }
        }
    }

    public Cursor getAllcStates() {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res;
        String SQLCmd = "SELECT * FROM Estados";
        res = db.rawQuery(SQLCmd, null);
        return res;
    }

    public Cursor getAllCities() {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res;
        String SQLCmd = "SELECT * FROM Cidades";
        res = db.rawQuery(SQLCmd, null);
        return res;
    }

    public Cursor getAllNeighborhoods() {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res;
        String SQLCmd = "SELECT * FROM Bairros";
        res = db.rawQuery(SQLCmd, null);
        return res;
    }

    public Cursor getAllStreets() {
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor res;
        String SQLCmd = "SELECT * FROM Ruas";
        res = db.rawQuery(SQLCmd, null);
        return res;
    }

    public List<String> getDataAdapterOf(String InfoToGet) {
        ArrayAdapter<String> dataAdapter;
        List<String> list = new ArrayList<String>();
        SQLiteDatabase db = getWritableDatabase();
        Cursor res = db.rawQuery("", null);
        if (InfoToGet.equals("City")) {
            res = db.rawQuery("SELECT Nome_Bairro FROM Bairros", null);
        }
        res.moveToFirst();
        do {
            list.add(res.getString(0));
        } while (res.moveToNext());
        return list;
    }

    public void InsertAllData() {
        SQLiteDatabase db = this.getWritableDatabase();
    }

    /*public void InsertNeighborHoods() {
        String SQLCmd = "SELECT * FROM Bairros";
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(SQLCmd, null);
        AddressManager addressManager = new AddressManager();
        if (cursor.moveToFirst()) {
            int ID = Integer.parseInt(cursor.getString(0));
            String Name = cursor.getString(1);
            String CityID = cursor.getString(2);
            do {
                addressManager.InsertInto("Neighborhood", ID, Name, CityID);
            } while (cursor.moveToNext());
        }
    }

    public void ShowNeighborhood1() {
        String Bairro = "NULL";
        String SQLCmd = "SELECT * FROM Bairros";
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(SQLCmd, null);
        AddressManager addressManager = new AddressManager();
        if (cursor.moveToFirst()) {
            String Name = cursor.getString(1);
            Bairro = Name;
        }
        Toast.makeText(ctx, Bairro, Toast.LENGTH_LONG).show();
    }*/

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS Estados;" +
                "DROP TABLE IF EXISTS Cidades;" +
                "DROP TABLE IF EXISTS Bairro;" +
                "DROP TABLE IF EXISTS Ruas;");
        onCreate(db);
    }
}