import 'package:drift/drift.dart';

/*
create view v_ordini as 
select
a.codAgente,
c.codCliente, c.ragioneSociale,
f.codFiliale as codFiliale,
f.descrizione as Filiale,
case when o.idFiliale is null then c.indirizzo else f.indirizzo end as indirizzo,
case when o.idFiliale is null then c.citta else f.citta end as citta,
case when o.idFiliale is null then c.provincia else f.provincia end as provincia,
case when o.idFiliale is null then c.cap else f.cap end as cap,
o.*,
(select sum(totale) from righe_ordine where idOrdine=o.idordine) as totale
from ordini o
join agenti a on a.idagente = o.idAgente 
join clienti c on c.idCliente  = o.idCliente
left join filiali f on f.idfiliale = o.idfiliale

*/
class VOrdini extends Table {
  TextColumn get codAgente => text().named('codAgente')();
  TextColumn get codCliente => text().named('codCliente').nullable()();
  TextColumn get ragioneSociale => text().named('ragioneSociale').nullable()();
  TextColumn get codFiliale => text().named('codFiliale').nullable()();
  TextColumn get filiale => text().named('Filiale').nullable()();
  TextColumn get indirizzo => text().named('indirizzo').nullable()();
  TextColumn get citta => text().named('citta').nullable()();
  TextColumn get provincia => text().named('provincia').nullable()();
  TextColumn get cap => text().named('cap').nullable()();

  IntColumn get idOrdine => integer().named('idOrdine')();
  IntColumn get idOrdineSede => integer().named('idOrdineSede').nullable()();
  IntColumn get numeroOrdine => integer().named('numeroOrdine').nullable()();
  TextColumn get numeroOrdineSede => text().named('numeroOrdineSede').nullable()();
  TextColumn get dataOrdine => text().named('dataOrdine').nullable()();
  TextColumn get dataConsegna => text().named('dataConsegna').nullable()();
  TextColumn get dataModifica => text().named('dataModifica').nullable()();
  TextColumn get riferimento => text().named('riferimento').nullable()();
  TextColumn get noteCliente => text().named('noteCliente').nullable()();
  TextColumn get noteOrdine => text().named('noteOrdine').nullable()();
  IntColumn get idCliente => integer().named('idCliente').nullable()();
  IntColumn get idFiliale => integer().named('idFiliale').nullable()();
  TextColumn get stato => text().named('stato').nullable()();
  IntColumn get idListino => integer().named('idListino').nullable()();
  IntColumn get idAgente => integer().named('idAgente').nullable()();
  IntColumn get idPagamento => integer().named('idPagamento').nullable()();
  RealColumn get totale => real().named('totale').nullable()();

  @override
  Set<Column> get primaryKey => {idOrdine};

  @override
  String get tableName => 'v_ordini';

  @override
  bool get withoutRowId => true;
}