drop table sodcafe;

create table sodcafe
(
  cia         char(3) not null,
  empleado    varchar(15) not null,
  entrada     timestamp not null,
  salida      timestamp,
  ano         computed by (extract (year from entrada)),
  mes         computed by (extract (month from entrada)), 
  constraint sodcafe_pk 
     primary key(cia, empleado, entrada)
);

create index sodcafe_entrada_idx on sodcafe(cia, entrada);

create view sodcafe_det (cia, empleado, entrada, salida, minutos)
   as
   select cia, empleado, entrada, salida, (salida-entrada)*24
   from sodcafe;

create view sodcafe_res (cia, empleado, entrada, salida, minutos)
   as
   select cia, empleado, max(entrada), null, sum((salida-entrada)*24)
   from sodcafe
   group by cia, empleado, ano, mes;

create view sodcafe_exc (cia, empleado, entrada, salida, minutos)
   as
   select cia, empleado, max(entrada), null, sum((salida-entrada)*24-15)
   from sodcafe
   where (salida-entrada)*1440>15
   group by cia, empleado, ano, mes;


create table plaempleado
(
  cia         char(3) not null,
  empleado    varchar(15) not null,
  nombre      varchar(50) not null,
  constraint  plaempleado_pk 
     primary key(cia, empleado)
);

drop table dual1;
create table dual1
(
  x         char(1) not null,
  ct        computed by (cast(current_time as character(16))),
  cd        computed by (cast(current_date as character(10))),
  cts       computed by (cast(current_date as character(26)))
);


