select * from prueba;

create table Rol(
    id number(5) primary key,
    tipo varchar(30)
);

create sequence rolSec
start with 1
increment by 1
order;

-- Creacion de trigger
create or replace trigger autoidRol
before insert on Rol
for each row
begin
  select rolSec.nextval into :new.id from dual;
end;

insert into Rol(tipo)  values('Administrador');
insert into Rol(tipo)  values('Help Desk');
insert into Rol(tipo)  values('Cliente');

select * from Rol;


create table Genero(
    id number(5) primary key,
    tipo varchar(30)
);

insert into Genero(id,tipo)  values(1,'Masculino');
insert into Genero(id,tipo)  values(2,'Femenino');

select * from Genero;

create table Usuarios(
    id number(5) primary key,
    nombre varchar(30),
    apellido varchar(30),
    password varchar(25),
    correo varchar(30),
    telefono number(8),
    Foto varchar(200),
    id_genero number(5),
    id_Rol number(5),
    fecha_Nac date,
    fecha_Reg date DEFAULT SYSDATE,
    direccion varchar(50),
    credito decimal(17,2),
    ganancia decimal(17,2),
    clase int,
    puntuacion decimal(3,1),
    CONSTRAINT fk_1 FOREIGN KEY (id_genero) REFERENCES Genero(id),
    CONSTRAINT fk_2 FOREIGN KEY (id_Rol) REFERENCES Rol(id)
);

create sequence UsuariosSec
start with 1
increment by 1
order;

-- Creacion de trigger
create or replace trigger autoidUsuarios
before insert on Usuarios
for each row
begin
  select UsuariosSec.nextval into :new.id from dual;
end;

create table Conversacion(
    id number(5) primary key,
    id_cliente number(5),
    id_help number(5),
    puntuacion number(2),
    CONSTRAINT fka_1 FOREIGN KEY (id_cliente) REFERENCES Usuarios(id),
    CONSTRAINT fka_2 FOREIGN KEY (id_help) REFERENCES Usuarios(id)
);

create table Puntuacion(
    id number(5) primary key,
    puntuacion number(5),
    cantidad number(2),
    id_Usuario number(5),
    CONSTRAINT fka_4 FOREIGN KEY (id_Usuario) REFERENCES Usuarios(id)
);

drop table Puntuacion

create sequence PuntuacionSec
start with 1
increment by 1
order;


create or replace trigger autoiPuntuacion
before insert on Puntuacion
for each row
begin
  select PuntuacionSec.nextval into :new.id from dual;
end;

create table Mensajes(
    id number(5) primary key,
    tipo number(2),
    Cuerpo varchar(100),
    fecha date DEFAULT SYSDATE,
    id_chat number(5),
    CONSTRAINT fka_3 FOREIGN KEY (id_chat) REFERENCES Conversacion(id)
);



drop table  Conversacion

create sequence ConversacionSec
start with 1
increment by 1
order;

create or replace trigger autoidConver
before insert on Conversacion
for each row
begin
  select ConversacionSec.nextval into :new.id from dual;
end;

create table Mensajes(
    id number(5) primary key,
    tipo number(2),
    Cuerpo varchar(100),
    fecha date DEFAULT SYSDATE,
    id_chat number(5),
    CONSTRAINT fka_3 FOREIGN KEY (id_chat) REFERENCES Conversacion(id)
);

create sequence MensajeSec
start with 1
increment by 1
order;

create or replace trigger autoidMensaje
before insert on Mensajes
for each row
begin
  select MensajeSec.nextval into :new.id from dual;
end;

create table Carrito(
    id number(5) primary key,
    id_Usuario number(5),
    fecha date DEFAULT SYSDATE,
    CONSTRAINT fkc_1 FOREIGN KEY (id_Usuario) REFERENCES Usuarios(id)
);

create sequence CarritoSec
start with 1
increment by 1
order;

create or replace trigger autoidCarrito
before insert on Carrito
for each row
begin
  select CarritoSec.nextval into :new.id from dual;
end;

create table Detalle(
    id number(5) primary key,
    id_producto number(5),
    cantidad number(5),
    id_carrito number(5),
    fecha date DEFAULT SYSDATE,
    CONSTRAINT fkc_2 FOREIGN KEY (id_producto) REFERENCES productos(id),
    CONSTRAINT fkc_3 FOREIGN KEY (id_carrito) REFERENCES Carrito(id)
);

create sequence DetalleSec
start with 1
increment by 1
order;

create or replace trigger autoidDetalle
before insert on Detalle
for each row
begin
  select DetalleSec.nextval into :new.id from dual;
end;

CREATE OR REPLACE PROCEDURE VerificaCarrito(id_c varchar2)
IS
 cant int;
  BEGIN
    SELECT count(*) into cant FROM CARRITO WHERE ID_USUARIO=id_c and ESTADO=1;
    IF cant=0 THEN
        insert into carrito(id_usuario)
        values(id_c);
    END IF;
END;

CREATE OR REPLACE PROCEDURE AgregarAlCarrito(idUsu varchar2,idPro varchar2,cantidad_c varchar2)
IS
    gen int;
    cant int;
    idA int;
    dispo int;
  BEGIN
    SELECT id into gen  FROM CARRITO WHERE ID_USUARIO=idUsu and ESTADO=1;
    SELECT count(*) into cant   FROM DETALLE WHERE id_carrito=gen and id_producto=idPro;
    SELECT disponible into dispo   FROM productos WHERE id=idPro;
    IF dispo>=cantidad_c THEN
        UPDATE productos SET disponible=disponible-cantidad_c where id=idPro;
        IF cant=0 THEN
            insert into detalle(id_producto,cantidad,id_carrito)
            values(idPro,cantidad_c,gen);

        ELSE
            SELECT id into idA  FROM DETALLE WHERE id_carrito=gen and id_producto=idPro;
            UPDATE DETALLE SET cantidad=cantidad+cantidad_c  WHERE id=idA;
        END IF;
    END IF;
END;

CREATE OR REPLACE PROCEDURE EliminarDelCarrito(id_c varchar2,id_p varchar2)
IS
    gen int;

  BEGIN
    select cantidad into gen from  detalle where id_producto=id_p and id_carrito=id_c;
    delete from detalle where id_producto=id_p and id_carrito=id_c;
    UPDATE productos SET disponible=disponible+gen where id=id_p;

END;

delete from detalle where id_producto=1




CREATE OR REPLACE PROCEDURE comprar(idUsu varchar2)
IS
    total int;
    gen int;
    credi int;
  BEGIN

    SELECT id into gen  FROM CARRITO WHERE ID_USUARIO=idUsu and ESTADO=1;
    SELECT SUM(Subtotal) into total from (Select c.id_carrito as id,(c.cantidad*p.precio) as Subtotal FROM DETALLE c INNER JOIN Productos p ON c.id_producto=p.id) WHERE id=gen;
    SELECT credito into credi from usuarios where id=idUsu;
    IF credi>=total THEN

    MERGE INTO usuarios t1
    USING (select * from(SELECT u.id as  id,sum(c.cantidad*p.precio) as subtotal FROM DETALLE c INNER JOIN Productos p ON c.id_producto=p.id INNER JOIN Usuarios u ON p.id_pro=u.id WHERE c.id_carrito=gen group by u.id)  ) t2
    ON (t2.id= t1.id)
    WHEN MATCHED THEN
    UPDATE SET t1.ganancia  = t1.ganancia+t2.subtotal;

    UPDATE USUARIOS SET CREDITO=CREDITO-total where id=idUsu;
    END IF;

END;



SELECT *  FROM vista
drop view vista


gardarrr
describe guardar

SELECT *  FROM productos

SELECT *  FROM usuarios
SELECT *  FROM CARRITO WHERE ID_USUARIO=idUsu;

SELECT * from (Select c.id_carrito as id,(c.cantidad*p.precio) as Subtotal FROM DETALLE c INNER JOIN Productos p ON c.id_producto=p.id) WHERE id=1



SELECT SUM(c.cantidad*p.precio) as Total FROM DETALLE c INNER JOIN Productos p ON c.id_producto=p.id WHERE c.id_carrito=1


SELECT SUM(Subtotal) as Total from (Select c.id_carrito as id,(c.cantidad*p.precio) as Subtotal FROM DETALLE c INNER JOIN Productos p ON c.id_producto=p.id) WHERE id=2



UPDATE usuarios u
   SET (ganancia) = ganancia+(SELECT (c.cantidad*p.precio)
                              FROM DETALLE c
                              INNER JOIN Productos p ON c.id_producto=p.id
                              WHERE d.id_carrito=gen)
 WHERE u.id = (SELECT u.id
               FROM DETALLE c
               INNER JOIN Productos p ON c.id_producto=p.id
               INNER JOIN Usuarios u ON p.id_pro=u.id
               WHERE d.id_carrito=gen);

UPDATE usuarios u
SET (credito) = credito-(SELECT (c.cantidad*p.precio)
                              FROM DETALLE c
                              INNER JOIN Productos p ON c.id_producto=p.id
                              WHERE d.id_carrito=gen)
 WHERE u.id = idUsu;



SELECT c.cantidad,p.precio,u.id
FROM DETALLE c
INNER JOIN Productos p ON c.id_producto=p.id
INNER JOIN Usuarios u ON p.id_pro=u.id


SELECT (c.cantidad*p.precio)
FROM DETALLE c
INNER JOIN Productos p ON c.id_producto=p.id


SELECT u.id
FROM DETALLE c
INNER JOIN Productos p ON c.id_producto=p.id
INNER JOIN Usuarios u ON p.id_pro=u.id




SELECT * FROM DETALLE where estado=1
SELECT * FROM productos


CREATE OR REPLACE VIEW helps AS


SELECT u.id into gen,c.id, count(*) as Cant FROM helps u
LEFT OUTER JOIN Conversacion c
ON u.id = c.id_help
group by c.id,u.id
ORDER BY Cant desc;

select id  from usuarios where id_rol=2 and congelado=1 order by clientes desc;

select id
from (select id
      from usuarios
      where id_rol = 2
      and congelado=1
      order by clientes asc)
where rownum <= 1

CREATE OR REPLACE PROCEDURE CrearConversacion(id_c varchar2)
IS
    gen int;
  BEGIN
    select id into gen
    from (select id
      from usuarios
      where id_rol = 2
      and congelado=1
      order by clientes asc)
    where rownum <= 1;
    insert into Conversacion(id_cliente,id_help,puntuacion)
    values(id_c,gen,0);

    UPDATE Usuarios SET clientes=clientes+1 WHERE id=gen;
END;

select * from mensajes where id_chat=1

select id from conversacion where id_Cliente=1

select c.id,u.nombre from conversacion c,usuarios u where id_help=1 and c.id_cliente=u.id

CREATE OR REPLACE PROCEDURE CrearMensaje(idChat varchar2,tipo_c varchar2, cuerpo_c varchar2)
IS

  BEGIN
    insert into mensajes(tipo,cuerpo,id_chat)
    values(tipo_c,cuerpo_c,idChat);

END;

CREATE OR REPLACE PROCEDURE CrearMensaje(idChat varchar2,tipo_c varchar2, cuerpo_c varchar2)
IS

  BEGIN
    insert into mensajes(tipo,cuerpo,id_chat)
    values(tipo_c,cuerpo_c,idChat);
END;

CREATE OR REPLACE PROCEDURE PuntuarHelp(idChat varchar2,idHelp_c varchar2, puntuacion_c varchar2)
IS
  aten int;
  puntu int;
  BEGIN
    delete from mensajes where id_chat=idChat;
    delete from conversacion where id=idChat;
    UPDATE Usuarios SET clientes=clientes-1 WHERE id=idHelp_c;
    select puntuacion into puntu from usuarios where id=idHelp_c;
    puntu := puntu+ puntuacion_c;
    UPDATE Usuarios SET atendidos=atendidos+1, puntuacion=puntu  WHERE id=idHelp_c;
END;

delete from conversacion where id_chat=1;



select * from usuarios where id_rol=2
select * from conversacion
select * from mensajes



create table Sesion(
    id number(5),
    nombre varchar(30),
    apellido varchar(30),
    password varchar(25),
    correo varchar(30),
    telefono number(8),
    Foto varchar(200),
    id_genero number(5),
    id_Rol number(5),
    fecha_Nac date,
    fecha_Reg date DEFAULT SYSDATE,
    direccion varchar(50),
    credito decimal(17,2),
    ganancia decimal(17,2),
    clase int,
    puntuacion decimal(3,1),
    sesion int default 1
);


create table Categoria(
    id number(5) primary key,
    nombre varchar(30),
    descripcion varchar(50),
    id_Padre number(5),
    CONSTRAINT fk_3 FOREIGN KEY (id_Padre) REFERENCES Categoria(id)
);

create sequence CategoriaSec
start with 1
increment by 1
order;

create or replace trigger autoidCategoria
before insert on Categoria
for each row
begin
  select CategoriaSec.nextval into :new.id from dual;
end;

create table Bitacora(
    id number(5),
    descripcion varchar(200),
    fecha date DEFAULT SYSDATE
);

create sequence bitacoraSec
start with 1
increment by 1
order;

create or replace trigger autoidBitacora
before insert on Bitacora
for each row
begin
  select bitacoraSec.nextval into :new.id from dual;
end;

create table Productos(
    id number(5),
    nombre varchar(30),
    descripcion varchar(300),
    imagen varchar(350),
    id_categoria number(5),
    precio decimal(10,2),
    fecha_publi date DEFAULT SYSDATE,
    disponible int,
    Puntuacion decimal(3,1),
    id_pro number(5),
    CONSTRAINT fk_4 FOREIGN KEY (id_categoria) REFERENCES Categoria(id),
    CONSTRAINT fk_5 FOREIGN KEY (id_pro) REFERENCES Usuarios(id)
);

create table Inicio(
    id number(5),
    about varchar(250),
    mision varchar(250),
    vision varchar(250),
    imagen varchar(250)
);



create sequence ProductosSec
start with 1
increment by 1
order;

create or replace trigger autoidProductos
before insert on Productos
for each row
begin
  select ProductosSec.nextval into :new.id from dual;
end;



drop table Sesion;


CREATE OR REPLACE PROCEDURE CrearInicio(about_c varchar2,mision_c varchar2,vision_c varchar2,imagen_c varchar2)
IS
  BEGIN
    insert into Inicio(id,about,mision,vision,imagen)
    values(1,about_c,mision_c,vision_c,imagen_c);
END;

CREATE OR REPLACE PROCEDURE ActualizarInicio(about_c varchar2,mision_c varchar2,vision_c varchar2,imagen_c varchar2)
IS
  BEGIN
    UPDATE Inicio SET about = about_c,mision = mision_c,vision = vision_c,imagen = imagen_c WHERE id=1;
END;



CREATE OR REPLACE PROCEDURE ActualizarUsuario(correo_C varchar2,nombre_c varchar2,apellido_c varchar2,direccion_c varchar2,telefono_c number,clave_c varchar2)
IS
    gen int;
  BEGIN
    SELECT id into gen FROM SESION where  sesion=1  order by rowid desc;
    UPDATE Usuarios SET nombre=nombre_c,apellido=apellido_c,direccion=direccion_c,telefono=telefono_c,password=clave_c WHERE id=gen;
    UPDATE sesion SET nombre=nombre_c,apellido=apellido_c,direccion=direccion_c,telefono=telefono_c,password=clave_c WHERE id=gen;
END;

insert into Inicio(id,about,mision,vision,imagen)
values(1,'sin about','sin mision','sin vision','http://www.rangerwoodperiyar.com/images/joomlart/demo/default.jpg');

Select * from Inicio where id=1

CREATE OR REPLACE PROCEDURE CrearUsuarioCliente(c_nombre varchar2,c_apellidos varchar2,c_password varchar2,c_correo varchar2,c_telefono number,c_Foto varchar2,c_genero varchar2,c_fecha varchar2,c_direccion varchar2)
IS
    c_clase int;
    c_credito int;
    gen int;

  BEGIN
    c_clase := floor(dbms_random.value(1,6));
    IF c_clase=1 THEN c_credito:=1000;
    ELSIF c_clase=2 THEN c_credito:=5000;
    ELSIF c_clase=3 THEN c_credito:=10000;
    ELSIF c_clase=4 THEN c_credito:=25000;
    ELSIF c_clase=5 THEN c_credito:=50000;
    END IF;
    SELECT id into gen from Genero where tipo=c_genero;
    insert into Usuarios(nombre,apellido,password,correo,telefono,Foto,id_genero,id_rol,fecha_Nac,direccion,credito,ganancia,clase,puntuacion)
    values(c_nombre,c_apellidos,c_password,c_correo,c_telefono,c_Foto,gen,3,TO_DATE(c_fecha, 'YYYY-MM-DD'),c_direccion,c_credito,0.00,c_clase,0.0);
END;

CREATE OR REPLACE PROCEDURE CrearUsuario(c_nombre varchar2,c_apellidos varchar2,c_password varchar2,c_correo varchar2,c_telefono varchar2,c_Foto varchar2,c_genero varchar2,c_fecha varchar2,c_direccion varchar2,tipo varchar2)
IS
    gen int;

  BEGIN
    insert into Usuarios(nombre,apellido,password,correo,telefono,Foto,id_genero,id_rol,fecha_Nac,direccion,credito,ganancia,clase,puntuacion)
    values(c_nombre,c_apellidos,c_password,c_correo,c_telefono,c_Foto,c_genero,tipo,TO_DATE(c_fecha, 'YYYY-MM-DD'),c_direccion,0.00,0.00,0,0.0);
END;


    insert into Usuarios(nombre,apellido,password,correo,telefono,Foto,id_genero,id_rol,fecha_Nac,direccion,credito,ganancia,clase,puntuacion)
    values('Admin','admin','1234','herbercocargueta@gmail.com',56960795,'https://www.presidia.it/wp-content/uploads/2015/09/flat-faces-icons-circle-3.png',1,1,TO_DATE(c_fecha, 'YYYY-MM-DD'),'Guatemala',0.00,0.00,0,0.0,1);

CREATE OR REPLACE PROCEDURE InsartarBitacora(c_descripcion varchar2,tipo_c varchar2)
IS

  BEGIN
    insert into Bitacora(descripcion,tipo)
    values(c_descripcion,tipo_c);
END;

CREATE OR REPLACE PROCEDURE Login(email varchar2,password_c varchar2)
IS
  BEGIN
    INSERT INTO SESION(id,nombre,apellido,password,correo,telefono,Foto,id_genero,id_Rol,fecha_Nac,fecha_Reg,direccion,credito,ganancia,clase,puntuacion,congelado,atendidos,clientes)
    SELECT * FROM USUARIOS where correo = email and password = password_c and congelado = 1 and rownum < 2;
END;

CREATE OR REPLACE PROCEDURE CrearCategoria(nombre_c varchar2,descripcion_c varchar2,padre varchar2)
IS
    gen int;
    cou int;
  BEGIN
    SELECT count(*) into cou  from Categoria where nombre=padre;
    IF cou < 1 THEN
    insert into Categoria(nombre,descripcion,id_Padre)
    values(nombre_c,descripcion_c,null);
    ELSE
    SELECT id into gen from Categoria where nombre=padre;
    insert into Categoria(nombre,descripcion,id_Padre)
    values(nombre_c,descripcion_c,gen);
    END IF;
END;

CREATE OR REPLACE PROCEDURE CrearProductos(nombre_c varchar2,imagen_c varchar2,categoria_c varchar2,precio_c varchar2,disponi_c varchar2,pro number,descripcion_c varchar2)
IS
    gen int;
    cou int;
  BEGIN
    SELECT count(*) into cou  from Categoria where nombre=categoria_c;
    IF cou < 1 THEN
   insert into Productos(nombre,descripcion,imagen,id_categoria,precio,disponible,Puntuacion,id_pro)
    values(nombre_c,descripcion_c,imagen_c,null,precio_c,disponi_c,00.0,pro);
    ELSE
    SELECT id into gen from Categoria where nombre=categoria_c;
    insert into Productos(nombre,descripcion,imagen,id_categoria,precio,disponible,Puntuacion,id_pro)
    values(nombre_c,descripcion_c,imagen_c,gen,precio_c,disponi_c,00.0,pro);
    END IF;
END;

SELECT * FROM Categoria where id_Padre is null

SELECT id  from Categoria where nombre='muebles';
SELECT count(*)  from Categoria where nombre='Tecnologia';

SELECT * FROM Productos where id=7

SELECT p.nombre, u.nombre as Propietario,p.puntuacion,p.descripcion,p.precio,p.imagen FROM Productos p INNER JOIN usuarios u ON p.id_pro = u.id and p.id=7;


 delete from usuarios

  delete from Categoria where id_Padre is null
delete from SESION;
delete from Categoria;
delete from Productos;

delete from Bitacora;

drop table Productos;
drop table Categoria;

select * from usuarios WHERE ROWNUM <numRegistro -1;

SELECT * FROM USUARIOS where correo = 'dulce@gmail.com' and password = '123'  and congelado = 1;
SELECT Count(*) as Login FROM usuarios where correo='richimenen@gmail.com' and password='1234'

SELECT Count(*) as Login FROM sesion where  sesion=1
SELECT * FROM SESION where  sesion=1  order by rowid desc

UPDATE Sesion SET sesion = 0 WHERE sesion=1;

SELECT Count(*) as Login FROM sesion where  sesion=1

SELECT id_Rol FROM sesion where  sesion=1

Select * from Inicio where id=1

alter table bitacora
  add tipo varchar(30) default 'fas fa-check-square';

  UPDATE Usuarios SET congelado=0  WHERE id=:idd;

  Select * from Sesion

alter table USUARIOS
  drop column congelado;

ALTER TABLE carrito
add  estado number default 1;

SELECT id FROM Usuarios order by id desc

SELECT * FROM Usuarios order by rowid desc

SELECT * FROM comentarios

SELECT * FROM productos

SELECT * FROM respuestas

describe respuestas

CREATE OR REPLACE PROCEDURE CrearRespuesta(cuerpo_c varchar2,idC varchar2, idA varchar2)
IS

  BEGIN
    insert into respuestas(cuerpo,id_comentario,id_autor)
    values(cuerpo_c,idC,idA);

END;

ALTER TABLE respuestas
modify  cuerpo varchar(150);

SELECT r.cuerpo,r.fecha,u.nombre FROM RESPUESTAS r,USUARIOS u WHERE r.id_comentario=1 and r.id_autor=u.id

SELECT ,r.cuerpo,r.fecha,u.nombre FROM RESPUESTAS r,USUARIOS u WHERE r.id_producto=1 and r.id_autor=u.id

SELECT r.cuerpo,r.titulo,r.fecha,u.nombre FROM COMENTARIOS r,USUARIOS u WHERE r.id_producto=12 and r.id_autor=u.id