-- Base inicial de abisfact
-- Enfoque: seguridad + multiempresa + auditoría

CREATE TABLE IF NOT EXISTS usuarios (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    nombre VARCHAR(150) NOT NULL,
    usuario VARCHAR(80) NOT NULL,
    email VARCHAR(150) NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    es_super_admin TINYINT(1) NOT NULL DEFAULT 0,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    ultimo_login DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_usuarios_usuario (usuario),
    UNIQUE KEY uq_usuarios_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS tipo_empresa (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(30) NOT NULL,
    nombre VARCHAR(120) NOT NULL,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_tipo_empresa_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS empresas (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    ruc CHAR(11) NOT NULL,
    razon_social VARCHAR(200) NOT NULL,
    nombre_comercial VARCHAR(200) NULL,
    tipo_empresa_id BIGINT UNSIGNED NOT NULL DEFAULT 1,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_empresas_ruc (ruc),
    KEY idx_empresas_tipo_empresa (tipo_empresa_id),
    CONSTRAINT fk_empresas_tipo_empresa
        FOREIGN KEY (tipo_empresa_id) REFERENCES tipo_empresa(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS roles (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empresa_id BIGINT UNSIGNED NOT NULL,
    nombre VARCHAR(120) NOT NULL,
    descripcion VARCHAR(255) NULL,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    KEY idx_roles_empresa (empresa_id),
    CONSTRAINT fk_roles_empresa
        FOREIGN KEY (empresa_id) REFERENCES empresas(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS empresa_usuarios (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empresa_id BIGINT UNSIGNED NOT NULL,
    usuario_id BIGINT UNSIGNED NOT NULL,
    rol_id BIGINT UNSIGNED NULL,
    estado ENUM('ACTIVO','INACTIVO') NOT NULL DEFAULT 'ACTIVO',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    deleted_at DATETIME NULL,
    UNIQUE KEY uq_empresa_usuario (empresa_id, usuario_id),
    KEY idx_eu_usuario (usuario_id),
    KEY idx_eu_rol (rol_id),
    CONSTRAINT fk_eu_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id),
    CONSTRAINT fk_eu_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id),
    CONSTRAINT fk_eu_rol FOREIGN KEY (rol_id) REFERENCES roles(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS modulos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(80) NOT NULL,
    nombre VARCHAR(120) NOT NULL,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_modulos_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS acciones (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    codigo VARCHAR(80) NOT NULL,
    nombre VARCHAR(120) NOT NULL,
    estado TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    UNIQUE KEY uq_acciones_codigo (codigo)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS permisos (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    rol_id BIGINT UNSIGNED NOT NULL,
    modulo_id BIGINT UNSIGNED NOT NULL,
    accion_id BIGINT UNSIGNED NOT NULL,
    permitido TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NULL,
    UNIQUE KEY uq_permiso (rol_id, modulo_id, accion_id),
    CONSTRAINT fk_permisos_rol FOREIGN KEY (rol_id) REFERENCES roles(id),
    CONSTRAINT fk_permisos_modulo FOREIGN KEY (modulo_id) REFERENCES modulos(id),
    CONSTRAINT fk_permisos_accion FOREIGN KEY (accion_id) REFERENCES acciones(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS historial (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    empresa_id BIGINT UNSIGNED NOT NULL,
    usuario_id BIGINT UNSIGNED NULL,
    modulo VARCHAR(80) NOT NULL,
    accion VARCHAR(80) NOT NULL,
    registro_id BIGINT UNSIGNED NULL,
    resumen JSON NULL,
    ip VARCHAR(60) NULL,
    created_at DATETIME NOT NULL,
    deleted_at DATETIME NULL,
    KEY idx_historial_empresa_fecha (empresa_id, created_at),
    KEY idx_historial_modulo_registro (modulo, registro_id),
    CONSTRAINT fk_historial_empresa FOREIGN KEY (empresa_id) REFERENCES empresas(id),
    CONSTRAINT fk_historial_usuario FOREIGN KEY (usuario_id) REFERENCES usuarios(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT IGNORE INTO tipo_empresa (id, codigo, nombre, estado, created_at)
VALUES (1, 'GENERAL', 'General', 1, NOW());

INSERT IGNORE INTO modulos (id, codigo, nombre, estado, created_at) VALUES
(1, 'usuarios', 'Usuarios', 1, NOW()),
(2, 'empresas', 'Empresas', 1, NOW()),
(3, 'facturacion', 'Facturación', 1, NOW()),
(4, 'auditoria', 'Auditoría', 1, NOW()),
(5, 'catalogos_sunat', 'Catalogos SUNAT', 1, NOW()),
(6, 'terceros', 'Terceros', 1, NOW()),
(7, 'productos', 'Productos', 1, NOW()),
(8, 'inventario', 'Inventario', 1, NOW()),
(9, 'caja', 'Caja', 1, NOW()),
(10, 'compras', 'Compras', 1, NOW()),
(11, 'ventas', 'Ventas', 1, NOW()),
(12, 'polleria', 'Polleria', 1, NOW()),
(13, 'restaurante', 'Restaurante', 1, NOW()),
(14, 'bodega', 'Bodega', 1, NOW()),
(15, 'spa', 'Spa', 1, NOW()),
(16, 'servicios', 'Servicios', 1, NOW()),
(17, 'ferreteria', 'Ferreteria', 1, NOW()),
(18, 'cotizaciones', 'Cotizaciones', 1, NOW()),
(19, 'guias', 'Guias de remision', 1, NOW()),
(20, 'reportes', 'Reportes', 1, NOW()),
INSERT IGNORE INTO acciones (id, codigo, nombre, estado, created_at) VALUES
(1, 'ver', 'Ver', 1, NOW()),
(2, 'crear', 'Crear', 1, NOW()),
(3, 'editar', 'Editar', 1, NOW()),
(4, 'eliminar', 'Eliminar', 1, NOW()),
(5, 'anular', 'Anular', 1, NOW());