-- Estructura de base de datos para el sistema de predicción de cacao

-- Tabla de roles
CREATE TABLE roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado TINYINT(1)
);

INSERT INTO roles (id, nombre, estado) VALUES
    (1, 'administrador', 1),
    (2, 'gerente', 1),
    (3, 'empleado', 1)
ON DUPLICATE KEY UPDATE nombre=VALUES(nombre), estado=VALUES(estado);

-- Tabla de usuarios
CREATE TABLE usuario (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    apellido VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    password VARCHAR(255) NOT NULL,
    id_rol INT NOT NULL,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado TINYINT(1),
    FOREIGN KEY (id_rol) REFERENCES roles(id)
);

-- Tabla de clima
CREATE TABLE clima (
    id INT AUTO_INCREMENT PRIMARY KEY,
    anio INT NOT NULL,
    mes VARCHAR(20) NOT NULL,
    temperatura_media DECIMAL(5,2),
    temperatura_maxima DECIMAL(5,2),
    temperatura_minima DECIMAL(5,2),
    temperatura_oscilacion DECIMAL(5,2),
    humedad_media DECIMAL(5,2),
    humedad_maxima DECIMAL(5,2),
    humedad_minima DECIMAL(5,2),
    tension_vapor_agua DECIMAL(6,2),
    punto_rocio_grados DECIMAL(6,2),
    heliofania DECIMAL(6,2),
    evaporacion_mm DECIMAL(6,2),
    lluvia_mm DECIMAL(6,2),
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado TINYINT(1)
);

-- Tabla de producción
CREATE TABLE produccion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    anio INT NOT NULL,
    mes VARCHAR(20) NOT NULL,
    rendicion_mensual_percent DECIMAL(5,2),
    superficie_ha DECIMAL(10,2),
    produccion_t DECIMAL(10,2),
    rend_ccn51_t_ha DECIMAL(10,2),
    rend_fino_aroma_t_ha DECIMAL(10,2),
    rend_nacional_t_ha DECIMAL(10,2),
    fertilizantes_percent DECIMAL(5,2),
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado TINYINT(1)
);

-- Relación usuario-clima
CREATE TABLE usuario_clima (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT NOT NULL,
    id_clima INT NOT NULL,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado TINYINT(1),
    FOREIGN KEY (id_usuario) REFERENCES usuario(id),
    FOREIGN KEY (id_clima) REFERENCES clima(id)
);

-- Relación usuario-produccion
CREATE TABLE usuario_produccion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    id_usuario INT NOT NULL,
    id_produccion INT NOT NULL,
    fecha_creacion TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    estado TINYINT(1),
    FOREIGN KEY (id_usuario) REFERENCES usuario(id),
    FOREIGN KEY (id_produccion) REFERENCES produccion(id)
);
