-- Base de datos para Sistema de Rendición de Cuentas ONG
-- Creación de base de datos
CREATE DATABASE IF NOT EXISTS rendicionpy CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE rendicionpy;

-- Tabla de roles
CREATE TABLE IF NOT EXISTS roles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(50) NOT NULL UNIQUE,
    descripcion VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar roles predefinidos
INSERT INTO roles (nombre, descripcion) VALUES
('Administrador', 'Nivel superior con acceso total'),
('Supervisor', 'Gestiona operativos y fondos asignados'),
('Operativo', 'Registra movimientos y gastos');

-- Tabla de configuración del proyecto
CREATE TABLE IF NOT EXISTS configuracion (
    id INT AUTO_INCREMENT PRIMARY KEY,
    clave VARCHAR(100) NOT NULL UNIQUE,
    valor TEXT,
    descripcion VARCHAR(255),
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar configuración inicial del fondo total del proyecto
INSERT INTO configuracion (clave, valor, descripcion) VALUES
('fondo_total_proyecto', '0', 'Fondo total asignado al proyecto');

-- Tabla de usuarios
CREATE TABLE IF NOT EXISTS usuarios (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    nombre_completo VARCHAR(255) NOT NULL,
    cedula_ruc_pasaporte VARCHAR(50) NOT NULL,
    telefono VARCHAR(20),
    ciudad VARCHAR(100),
    direccion TEXT,
    latitud DECIMAL(10, 8),
    longitud DECIMAL(11, 8),
    observacion TEXT,
    rol_id INT NOT NULL,
    supervisor_id INT NULL,
    fondo_asignado DECIMAL(15, 2) DEFAULT 0,
    saldo_disponible DECIMAL(15, 2) DEFAULT 0,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (rol_id) REFERENCES roles(id),
    FOREIGN KEY (supervisor_id) REFERENCES usuarios(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar usuario administrador por defecto
INSERT INTO usuarios (username, password, nombre_completo, cedula_ruc_pasaporte, rol_id, fondo_asignado, saldo_disponible) 
VALUES ('Rendicion', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'Administrador Principal', '0000000', 1, 0, 0);
-- Contraseña: cuentas2025

-- Tabla de categorías
CREATE TABLE IF NOT EXISTS categorias (
    id INT AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(100) NOT NULL,
    tipo ENUM('Ingreso', 'Egreso') NOT NULL,
    descripcion TEXT,
    activo TINYINT(1) DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insertar categorías por defecto
INSERT INTO categorias (nombre, tipo, descripcion) VALUES
('Donación', 'Ingreso', 'Donaciones recibidas'),
('Asignación de Fondos', 'Ingreso', 'Fondos asignados por superior'),
('Alimentación', 'Egreso', 'Gastos en alimentación'),
('Transporte', 'Egreso', 'Gastos en transporte'),
('Materiales', 'Egreso', 'Compra de materiales'),
('Servicios', 'Egreso', 'Pago de servicios'),
('Otros Gastos', 'Egreso', 'Otros gastos varios');

-- Tabla de movimientos
CREATE TABLE IF NOT EXISTS movimientos (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT NOT NULL,
    categoria_id INT NOT NULL,
    tipo ENUM('Ingreso', 'Egreso') NOT NULL,
    monto DECIMAL(15, 2) NOT NULL,
    descripcion TEXT,
    comprobante VARCHAR(255),
    fecha_movimiento DATE NOT NULL,
    saldo_anterior DECIMAL(15, 2) NOT NULL,
    saldo_posterior DECIMAL(15, 2) NOT NULL,
    created_by INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE,
    FOREIGN KEY (categoria_id) REFERENCES categorias(id),
    FOREIGN KEY (created_by) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de asignaciones de fondos
CREATE TABLE IF NOT EXISTS asignaciones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    asignado_por INT NOT NULL,
    asignado_a INT NOT NULL,
    monto DECIMAL(15, 2) NOT NULL,
    descripcion TEXT,
    comprobante VARCHAR(255),
    fecha_asignacion DATE NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (asignado_por) REFERENCES usuarios(id),
    FOREIGN KEY (asignado_a) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Tabla de logs de actividad
CREATE TABLE IF NOT EXISTS logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    usuario_id INT NOT NULL,
    accion VARCHAR(255) NOT NULL,
    detalles TEXT,
    ip_address VARCHAR(45),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (usuario_id) REFERENCES usuarios(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Índices para mejorar el rendimiento
CREATE INDEX idx_usuarios_rol ON usuarios(rol_id);
CREATE INDEX idx_usuarios_supervisor ON usuarios(supervisor_id);
CREATE INDEX idx_movimientos_usuario ON movimientos(usuario_id);
CREATE INDEX idx_movimientos_fecha ON movimientos(fecha_movimiento);
CREATE INDEX idx_asignaciones_asignado_a ON asignaciones(asignado_a);
CREATE INDEX idx_logs_usuario ON logs(usuario_id);

