package it.integry.integrywmsnative.core.data_store.db;

import android.content.Context;

import androidx.annotation.NonNull;
import androidx.room.Database;
import androidx.room.Room;
import androidx.room.RoomDatabase;
import androidx.room.TypeConverters;
import androidx.room.migration.Migration;
import androidx.sqlite.db.SupportSQLiteDatabase;

import it.integry.integrywmsnative.core.data_store.db.converter.AppDatabaseConverters;
import it.integry.integrywmsnative.core.data_store.db.converter.BigDecimalConverter;
import it.integry.integrywmsnative.core.data_store.db.converter.DateConverter;
import it.integry.integrywmsnative.core.data_store.db.dao.ArticoloGrigliaDao;
import it.integry.integrywmsnative.core.data_store.db.dao.ArticoloOrdineDao;
import it.integry.integrywmsnative.core.data_store.db.dao.GrigliaDao;
import it.integry.integrywmsnative.core.data_store.db.dao.InventarioDao;
import it.integry.integrywmsnative.core.data_store.db.dao.InventarioRowDao;
import it.integry.integrywmsnative.core.data_store.db.dao.MtbColrDao;
import it.integry.integrywmsnative.core.data_store.db.dao.MtbColtDao;
import it.integry.integrywmsnative.core.data_store.db.dao.OrdineDao;
import it.integry.integrywmsnative.core.data_store.db.dao.RawDao;
import it.integry.integrywmsnative.core.data_store.db.entity.ArticoloGriglia;
import it.integry.integrywmsnative.core.data_store.db.entity.ArticoloOrdine;
import it.integry.integrywmsnative.core.data_store.db.entity.Griglia;
import it.integry.integrywmsnative.core.data_store.db.entity.InventarioRoomDTO;
import it.integry.integrywmsnative.core.data_store.db.entity.InventarioRowRoomDTO;
import it.integry.integrywmsnative.core.data_store.db.entity.Ordine;
import it.integry.integrywmsnative.core.data_store.db.entity.SqlMtbColr;
import it.integry.integrywmsnative.core.data_store.db.entity.SqlMtbColt;

@Database(entities = {
        ArticoloGriglia.class,
        Griglia.class,
        Ordine.class,
        ArticoloOrdine.class,
        SqlMtbColt.class,
        SqlMtbColr.class,
        InventarioRoomDTO.class,
        InventarioRowRoomDTO.class
},
        version = 17)
@TypeConverters({
        DateConverter.class,
        BigDecimalConverter.class,
        AppDatabaseConverters.class
})
public abstract class AppDatabase extends RoomDatabase {

    /**
     * The only instance
     */
    private static AppDatabase sInstance;

    /**
     * Gets the singleton instance of SampleDatabase.
     *
     * @param applicationContext The context.
     * @return The singleton instance of SampleDatabase.
     */
    public static synchronized AppDatabase getInstance(Context applicationContext) {
        if (sInstance == null) {
            var builder = Room.databaseBuilder(applicationContext,
                            AppDatabase.class, "integry_wms")
                    .addMigrations(MIGRATION_10_11)
                    .addMigrations(MIGRATION_11_12)
                    .addMigrations(MIGRATION_12_13)
                    .addMigrations(MIGRATION_13_14)
                    .addMigrations(MIGRATION_14_15)
                    .addMigrations(MIGRATION_15_16)
                    .addMigrations(MIGRATION_16_17);

            sInstance = builder.build();
        }
        return sInstance;
    }

    public abstract RawDao rawDao();

    public abstract ArticoloGrigliaDao articoloGrigliaDao();

    public abstract GrigliaDao grigliaDao();

    public abstract OrdineDao ordineDao();

    public abstract ArticoloOrdineDao articoloOrdineDao();

    public abstract MtbColtDao mtbColtDao();

    public abstract MtbColrDao mtbColrDao();

    public abstract InventarioDao inventarioDao();

    public abstract InventarioRowDao inventarioRowDao();



    static final Migration MIGRATION_10_11 = new Migration(10, 11) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {
            database.execSQL("CREATE TABLE IF NOT EXISTS inventari (_id INTEGER PRIMARY KEY AUTOINCREMENT, id_inventario INTEGER, cod_mdep TEXT NOT NULL, zona TEXT, data_inventario INTEGER, data_reg INTEGER, data_ver INTEGER, filtro TEXT, flag_stato TEXT, flag_operazione TEXT, cod_anag TEXT, cod_dtip TEXT, inserito_da TEXT, registrato_da TEXT, verificato_da TEXT, data_ora_inizio INTEGER, data_ora_fine INTEGER, causale TEXT, remote_sync_date INTEGER)");
            database.execSQL("CREATE INDEX IF NOT EXISTS index_inventari__id ON inventari (_id)");
            database.execSQL("CREATE INDEX IF NOT EXISTS index_inventari_id_inventario ON inventari (id_inventario)");


            database.execSQL("CREATE TABLE IF NOT EXISTS inventario_rows (_id INTEGER PRIMARY KEY AUTOINCREMENT, parent_id INTEGER, cod_mart TEXT NOT NULL, descrizione TEXT, partita_mag TEXT, qta REAL NOT NULL DEFAULT 0, num_cnf REAL NOT NULL DEFAULT 0, qta_cnf REAL NOT NULL DEFAULT 0, unt_mis TEXT NOT NULL DEFAULT '0', data_ora_inv INTEGER, scan_cod_barre TEXT, zona TEXT, remote_sync_date INTEGER, FOREIGN KEY(parent_id) REFERENCES inventari(_id) ON UPDATE NO ACTION ON DELETE NO ACTION )");
            database.execSQL("CREATE INDEX IF NOT EXISTS index_inventario_rows__id ON inventario_rows (_id)");
            database.execSQL("CREATE INDEX IF NOT EXISTS index_inventario_rows__parent_id ON inventario_rows (parent_id)");
        }
    };

    static final Migration MIGRATION_11_12 = new Migration(11, 12) {
        @Override
        public void migrate(SupportSQLiteDatabase database) {
            database.execSQL("DROP TABLE IF EXISTS ordini_tmp;");
            database.execSQL("CREATE TABLE ordini_tmp (ordine_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, data_ins INTEGER, annotazioni TEXT, transmitted INTEGER NOT NULL, transmission_date INTEGER, gestione TEXT, data_ord INTEGER, num_ord INTEGER, cod_mdep TEXT, id_griglia INTEGER NOT NULL, cod_alis TEXT NOT NULL);");
            database.execSQL("INSERT INTO ordini_tmp(ordine_id,data_ins,annotazioni,transmitted,transmission_date,gestione,data_ord,num_ord,cod_mdep,id_griglia,cod_alis) SELECT ordini.ordine_id,ordini.data_ins,ordini.annotazioni,ordini.transmitted,ordini.transmission_date,ordini.gestione,ordini.data_ord,ordini.num_ord,ordini.cod_mdep,ordini.id_griglia,griglie.cod_alis from ordini left outer join griglie on ordini.id_griglia = griglie.griglia_id;");
            database.execSQL("DROP TABLE ordini;");
            database.execSQL("ALTER TABLE ordini_tmp RENAME TO ordini;");
        }
    };

    static final Migration MIGRATION_12_13 = new Migration(12, 13) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            database.execSQL("CREATE TABLE IF NOT EXISTS inventario_rows_tmp (_id INTEGER PRIMARY KEY AUTOINCREMENT, parent_id INTEGER, cod_mart TEXT, descrizione TEXT, partita_mag TEXT, qta REAL NOT NULL DEFAULT 0, num_cnf REAL NOT NULL DEFAULT 0, qta_cnf REAL NOT NULL DEFAULT 0, unt_mis TEXT NOT NULL DEFAULT '0', data_ora_inv INTEGER, scan_cod_barre TEXT, zona TEXT, remote_sync_date INTEGER, FOREIGN KEY(parent_id) REFERENCES inventari(_id) ON UPDATE NO ACTION ON DELETE NO ACTION )");
            database.execSQL("INSERT INTO inventario_rows_tmp SELECT * FROM inventario_rows");
            database.execSQL("DROP TABLE inventario_rows;");
            database.execSQL("ALTER TABLE inventario_rows_tmp RENAME TO inventario_rows;");
            database.execSQL("CREATE INDEX IF NOT EXISTS index_inventario_rows__id ON inventario_rows (_id)");
            database.execSQL("CREATE INDEX IF NOT EXISTS index_inventario_rows__parent_id ON inventario_rows (parent_id)");
        }
    };

    static final Migration MIGRATION_13_14 = new Migration(13, 14) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            database.execSQL("ALTER TABLE inventari"
                    + " ADD COLUMN new INTEGER");
        }
    };

    static final Migration MIGRATION_14_15 = new Migration(14, 15) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            // rimuovo vincoli "not null" dai campi id_griglia e cod_alis
            database.execSQL("DROP TABLE IF EXISTS ordini_tmp;");
            database.execSQL("CREATE TABLE ordini_tmp (ordine_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, data_ins INTEGER, annotazioni TEXT, transmitted INTEGER NOT NULL, transmission_date INTEGER, gestione TEXT, data_ord INTEGER, num_ord INTEGER, cod_mdep TEXT, id_griglia INTEGER, cod_alis TEXT);");
            database.execSQL("INSERT INTO ordini_tmp(ordine_id,data_ins,annotazioni,transmitted,transmission_date,gestione,data_ord,num_ord,cod_mdep,id_griglia,cod_alis) SELECT ordini.ordine_id,ordini.data_ins,ordini.annotazioni,ordini.transmitted,ordini.transmission_date,ordini.gestione,ordini.data_ord,ordini.num_ord,ordini.cod_mdep,ordini.id_griglia,griglie.cod_alis from ordini left outer join griglie on ordini.id_griglia = griglie.griglia_id;");
            database.execSQL("DROP TABLE ordini;");
            database.execSQL("ALTER TABLE ordini_tmp RENAME TO ordini;");
            // aggiungo campo cod_alis sulle righe dell'ordine
            database.execSQL("DROP TABLE IF EXISTS art_ord_tmp;");
            database.execSQL("CREATE TABLE IF NOT EXISTS art_ord_tmp( articolo_ordine_id INTEGER PRIMARY KEY AUTOINCREMENT, cod_mart TEXT, descrizione TEXT, unt_mis TEXT, qta_cnf REAL NOT NULL, bar_code TEXT, merce_da_ric REAL NOT NULL, media_sett REAL NOT NULL, flag_qta_multipla TEXT, qta_min_ordinabile REAL NOT NULL, id_ordine INTEGER NOT NULL, qta_ord REAL NOT NULL, data_ins INTEGER, new_no_promo INTEGER NOT NULL, system_note TEXT, cod_alis TEXT , FOREIGN KEY(id_ordine) REFERENCES ordini(ordine_id) ON UPDATE NO ACTION ON DELETE CASCADE );");
            database.execSQL("INSERT INTO art_ord_tmp(articolo_ordine_id, cod_mart, descrizione, unt_mis, qta_cnf, bar_code, merce_da_ric, media_sett, flag_qta_multipla, qta_min_ordinabile, id_ordine, qta_ord, data_ins, new_no_promo, system_note, cod_alis) SELECT articolo_ordine_id, cod_mart, descrizione, unt_mis, qta_cnf, bar_code, merce_da_ric, media_sett, flag_qta_multipla, qta_min_ordinabile, id_ordine, qta_ord, data_ins, new_no_promo, system_note, null from articoli_ordine;");
            database.execSQL("DROP TABLE articoli_ordine;");
            database.execSQL("ALTER TABLE art_ord_tmp RENAME TO articoli_ordine;");
            database.execSQL("CREATE INDEX IF NOT EXISTS index_articoli_ordine_id_ordine ON articoli_ordine (id_ordine);");
            database.execSQL("CREATE UNIQUE INDEX IF NOT EXISTS index_articoli_ordine_id_ordine_cod_mart ON articoli_ordine (id_ordine, cod_mart);");
        }
    };
    static final Migration MIGRATION_15_16 = new Migration(15, 16) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
        }
    };
    static final Migration MIGRATION_16_17 = new Migration(16, 17) {
        @Override
        public void migrate(@NonNull SupportSQLiteDatabase database) {
            database.execSQL("ALTER TABLE inventario_rows RENAME TO _inventario_rows_old;");
            database.execSQL("DROP INDEX index_inventario_rows__id;");
            database.execSQL("DROP INDEX index_inventario_rows_parent_id;");

            database.execSQL("CREATE TABLE inventario_rows (_id INTEGER PRIMARY KEY AUTOINCREMENT, parent_id INTEGER, cod_mart TEXT, partita_mag TEXT, descrizione TEXT, qta REAL NOT NULL DEFAULT 0, num_cnf REAL NOT NULL DEFAULT 0, qta_cnf REAL NOT NULL DEFAULT 0, unt_mis TEXT NOT NULL DEFAULT '0', data_ora_inv INTEGER, scan_cod_barre TEXT, zona TEXT, remote_sync_date INTEGER, FOREIGN KEY(parent_id) REFERENCES inventari(_id) ON UPDATE NO ACTION ON DELETE CASCADE);");

            database.execSQL("INSERT INTO inventario_rows SELECT * FROM _inventario_rows_old;");

            database.execSQL("CREATE INDEX index_inventario_rows__id ON inventario_rows (_id);");
            database.execSQL("CREATE INDEX index_inventario_rows_parent_id ON inventario_rows (parent_id)");
            database.execSQL("DROP TABLE _inventario_rows_old;");
        }
    };
}
