CREATE TABLE IF NOT EXISTS `wpjy_dashboards` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_contrato` BIGINT UNSIGNED NOT NULL DEFAULT 0,
  `nome` VARCHAR(150) NOT NULL,
  `slug` VARCHAR(150) NOT NULL,
  `descricao` TEXT NULL,
  `tipo` VARCHAR(60) NOT NULL DEFAULT 'custom',
  `status` VARCHAR(20) NOT NULL DEFAULT 'draft',
  `route_path` VARCHAR(190) NULL,
  `icone` VARCHAR(80) NULL,
  `cor` VARCHAR(40) NULL,
  `is_system` TINYINT(1) NOT NULL DEFAULT 0,
  `published_version_id` BIGINT UNSIGNED NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `updated_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`),
  UNIQUE KEY `uq_dashboards_contract_slug` (`id_contrato`, `slug`),
  UNIQUE KEY `uq_dashboards_route_path` (`route_path`),
  KEY `idx_dashboards_contract_status` (`id_contrato`, `status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dashboard_versions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_dashboard` BIGINT UNSIGNED NOT NULL,
  `version_no` INT NOT NULL DEFAULT 1,
  `status` VARCHAR(20) NOT NULL DEFAULT 'draft',
  `titulo_override` VARCHAR(160) NULL,
  `layout_json` LONGTEXT NOT NULL,
  `filters_json` LONGTEXT NULL,
  `theme_json` LONGTEXT NULL,
  `notes` TEXT NULL,
  `created_by` BIGINT UNSIGNED NULL,
  `published_by` BIGINT UNSIGNED NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `published_at` DATETIME NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_dashboard_versions_number` (`id_dashboard`, `version_no`),
  KEY `idx_dashboard_versions_status` (`id_dashboard`, `status`),
  CONSTRAINT `fk_dashboard_versions_dashboard`
    FOREIGN KEY (`id_dashboard`) REFERENCES `wpjy_dashboards` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dashboard_acl` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_dashboard` BIGINT UNSIGNED NOT NULL,
  `target_kind` VARCHAR(20) NOT NULL DEFAULT 'dashboard',
  `target_key` VARCHAR(100) NULL,
  `id_grupo` BIGINT UNSIGNED NOT NULL,
  `rule` VARCHAR(10) NOT NULL DEFAULT 'allow',
  `inherit_mode` VARCHAR(20) NOT NULL DEFAULT 'exact',
  `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 `uq_dashboard_acl_rule` (`id_dashboard`, `target_kind`, `target_key`, `id_grupo`, `rule`, `inherit_mode`),
  KEY `idx_dashboard_acl_dashboard_target` (`id_dashboard`, `target_kind`, `target_key`),
  KEY `idx_dashboard_acl_group` (`id_grupo`),
  CONSTRAINT `fk_dashboard_acl_dashboard`
    FOREIGN KEY (`id_dashboard`) REFERENCES `wpjy_dashboards` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `wpjy_dashboard_user_preferences` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `id_dashboard` BIGINT UNSIGNED NOT NULL,
  `id_usuario` BIGINT UNSIGNED NOT NULL,
  `is_favorite` TINYINT(1) NOT NULL DEFAULT 0,
  `layout_overrides_json` LONGTEXT NULL,
  `filters_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 `uq_dashboard_user_pref` (`id_dashboard`, `id_usuario`),
  KEY `idx_dashboard_user_pref_user` (`id_usuario`),
  CONSTRAINT `fk_dashboard_user_pref_dashboard`
    FOREIGN KEY (`id_dashboard`) REFERENCES `wpjy_dashboards` (`id`)
    ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;