create user patron identified by 123; --Crear un usuario dueño de los objetos

grant dba,connect,resource to patron; --Otorgar permisos de administración del esquema

create tablespace users1 logging datafile  'C:\oraclexe\app\oracle\oradata\users1.dbf'size 1024M; --Crear 3 tablespaces (Usuarios, Tablas, Índices)
create tablespace index1 logging datafile  'C:\oraclexe\app\oracle\oradata\index1.dbf'size 1024M;
create tablespace table1 logging datafile  'C:\oraclexe\app\oracle\oradata\table1.dbf'size 1024M;

create user solo_consultas identified by 123 default tablespace users1; --Crear dos usuarios: Consular, Insertar

create user  solo_insert identified by 123 default tablespace users1;-- Crear usuarios
grant select any table to solo_consultas;--otorgar permisos
grant insert any table to solo_insert;--otorgar permisos
grant create session to solo_consultas;--otorgar permisos
grant create session to solo_insert;--otorgar permisos
--///////////////////////////////////////// C R E A T E    T A B L E //////////////////////////////////////////////////////
create table categoria (
codigo_categoria number not null primary key,
nombre_categoria varchar(50)
constraint fk5 add foreign key(nombre_categoria) reference producto(categoria))tablespace table1;
)
producto


create table cliente(
codigo smallint not null primary key,
ap_paterno varchar(50) not null,
ap_materno varchar(50) not null,
direccion varchar(50) not null,
telefono varchar(50) not null,
clase varchar(50) not null,
num_compras integer not null,
edad smallint not null,
createby varchar(10) not null,
inserttime timestamp not null,
fecha_nacimiento date not null,
credito_ocupado number not null,
credito_disponible number not null,
credito_otorgado number not null)
tablespace table1;

create table cargos(
cargo varchar(50) primary key,
salario_max number not null,
salario_min number not null)
tablespace table1;

create table empleado(
rut varchar(12) not null primary key,
ap_paterno varchar(50) not null,
direccion varchar(50)NOT NULL,
telefono varchar(50) not null,
cargo varchar(50) not null,
edad smallint not null,
createby varchar(10) not null,
inserttime timestamp not null,
fecha_nacimiento date not null,
salario int not null)
tablespace table1

create table producto(
codigo varchar(50) not null primary key,
descripcion varchar(50) not null,
categoria varchar(50) not null,
stock_minimo number not null,
stock_maximo number not null,
precio_actual number not null)
tablespace table1

create table pedido(
codigo varchar(50) not null primary key,
cantidad number not null,
fecha date,
codigo_cliente smallint not null,
total_pedido number not null,
estado varchar(50) not null,
fecha_entrega date not null,
rut_empleado varchar(50) not null)
tablespace table1

create table detalle_pedido(
id smallint not null,
codigo_producto_id varchar(50) not null,
cantidad number not null,
precio_unitario number not null,
subtotal number not null,
impuestos number not null,
num_linea smallint not null
)tablespace table1;

--/////////////////////////////////////////A L T E R    T A B L E/////////////////////////////////////////////////////////////////////
alter table empleado add constraint clave_foranea1 foreign key (cargo) references cargos(cargo);
alter table pedido add constraint clave_foranea2 foreign key (codigo_cliente) references cliente(codigo);
alter table pedido add constraint clave_foranea3 foreign key (rut_empleado) references empleado(rut);
alter table detalle_pedido add constraint clave_foranea4 foreign key (codigo_producto_id) references producto(codigo);
alter table cliente add fecha_ultimo_pedido date;
alter table cliente add constraint check_credito check (credito_otorgado >= 0);
alter table empleado add constraint check_rango_salario check(salario >=0);

--//////////////////////////////// INSERT CLIENTES///////////////////////////////////
insert into cliente (codigo, ap_paterno, ap_materno, direccion, telefono, clase, num_compras, edad, createby, inserttime,fecha_nacimiento,
credito_ocupado, credito_disponible, credito_otorgado)
values ('1' ,'FARKAS','KLEIN','NEW YORK (EEUU)','11111111','VIP','9','51','DIOS',current_timestamp,'20/03/1967','50','150','200');

insert into cliente (codigo, ap_paterno, ap_materno, direccion, telefono, clase, num_compras, edad, createby, inserttime,fecha_nacimiento,
credito_ocupado, credito_disponible, credito_otorgado)
values ('2' ,'VASQUEZ','CAMPOS','CALIFORNIA (EEUU)','22222222','CLIENTE ESPORADICO','2','22','DIOS',current_timestamp,'04/01/1996','10','15','25');

insert into cliente (codigo, ap_paterno, ap_materno, direccion, telefono, clase, num_compras, edad, createby, inserttime,fecha_nacimiento,
credito_ocupado, credito_disponible, credito_otorgado)
values ('3' ,'QUINTEROS','ELGUETA','TALCA (CHILE)','33333333','CLIENTE ESPORADICO','0','22','DIOS',current_timestamp,'12/09/1996','10','15','25');

--////////////////////////////I N S E R T   C A R G O/////////////////////////////////

insert into cargos(cargo,salario_max,salario_min)
values ('GERENTE(OPERACIONES)','5000000','2000000');

insert into cargos(cargo,salario_max,salario_min)
values ('INGENIERO','2300000','1500000');

insert into cargos(cargo,salario_max,salario_min)
values ('OPERADOR','1500000','1000000');

insert into cargos(cargo,salario_max,salario_min)
values ('JEFE REGIONAL','7000000','4000000');

insert into cargos(cargo,salario_max,salario_min)
values ('GERENTE GENERAL','10000000','8000000');


--///////////////////////////////// I N S E R T     E M P L E A D O/////////////////////////////////////



insert into empleado(rut,ap_paterno,direccion,telefono,cargo,edad,createby,inserttime,fecha_nacimiento,salario)
values ('1111111111-1','PEREZ','P SHERMAN CALLE WALLABY 42 SYDNEY','123456789','INGENIERO','29','DIOS',CURRENT_TIMESTAMP,'21/01/2018','1550000');

insert into empleado(rut,ap_paterno,direccion,telefono,cargo,edad,createby,inserttime,fecha_nacimiento,salario)
values ('1111111111-2','LOPEZ','TALCA','123456789','INGENIERO','22','DIOS',CURRENT_TIMESTAMP,'21/01/2018','1250000');

insert into empleado(rut,ap_paterno,direccion,telefono,cargo,edad,createby,inserttime,fecha_nacimiento,salario)
values ('1111111111-3','NAVARRETE','SANTIAGO','123456789','INGENIERO','29','DIOS',CURRENT_TIMESTAMP,'21/01/2018','1550000');

insert into empleado(rut,ap_paterno,direccion,telefono,cargo,edad,createby,inserttime,fecha_nacimiento,salario)
values ('1111111111-4','VENEGAS','CONCEPCION','123456789','INGENIERO','29','DIOS',CURRENT_TIMESTAMP,'21/01/2018','1550000');

insert into empleado(rut,ap_paterno,direccion,telefono,cargo,edad,createby,inserttime,fecha_nacimiento,salario)
values ('1111111111-5','VILLASECA','ANTOFALOMBIA','123456789','GERENTE(OPERACIONES)','23','DIOS',CURRENT_TIMESTAMP,'21/01/2018','4550000');

insert into empleado(rut,ap_paterno,direccion,telefono,cargo,edad,createby,inserttime,fecha_nacimiento,salario)
values ('1111111111-6','VASQUEZ','TALCA','123456789','JEFE REGIONAL','29','DIOS',CURRENT_TIMESTAMP,'21/01/2018','9550000');

SELECT*FROM empleado



--/////////////////////////////// T E S T E R/////////////
--conn solo_insert contraseña 123


--////////////////////////////////// INSERT PRODUCTOS/////////////////////////////////////
insert into producto (codigo, descripcion , categoria, stock_minimo, stock_maximo,precio_actual)
values ('111' , 'QUESO DE ELABORACION ARTESANAL', 'LACTEOS','10','100','3990');

insert into producto (codigo, descripcion , categoria, stock_minimo, stock_maximo,precio_actual)
values ('112' , 'HELADO DE CHOCOLATE', 'POSTRES','10','100','2590');

insert into producto (codigo, descripcion , categoria, stock_minimo, stock_maximo,precio_actual)
values ('113' , 'YOGUR', 'LACTEOS','10','100','390');

insert into producto (codigo, descripcion , categoria, stock_minimo, stock_maximo,precio_actual)
values ('114' , 'LECHE ENTERA(1 LITRO)', 'LACTEOS','10','100','790');

insert into producto (codigo, descripcion , categoria, stock_minimo, stock_maximo,precio_actual)
values ('115' , 'LENTEJAS (1 KILO)','LEGUMBRES ','10','50','2279');

insert into producto (codigo, descripcion , categoria, stock_minimo, stock_maximo,precio_actual)
values ('116' , 'LOMO LISO Cat.V(1 KILO)', 'CARNES','10','60','9490');

insert into producto (codigo, descripcion , categoria, stock_minimo, stock_maximo,precio_actual)
values ('117' , 'CARMENERE CASILLERO DEL DIABLO 1.5 LITROS ', 'VINOS','1','25','4529');

insert into producto (codigo, descripcion , categoria, stock_minimo, stock_maximo,precio_actual)
values ('118' , 'TORTA MIL HOJAS (15 PORCIONES)', 'PASTELERIA','0','5','12390');

insert into producto (codigo, descripcion , categoria, stock_minimo, stock_maximo,precio_actual)
values ('119' , 'CHEESECAKE(UNIDAD)', 'PASTELERIA','0','10','7590');

insert into producto (codigo, descripcion, categoria, stock_minimo, stock_maximo, precio_actual)
values ('120' , 'PALTA (HASS 1 KILO)', 'FRUTAS Y VERDURAS' ,'5','15','5000');


--/////////////////////////I N S E R T   P E D I D O////////////////////
INSERT INTO pedido(codigo,cantidad,fecha,codigo_cliente,total_pedido,estado,fecha_entrega,rut_empleado)
values ('0001','2',current_date,'1','250','PENDIENTE','29/05/2018','1111111111-2');

delete from pedido
--//////////////////////////////////////////////I N S E R T    DETALLE_PEDIDO//////////////////////////
insert into detalle_pedido(id,codigo_producto_id,cantidad,precio_unitario,subtotal,impuestos,num_linea)
values ('11','112','1','2590','2590','4000','321');

desc detalle_pedido;
select*from detalle_pedido;
--/////////////////////////////////////////////////F U N C I O N E S///////////////////////////////////////////////////

--new y old se usan en los trigger
--los triger se ejecutan sobre tablas


CREATE OR REPLACE FUNCTION total_pedido(codigo varchar(50))
RETURN NUMBER is

total NUMBER;
codigo_pedido VARCHAR;

BEGIN
    UPDATE pedido SET total_pedido=total_pedido(codigo);
    SELECT round (sum(precio*cantidad)*1.19),codigo_pedido into total_pedido,codigo_pedido,
    FROM pedido P JOIN detalle_pedido DP on p.codigo_pedido=DP codigo_pedido,
    WHERE p.codigo=codigo_producto,
    GROUP BY codigo_preducto return total_pedido
    END;

begin
    update pedido SET total_pedido = pedido(codigo);
    select


update pedido set total_pedido = total_pedido(codigo.producto



CREATE OR REPLACE FUNCTION fn_suma_precio_productos
(calculando IN number)
return number
IS
calculo1 number (9);
BEGIN
select sum(precio_actual)
into calculo1
from producto
where precio_actual = calculando;
return calculo1;
END;

select  descripcion as nombre_del_producto
,fn_promedio_precio_productos(precio_actual)as
from producto;

select sum (p.precio_actual)
from producto p;

create view selecciona_todo as select*from producto

select *from  selecciona_todo
create view sumas as select sum (p.precio_actual) as sumar_algo
from producto p;
select *from sumas
-- ME DA SUMA DE TODOS LOS PRODUCTOS INSERTADOS EN LA TABLA PRODUCTOS
select avg (pr.precio_actual)
from producto pr;

select p from p;
select sum (d.subtotal)
from detalle_pedido d join producto p
on d.codigo_preducto_id = p.id


desc cliente;
select *from producto
SELECT *FROM CLIENTE
delete from producto-- Borra todas las filas de una tabla U S A R   C O N   C U I D A D O


--////////////////////////////////////C R E A T E       T R I G G E R//////////////////////

--caso de uso para el informe (crear un pedido) ejecutar todas las cosas , todos los trigger,todas las funcines,consultas etc
--SALARIO POR CARGO
create or replace trigger rango_salario
before insert on empleado
for each row
declare
  raise_fuera_rango exception;
  min_salario number;
  max_salario number;
begin
  select salario_max, salario_min into max_salario, min_salario from cargos where cargo=:NEW.cargo;
  if(:NEW.salario<min_salario or :NEW.salario>max_salario) then
    raise raise_fuera_rango;
  end if;
exception
  when raise_fuera_rango then
    raise_application_error(-20300,'El salario esta fuera de rango');
end;

select*from empleado
--	Verificar si el pedido se puede hacer si tiene crédito 	disponible

CREATE OR REPLACE TRIGGER verifica_pedido
before insert on pedido
for each row
declare
credit_dispo number;
total_ped number;
raise_credito_insufuciente exception;
begin
select credito_disponible into credito_disponible from cliente where pedido =:NEW.total_pedido;
 if(:NEW.total_pedido>credito_disponible or :NEW.credito_disponible <1) then --Con credito == 1 nadie puede comprar nada
    raise raise_credito_insuficiente;
    end if;
    exception
    when raise_credito_insuficiente then
    raise_application_error(-20301,'No se puede realizar su pedido (Credito Insuficiente');
end;

-- VENTA DE ALCOHOL A MENORES



create or replace trigger venta_alcohol
before insert on pedido
for each row
declare
  raise_edad_no_permitida exception;
  cliente_edad number;
begin
  select edad into cliente_edad from cliente where cargo=:NEW.cargo;
  if(:NEW.edad<18 ) then
    raise raise_edad_no_permitida;
  end if;
exception
  when raise_edad_no_permitida then
    raise_application_error(-20300,'MENOR DE EDAD DETECTED LLAMANDO A LA PDI');
end;

create or replace trigger licores_mayores_18
before insert on pedido
for each row

declare
  edad_excedida exception;

begin
  select edad from cliente where cliente.codigo=pedido.codigo_cliente;
  if(:new.edad<17) then
    raise edad_excedida;
  end if;
exception
  when edad_excedida then
raise_application_error(-20310,'El cliente debe ser mayor de 18 para comprar licores');
end;

select c.credito_disponible
from cliente c join pedido p
on  p.codigo_cliente=c.codigo



select*from pedido
select*from cliente
delete from pedido


--/////////////////////////////////////D A N G E R  C U I D A D O /////////////////////////////////////////////////

create or replace TRIGGER TGR_detalle_pedido_subtotal
BEFORE INSERT ON detalle_pedido_aux
FOR EACH ROW
DECLARE
    num_lineas SMALLINT;
    sub_total NUMBER;
    stock_t numbeR;
    cliente_t smallint;
    credito_t number;
    raise_no_credito exception;
BEGIN

    select count() into num_lineas from detalle_pedido where (detalle_pedido.codigo_pedido=:new.codigo_pedido);
    sub_total:=(:new.cantidad:new.precio_unitario)-(:new.cantidad:new.precio_unitario:new.impuestos);
    insert into detalle_pedido(id,CODIGO_PRODUCTO_ID,cantidad,PRECIO_UNITARIO,SUBTOTAL,IMPUESTOS,NUM_LINEA,CODIGO_PEDIDO) values(:new.id,:new.codigo_producto_id,:new.cantidad,:new.precio_unitario,sub_total,:new.impuestos,num_lineas,:new.codigo_pedido);

    select codigo_cliente into cliente_t from pedido where pedido.codigo=:new.codigo_pedido;
    select credito_disponible into credito_t from cliente where codigo=cliente_t;
    if(sub_total<credito_t) then
        update CLIENTE set credito_disponible=(credito_disponible-sub_total) where cliente.codigo=cliente_t;
        update cliente set credito_ocupado=(credito_otorgado-credito_disponible) where cliente.codigo=cliente_t;
        update pedido set total_pedido=pedido.total_pedido+sub_total where pedido.codigo=:new.codigo_pedido;

        select stock_actual into stock_t from pedido.producto where pedido.producto.codigo=:NEW.codigo_producto_id;
        update PRODUCTO set STOCK_ACTUAL=(stock_t-:new.cantidad) where PRODUCTO.CODIGO=:new.codigo_producto_id;
    else
        raise raise_no_credito;
    end if;
exception
  when raise_no_credito then
    raise_application_error(-20300,'No hay credito suficiente');
END;