CREATE DATABASE commerk;

CREATE TABLE todo(
  todo_id SERIAL PRIMARY KEY,
  description VARCHAR(255)
);

CREATE TABLE users(
  user_id SERIAL PRIMARY KEY,
  userName VARCHAR(255),
  googleId VARCHAR(255),
  userEmail VARCHAR(255)
);

ALTER TABLE users
ADD userImg VARCHAR(255);

-- -----------------------------------------------------
-- Table `commerk`.`vehiculo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS vehiculo (
  id_vehiculo SERIAL PRIMARY KEY,
  placa_vehiculo VARCHAR(45) NOT NULL,
  descripcion VARCHAR(45) NOT NULL,
  capacidad_vehiculo INT NOT NULL,
  disponibilidad_vehiculo SMALLINT NOT NULL,
  soat_vehiculo DATE NOT NULL,
  tecno_vehiculo DATE NOT NULL,
  impuesto_vehiculo DATE ,
  proyecto_id_proyecto INT NOT NULL,
  modelo BIGINT NOT NULL,
  peso_vacio INT,
  peso_maximo INT,
  ciudad_matricula VARCHAR(255) NOT NULL,
  tipo_transporte VARCHAR(255) NOT NULL
);

ALTER TABLE vehiculo
ADD COLUMN modelo BIGINT NOT NULL,
ADD COLUMN ciudad_matricula VARCHAR(255) NOT NULL,
ADD COLUMN tipo_transporte VARCHAR(255) NOT NULL;

ALTER TABLE vehiculo
ALTER COLUMN disponibilidad_vehiculo TYPE VARCHAR(255) USING disponibilidad_vehiculo::VARCHAR(255),
ALTER COLUMN disponibilidad_vehiculo SET DEFAULT '';

ALTER TABLE vehiculo
ADD COLUMN proyecto_id_proyecto INT NOT NULL;

ALTER TABLE vehiculo
ADD CONSTRAINT fk_vehiculo_proyecto FOREIGN KEY (proyecto_id_proyecto) REFERENCES proyecto(id_proyecto);

-- -----------------------------------------------------
-- Table `commerk`.`zona`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS zona (
  id_zona SERIAL PRIMARY KEY,
  nombre_zona VARCHAR(45) NOT NULL
);

-- -----------------------------------------------------
-- Table `commerk`.`tipo_usuario`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tipo_usuario (
  id_tipo_usuario SERIAL PRIMARY KEY,
  tipo_usuario VARCHAR(45) NOT NULL
);

-- -----------------------------------------------------
-- Table `commerk`.`usuario`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS usuario (
  id_usuario SERIAL PRIMARY KEY,
  password VARCHAR(255) NOT NULL,
  nombre_usuario VARCHAR(45) NOT NULL,
  apellidos_usuario VARCHAR(45) NOT NULL,
  googleid VARCHAR(255),
  useremail VARCHAR(255),
  userimg VARCHAR(255),
  username VARCHAR(255),
  telefono_usuario BIGINT NOT NULL,
  documento_usuario BIGINT NOT NULL,
  tipo_usuario_id_tipo_usuario INT NOT NULL,
  proyecto_id_proyecto INT NOT NULL,
  estado_usuario BOOLEAN NOT NULL,
  rol VARCHAR(255) NOT NULL,
  reset_token VARCHAR(64),
  reset_token_expiry BIGINT;
);
  -- Agregar clave foránea para la tabla proyecto
ALTER TABLE usuario
ADD CONSTRAINT fk_proyecto
FOREIGN KEY (proyecto_id_proyecto) 
REFERENCES proyecto(id_proyecto);

ALTER TABLE usuario
ADD CONSTRAINT fk_tipo_usuario
FOREIGN KEY (tipo_usuario_id_tipo_usuario)
REFERENCES tipo_usuario(id_tipo_usuario);

ALTER TABLE usuario
ADD COLUMN disponibilidad VARCHAR(255) DEFAULT '';

UPDATE usuario
SET disponibilidad = '';

-- -----------------------------------------------------
-- Table `commerk`.`encabezado_cargue`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS encabezado_cargue (
  id_encabezado_cargue SERIAL PRIMARY KEY,
  peso_a_cargar INT NOT NULL,
  ocupacion_carga INT NOT NULL,
  estado_cargue VARCHAR(45) NOT NULL,
  usuario_id_tipo_usuario INT NOT NULL,
  vehiculo_id_vehiculo INT NOT NULL,
  zona_id_zona INT NOT NULL,
  tipo_documento_id_tipo_documento INT NOT NULL
);

-- Adding Foreign Key Constraints for encabezado_cargue table
ALTER TABLE encabezado_cargue
  ADD CONSTRAINT fk_encabezado_cargue_usuario FOREIGN KEY (usuario_id_tipo_usuario) REFERENCES tipo_usuario (id_tipo_usuario),
  ADD CONSTRAINT fk_encabezado_cargue_vehiculo FOREIGN KEY (vehiculo_id_vehiculo) REFERENCES vehiculo (id_vehiculo),
  ADD CONSTRAINT fk_encabezado_cargue_zona FOREIGN KEY (zona_id_zona) REFERENCES zona (id_zona),
  ADD CONSTRAINT fk_encabezado_cargue_tipo_documento FOREIGN KEY (tipo_documento_id_tipo_documento) REFERENCES tipo_documento (id_tipo_documento);

-- -----------------------------------------------------
-- Table `commerk`.`tipo_estado`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tipo_estado (
  id_tipo_estado SERIAL PRIMARY KEY,
  nombre_tipo_estado VARCHAR(45) NOT NULL
);

-- -----------------------------------------------------
-- Table `commerk`.`estado`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS estado (
  id_estado SERIAL PRIMARY KEY,
  nombre_estado VARCHAR(45) NOT NULL,
  encabezado_cargue_id_encabezado_cargue INT NOT NULL,
  tipo_estado_id_tipo_estado INT NOT NULL
);

-- Adding Foreign Key Constraints for estado table
ALTER TABLE estado
  ADD CONSTRAINT fk_estado_encabezado_cargue FOREIGN KEY (encabezado_cargue_id_encabezado_cargue) REFERENCES encabezado_cargue (id_encabezado_cargue),
  ADD CONSTRAINT fk_estado_tipo_estado FOREIGN KEY (tipo_estado_id_tipo_estado) REFERENCES tipo_estado (id_tipo_estado);

-- -----------------------------------------------------
-- Table `commerk`.`detalle_cargue`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS detalle_cargue (
  id_detalle_cargue SERIAL PRIMARY KEY,
  factura VARCHAR(45) NOT NULL,
  encabezado_cargue_id_encabezado_cargue INT NOT NULL
);

-- Adding Foreign Key Constraints for detalle_cargue table
ALTER TABLE detalle_cargue
  ADD CONSTRAINT fk_detalle_cargue_encabezado_cargue FOREIGN KEY (encabezado_cargue_id_encabezado_cargue) REFERENCES encabezado_cargue (id_encabezado_cargue);

-- -----------------------------------------------------
-- Table `commerk`.`proyecto`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS proyecto (
  id_proyecto SERIAL PRIMARY KEY,
  nombre_proyecto VARCHAR(255) NOT NULL,
  descripcion_proyecto VARCHAR(255) NOT NULL,
  estado_proyecto BOOLEAN NOT NULL
);

-- -----------------------------------------------------
-- Table `commerk`.`zona`
-- -----------------------------------------------------

CREATE TABLE IF NOT EXISTS zona (
  id_zona SERIAL PRIMARY KEY,
  nombre_zona VARCHAR(45) NOT NULL,
  proyecto_id_proyecto INT NOT NULL
);

ALTER TABLE zona
  ADD CONSTRAINT fk_zona_proyecto1 FOREIGN KEY (proyecto_id_proyecto) REFERENCES proyecto(id_proyecto);

  -- -----------------------------------------------------
-- Table "commerk"."tipo_documento"
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS tipo_documento (
  id_tipo_documento SERIAL PRIMARY KEY,
  nombre_documento VARCHAR(255) NOT NULL,
  tipo_documento VARCHAR(255) NOT NULL,
  estado_documento BOOLEAN NOT NULL
);

-- -----------------------------------------------------
-- Table `commerk`.`consecutivo`
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS consecutivo (
  id_consecutivo SERIAL PRIMARY KEY,
  prefijo VARCHAR(255) NOT NULL,
  rango_inicial INT NOT NULL,
  rango_final INT NOT NULL,
  siguiente INT NOT NULL,
  fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  tipo_documento_id_tipo_documento INT NOT NULL
);

ALTER TABLE consecutivo
ADD CONSTRAINT fk_tipo_documento
FOREIGN KEY (tipo_documento_id_tipo_documento)
REFERENCES tipo_documento(id_tipo_documento);


CREATE TABLE encabezado_carga (
    id_encabezado_carga SERIAL PRIMARY KEY,
    fecha_registro TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    proyecto_id_proyecto INTEGER NOT NULL,
    peso_carga NUMERIC NOT NULL,
    ocupacion_carga NUMERIC NOT NULL,
    tipo_documento_id_tipo_documento INTEGER NOT NULL,
    vehiculo_id_vehiculo INTEGER NOT NULL,
    prefijo_consecutivo VARCHAR(255) NOT NULL,
    id_consecutivo INTEGER NOT NULL,
    estado_carga VARCHAR(255) NOT NULL,
    inicio_carga TIMESTAMP ,
    fin_carga TIMESTAMP ,
    inicio_despacho TIMESTAMP ,
    fin_despacho TIMESTAMP ,
    responsable VARCHAR(255) NOT NULL,
    endDate VARCHAR(255) NOT NULL,
    startDate VARCHAR(255) NOT NULL,
    CONSTRAINT fk_tipo_documento FOREIGN KEY (tipo_documento_id_tipo_documento) REFERENCES tipo_documento(id_tipo_documento),
    CONSTRAINT fk_vehiculo FOREIGN KEY (vehiculo_id_vehiculo) REFERENCES vehiculo(id_vehiculo)
);


CREATE TABLE encabezado_carga_usuario (
    id_encabezado_carga_usuario SERIAL PRIMARY KEY,
    encabezado_carga_id INTEGER NOT NULL,
    usuario_id_usuario INTEGER NOT NULL,
    tipo_usuario_id_tipo_usuario INTEGER NOT NULL,
    CONSTRAINT fk_encabezado_carga FOREIGN KEY (encabezado_carga_id) REFERENCES encabezado_carga(id_encabezado_carga),
    CONSTRAINT fk_usuario FOREIGN KEY (usuario_id_usuario) REFERENCES usuario(id_usuario),
    CONSTRAINT fk_tipo_usuario FOREIGN KEY (tipo_usuario_id_tipo_usuario) REFERENCES tipo_usuario(id_tipo_usuario)
);

CREATE TABLE detalle_carga (
    id_detalle_carga SERIAL PRIMARY KEY,
    id_encabezado_carga INTEGER NOT NULL,
    estado VARCHAR(255) NOT NULL,
    DocEntry VARCHAR(255) NOT NULL,
    factura VARCHAR(255) NOT NULL,
    imagen_factura VARCHAR(255),
    hora_entrega TIMESTAMP,
    imagen_producto VARCHAR(255),
    novedad VARCHAR(255),
    latitud DOUBLE PRECISION,
    longitud DOUBLE PRECISION,
    ADD COLUMN imagen_producto VARCHAR(255),
    novedad VARCHAR(255),
    latitud DOUBLE PRECISION,
    longitud DOUBLE PRECISION,
    CONSTRAINT fk_encabezado_carga FOREIGN KEY (id_encabezado_carga) REFERENCES encabezado_carga(id_encabezado_carga)
);

ALTER TABLE detalle_carga
ADD COLUMN imagen_producto VARCHAR(255),
ADD COLUMN novedad VARCHAR(255),
ADD COLUMN latitud DOUBLE PRECISION,
ADD COLUMN longitud DOUBLE PRECISION;

ALTER TABLE detalle_carga ADD COLUMN imagen_factura BYTEA;
ALTER TABLE detalle_carga
ADD COLUMN hora_entrega TIMESTAMP;

CREATE TABLE encabezado_carga_zona (
    id_encabezado_carga_zona SERIAL PRIMARY KEY,
    encabezado_carga_id INTEGER NOT NULL,
    zona_id INTEGER NOT NULL,
    FOREIGN KEY (encabezado_carga_id) REFERENCES encabezado_carga(id_encabezado_carga),
    FOREIGN KEY (zona_id) REFERENCES zona(id_zona)
);

CREATE TABLE unidades (
    id_unidades SERIAL PRIMARY KEY,
    id_encabezado_carga INTEGER NOT NULL,
    cantidad VARCHAR(255) NOT NULL,
    descripcion VARCHAR(255) NOT NULL,
     estado VARCHAR(255) NOT NULL,
    FOREIGN KEY (id_encabezado_carga) REFERENCES encabezado_carga(id_encabezado_carga)
);

CREATE TABLE cajas (
    id_cajas SERIAL PRIMARY KEY,
    id_encabezado_carga INTEGER NOT NULL,
    cantidad VARCHAR(255) NOT NULL,
    descripcion VARCHAR(255) NOT NULL,
    estado VARCHAR(255) NOT NULL,
    FOREIGN KEY (id_encabezado_carga) REFERENCES encabezado_carga(id_encabezado_carga)
);

CREATE TABLE botellas (
    id_botellas SERIAL PRIMARY KEY,
    id_encabezado_carga INTEGER NOT NULL,
    cantidad VARCHAR(255) NOT NULL,
    descripcion VARCHAR(255) NOT NULL,
     estado VARCHAR(255) NOT NULL,
    FOREIGN KEY (id_encabezado_carga) REFERENCES encabezado_carga(id_encabezado_carga)
);


CREATE OR REPLACE FUNCTION obtener_detalle_carga_relacionados(datos json)
RETURNS TABLE (id_detalle_carga integer) AS
$$
BEGIN
    -- Recorremos el array de objetos JSON
    FOR i IN 1..json_array_length(datos)
    LOOP
        -- Extraemos los valores del objeto JSON
        WITH obj AS (
            SELECT (json_array_elements(datos) ->> 'id_detalle_carga')::integer AS id_detalle_carga,
                   (json_array_elements(datos) ->> 'factura')::text AS factura,
                   (json_array_elements(datos) ->> 'id_encabezado_carga')::integer AS id_encabezado_carga,
                   (json_array_elements(datos) ->> 'estado')::text AS estado
        )
        -- Verificamos si existe una relación con el id_detalle_carga dado
        SELECT obj.id_detalle_carga INTO id_detalle_carga
        FROM obj
        WHERE EXISTS (
            SELECT 1
            FROM detalle_carga dc
            WHERE dc.id_detalle_carga = obj.id_detalle_carga
        );
    END LOOP;
    RETURN;
END;
$$
LANGUAGE plpgsql;

SET TIME ZONE 'America/Bogota';

ALTER TABLE usuario 
ADD COLUMN reset_token VARCHAR(64),
ADD COLUMN reset_token_expiry BIGINT;

DELETE FROM usuario WHERE id_usuario IN (1, 2, 3, 4, 5);

CREATE TABLE cambios_estado (
    id_cambio_estado SERIAL PRIMARY KEY,
    id_detalle_carga INT,
    id_encabezado_carga INT,
    estado_anterior VARCHAR(50),
    estado_nuevo VARCHAR(50),
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    usuario_id INT,
    FOREIGN KEY (id_detalle_carga) REFERENCES detalle_carga(id_detalle_carga),
    FOREIGN KEY (id_encabezado_carga) REFERENCES encabezado_carga(id_encabezado_carga),
    FOREIGN KEY (usuario_id) REFERENCES usuario(id_usuario) 
);







































-- Borra los cambios de estado asociados a los encabezados de carga
DELETE FROM cambios_estado
WHERE id_encabezado_carga IN (SELECT id_encabezado_carga FROM encabezado_carga);

-- Si la consulta anterior se ejecuta correctamente, procede con la siguiente

-- Borra las unidades asociadas a los encabezados de carga
DELETE FROM unidades
WHERE id_encabezado_carga IN (SELECT id_encabezado_carga FROM encabezado_carga);

-- Borra las cajas asociadas a los encabezados de carga
DELETE FROM cajas
WHERE id_encabezado_carga IN (SELECT id_encabezado_carga FROM encabezado_carga);

-- Borra las botellas asociadas a los encabezados de carga
DELETE FROM botellas
WHERE id_encabezado_carga IN (SELECT id_encabezado_carga FROM encabezado_carga);

-- Borra los detalles de carga asociados a los encabezados de carga
DELETE FROM detalle_carga
WHERE id_encabezado_carga IN (SELECT id_encabezado_carga FROM encabezado_carga);

-- Borra las zonas asociadas a los encabezados de carga
DELETE FROM encabezado_carga_zona
WHERE encabezado_carga_id IN (SELECT id_encabezado_carga FROM encabezado_carga);

-- Borra los usuarios asociados a los encabezados de carga
DELETE FROM encabezado_carga_usuario
WHERE encabezado_carga_id IN (SELECT id_encabezado_carga FROM encabezado_carga);

-- Borra los encabezados de carga
DELETE FROM encabezado_carga;






UPDATE vehiculo
SET disponibilidad_vehiculo = ''
WHERE disponibilidad_vehiculo = 'ocupado';

UPDATE usuario
SET disponibilidad = ''
WHERE disponibilidad = 'ocupado';