-- Script de Creación de Base de Datos
-- Sistema de Gestión de Agua Mineral
-- Base de datos: aguadb
-- Usuario: mineral
-- Contraseña: @gu@miner@l

-- Crear la base de datos (ejecutar como root)
CREATE DATABASE IF NOT EXISTS aguadb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Crear usuario y otorgar permisos (ejecutar como root)
CREATE USER IF NOT EXISTS 'mineral'@'localhost' IDENTIFIED BY '@gu@miner@l';
GRANT ALL PRIVILEGES ON aguadb.* TO 'mineral'@'localhost';
FLUSH PRIVILEGES;

-- Usar la base de datos
USE aguadb;

-- Tabla de zonas
CREATE TABLE zonas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de usuarios
CREATE TABLE usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(255) NOT NULL,
    usuario VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    rol ENUM('administrador', 'vendedor') NOT NULL,
    zona_id INT NULL,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (zona_id) REFERENCES zonas(id) ON DELETE SET NULL
);

-- Tabla de productos
CREATE TABLE productos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    precio DECIMAL(10, 2) NOT NULL,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tabla de clientes
CREATE TABLE clientes (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(255) NOT NULL,
    direccion TEXT,
    latitud DECIMAL(10, 8) NULL,
    longitud DECIMAL(11, 8) NULL,
    telefono VARCHAR(20) NULL,
    vendedor_id INT NOT NULL,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (vendedor_id) REFERENCES usuarios(id) ON DELETE CASCADE
);

-- Tabla de ventas
CREATE TABLE ventas (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NOT NULL,
    fecha DATE NOT NULL,
    cantidad_bidones INT NOT NULL,
    precio_unitario DECIMAL(10, 2) NOT NULL,
    total DECIMAL(10, 2) GENERATED ALWAYS AS (cantidad_bidones * precio_unitario) STORED,
    observaciones TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
);

-- Tabla de pagos
CREATE TABLE pagos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    cliente_id INT NOT NULL,
    fecha DATE NOT NULL,
    monto DECIMAL(10, 2) NOT NULL,
    metodo_pago ENUM('efectivo', 'transferencia', 'cheque', 'otro') DEFAULT 'efectivo',
    observaciones TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (cliente_id) REFERENCES clientes(id) ON DELETE CASCADE
);

-- Tabla de logs (opcional, para auditoría)
CREATE TABLE logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT NULL,
    accion VARCHAR(100) NOT NULL,
    descripcion TEXT NULL,
    fecha TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE SET NULL
);

-- Índices para mejorar el rendimiento
CREATE INDEX idx_ventas_cliente_fecha ON ventas(cliente_id, fecha);
CREATE INDEX idx_ventas_fecha ON ventas(fecha);
CREATE INDEX idx_pagos_cliente_fecha ON pagos(cliente_id, fecha);
CREATE INDEX idx_clientes_vendedor ON clientes(vendedor_id);
CREATE INDEX idx_usuarios_rol ON usuarios(rol);

-- Insertar datos iniciales

-- Insertar zonas por defecto
INSERT INTO zonas (nombre) VALUES 
('Zona Centro'),
('Zona Norte'),
('Zona Sur'),
('Zona Este'),
('Zona Oeste');

-- Insertar producto por defecto
INSERT INTO productos (nombre, precio) VALUES 
('Bidón de Agua Mineral 20L', 15000);

-- Insertar usuario administrador
-- Contraseña: miner@l (hasheada)
INSERT INTO usuarios (nombre, usuario, password, rol) VALUES 
('Administrador del Sistema', 'agua', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'administrador');

-- Insertar vendedor de ejemplo
INSERT INTO usuarios (nombre, usuario, password, rol, zona_id) VALUES 
('Juan Pérez', 'vendedor1', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'vendedor', 1);

-- Insertar clientes de ejemplo
INSERT INTO clientes (nombre, direccion, latitud, longitud, telefono, vendedor_id) VALUES 
('María González', 'Av. Mariscal López 1234, Asunción', -25.2637, -57.5759, '0981-123-456', 2),
('Carlos Rodríguez', 'Palma 567, Asunción', -25.2808, -57.6311, '0982-765-432', 2),
('Ana López', 'Chile 890, Asunción', -25.2866, -57.6470, '0983-555-123', 2);

-- Insertar ventas de ejemplo
INSERT INTO ventas (cliente_id, fecha, cantidad_bidones, precio_unitario) VALUES 
(1, '2024-01-15', 2, 15000),
(1, '2024-02-15', 2, 15000),
(2, '2024-01-20', 1, 15000),
(2, '2024-02-20', 1, 15000),
(3, '2024-01-25', 3, 15000);

-- Insertar pagos de ejemplo
INSERT INTO pagos (cliente_id, fecha, monto, metodo_pago) VALUES 
(1, '2024-01-30', 30000, 'efectivo'),
(2, '2024-02-25', 15000, 'transferencia'),
(3, '2024-02-01', 20000, 'efectivo');
