CRM-Hotel/docs/ARQUITECTURA.md

Arquitectura del Sistema - Hacienda San Angel

Este documento describe la arquitectura del sistema y la estructura de la base de datos.

---

Diagrama de Arquitectura General

flowchart TB
    subgraph Cliente["Cliente (Browser)"]
        FE[Frontend React]
    end

    subgraph Docker["Docker Compose"]
        subgraph Frontend["Frontend Container :5172"]
            VITE[Vite + React 19]
        end

        subgraph Backend["Backend Container :4000"]
            EXPRESS[Express.js API]
            CONTROLLERS[Controllers]
            ROUTES[Routes]
            SERVICES[Services]
        end

        subgraph Database["PostgreSQL Container :5432"]
            PG[(PostgreSQL 15)]
            FUNCTIONS[Stored Functions]
        end
    end

    subgraph External["Servicios Externos"]
        STRIPE[Stripe API]
        BANXICO[Banxico API]
        BREVO[Brevo SMTP]
    end

    FE <-->|HTTP/HTTPS| VITE
    VITE <-->|API Calls| EXPRESS
    EXPRESS --> CONTROLLERS
    CONTROLLERS --> ROUTES
    CONTROLLERS --> SERVICES
    EXPRESS <-->|pg driver| PG
    PG --> FUNCTIONS

    SERVICES -->|Payments| STRIPE
    SERVICES -->|Exchange Rate| BANXICO
    SERVICES -->|Email| BREVO

    style FE fill:#61dafb
    style VITE fill:#646cff
    style EXPRESS fill:#68a063
    style PG fill:#336791
    style STRIPE fill:#635bff
    style BANXICO fill:#006847
    style BREVO fill:#0092ff

---

Flujo de Datos

sequenceDiagram
    participant U as Usuario
    participant F as Frontend (React)
    participant B as Backend (Express)
    participant DB as PostgreSQL
    participant E as Servicios Externos

    U->>F: Interaccion UI
    F->>B: HTTP Request (Axios)
    B->>DB: Query/Function Call
    DB-->>B: Result Set

    alt Requiere servicio externo
        B->>E: API Call (Stripe/Banxico/Email)
        E-->>B: Response
    end

    B-->>F: JSON Response
    F-->>U: Actualiza UI

---

Estructura de Modulos

flowchart LR
    subgraph FrontendModules["Frontend Modules"]
        direction TB
        AUTH[Auth Context]
        DASH[Dashboard]
        EMP[Employees]
        PAY[Payroll]
        EXP[Expenses]
        INV[Inventory]
        INC[Income]
        SET[Settings]
    end

    subgraph BackendAPI["Backend API Endpoints"]
        direction TB
        A1[/api/auth]
        A2[/api/employees]
        A3[/api/contracts]
        A4[/api/expenses]
        A5[/api/products]
        A6[/api/incomes]
        A7[/api/payment]
        A8[/api/settings]
    end

    AUTH --> A1
    DASH --> A6
    EMP --> A2
    PAY --> A3
    EXP --> A4
    INV --> A5
    INC --> A6
    SET --> A8

---

Diagrama de Base de Datos (ERD)

Modulo de Empleados y Nomina

erDiagram
    EMPLOYEES {
        int id_employees PK
        varchar name_employee
        varchar rfc_employee UK
        bigint nss_employee
        int id_position FK
        int id_area FK
        varchar addres_employee
        varchar phone_employee
        varchar email_employee
        date birthday
        varchar curp
    }

    CONTRACTS {
        int id_contract PK
        int id_company FK
        int id_properties FK
        varchar rfc_employee FK
        date contratc_start
        date contratc_end
        int id_contract_status FK
        numeric daily_pay
    }

    POSITIONS {
        int id_position PK
        varchar name_position
        varchar spanish_name
    }

    AREAS {
        int id_area PK
        varchar name_area
    }

    STATUS_CONTRACT {
        int id_stat_contract PK
        varchar name_stat_contract
    }

    UNIFORMS {
        int id_uniform PK
        varchar name_uniform
    }

    DETAIL_UNIFOR_EMPLOYEE {
        int id_detail PK
        varchar rfc_employee FK
        int id_uniform FK
    }

    ATTENDANCE {
        int id_attendance PK
        varchar first_name
        varchar last_name
        varchar department
        date date
        date weekday
        time work_start_time
        time work_end_time
    }

    EMPLOYEES ||--o{ CONTRACTS : has
    EMPLOYEES ||--o{ DETAIL_UNIFOR_EMPLOYEE : wears
    EMPLOYEES }o--|| POSITIONS : has
    EMPLOYEES }o--|| AREAS : belongs_to
    CONTRACTS }o--|| STATUS_CONTRACT : has
    UNIFORMS ||--o{ DETAIL_UNIFOR_EMPLOYEE : assigned_to

Modulo de Gastos y Pagos

erDiagram
    EXPENSES {
        int id_expense PK
        int id_properties FK
        int id_expense_type FK
        int id_recurrence FK
        varchar description
        int id_suppliers FK
        date request_date
        date payment_deadline
        date approval_date
        int id_request_by FK
        int id_approval_by FK
        int id_area FK
        int id_expense_cat FK
        int id_approval_stat FK
        int id_currency FK
        numeric subtotal
        numeric iva
        numeric ieps
        numeric total
        boolean is_monthly_expense
    }

    PURCHASE_DETAIL {
        int id_purchase_dt PK
        int id_expense FK
        int id_product FK
        int quantity
        int id_tax FK
        numeric unit_cost
        numeric total
        int id_stock_mov FK
    }

    PAYMENTS {
        int id_payment PK
        int id_expense FK
        int id_payment_status FK
        date payment_date
    }

    SUPPLIERS {
        int id_suppliers PK
        varchar name_suppliers
        varchar rfc_suppliers UK
        varchar mail
        varchar phone
    }

    EXPENSE_CATEGORY {
        int id_expense_cat PK
        varchar name_category
        varchar spanish_name
    }

    APPROVAL_STATUS {
        int id_approval_stat PK
        varchar name_approval_stat
    }

    PAYMENT_STATUS {
        int id_payment_status PK
        varchar name_pay_status
    }

    CURRENCY {
        int id_currency PK
        varchar name_currency
    }

    TAXES {
        int id_tax PK
        varchar percentage
        numeric number
    }

    EXPENSES ||--o{ PURCHASE_DETAIL : contains
    EXPENSES ||--|| PAYMENTS : has
    EXPENSES }o--|| SUPPLIERS : from
    EXPENSES }o--|| EXPENSE_CATEGORY : categorized_as
    EXPENSES }o--|| APPROVAL_STATUS : has
    EXPENSES }o--|| CURRENCY : in
    EXPENSES }o--|| AREAS : belongs_to
    PAYMENTS }o--|| PAYMENT_STATUS : has
    PURCHASE_DETAIL }o--|| TAXES : taxed_by
    PURCHASE_DETAIL }o--o| PRODUCTS : includes

Modulo de Inventario

erDiagram
    PRODUCTS {
        int id_product PK
        bytea image_product
        varchar name_product
        bigint sku_product
        varchar rfc_suppliers FK
        numeric price_product
        int id_tax FK
        int id_prod_category FK
        int id_currency FK
        int id_unit FK
    }

    STOCK {
        int id_stock PK
        int id_product FK
        bigint stock
    }

    STOCK_MOVEMENTS {
        int id_sto_mov PK
        int id_product FK
        int id_mov_type FK
        date date_st_mov
        bigint stock_before
        bigint stock_current
    }

    STOCK_ADJUSMENTS {
        int id_stock_adjusment PK
        int id_stock FK
        varchar adjustment
        date date_adjusment
        int id_stock_mov FK
        int id_expense FK
        int id_product FK
    }

    STOCK_DISCARD {
        int id_stock_discard PK
        int id_product FK
        int quantity
        varchar reason
        date date_discard
        int id_stock_mov FK
    }

    STOCK_CONSUMPTION {
        int id_consumption PK
        varchar rfc_employee FK
        date consumption_date
        int consumption_quantity
        int id_product FK
        int id_stock_mov FK
    }

    PRODUCT_CATEGORY {
        int id_prod_category PK
        varchar name_prod_category
        varchar spanish_name
    }

    PRODUCT_TYPE {
        int id_product_type PK
        varchar name_product_type
        varchar spanish_name
    }

    DETAIL_PRODUCT_TYPE {
        int id_detail PK
        int id_product FK
        int id_product_type FK
    }

    MOVEMENT_TYPE {
        int id_mov_type PK
        varchar name_mov_type
    }

    UNIT {
        int id_unit PK
        varchar name_unit
    }

    PRODUCTS ||--|| STOCK : has
    PRODUCTS ||--o{ STOCK_MOVEMENTS : generates
    PRODUCTS ||--o{ DETAIL_PRODUCT_TYPE : has
    PRODUCTS }o--|| PRODUCT_CATEGORY : categorized_as
    PRODUCTS }o--|| UNIT : measured_in
    PRODUCTS }o--|| SUPPLIERS : from
    STOCK_MOVEMENTS }o--|| MOVEMENT_TYPE : of_type
    STOCK_MOVEMENTS ||--o| STOCK_ADJUSMENTS : creates
    STOCK_MOVEMENTS ||--o| STOCK_DISCARD : creates
    STOCK_MOVEMENTS ||--o| STOCK_CONSUMPTION : creates
    PRODUCT_TYPE ||--o{ DETAIL_PRODUCT_TYPE : categorizes
    STOCK_CONSUMPTION }o--|| EMPLOYEES : by

Modulo de Ingresos y Hotel

erDiagram
    INCOMES {
        int id_income PK
        varchar room_type
        varchar channel_name
        date check_in
        date check_out
        numeric payment_total
        varchar guest_name
        varchar reservation_id
    }

    PROPERTIES {
        int id_properties PK
        varchar name_propertie
        int id_company FK
        varchar addres_property
        int main_area FK
        varchar rfc_property
        varchar phone_property
    }

    ROOMS {
        int id_room PK
        varchar name_room
        int id_properties FK
        int units
        numeric average_cost_per_night
        int guests
        int id_bed FK
    }

    AMENITIES {
        int id_amenity PK
        varchar name_amenity
    }

    DETAIL_AMENITIES {
        int id_detail PK
        int id_amenity FK
        int id_room FK
    }

    ROOM_PRODUCT {
        int id_room_product PK
        int id_room FK
        int id_product FK
        int quantity_product
    }

    SERVICES {
        int id_service PK
        varchar name_service
    }

    SERVICES_DETAIL {
        int id_service_detail PK
        int id_service FK
        int id_property FK
        varchar percentage
    }

    EXCHANGE_RATE {
        int id_exchange PK
        date date_exchange
        numeric rate
    }

    PROPERTIES ||--o{ ROOMS : contains
    ROOMS ||--o{ DETAIL_AMENITIES : has
    ROOMS ||--o{ ROOM_PRODUCT : contains
    AMENITIES ||--o{ DETAIL_AMENITIES : in
    PRODUCTS ||--o{ ROOM_PRODUCT : used_in
    PROPERTIES ||--o{ SERVICES_DETAIL : offers
    SERVICES ||--o{ SERVICES_DETAIL : provided_at

Modulo de Usuarios y Seguridad

erDiagram
    USERS {
        int id_users PK
        varchar name_user
        int id_properties FK
        int id_roles FK
        varchar mail
        varchar password_user
    }

    ROLES {
        int id_roles PK
        varchar name_roles
    }

    COMPANY {
        int id_company PK
        varchar name_company
        varchar rfc_company
    }

    USERS }o--|| ROLES : has
    USERS }o--|| PROPERTIES : belongs_to
    PROPERTIES }o--|| COMPANY : owned_by

---

Diagrama Completo de Relaciones

flowchart TB
    subgraph Core["Nucleo del Sistema"]
        COMPANY[Company]
        PROPERTIES[Properties]
        USERS[Users]
    end

    subgraph HR["Recursos Humanos"]
        EMPLOYEES[Employees]
        CONTRACTS[Contracts]
        ATTENDANCE[Attendance]
    end

    subgraph Finance["Finanzas"]
        EXPENSES[Expenses]
        PAYMENTS[Payments]
        INCOMES[Incomes]
    end

    subgraph Inventory["Inventario"]
        PRODUCTS[Products]
        STOCK[Stock]
        MOVEMENTS[Stock Movements]
    end

    subgraph Hotel["Hotel"]
        ROOMS[Rooms]
        AMENITIES[Amenities]
        SERVICES[Services]
    end

    subgraph Catalogs["Catalogos"]
        SUPPLIERS[Suppliers]
        CATEGORIES[Categories]
        POSITIONS[Positions]
        AREAS[Areas]
    end

    COMPANY --> PROPERTIES
    PROPERTIES --> USERS
    PROPERTIES --> ROOMS
    PROPERTIES --> EMPLOYEES

    EMPLOYEES --> CONTRACTS
    EMPLOYEES --> ATTENDANCE

    EXPENSES --> PAYMENTS
    EXPENSES --> PRODUCTS
    SUPPLIERS --> EXPENSES
    SUPPLIERS --> PRODUCTS

    PRODUCTS --> STOCK
    STOCK --> MOVEMENTS

    ROOMS --> AMENITIES
    ROOMS --> SERVICES
    ROOMS --> PRODUCTS

    CATEGORIES --> PRODUCTS
    CATEGORIES --> EXPENSES
    POSITIONS --> EMPLOYEES
    AREAS --> EMPLOYEES
    AREAS --> EXPENSES

---

Funciones Almacenadas Principales

Funcion	Descripcion	Modulo
validarusuario()	Autenticacion de usuarios	Auth
getemployees()	Listar empleados con contratos	HR
newcontract()	Crear/renovar contrato de empleado	HR
getcontracts()	Listar contratos	HR
expiredcontracts()	Actualizar contratos vencidos	HR
newexpensev2()	Registrar nuevo gasto	Finance
updateexpensev4()	Actualizar gasto existente	Finance
getmonthlypayments()	Obtener pagos mensuales	Finance
getpendingappexpenses()	Gastos pendientes de aprobacion	Finance
getproductsdisplay()	Listar productos con stock	Inventory
discardproductsstock()	Descartar productos	Inventory
consumptionstock()	Registrar consumo de stock	Inventory
setstockadjusmentsv2()	Ajustar inventario	Inventory
report_inventoryv2()	Reporte de movimientos de inventario	Inventory
getincomes()	Obtener ingresos por periodo	Income
channelscards()	Analisis de canales de reserva	Income
onegetchange()	Obtener tipo de cambio	Exchange
new_room()	Crear habitacion con amenidades	Hotel
new_property()	Crear propiedad	Hotel

---

Tipos de Movimientos de Inventario

ID	Tipo	Descripcion
1	Purchase	Entrada por compra
2	Adjustment	Ajuste de inventario
3	Discard	Descarte/Baja
4	Sale	Venta
5	Consumption	Consumo interno

---

Estados del Sistema

Estados de Contrato

ID	Estado
1	Active
2	Expired

Estados de Aprobacion

ID	Estado
1	Approved
2	Rejected
3	Pending

Estados de Pago

ID	Estado
1	Paid
2	Pending

---

Notas Tecnicas

1. Tipo de Cambio: El sistema consulta automaticamente el tipo de cambio de Banxico para gastos en dolares
2. Validacion de Stock: Las funciones de descarte y consumo validan stock disponible antes de procesar
3. Historial de Movimientos: Cada movimiento de inventario registra stock anterior y actual
4. Contratos Automaticos: Existe una funcion para actualizar automaticamente contratos vencidos