-- 2026-04-16
-- Modulo de espacos publicitarios para paginas de profissionais e clientes
-- Inclui:
-- - schema base (slots, campanhas, vinculos e eventos)
-- - slots padrao para paginas de cliente/profissional
-- - tela/menu/namespace da area administrativa

START TRANSACTION;

CREATE TABLE IF NOT EXISTS wpjy_ads_slots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_contrato BIGINT UNSIGNED NOT NULL DEFAULT 0,
  code VARCHAR(120) NOT NULL,
  page_key VARCHAR(120) NOT NULL,
  placement_key VARCHAR(120) NOT NULL,
  title VARCHAR(160) NOT NULL,
  description TEXT NULL,
  max_items INT NOT NULL DEFAULT 1,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  settings_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_ads_slots_contract_code (id_contrato, code),
  KEY idx_ads_slots_page (page_key, placement_key, is_active),
  KEY idx_ads_slots_contract (id_contrato, is_active)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wpjy_ads_campaigns (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  id_contrato BIGINT UNSIGNED NOT NULL DEFAULT 0,
  title VARCHAR(180) NOT NULL,
  description TEXT NULL,
  advertiser_name VARCHAR(180) NULL,
  advertiser_type VARCHAR(40) NOT NULL DEFAULT 'externo',
  advertiser_id BIGINT UNSIGNED NULL,
  pricing_model VARCHAR(20) NOT NULL DEFAULT 'free',
  price DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  status VARCHAR(24) NOT NULL DEFAULT 'draft',
  creative_type VARCHAR(20) NOT NULL DEFAULT 'image',
  image_url TEXT NULL,
  mobile_image_url TEXT NULL,
  html_content MEDIUMTEXT NULL,
  target_url TEXT NULL,
  cta_label VARCHAR(80) NULL,
  badge_text VARCHAR(80) NULL,
  start_at DATETIME NULL,
  end_at DATETIME NULL,
  targeting_json LONGTEXT NULL,
  order_id BIGINT UNSIGNED NULL,
  invoice_id BIGINT UNSIGNED NULL,
  payment_id BIGINT UNSIGNED NULL,
  created_by BIGINT UNSIGNED NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ads_campaigns_contract (id_contrato, status),
  KEY idx_ads_campaigns_schedule (status, start_at, end_at),
  KEY idx_ads_campaigns_finance (order_id, invoice_id, payment_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wpjy_ads_campaign_slots (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  campaign_id BIGINT UNSIGNED NOT NULL,
  slot_id BIGINT UNSIGNED NOT NULL,
  sort_order INT NOT NULL DEFAULT 0,
  weight INT NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uniq_ads_campaign_slot (campaign_id, slot_id),
  KEY idx_ads_campaign_slots_slot (slot_id, sort_order),
  CONSTRAINT fk_ads_campaign_slots_campaign
    FOREIGN KEY (campaign_id) REFERENCES wpjy_ads_campaigns(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_ads_campaign_slots_slot
    FOREIGN KEY (slot_id) REFERENCES wpjy_ads_slots(id)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS wpjy_ads_events (
  id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  campaign_id BIGINT UNSIGNED NOT NULL,
  slot_id BIGINT UNSIGNED NULL,
  event_type VARCHAR(20) NOT NULL,
  user_id BIGINT UNSIGNED NULL,
  session_key VARCHAR(120) NULL,
  page_key VARCHAR(120) NULL,
  entity_type VARCHAR(60) NULL,
  entity_id BIGINT UNSIGNED NULL,
  context_json LONGTEXT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  KEY idx_ads_events_campaign (campaign_id, event_type, created_at),
  KEY idx_ads_events_slot (slot_id, event_type, created_at),
  KEY idx_ads_events_user (user_id, created_at),
  CONSTRAINT fk_ads_events_campaign
    FOREIGN KEY (campaign_id) REFERENCES wpjy_ads_campaigns(id)
    ON DELETE CASCADE,
  CONSTRAINT fk_ads_events_slot
    FOREIGN KEY (slot_id) REFERENCES wpjy_ads_slots(id)
    ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

INSERT INTO wpjy_ads_slots
  (id_contrato, code, page_key, placement_key, title, description, max_items, is_active, settings_json)
SELECT 0, 'cliente.profissional_perfil.topo', 'cliente.profissional_perfil', 'topo', 'Perfil do profissional - topo', 'Espaco exibido abaixo do hero do profissional', 1, 1, JSON_OBJECT('variant','card')
WHERE NOT EXISTS (
  SELECT 1 FROM wpjy_ads_slots WHERE id_contrato = 0 AND code = 'cliente.profissional_perfil.topo'
);

INSERT INTO wpjy_ads_slots
  (id_contrato, code, page_key, placement_key, title, description, max_items, is_active, settings_json)
SELECT 0, 'cliente.meu_perfil.topo', 'cliente.meu_perfil', 'topo', 'Meu perfil do cliente - topo', 'Espaco exibido abaixo do cupom principal do perfil do cliente', 1, 1, JSON_OBJECT('variant','banner')
WHERE NOT EXISTS (
  SELECT 1 FROM wpjy_ads_slots WHERE id_contrato = 0 AND code = 'cliente.meu_perfil.topo'
);

INSERT INTO wpjy_ads_slots
  (id_contrato, code, page_key, placement_key, title, description, max_items, is_active, settings_json)
SELECT 0, 'cliente.perfil_cliente.topo', 'cliente.perfil_cliente', 'topo', 'Perfil detalhado do cliente - topo', 'Espaco exibido no topo do perfil do cliente visto pelos profissionais', 1, 1, JSON_OBJECT('variant','banner')
WHERE NOT EXISTS (
  SELECT 1 FROM wpjy_ads_slots WHERE id_contrato = 0 AND code = 'cliente.perfil_cliente.topo'
);

INSERT INTO wpjy_permissoes (slug, nome, descricao, id_tela, tipo)
SELECT 'menu.adm.publicidade', 'Menu - Publicidade', 'Acesso ao menu de gestao de publicidade', NULL, 'menu'
WHERE NOT EXISTS (
  SELECT 1 FROM wpjy_permissoes WHERE slug = 'menu.adm.publicidade'
);

INSERT INTO wpjy_menus_items
  (label, link, area, parent, sort, menu, target, icon, slug, tag, parametro_url)
SELECT 'Publicidade', 'anuncios-publicitarios', 'adm', 0, 91, 35, '_self', 'ti ti-ad-2', 'menu.adm.publicidade', 'adm_publicidade', NULL
WHERE NOT EXISTS (
  SELECT 1
    FROM wpjy_menus_items
   WHERE menu = 35
     AND area = 'adm'
     AND slug = 'menu.adm.publicidade'
);

INSERT INTO wpjy_telas (arquivo, nome, descricao, icone, ativa, ajuda)
SELECT 'admin/anuncios-publicitarios', 'Admin - Publicidade', 'Gestao de espacos publicitarios para profissionais e clientes', 'ti ti-ad-2', 1, NULL
WHERE NOT EXISTS (
  SELECT 1 FROM wpjy_telas WHERE arquivo = 'admin/anuncios-publicitarios'
);

INSERT INTO wpjy_perm_namespaces (ns, label, descricao, escopo, ativo)
SELECT 'adm.ads', 'Admin: Publicidade', 'Tela administrativa do modulo de publicidade', 'contrato', 1
WHERE NOT EXISTS (
  SELECT 1 FROM wpjy_perm_namespaces WHERE ns = 'adm.ads'
);

INSERT INTO wpjy_perm_tela_namespace (padrao_arquivo, ns, sensivel)
SELECT 'pages/admin/anuncios-publicitarios.php', 'adm.ads', 0
WHERE NOT EXISTS (
  SELECT 1
    FROM wpjy_perm_tela_namespace
   WHERE padrao_arquivo = 'pages/admin/anuncios-publicitarios.php'
     AND ns = 'adm.ads'
);

INSERT INTO wpjy_grupo_permissao (id_grupo, id_permissao)
SELECT g.id, p.id
  FROM wpjy_grupos_permissoes g
  JOIN wpjy_permissoes p ON p.slug = 'menu.adm.publicidade'
 WHERE g.slug = 'super_admin'
   AND NOT EXISTS (
     SELECT 1
       FROM wpjy_grupo_permissao gp
      WHERE gp.id_grupo = g.id
        AND gp.id_permissao = p.id
   );

COMMIT;