79 lines
2.8 KiB
SQL
79 lines
2.8 KiB
SQL
-- =============================================
|
|
-- Módulo Chat — Crear tablas en PostgreSQL
|
|
-- Ejecutar en el servidor con:
|
|
-- psql -U <usuario> -d <basedatos> -f chat_tables.sql
|
|
-- =============================================
|
|
|
|
CREATE TABLE IF NOT EXISTS chat_config (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
config_key VARCHAR(255) NOT NULL UNIQUE,
|
|
config_value TEXT,
|
|
description VARCHAR(255),
|
|
created_at TIMESTAMP NULL,
|
|
updated_at TIMESTAMP NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS chat_contacts (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
canal VARCHAR(20) NOT NULL,
|
|
canal_id VARCHAR(255) NOT NULL,
|
|
nombre VARCHAR(255),
|
|
telefono VARCHAR(255),
|
|
metadata JSONB,
|
|
created_at TIMESTAMP NULL,
|
|
updated_at TIMESTAMP NULL,
|
|
UNIQUE (canal, canal_id)
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS chat_conversations (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
contact_id BIGINT NOT NULL REFERENCES chat_contacts(id) ON DELETE CASCADE,
|
|
canal VARCHAR(20) NOT NULL,
|
|
estado VARCHAR(20) NOT NULL DEFAULT 'bot',
|
|
menu_actual_id INTEGER,
|
|
ultimo_mensaje_at TIMESTAMP NULL,
|
|
mensajes_no_leidos INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP NULL,
|
|
updated_at TIMESTAMP NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS chat_messages (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
conversation_id BIGINT NOT NULL REFERENCES chat_conversations(id) ON DELETE CASCADE,
|
|
tipo VARCHAR(20) NOT NULL,
|
|
contenido TEXT NOT NULL,
|
|
leido BOOLEAN NOT NULL DEFAULT FALSE,
|
|
created_at TIMESTAMP NULL,
|
|
updated_at TIMESTAMP NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS chat_menus (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
nombre VARCHAR(255) NOT NULL,
|
|
mensaje TEXT NOT NULL,
|
|
es_raiz BOOLEAN NOT NULL DEFAULT FALSE,
|
|
activo BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMP NULL,
|
|
updated_at TIMESTAMP NULL
|
|
);
|
|
|
|
CREATE TABLE IF NOT EXISTS chat_menu_options (
|
|
id BIGSERIAL PRIMARY KEY,
|
|
menu_id BIGINT NOT NULL REFERENCES chat_menus(id) ON DELETE CASCADE,
|
|
clave VARCHAR(20) NOT NULL,
|
|
etiqueta VARCHAR(255) NOT NULL,
|
|
accion VARCHAR(30) NOT NULL,
|
|
menu_destino_id BIGINT,
|
|
respuesta_texto TEXT,
|
|
orden INTEGER NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMP NULL,
|
|
updated_at TIMESTAMP NULL
|
|
);
|
|
|
|
-- Registrar la migración en la tabla de Laravel para que no se corra de nuevo
|
|
INSERT INTO migrations (migration, batch)
|
|
SELECT '2026_04_30_000001_create_chat_tables', COALESCE((SELECT MAX(batch) FROM migrations), 0) + 1
|
|
WHERE NOT EXISTS (
|
|
SELECT 1 FROM migrations WHERE migration = '2026_04_30_000001_create_chat_tables'
|
|
);
|