-- 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;
