-- Creación de la base de datos
CREATE DATABASE IF NOT EXISTS portal_valurq CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE portal_valurq;

-- Tabla Cliente
CREATE TABLE cliente (
    id INT AUTO_INCREMENT PRIMARY KEY,
    razon_social VARCHAR(255) NOT NULL,
    ruc_ci VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL,
    telefono VARCHAR(20) NOT NULL,
    pass VARCHAR(255) NOT NULL,
    cambiar_contrasena TINYINT(1) DEFAULT 0,
    url_cambio_contrasena VARCHAR(255),
    notificacion_nuevo_usuario TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Tabla Empresa
CREATE TABLE empresa (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_cliente INT NOT NULL,
    razon_social VARCHAR(255) NOT NULL,
    nombre_fantasia VARCHAR(255) NOT NULL,
    ruc VARCHAR(50) NOT NULL,
    logo VARCHAR(255),
    notificacion_cotizado TINYINT(1) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    FOREIGN KEY (id_cliente) REFERENCES cliente(id) ON DELETE CASCADE,
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- Tabla Sistema
CREATE TABLE sistema (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_empresa INT NOT NULL,
    cantidad_aproximada_facturas INT NOT NULL,
    fact_razon_social VARCHAR(255),
    fact_ruc VARCHAR(50),
    fact_telefono VARCHAR(20),
    fact_direccion VARCHAR(255),
    rubro VARCHAR(100),
    timbrado_prueba VARCHAR(100),
    timbrado_definitivo VARCHAR(100),
    doc_timbrado VARCHAR(100),
    doc_firma_digital VARCHAR(100),
    doc_const_ruc VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (id_empresa) REFERENCES empresa(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- portal_interno.vista_ticket_interno source

CREATE OR REPLACE VIEW `portal_interno`.`vista_ticket_interno` AS
select
    `t`.`id` AS `id`,
    `c`.`nombre_empresa` AS `empresa`,
    `t`.`titulo` AS `titulo`,
    `t`.`tipo` AS `tipo`,
    `t`.`usuario` AS `usuario`,
    `t`.`estado_ticket` AS `estado_ticket`,
    cast(`t`.`creacion` as date) AS `fecha`,
    `t`.`id_cliente_servidor_sistema` AS `id_cliente_servidor_sistema`,
    `t`.`subdominio` AS `subdominio`,
    `t`.`usuario_asignado` AS `usuario_asignado`
from
    ((`portal_interno`.`ticket` `t`
left join `portal_interno`.`cliente_servidor_sistema` `css` on
    ((`t`.`id_cliente_servidor_sistema` = `css`.`id`)))
left join `portal_interno`.`cliente` `c` on
    ((`css`.`id_cliente` = `c`.`id`)));

    -- portal_interno.vista_ticket_cliente source

CREATE OR REPLACE VIEW `portal_interno`.`vista_ticket_cliente` AS
select
    `t`.`id` AS `id`,
    `t`.`titulo` AS `titulo`,
    `t`.`tipo` AS `tipo`,
    `t`.`usuario` AS `usuario`,
    `t`.`estado_ticket` AS `estado_ticket`,
    cast(`t`.`creacion` as date) AS `fecha_creacion`,
    `t`.`id_cliente_servidor_sistema` AS `id_cliente_servidor_sistema`,
    `t`.`subdominio` AS `subdominio`
from
    `portal_interno`.`ticket` `t`;

    -- portal_interno.vista_requerimiento_interno source

CREATE OR REPLACE VIEW `portal_interno`.`vista_requerimiento_interno` AS
select
    `r`.`id` AS `id`,
    `c`.`nombre_empresa` AS `empresa`,
    `r`.`titulo` AS `titulo`,
    `r`.`tipo` AS `tipo`,
    `r`.`usuario` AS `usuario`,
    `r`.`estado_cliente` AS `estado_cliente`,
    `r`.`estado_interno` AS `estado_interno`,
    cast(`r`.`creacion` as date) AS `creacion`,
    `r`.`id_cliente_servidor_sistema` AS `id_cliente_servidor_sistema`
from
    ((`portal_interno`.`requerimiento` `r`
join `portal_interno`.`cliente_servidor_sistema` `css` on
    ((`r`.`id_cliente_servidor_sistema` = `css`.`id`)))
join `portal_interno`.`cliente` `c` on
    ((`css`.`id_cliente` = `c`.`id`)));

    -- portal_interno.vista_requerimiento_cliente source

CREATE OR REPLACE VIEW `portal_interno`.`vista_requerimiento_cliente` AS
select
    `r`.`id` AS `id`,
    `r`.`titulo` AS `titulo`,
    `r`.`tipo` AS `tipo`,
    `r`.`usuario` AS `usuario`,
    `r`.`estado_cliente` AS `estado_cliente`,
    cast(`r`.`creacion` as date) AS `fecha_creacion`,
    `r`.`id_cliente_servidor_sistema` AS `id_cliente_servidor_sistema`,
    `r`.`subdominio` AS `subdominio`
from
    `portal_interno`.`requerimiento` `r`;


