CREATE OR REPLACE FUNCTION update_public_website()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
DECLARE
payload JSON;
response JSON;
current_timestamp TIMESTAMP;
existing_record_id INTEGER;
website_slug TEXT;
website_domain TEXT;
BEGIN
-- Obtener el slug y domain del Website
SELECT slug, domain INTO website_slug, website_domain
FROM "Website"
WHERE id = NEW."websiteId";
-- Construir el payload JSON
payload := json_build_object(
'menu', (
SELECT json_agg(json_build_object(
'menuName', md."menuName",
'slug', md.slug,
'iconName', md."iconName",
'order', md."order"
))
FROM "MenuDetail" md
JOIN "MenuHeader" mh ON md."menuHeaderId" = mh.id
WHERE mh."websiteId" = NEW."websiteId"
),
'pages', (
SELECT json_agg(json_build_object(
'id', p.id,
'pageName', p."pageName",
'slug', (
SELECT md.slug
FROM "MenuDetail" md
WHERE md."pageId" = p.id
LIMIT 1
),
'sections', (
SELECT pt.sections
FROM "PageTemplate" pt
WHERE pt."id" = p."templateId"
)
))
FROM "Page" p
WHERE p."websiteId" = NEW."websiteId"
)
);
-- Verificar si ya existe un registro para este websiteId
SELECT id INTO existing_record_id
FROM "PublicWebsite"
WHERE "websiteId" = NEW."websiteId"
LIMIT 1;
IF existing_record_id IS NULL THEN
-- Obtener el timestamp actual justo antes de la inserción
SELECT NOW() INTO current_timestamp;
-- No existe un registro, así que insertamos uno nuevo
INSERT INTO "PublicWebsite" (
"websiteId",
content,
"isPublic",
"isActive",
"publishedAt",
"createdAt",
"updatedAt",
"websiteSlug",
"websiteDomain"
)
VALUES (
NEW."websiteId",
payload,
NEW."isPublic",
NEW."isActive",
NEW."publishedAt",
current_timestamp,
current_timestamp,
website_slug,
website_domain
);
ELSE
-- Obtener el timestamp actual justo antes de la actualización
SELECT NOW() INTO current_timestamp;
-- Existe un registro, así que lo actualizamos
UPDATE "PublicWebsite"
SET
content = payload,
"isPublic" = NEW."isPublic",
"isActive" = NEW."isActive",
"publishedAt" = NEW."publishedAt",
"updatedAt" = current_timestamp,
"websiteSlug" = website_slug,
"websiteDomain" = website_domain
WHERE id = existing_record_id;
END IF;
RETURN NEW;
END;
$$;
CREATE TRIGGER trigger_update_public_website
AFTER INSERT OR UPDATE ON "PublishHistory"
FOR EACH ROW
EXECUTE FUNCTION update_public_website();
CREATE OR REPLACE FUNCTION update_public_website() RETURNS TRIGGER LANGUAGE plpgsql AS $$ DECLARE payload JSON; response JSON; current_timestamp TIMESTAMP; existing_record_id INTEGER; website_slug TEXT; website_domain TEXT; BEGIN -- Obtener el slug y domain del Website SELECT slug, domain INTO website_slug, website_domain FROM "Website" WHERE id = NEW."websiteId";
END; $$;
CREATE TRIGGER trigger_update_public_website AFTER INSERT OR UPDATE ON "PublishHistory" FOR EACH ROW EXECUTE FUNCTION update_public_website();