import 'package:drift/drift.dart';
import 'package:ristoris/core/constants.dart';
import 'db_connector.dart';
import 'tables/agenti.dart';
import 'tables/articoli.dart';
import 'tables/clienti.dart';
import 'tables/ordini.dart';
import 'tables/righe_ordine.dart';
import 'tables/filiali.dart';
import 'tables/listini.dart';
import 'tables/listini_articolo.dart';
import 'tables/listini_cliente.dart';
import 'tables/sconti.dart';
import 'tables/sconti_cliente.dart';
import 'tables/pagamenti.dart';
import 'tables/parametri.dart';
import 'tables/tabelle.dart';
import 'tables/promozioni.dart';
import 'tables/promo_articoli.dart';
import 'tables/promo_listini.dart';
import 'tables/promo_omaggi.dart';
import 'tables/clienti_agente.dart';
import 'tables/scadenze.dart';
import 'tables/statistiche_vendita.dart';
import 'views/articoli_con_promo.dart';
import 'views/ordini_senza_righe.dart';
import 'views/top_item.dart';
import 'views/top_customer.dart';
import 'views/v_ordini.dart';
import 'views/v_righe_ordine.dart';
import 'views/v_scadenze.dart';

part 'database.g.dart';

@DriftDatabase(
  tables: [
    Agenti,
    Articoli,
    Clienti,
    Ordini,
    RigheOrdine,
    Filiali,
    Listini,
    ListiniArticolo,
    ListiniCliente,
    Sconti,
    ScontiCliente,
    Pagamenti,
    Parametri,
    Tabelle,
    Promozioni,
    PromoArticoli,
    PromoListini,
    PromoOmaggi,
    ClientiAgente,
    Scadenze,
    StatisticheVendita,
    ArticoliConPromo,
    OrdiniSenzaRighe,
    TopItem,
    TopCustomer,
    VOrdini,
    VRigheOrdine,
    VScadenze,
  ],
)
class AppDatabase extends _$AppDatabase {
  AppDatabase() : super(_createDatabase());

  static String? _dbDirPath;

  static QueryExecutor _createDatabase() {
    return createDb(_dbDirPath ?? '', AppConstants.kDbName);
  }

  static Future<void> ensureDatabaseReady() async {
    _dbDirPath = await ensureDbReady();
  }

  static Future<bool> isDbEmpty() async {
    return isDatabaseEmpty(_dbDirPath ?? '');
  }

  @override
  int get schemaVersion => 11;

  @override
  MigrationStrategy get migration => MigrationStrategy(
        onCreate: (m) async {
          await m.createAll();
        },
        onUpgrade: (m, from, to) async {
          if (from < 2) {
            await m.addColumn(
              statisticheVendita,
              statisticheVendita.codStatistiche,
            );
          }
          if (from < 3) {
            await customStatement(
              'CREATE INDEX IF NOT EXISTS ndx_ro_idordine ON Righe_ordine(idOrdine)',
            );
          }
          if (from < 4) {
            await customStatement(
              'CREATE INDEX IF NOT EXISTS NDX_FILIALE_REMOTO ON Filiali(idFilialeRemoto)',
            );
            await customStatement(
              'CREATE INDEX IF NOT EXISTS NDX_CLI_COD ON Clienti(codCliente)',
            );
            await customStatement(
              'CREATE INDEX IF NOT EXISTS NDX_CLI_REMOTO ON Clienti(idClienteRemoto)',
            );
            await customStatement(
              'CREATE INDEX IF NOT EXISTS NDX_idOrdine_SEDE ON Ordini(idOrdineSede)',
            );
          }
          if (from < 5) {
            await customStatement(
              'CREATE INDEX IF NOT EXISTS NDX_NUMERO_ORDINE ON Ordini(numeroOrdine)',
            );
          }
          if (from < 6) {
            await customStatement(
              'CREATE INDEX IF NOT EXISTS NDX_filiali_cliente ON Filiali(idCliente)',
            );
          }
          if (from < 7) {
            await customStatement(
              'CREATE INDEX IF NOT EXISTS ndx_stat_cliart ON Statistiche_vendita(idCliente, idArticolo)',
            );
            await customStatement(
              'CREATE INDEX IF NOT EXISTS ndx_righeOrdine_promo ON Righe_ordine(idOrdine, rigaPromozione)',
            );
          }
          if (from < 8) {
            await customStatement('DROP VIEW IF EXISTS v_ordini');
            await customStatement('''
          CREATE VIEW v_ordini AS
          SELECT
            a.codAgente,
            c.codCliente,
            c.ragioneSociale,
            f.codFiliale,
            f.descrizione Filiale,
            CASE WHEN o.idFiliale IS NOT NULL THEN f.indirizzo ELSE c.indirizzo END AS indirizzo,
            CASE WHEN o.idFiliale IS NOT NULL THEN f.citta ELSE c.citta END AS citta,
            CASE WHEN o.idFiliale IS NOT NULL THEN f.provincia ELSE c.provincia END AS provincia,
            CASE WHEN o.idFiliale IS NOT NULL THEN f.cap ELSE c.cap END AS cap,
            o.*,
            (SELECT SUM(ro.totale) FROM Righe_ordine ro WHERE ro.idOrdine = o.idOrdine) AS totale
          FROM Ordini o
          JOIN Clienti c ON c.idCliente = o.idCliente
          LEFT JOIN Filiali f ON f.idFiliale = o.idFiliale
          JOIN Agenti a ON a.idAgente = o.idAgente
        ''');
            await customStatement('DROP VIEW IF EXISTS v_righe_ordine');
            await customStatement('''
          CREATE VIEW v_righe_ordine AS
          SELECT ro.*, a.codArticolo, a.descrizione, l.codListino, l.descrizione AS Listino
          FROM Righe_ordine ro
          JOIN Articoli a ON a.idArticolo = ro.idArticolo
          LEFT JOIN Listini l ON l.idListino = ro.idListino
        ''');
          }
          if (from < 9) {
            await m.addColumn(clienti, clienti.creditScoreColor);
            await m.addColumn(clienti, clienti.dataCreditScore);
          }
          if (from < 10) {
            // await m.addColumn(listini, listini.listinoAgente);
          }
          if (from < 11) {
            await customStatement('DROP VIEW IF EXISTS v_scadenze');
            await customStatement('''
          CREATE VIEW v_scadenze AS
          SELECT s.*, c.codCliente, c.ragioneSociale
          FROM Scadenze s
          LEFT JOIN Clienti c ON c.idCliente = s.idCliente
        ''');
          }
        },
      );
}