drop table sodcafe;

create table SODCAFE
(
  CIA         char(3) not null,
  EMPLEADO    varchar(15) not null,
  ENTRADA     timestamp not null,
  SALIDA      timestamp,
  constraint sodcafe_pk 
     primary key(CIA, EMPLEADO, ENTRADA)
);

create index sodcafe_entrada_idx on SODCAFE(CIA, ENTRADA);

create VIEW SODCAFE_DET
   as
   select CIA, EMPLEADO, ENTRADA, SALIDA, (SALIDA-ENTRADA)*24 as TIEMPOT, 0 as TIEMPOE
   from SODCAFE;

create VIEW SODCAFE_DIA
   as
   select CIA, EMPLEADO, min(C.ENTRADA) as ENTRADA, max(IFF(C.SALIDA IS NULL, C.ENTRADA, C.SALIDA)) as SALIDA,
          IFF(sum(C.TIEMPOT)>8,8,sum(C.TIEMPOT)) as TIEMPOT,
          IFF(sum(C.TIEMPOT)>8,sum(C.TIEMPOT)-8,0) as TIEMPOE
   from SODCAFE_DET C
   group by CIA, EMPLEADO, YEAR(C.ENTRADA), MONTH(C.ENTRADA), DAY(C.ENTRADA);

create view SODCAFE_RES
   as
   select CIA, EMPLEADO, min(C.ENTRADA) as ENTRADA, max(C.SALIDA) as SALIDA,
          sum(C.TIEMPOT) as TIEMPOT, SUM(C.TIEMPOE) as TIEMPOE
   from SODCAFE_DIA C
   group by CIA, EMPLEADO, YEAR(C.ENTRADA), MONTH(C.ENTRADA);


create view SODCAFE_DEMP
   as
   select d.cia, d.empleado, d.entrada, d.salida, d.tiempot, d.tiempoe,
       c.entrada as dent, c.salida as dsal
   from sodcafe_dia d, sodcafe c
   where d.cia=c.cia
     and d.empleado=c.empleado
     and c.entrada >= d.entrada
     and c.entrada <= d.salida;


create table PLAEMPLEADO (
   CIA                  CHAR(3)                         not null,
   EMPLEADO             VARCHAR(15)                     not null,
   NOMBRE               VARCHAR(40)                     not null,
   FECHANAC             DATE                            not null,
   SEXO                 CHAR(1)                         not null,
   CEDULA               VARCHAR(20)                     not null,
   TIPOID               INTEGER                         not null,
   SEGUROSOCIAL         VARCHAR(15),
   ESTADOCIVIL          CHAR(1)                         not null,
   DIRECCION            VARCHAR(120),
   TELEFONO             VARCHAR(12),
   CONYUGUE             CHAR(1)                         not null,
   HIJOS                INTEGER                         not null,
   HIJOSMAY             INTEGER                         not null,
   DEPENDIENTES         INTEGER                         not null,
   PLANILLA             CHAR(2),
   SUCURSAL             CHAR(3),
   DEPARTAMENTO         CHAR(3),
   CCOSTO               VARCHAR(7),
   PUESTO               CHAR(3),
   CATEGORIA            CHAR(2),
   JEFEINMEDIATO        VARCHAR(15),
   HORARIO              CHAR(2)                         not null,
   GANAEXTRAS           CHAR(1)                         not null,
   GANACOMIS            CHAR(1)                         not null,
   MARCATARJETA         CHAR(1)                         not null,
   TIPOPAGO             CHAR(1)                         not null,
   BANCO                VARCHAR(12),
   CUENTA               VARCHAR(20),
   SALARIOACTUAL        INTEGER                         not null,
   ASOCIACION           CHAR(1)                         not null,
   FECHAINGRESO         DATE                            not null,
   FECHASALIDA          DATE,
   ESTADO               CHAR(1)                         not null,
   constraint PLAEMPLEADO_PK primary key (cia, empleado)
);

create table PLADEPARTAMENTO (
   CIA                  CHAR(3)                         not null,
   SUCURSAL             CHAR(3)                         not null,
   DEPARTAMENTO         CHAR(3)                         not null,
   DESCRIPCION          VARCHAR(40)                     not null,
   CCOSTO               VARCHAR(7)                      not null,
   constraint PLADEPARTAMENTO_PK primary key (cia, sucursal, departamento)
);

create table GEN_SUCURSAL (
   COD_CIA              CHAR(3) not null,
   SUCURSAL             CHAR(3) not null,
   DESCRIPCION          VARCHAR(30) not null,
   constraint GEN_SUCURSAL_PK primary key (cod_cia, sucursal)
);

create table GEN_IMP_BARRAS (
   COD_CIA              CHAR(3)                         not null,
   SESION               INTEGER                         not null,
   COD_ARTICULO         VARCHAR(25)                     not null,
   COD_BODEGA           CHAR(3)                         not null,
   CANTIDAD             INTEGER                         not null,
   DESCRIPCION          VARCHAR(50)                     not null,
   PRECIO               INTEGER                         not null,
   constraint GEN_IMP_BARRAS_PK primary key (cod_cia, sesion, cod_articulo, cod_bodega)
);

create table INV_ARTICULO (
   COD_CIA              CHAR(3)                         not null,
   COD_ARTICULO         VARCHAR(25)                     not null,
   TIPO_INV             VARCHAR(3)                      not null,
   COD_LINEA            VARCHAR(5)                      not null,
   COD_CLASE            VARCHAR(5)                      not null,
   COD_MARCA            VARCHAR(5)                      not null,
   DES_ARTICULO         VARCHAR(50)                     not null,
   COD_DEPTO            VARCHAR(3),
   PRECIO               INTEGER                         not null,
   constraint INV_ARTICULO_PK primary key (cod_cia, cod_articulo)
);


drop table dual;
create table DUAL
(
  X         char(1) not null
);

insert into gen_sucursal values ('001','001','MULTIPLAZA');
insert into gen_sucursal values ('001','002','TRES RIOS');
insert into gen_sucursal values ('001','003','ALAJUELA');
commit;

insert into inv_articulo values ('001','27','001','001','001','001','A 27','027',1250);
commit;