-- ##########################################################################
-- # Script SQL para la creación de la base de datos y tablas del CMS     #
-- # para la gestión de manuales de usuario.                              #
-- # Adaptado para PostgreSQL.                                            #
-- ##########################################################################

-- 1. Crear la Base de Datos (Si no existe)
CREATE DATABASE cms_manual_db ENCODING 'UTF8' LC_COLLATE 'es_VE.UTF-8' LC_CTYPE 'es_VE.UTF-8' TEMPLATE template0;

-- Después de crear la base de datos, conéctate a ella antes de ejecutar las siguientes sentencias:
-- \c cms_manual_db;

-- ##########################################################################
-- # 2. Creación de Tablas                                                #
-- ##########################################################################

-- Tabla para usuarios (autores/editores del contenido)
CREATE TABLE users (
    user_id SERIAL PRIMARY KEY,             -- ID único del usuario (SERIAL para auto-incremento en PostgreSQL)
    username VARCHAR(50) UNIQUE NOT NULL,   -- Nombre de usuario (único)
    email VARCHAR(100) UNIQUE NOT NULL,     -- Correo electrónico (único)
    password_hash VARCHAR(255) NOT NULL,    -- Hash de la contraseña (NUNCA almacenar contraseñas planas)
    full_name VARCHAR(100),                 -- Nombre completo del usuario
    role VARCHAR(50) DEFAULT 'author',      -- Rol del usuario (ej: 'admin', 'author', 'reviewer')
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP, -- Fecha y hora de creación del registro
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP  -- Fecha y hora de la última actualización
    -- En PostgreSQL, 'ON UPDATE CURRENT_TIMESTAMP' se implementa con un TRIGGER.
    -- Para una implementación completa, necesitarías un trigger como este:
    -- CREATE OR REPLACE FUNCTION update_updated_at_column()
    -- RETURNS TRIGGER AS $$
    -- BEGIN
    --    NEW.updated_at = NOW();
    --    RETURN NEW;
    -- END;
    -- $$ language 'plpgsql';
    --
    -- CREATE TRIGGER update_users_updated_at
    -- BEFORE UPDATE ON users
    -- FOR EACH ROW
    -- EXECUTE PROCEDURE update_updated_at_column();
);

-- Tabla para definir los tipos de documentos/plantillas
CREATE TABLE doc_types (
    doc_type_id SERIAL PRIMARY KEY,             -- ID único del tipo de documento
    type_name VARCHAR(50) UNIQUE NOT NULL,      -- Nombre del tipo de documento (ej: 'Opcion de Software', 'Proceso', 'Como se hace')
    description TEXT,                           -- Descripción del tipo de documento
    -- template_schema almacena un JSON que describe los campos esperados para este tipo de documento.
    -- Esto permite que la interfaz de edición se adapte dinámicamente.
    template_schema JSONB                       -- JSONB es una versión binaria optimizada de JSON en PostgreSQL
);

-- Tabla para categorías de documentos (ej: Recepción, Clientes, etc.)
CREATE TABLE categories (
    category_id SERIAL PRIMARY KEY,             -- ID único de la categoría
    name VARCHAR(100) UNIQUE NOT NULL,          -- Nombre de la categoría
    description TEXT,                           -- Descripción de la categoría
    parent_category_id INT,                     -- Para categorías anidadas (FK a sí misma)
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (parent_category_id) REFERENCES categories(category_id) ON DELETE SET NULL
);

-- Definición del tipo ENUM para el estado del documento en PostgreSQL
CREATE TYPE document_status AS ENUM ('draft', 'published', 'archived');

-- Tabla principal para almacenar los documentos/manuales
CREATE TABLE documents (
    document_id SERIAL PRIMARY KEY,             -- ID único del documento
    doc_type_id INT NOT NULL,                   -- FK al tipo de documento (Opcion, Proceso, Como se hace)
    category_id INT,                            -- FK a la categoría (ej: Recepcion, Clientes)
    title VARCHAR(255) NOT NULL,                -- Título del documento
    slug VARCHAR(255) UNIQUE NOT NULL,          -- Slug para URL amigable (ej: "como-hacer-checkin")
    status document_status DEFAULT 'draft',     -- Estado del documento (usando el tipo ENUM creado)
    version VARCHAR(20) DEFAULT '1.0',          -- Versión del documento

    -- Campos de contenido, mapeados de las plantillas.
    -- Se almacenan como TEXT (para Markdown) o JSONB (para datos estructurados/tablas).

    -- Campos comunes a todas las plantillas (si aplica, o específicos por plantilla)
    purpose TEXT,                               -- Propósito / Objetivo / Qué aprenderás
    best_practices TEXT,                        -- Consejos y Mejores Prácticas / Consejos Adicionales

    -- Campos específicos de 'Opcion de Software'
    access_route TEXT,                          -- Acceso a la Opción/Módulo
    ui_description TEXT,                        -- Interfaz de Usuario (UI)
    fields_controls JSONB,                      -- Campos y Controles (almacenado como JSONB)
    functionalities_actions TEXT,               -- Funcionalidades y Acciones
    system_messages JSONB,                      -- Mensajes del Sistema y Errores Comunes (almacenado como JSONB)

    -- Campos específicos de 'Proceso'
    roles_responsibilities JSONB,               -- Roles y Responsabilidades (almacenado como JSONB)
    requirements_prerequisites TEXT,            -- Requisitos / Prerrequisitos
    process_flow TEXT,                          -- Descripción del Flujo del Proceso
    exceptions_error_handling TEXT,             -- Excepciones / Manejo de Errores

    -- Campos específicos de 'Como se hace'
    prerequisites_task TEXT,                    -- Prerrequisitos (específico de tarea)
    steps_to_follow TEXT,                       -- Pasos a Seguir
    troubleshooting_common_issues TEXT,         -- Solución de Problemas Comunes

    -- Relación con el autor
    author_id INT NOT NULL,

    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

    -- Definición de claves foráneas
    FOREIGN KEY (doc_type_id) REFERENCES doc_types(doc_type_id) ON DELETE RESTRICT,
    FOREIGN KEY (category_id) REFERENCES categories(category_id) ON DELETE SET NULL,
    FOREIGN KEY (author_id) REFERENCES users(user_id) ON DELETE RESTRICT
);

-- Tabla para almacenar referencias a imágenes de los documentos
CREATE TABLE document_images (
    image_id SERIAL PRIMARY KEY,                -- ID único de la imagen
    document_id INT NOT NULL,                   -- FK al documento al que pertenece la imagen
    image_url VARCHAR(255) NOT NULL,            -- URL o ruta de la imagen (ej: /uploads/img_modulo_clientes.png)
    description VARCHAR(255),                   -- Descripción alternativa de la imagen (alt text)
    section_identifier VARCHAR(50),             -- Identificador de la sección del documento donde se usa la imagen (ej: 'ui_principal', 'paso_2')
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (document_id) REFERENCES documents(document_id) ON DELETE CASCADE
);

-- ##########################################################################
-- # 3. Inserción de Datos Iniciales (Opcional)                           #
-- ##########################################################################

-- Insertar tipos de documentos base
INSERT INTO doc_types (type_name, description, template_schema) VALUES
('Opcion de Software', 'Plantilla para describir módulos o funciones del software.', '{
    "sections": [
        {"id": "purpose", "name": "Propósito / Descripción General", "type": "text", "required": true},
        {"id": "access_route", "name": "Acceso a la Opción/Módulo", "type": "text", "required": true},
        {"id": "ui_description", "name": "Interfaz de Usuario (UI)", "type": "text", "required": true},
        {"id": "fields_controls", "name": "Campos y Controles", "type": "table", "required": true, "columns": ["Nombre del Campo/Control", "Tipo de Control", "Descripción / Propósito", "Ejemplos de Uso / Notas"]},
        {"id": "functionalities_actions", "name": "Funcionalidades y Acciones", "type": "text", "required": true},
        {"id": "system_messages", "name": "Mensajes del Sistema y Errores Comunes", "type": "table", "required": false, "columns": ["Mensaje", "Tipo", "Cuándo Aparece", "Solución Sugerida"]},
        {"id": "best_practices", "name": "Consejos y Mejores Prácticas", "type": "text", "required": false}
    ]
}'),
('Proceso', 'Plantilla para describir flujos de trabajo o procesos de negocio.', '{
    "sections": [
        {"id": "purpose_objective", "name": "Propósito / Objetivo del Proceso", "type": "text", "required": true},
        {"id": "roles_responsibilities", "name": "Roles y Responsabilidades (Involucrados)", "type": "table", "required": true, "columns": ["Rol / Departamento", "Responsabilidad Principal"]},
        {"id": "requirements_prerequisites", "name": "Requisitos / Prerrequisitos", "type": "text", "required": true},
        {"id": "process_flow", "name": "Descripción del Flujo del Proceso (Pasos Detallados)", "type": "text", "required": true},
        {"id": "system_messages", "name": "Eventos / Mensajes del Sistema", "type": "table", "required": false, "columns": ["Mensaje del Sistema", "Tipo", "Cuándo Aparece", "Acción Requerida"]},
        {"id": "exceptions_error_handling", "name": "Excepciones / Manejo de Errores", "type": "text", "required": false},
        {"id": "best_practices", "name": "Consideraciones y Mejores Prácticas", "type": "text", "required": false}
    ]
}'),
('Como se hace', 'Plantilla para guías paso a paso de tareas específicas.', '{
    "sections": [
        {"id": "objective_guide", "name": "Objetivo de esta Guía", "type": "text", "required": true},
        {"id": "prerequisites_task", "name": "Prerrequisitos", "type": "text", "required": true},
        {"id": "steps_to_follow", "name": "Pasos a Seguir", "type": "text", "required": true},
        {"id": "troubleshooting_common_issues", "name": "Solución de Problemas Comunes", "type": "text", "required": false},
        {"id": "additional_tips", "name": "Consejos Adicionales", "type": "text", "required": false}
    ]
}');

-- Insertar categorías de ejemplo
INSERT INTO categories (name, description) VALUES
('General', 'Documentación general del sistema.'),
('Recepción', 'Documentación relacionada con las operaciones de recepción.'),
('Caja', 'Documentación sobre gestión de cobros y pagos.'),
('Reportes', 'Guías y descripciones de los reportes del sistema.'),
('Configuración', 'Manuales sobre la configuración del sistema.'),
('Clientes', 'Gestión de la base de datos de clientes.');

-- Insertar subcategorías (ejemplo)
INSERT INTO categories (name, description, parent_category_id) VALUES
('Check-in', 'Procesos y guías de check-in de huéspedes.', (SELECT category_id FROM categories WHERE name = 'Recepción')),
('Check-out', 'Procesos y guías de check-out de huéspedes.', (SELECT category_id FROM categories WHERE name = 'Recepción')),
('Reservas', 'Gestión de reservas de habitaciones.', (SELECT category_id FROM categories WHERE name = 'Recepción'));

-- Insertar un usuario de ejemplo (¡CAMBIAR CONTRASEÑA EN PRODUCCIÓN!)
-- La contraseña 'password123' debe ser hasheada en una aplicación real.
-- Ejemplo de hash (NO USAR ESTO DIRECTAMENTE EN PRODUCCIÓN):
-- Para PostgreSQL, puedes usar la extensión pgcrypto (ej. crypt('password123', gen_salt('bf')))
INSERT INTO users (username, email, password_hash, full_name, role) VALUES
('admin_user', 'admin@hotelratio.com', 'hashed_password_here', 'Administrador Principal', 'admin');

-- ##########################################################################
-- # NOTA IMPORTANTE:                                                     #
-- # Los campos TEXT/JSONB en la tabla 'documents' están diseñados para   #
-- # almacenar el contenido en formato Markdown o JSON.                   #
-- # Tu aplicación web (frontend/backend) será la encargada de:           #
-- # 1. Parsear el JSON de 'template_schema' para construir el formulario #
-- #    de edición dinámicamente.                                         #
-- # 2. Guardar el contenido introducido por el usuario en Markdown/JSON  #
-- #    en los campos correspondientes de la tabla 'documents'.           #
-- # 3. Renderizar el Markdown a HTML para la visualización final.        #
-- ##########################################################################
