-- ===================================================================== -- SISTEMA DE GESTÃO DE GABINETE (SGG) -- database.sql v2 | MySQL 8.0+ / MariaDB 10.4+ | charset utf8mb4 -- ===================================================================== -- NOVIDADES v2: -- [1] Multi-mandato: tabela mandates + mandate_id nas entidades. -- [2] Origem da solicitação: tabela request_sources + source_id. -- [3] Regiões territoriais: tabela regions acima de neighborhoods. -- [4] SLA: campos de prazo/SLA em categorias e demandas. -- [5] Demandas recorrentes: tabela demand_groups + auto-detecção. -- [6] Histórico de relacionamento do cidadão: citizen_interactions. -- [7] Visitas técnicas: technical_visits. -- [8] Timeline do mandato: mandate_timeline. -- [9] Mapa de calor: coordenadas + índices geográficos preparados. -- [10] SaaS multi-gabinete: tabela tenants + tenant_id nas entidades. -- -- Hierarquia de isolamento: tenant (cliente SaaS) > mandate (mandato) > dados -- No MVP single-tenant: tenant_id = 1 fixo. Pronto p/ multi sem migração. -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; SET time_zone = '+00:00'; -- ===================================================================== -- 0. SAAS / MANDATO (novo topo da hierarquia) -- ===================================================================== -- [10] Tenant = gabinete/cliente do SaaS. No MVP existe só o tenant 1. CREATE TABLE tenants ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(180) NOT NULL, slug VARCHAR(80) NOT NULL, document VARCHAR(20) NULL, plan VARCHAR(40) NOT NULL DEFAULT 'mvp', status ENUM('active','suspended','trial','cancelled') NOT NULL DEFAULT 'active', max_users INT UNSIGNED NULL, storage_quota_mb INT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_tenants_slug (slug), KEY idx_tenants_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- [1] Mandato = período de mandato de um vereador dentro de um tenant. CREATE TABLE mandates ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL, councilman_name VARCHAR(180) NOT NULL, party VARCHAR(60) NULL, legislature VARCHAR(40) NULL, term_start DATE NULL, term_end DATE NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, city VARCHAR(150) NULL, state VARCHAR(2) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), KEY idx_mandates_tenant (tenant_id), KEY idx_mandates_active (is_active), CONSTRAINT fk_mandates_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 1. ACESSO: roles, permissions, users -- ===================================================================== CREATE TABLE roles ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(50) NOT NULL, name VARCHAR(100) NOT NULL, description VARCHAR(255) NULL, is_system TINYINT(1) NOT NULL DEFAULT 0, 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_roles_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE permissions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(80) NOT NULL, name VARCHAR(120) NOT NULL, module VARCHAR(50) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_permissions_slug (slug), KEY idx_permissions_module (module) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE role_permissions ( role_id BIGINT UNSIGNED NOT NULL, permission_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (role_id, permission_id), KEY idx_rp_permission (permission_id), CONSTRAINT fk_rp_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE CASCADE, CONSTRAINT fk_rp_permission FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, role_id BIGINT UNSIGNED NOT NULL, name VARCHAR(150) NOT NULL, email VARCHAR(190) NOT NULL, phone VARCHAR(30) NULL, position VARCHAR(100) NULL, avatar_path VARCHAR(255) NULL, -- foto de perfil [b11] preferences_json TEXT NULL, -- preferências JSON [b11] region_id BIGINT UNSIGNED NULL, -- região responsável [b2] password_hash VARCHAR(255) NOT NULL, must_change_password TINYINT(1) NOT NULL DEFAULT 0, password_changed_at DATETIME NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, last_login_at DATETIME NULL, failed_attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0, locked_until DATETIME NULL, remember_token VARCHAR(100) NULL, reset_token_hash VARCHAR(255) NULL, reset_expires_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_users_email_tenant (tenant_id, email), KEY idx_users_role (role_id), KEY idx_users_tenant (tenant_id), KEY idx_users_region (region_id), KEY idx_users_active (is_active), CONSTRAINT fk_users_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_users_role FOREIGN KEY (role_id) REFERENCES roles(id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE mandate_users ( mandate_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (mandate_id, user_id), KEY idx_mu_user (user_id), CONSTRAINT fk_mu_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE CASCADE, CONSTRAINT fk_mu_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- [v4] Identidade visual do sistema (por tenant) CREATE TABLE tenant_branding ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL, logo_path VARCHAR(255) NULL, logo_small_path VARCHAR(255) NULL, favicon_path VARCHAR(255) NULL, primary_color VARCHAR(7) NULL, -- #RRGGBB secondary_color VARCHAR(7) NULL, accent_color VARCHAR(7) NULL, slogan VARCHAR(200) NULL, public_name VARCHAR(180) NULL, -- nome público do gabinete 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_branding_tenant (tenant_id), CONSTRAINT fk_branding_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- [v4] Dados institucionais/políticos do mandato (configuração, não eleitoral) CREATE TABLE mandate_profile ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL, mandate_id BIGINT UNSIGNED NOT NULL, councilman_name VARCHAR(180) NULL, public_office_name VARCHAR(180) NULL, -- nome público do gabinete party_name VARCHAR(80) NULL, party_number VARCHAR(10) NULL, political_number VARCHAR(20) NULL, -- número, quando aplicável legislature VARCHAR(40) NULL, city VARCHAR(150) NULL, state VARCHAR(2) NULL, official_email VARCHAR(190) NULL, official_phone VARCHAR(30) NULL, website_url VARCHAR(190) NULL, instagram_url VARCHAR(190) NULL, facebook_url VARCHAR(190) NULL, youtube_url VARCHAR(190) NULL, slogan VARCHAR(200) NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_profile_mandate (mandate_id), KEY idx_profile_tenant (tenant_id), CONSTRAINT fk_profile_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_profile_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE sessions ( id VARCHAR(128) NOT NULL, user_id BIGINT UNSIGNED NULL, tenant_id BIGINT UNSIGNED NULL, mandate_id BIGINT UNSIGNED NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(255) NULL, payload MEDIUMTEXT NOT NULL, last_activity INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY idx_sessions_user (user_id), KEY idx_sessions_activity (last_activity) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 2. GEOGRAFIA / ÓRGÃOS ([3] regions acima de neighborhoods) -- ===================================================================== CREATE TABLE regions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, name VARCHAR(150) NOT NULL, description VARCHAR(255) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, 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_regions_name_tenant (tenant_id, name), KEY idx_regions_tenant (tenant_id), KEY idx_regions_mandate (mandate_id), CONSTRAINT fk_regions_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_regions_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE neighborhoods ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, region_id BIGINT UNSIGNED NULL, name VARCHAR(150) NOT NULL, city VARCHAR(150) NOT NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, 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_neigh_tenant_name (tenant_id, name), KEY idx_neigh_region (region_id), KEY idx_neigh_active (is_active), CONSTRAINT fk_neigh_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_neigh_region FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE departments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, name VARCHAR(180) NOT NULL, acronym VARCHAR(30) NULL, email VARCHAR(190) NULL, phone VARCHAR(30) NULL, contact_name VARCHAR(150) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_dept_acronym_tenant (tenant_id, acronym), KEY idx_dept_tenant (tenant_id), KEY idx_dept_active (is_active), CONSTRAINT fk_dept_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE request_sources ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, slug VARCHAR(40) NOT NULL, name VARCHAR(80) NOT NULL, icon VARCHAR(40) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, PRIMARY KEY (id), UNIQUE KEY uq_source_slug_tenant (tenant_id, slug), CONSTRAINT fk_source_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 3. CIDADÃOS -- ===================================================================== CREATE TABLE citizens ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, name VARCHAR(180) NOT NULL, nickname VARCHAR(120) NULL, -- apelido / nome conhecido [b2] phone VARCHAR(30) NULL, email VARCHAR(190) NULL, cpf VARCHAR(14) NULL, cep VARCHAR(9) NULL, address VARCHAR(200) NULL, address_number VARCHAR(20) NULL, address_complement VARCHAR(100) NULL, neighborhood_id BIGINT UNSIGNED NULL, source_id BIGINT UNSIGNED NULL, -- origem do contato [b2] city VARCHAR(150) NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, notes TEXT NULL, is_community_leader TINYINT(1) NOT NULL DEFAULT 0, -- liderança comunitária [b2] -- aniversário (sem ano, por minimização LGPD) [v4] birth_day TINYINT UNSIGNED NULL, -- 1..31 (validado na aplicação) birth_month TINYINT UNSIGNED NULL, -- 1..12 consent_contact TINYINT(1) NOT NULL DEFAULT 0, consent_at DATETIME NULL, consent_channel VARCHAR(50) NULL, whatsapp_opt_out TINYINT(1) NOT NULL DEFAULT 0, -- "não deseja receber mensagens" [v4] opt_out_at DATETIME 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, deleted_at DATETIME NULL, PRIMARY KEY (id), KEY idx_citizens_tenant (tenant_id), KEY idx_citizens_mandate (mandate_id), KEY idx_citizens_name (name), KEY idx_citizens_phone (phone), KEY idx_citizens_neigh (neighborhood_id), KEY idx_citizens_source (source_id), KEY idx_citizens_leader (is_community_leader), KEY idx_citizens_cpf (cpf), KEY idx_citizens_birthday (birth_month, birth_day), KEY idx_citizens_optout (whatsapp_opt_out), CONSTRAINT fk_citizens_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_citizens_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_citizens_neigh FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL, CONSTRAINT fk_citizens_source FOREIGN KEY (source_id) REFERENCES request_sources(id) ON DELETE SET NULL, CONSTRAINT fk_citizens_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- [6] Histórico completo de relacionamento do cidadão CREATE TABLE citizen_interactions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, citizen_id BIGINT UNSIGNED NOT NULL, type ENUM('contato','atendimento','demanda','visita','mensagem', 'documento','evento','observacao') NOT NULL, source_id BIGINT UNSIGNED NULL, demand_id BIGINT UNSIGNED NULL, summary VARCHAR(255) NOT NULL, details TEXT NULL, user_id BIGINT UNSIGNED NULL, occurred_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_ci_citizen (citizen_id), KEY idx_ci_tenant (tenant_id), KEY idx_ci_type (type), KEY idx_ci_occurred (occurred_at), CONSTRAINT fk_ci_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_ci_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_ci_citizen FOREIGN KEY (citizen_id) REFERENCES citizens(id) ON DELETE CASCADE, CONSTRAINT fk_ci_source FOREIGN KEY (source_id) REFERENCES request_sources(id) ON DELETE SET NULL, CONSTRAINT fk_ci_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 4. DEMANDAS ([4] SLA, [5] recorrência) -- ===================================================================== CREATE TABLE demand_categories ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, slug VARCHAR(60) NOT NULL, name VARCHAR(120) NOT NULL, default_department_id BIGINT UNSIGNED NULL, sla_days SMALLINT UNSIGNED NULL, color VARCHAR(7) NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_cat_slug_tenant (tenant_id, slug), KEY idx_cat_dept (default_department_id), CONSTRAINT fk_cat_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_cat_dept FOREIGN KEY (default_department_id) REFERENCES departments(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE protocol_counters ( tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, year SMALLINT UNSIGNED NOT NULL, last_number INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (tenant_id, year) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- [5] Grupo de demandas recorrentes CREATE TABLE demand_groups ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, category_id BIGINT UNSIGNED NULL, neighborhood_id BIGINT UNSIGNED NULL, signature VARCHAR(190) NOT NULL, label VARCHAR(200) NULL, occurrences INT UNSIGNED NOT NULL DEFAULT 0, first_seen_at DATETIME NULL, last_seen_at DATETIME NULL, is_chronic TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_group_signature (tenant_id, signature), KEY idx_group_neigh (neighborhood_id), KEY idx_group_cat (category_id), CONSTRAINT fk_group_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_group_cat FOREIGN KEY (category_id) REFERENCES demand_categories(id) ON DELETE SET NULL, CONSTRAINT fk_group_neigh FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE demands ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, protocol VARCHAR(24) NOT NULL, citizen_id BIGINT UNSIGNED NULL, category_id BIGINT UNSIGNED NULL, department_id BIGINT UNSIGNED NULL, neighborhood_id BIGINT UNSIGNED NULL, source_id BIGINT UNSIGNED NULL, group_id BIGINT UNSIGNED NULL, title VARCHAR(200) NOT NULL, description TEXT NOT NULL, occur_address VARCHAR(200) NULL, occur_cep VARCHAR(9) NULL, occur_number VARCHAR(20) NULL, occur_city VARCHAR(80) NULL, occur_state VARCHAR(2) NULL, occur_reference VARCHAR(200) NULL, occur_latitude DECIMAL(10,7) NULL, occur_longitude DECIMAL(10,7) NULL, priority ENUM('baixa','media','alta','urgente') NOT NULL DEFAULT 'media', status ENUM('nova','em_analise','encaminhada','em_andamento', 'aguardando_retorno','resolvida','arquivada') NOT NULL DEFAULT 'nova', assigned_to BIGINT UNSIGNED NULL, sla_days SMALLINT UNSIGNED NULL, due_date DATE NULL, sla_due_at DATETIME NULL, sla_status ENUM('dentro','vencendo','vencido','cumprido') NOT NULL DEFAULT 'dentro', first_response_at DATETIME NULL, opened_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, resolved_at DATETIME NULL, is_recurring TINYINT(1) NOT NULL DEFAULT 0, recurrence_count INT UNSIGNED NOT NULL DEFAULT 1, 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, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_demands_protocol (tenant_id, protocol), KEY idx_demands_mandate (mandate_id), KEY idx_demands_citizen (citizen_id), KEY idx_demands_category (category_id), KEY idx_demands_dept (department_id), KEY idx_demands_neigh (neighborhood_id), KEY idx_demands_source (source_id), KEY idx_demands_group (group_id), KEY idx_demands_status (status), KEY idx_demands_priority (priority), KEY idx_demands_assigned (assigned_to), KEY idx_demands_due (due_date), KEY idx_demands_sla (sla_status), KEY idx_demands_status_neigh (status, neighborhood_id), KEY idx_demands_created (created_at), KEY idx_demands_geo (occur_latitude, occur_longitude), CONSTRAINT fk_demands_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_demands_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_demands_citizen FOREIGN KEY (citizen_id) REFERENCES citizens(id) ON DELETE SET NULL, CONSTRAINT fk_demands_category FOREIGN KEY (category_id) REFERENCES demand_categories(id) ON DELETE SET NULL, CONSTRAINT fk_demands_dept FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL, CONSTRAINT fk_demands_neigh FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL, CONSTRAINT fk_demands_source FOREIGN KEY (source_id) REFERENCES request_sources(id) ON DELETE SET NULL, CONSTRAINT fk_demands_group FOREIGN KEY (group_id) REFERENCES demand_groups(id) ON DELETE SET NULL, CONSTRAINT fk_demands_assignee FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_demands_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE demand_status_history ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, demand_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL, event_type ENUM('criacao','mudanca_status','comentario','encaminhamento', 'resposta_recebida','documento_gerado','atribuicao','anexo','prazo','visita') NOT NULL, status_from VARCHAR(30) NULL, status_to VARCHAR(30) NULL, comment TEXT NULL, is_internal TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_dsh_demand (demand_id), KEY idx_dsh_user (user_id), KEY idx_dsh_created (created_at), CONSTRAINT fk_dsh_demand FOREIGN KEY (demand_id) REFERENCES demands(id) ON DELETE CASCADE, CONSTRAINT fk_dsh_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 5. VISITAS TÉCNICAS ([7]) -- ===================================================================== CREATE TABLE technical_visits ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, demand_id BIGINT UNSIGNED NULL, citizen_id BIGINT UNSIGNED NULL, neighborhood_id BIGINT UNSIGNED NULL, title VARCHAR(200) NOT NULL, purpose TEXT NULL, address VARCHAR(200) NULL, cep VARCHAR(9) NULL, address_number VARCHAR(20) NULL, city VARCHAR(80) NULL, state VARCHAR(2) NULL, latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, scheduled_at DATETIME NULL, performed_at DATETIME NULL, status ENUM('agendada','realizada','cancelada','remarcada') NOT NULL DEFAULT 'agendada', responsible_id BIGINT UNSIGNED NULL, findings TEXT 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, deleted_at DATETIME NULL, PRIMARY KEY (id), KEY idx_tv_tenant (tenant_id), KEY idx_tv_demand (demand_id), KEY idx_tv_neigh (neighborhood_id), KEY idx_tv_status (status), KEY idx_tv_scheduled (scheduled_at), CONSTRAINT fk_tv_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_tv_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_tv_demand FOREIGN KEY (demand_id) REFERENCES demands(id) ON DELETE SET NULL, CONSTRAINT fk_tv_citizen FOREIGN KEY (citizen_id) REFERENCES citizens(id) ON DELETE SET NULL, CONSTRAINT fk_tv_neigh FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL, CONSTRAINT fk_tv_resp FOREIGN KEY (responsible_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_tv_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 6. ANEXOS (polimórfico) -- ===================================================================== CREATE TABLE attachments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, owner_type ENUM('demand','document','citizen','visit') NOT NULL, owner_id BIGINT UNSIGNED NOT NULL, original_name VARCHAR(255) NOT NULL, stored_name VARCHAR(255) NOT NULL, mime_type VARCHAR(120) NOT NULL, size_bytes BIGINT UNSIGNED NOT NULL, is_public TINYINT(1) NOT NULL DEFAULT 0, uploaded_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), KEY idx_attach_owner (owner_type, owner_id), KEY idx_attach_tenant (tenant_id), KEY idx_attach_uploader (uploaded_by), CONSTRAINT fk_attach_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_attach_uploader FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 7. DOCUMENTOS + MODELOS -- ===================================================================== CREATE TABLE document_templates ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, type ENUM('oficio','indicacao','requerimento','memorando', 'resposta_cidadao','whatsapp','prestacao_contas','outro') NOT NULL, name VARCHAR(150) NOT NULL, subject VARCHAR(200) NULL, body MEDIUMTEXT NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, 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, deleted_at DATETIME NULL, PRIMARY KEY (id), KEY idx_tpl_tenant (tenant_id), KEY idx_tpl_type (type), CONSTRAINT fk_tpl_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_tpl_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE document_counters ( tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, doc_type VARCHAR(30) NOT NULL, year SMALLINT UNSIGNED NOT NULL, last_number INT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (tenant_id, doc_type, year) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE documents ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, doc_number VARCHAR(30) NOT NULL, type ENUM('oficio','indicacao','requerimento','memorando', 'resposta_cidadao','outro') NOT NULL, title VARCHAR(200) NOT NULL, department_id BIGINT UNSIGNED NULL, demand_id BIGINT UNSIGNED NULL, template_id BIGINT UNSIGNED NULL, body MEDIUMTEXT NOT NULL, status ENUM('rascunho','emitido','enviado','respondido','arquivado') NOT NULL DEFAULT 'rascunho', is_public TINYINT(1) NOT NULL DEFAULT 0, published_at DATETIME NULL, issued_at DATETIME 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, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_documents_number (tenant_id, doc_number), KEY idx_docs_mandate (mandate_id), KEY idx_docs_type (type), KEY idx_docs_dept (department_id), KEY idx_docs_demand (demand_id), KEY idx_docs_status (status), KEY idx_docs_public (is_public), CONSTRAINT fk_docs_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_docs_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_docs_dept FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE SET NULL, CONSTRAINT fk_docs_demand FOREIGN KEY (demand_id) REFERENCES demands(id) ON DELETE SET NULL, CONSTRAINT fk_docs_template FOREIGN KEY (template_id) REFERENCES document_templates(id) ON DELETE SET NULL, CONSTRAINT fk_docs_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 8. TAREFAS E AGENDA -- ===================================================================== CREATE TABLE tasks ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, demand_id BIGINT UNSIGNED NULL, title VARCHAR(200) NOT NULL, description TEXT NULL, assigned_to BIGINT UNSIGNED NULL, priority ENUM('baixa','media','alta','urgente') NOT NULL DEFAULT 'media', status ENUM('aberta','em_andamento','concluida','cancelada') NOT NULL DEFAULT 'aberta', due_date DATE NULL, completed_at DATETIME 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, deleted_at DATETIME NULL, PRIMARY KEY (id), KEY idx_tasks_tenant (tenant_id), KEY idx_tasks_demand (demand_id), KEY idx_tasks_assigned (assigned_to), KEY idx_tasks_status (status), KEY idx_tasks_due (due_date), CONSTRAINT fk_tasks_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_tasks_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_tasks_demand FOREIGN KEY (demand_id) REFERENCES demands(id) ON DELETE SET NULL, CONSTRAINT fk_tasks_assignee FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_tasks_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE task_comments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, task_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL, comment TEXT NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_tc_task (task_id), CONSTRAINT fk_tc_task FOREIGN KEY (task_id) REFERENCES tasks(id) ON DELETE CASCADE, CONSTRAINT fk_tc_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE agenda_events ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, title VARCHAR(200) NOT NULL, type ENUM('reuniao','visita','evento','atendimento','sessao','agenda_externa', 'audiencia','compromisso_interno','compromisso_externo') NOT NULL, starts_at DATETIME NOT NULL, ends_at DATETIME NULL, location VARCHAR(200) NULL, location_cep VARCHAR(9) NULL, location_number VARCHAR(20) NULL, location_city VARCHAR(80) NULL, location_state VARCHAR(2) NULL, latitude DECIMAL(10,8) NULL, longitude DECIMAL(11,8) NULL, neighborhood_id BIGINT UNSIGNED NULL, region_id BIGINT UNSIGNED NULL, participants TEXT NULL, responsible_id BIGINT UNSIGNED NULL, citizen_id BIGINT UNSIGNED NULL, demand_id BIGINT UNSIGNED NULL, notes TEXT NULL, resultado TEXT NULL, encaminhamentos TEXT NULL, status ENUM('agendado','confirmado','realizado','cancelado','reagendado') NOT NULL DEFAULT 'agendado', 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, deleted_at DATETIME NULL, PRIMARY KEY (id), KEY idx_agenda_tenant (tenant_id), KEY idx_agenda_starts (starts_at), KEY idx_agenda_status (status), KEY idx_agenda_resp (responsible_id), KEY idx_agenda_neigh (neighborhood_id), KEY idx_agenda_geo (latitude, longitude), KEY idx_agenda_region (region_id), CONSTRAINT fk_agenda_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_agenda_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_agenda_neigh FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL, CONSTRAINT fk_agenda_region FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL, CONSTRAINT fk_agenda_resp FOREIGN KEY (responsible_id) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_agenda_citizen FOREIGN KEY (citizen_id) REFERENCES citizens(id) ON DELETE SET NULL, CONSTRAINT fk_agenda_demand FOREIGN KEY (demand_id) REFERENCES demands(id) ON DELETE SET NULL, CONSTRAINT fk_agenda_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 9. TIMELINE DO MANDATO ([8]) + TRANSPARÊNCIA -- ===================================================================== CREATE TABLE mandate_timeline ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NOT NULL, event_type ENUM('demanda_resolvida','documento_emitido','indicacao','requerimento', 'visita_realizada','evento','marco','noticia') NOT NULL, ref_type VARCHAR(40) NULL, ref_id BIGINT UNSIGNED NULL, title VARCHAR(200) NOT NULL, description TEXT NULL, neighborhood_id BIGINT UNSIGNED NULL, is_public TINYINT(1) NOT NULL DEFAULT 0, occurred_at DATETIME NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_mt_mandate (mandate_id), KEY idx_mt_type (event_type), KEY idx_mt_occurred (occurred_at), KEY idx_mt_public (is_public), CONSTRAINT fk_mt_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_mt_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE CASCADE, CONSTRAINT fk_mt_neigh FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE public_reports ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, type ENUM('acao','noticia','indicador','documento') NOT NULL, title VARCHAR(200) NOT NULL, summary TEXT NULL, body MEDIUMTEXT NULL, neighborhood_id BIGINT UNSIGNED NULL, reference_period VARCHAR(30) NULL, cover_attachment_id BIGINT UNSIGNED NULL, is_published TINYINT(1) NOT NULL DEFAULT 0, published_at DATETIME 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, deleted_at DATETIME NULL, PRIMARY KEY (id), KEY idx_pr_tenant (tenant_id), KEY idx_pr_type (type), KEY idx_pr_published (is_published), KEY idx_pr_neigh (neighborhood_id), CONSTRAINT fk_pr_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_pr_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_pr_neigh FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL, CONSTRAINT fk_pr_cover FOREIGN KEY (cover_attachment_id) REFERENCES attachments(id) ON DELETE SET NULL, CONSTRAINT fk_pr_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 10. AUDITORIA / LGPD / CONFIG -- ===================================================================== CREATE TABLE audit_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NULL, user_id BIGINT UNSIGNED NULL, action VARCHAR(20) NOT NULL, table_name VARCHAR(64) NOT NULL, record_id BIGINT UNSIGNED NULL, old_values JSON NULL, new_values JSON NULL, ip_address VARCHAR(45) NULL, user_agent VARCHAR(255) NULL, session_id VARCHAR(128) NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_audit_tenant (tenant_id), KEY idx_audit_user (user_id), KEY idx_audit_table_record (table_name, record_id), KEY idx_audit_created (created_at), CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE consent_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, citizen_id BIGINT UNSIGNED NOT NULL, action ENUM('granted','revoked','updated') NOT NULL, channel VARCHAR(50) NULL, notes VARCHAR(255) NULL, user_id BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_consent_citizen (citizen_id), CONSTRAINT fk_consent_citizen FOREIGN KEY (citizen_id) REFERENCES citizens(id) ON DELETE CASCADE, CONSTRAINT fk_consent_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE settings ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, `key` VARCHAR(80) NOT NULL, `value` TEXT NULL, updated_by BIGINT UNSIGNED NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_settings_key_tenant (tenant_id, `key`), CONSTRAINT fk_settings_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_settings_user FOREIGN KEY (updated_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 11. PREPARAÇÃO PARA FUTURO -- ===================================================================== -- ===================================================================== -- 11. MENSAGENS / WHATSAPP (v4 — controle completo) -- ===================================================================== -- LGPD: envio só para citizens.consent_contact=1 E whatsapp_opt_out=0. -- A checagem é reforçada no Service; consent_checked grava o resultado. -- ===================================================================== -- Modelos de mensagem (por canal/categoria) CREATE TABLE message_templates ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, name VARCHAR(150) NOT NULL, slug VARCHAR(80) NOT NULL, category ENUM('aniversario','demanda','protocolo','institucional', 'manual','transparencia','outro') NOT NULL DEFAULT 'outro', channel ENUM('whatsapp') NOT NULL DEFAULT 'whatsapp', body MEDIUMTEXT NOT NULL, variables_allowed VARCHAR(255) NULL, -- lista de {{vars}} permitidas is_active TINYINT(1) NOT NULL DEFAULT 1, 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, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_msgtpl_slug_tenant (tenant_id, slug), KEY idx_msgtpl_category (category), CONSTRAINT fk_msgtpl_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_msgtpl_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_msgtpl_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Caixa de saída (uma linha por mensagem) CREATE TABLE message_outbox ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, citizen_id BIGINT UNSIGNED NULL, demand_id BIGINT UNSIGNED NULL, template_id BIGINT UNSIGNED NULL, channel ENUM('whatsapp') NOT NULL DEFAULT 'whatsapp', to_phone VARCHAR(30) NULL, message_body TEXT NOT NULL, message_type ENUM('aniversario','protocolo','demanda','institucional','manual','outro') NOT NULL DEFAULT 'manual', scheduled_at DATETIME NULL, status ENUM('draft','scheduled','queued','sent','failed','cancelled') NOT NULL DEFAULT 'draft', provider ENUM('zapi','manual','outro') NOT NULL DEFAULT 'manual', provider_ref VARCHAR(120) NULL, error_message VARCHAR(255) NULL, attempts SMALLINT UNSIGNED NOT NULL DEFAULT 0, last_attempt_at DATETIME NULL, consent_checked TINYINT(1) NOT NULL DEFAULT 0, created_by BIGINT UNSIGNED NULL, approved_by BIGINT UNSIGNED NULL, sent_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, sent_at DATETIME NULL, PRIMARY KEY (id), KEY idx_outbox_tenant (tenant_id), KEY idx_outbox_citizen (citizen_id), KEY idx_outbox_demand (demand_id), KEY idx_outbox_status (status), KEY idx_outbox_type (message_type), KEY idx_outbox_scheduled (scheduled_at), CONSTRAINT fk_outbox_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_outbox_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_outbox_citizen FOREIGN KEY (citizen_id) REFERENCES citizens(id) ON DELETE SET NULL, CONSTRAINT fk_outbox_demand FOREIGN KEY (demand_id) REFERENCES demands(id) ON DELETE SET NULL, CONSTRAINT fk_outbox_template FOREIGN KEY (template_id) REFERENCES message_templates(id) ON DELETE SET NULL, CONSTRAINT fk_outbox_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_outbox_approver FOREIGN KEY (approved_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_outbox_sender FOREIGN KEY (sent_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Histórico/trilha de cada mensagem (append-only) CREATE TABLE message_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, message_outbox_id BIGINT UNSIGNED NOT NULL, citizen_id BIGINT UNSIGNED NULL, action ENUM('created','queued','sent','failed','cancelled','retried') NOT NULL, status_before VARCHAR(20) NULL, status_after VARCHAR(20) NULL, response_payload JSON NULL, -- resposta do provedor (Z-API) error_message VARCHAR(255) NULL, user_id BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_msglog_outbox (message_outbox_id), KEY idx_msglog_citizen (citizen_id), KEY idx_msglog_action (action), CONSTRAINT fk_msglog_outbox FOREIGN KEY (message_outbox_id) REFERENCES message_outbox(id) ON DELETE CASCADE, CONSTRAINT fk_msglog_citizen FOREIGN KEY (citizen_id) REFERENCES citizens(id) ON DELETE SET NULL, CONSTRAINT fk_msglog_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE demand_public_tokens ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, demand_id BIGINT UNSIGNED NOT NULL, token VARCHAR(64) NOT NULL, expires_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_dpt_token (token), KEY idx_dpt_demand (demand_id), CONSTRAINT fk_dpt_demand FOREIGN KEY (demand_id) REFERENCES demands(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- [b2] região responsável do usuário (FK adicionada aqui pois regions vem depois de users) ALTER TABLE users ADD CONSTRAINT fk_users_region FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL; -- [b2] bairros atendidos por membro da equipe (N:N) CREATE TABLE user_neighborhoods ( user_id BIGINT UNSIGNED NOT NULL, neighborhood_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (user_id, neighborhood_id), KEY idx_un_neigh (neighborhood_id), CONSTRAINT fk_un_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE, CONSTRAINT fk_un_neigh FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- 12. MÍDIA DO GABINETE (v3) -- ===================================================================== -- Distinção de papéis: -- attachments -> anexo administrativo simples preso a UM registro (PDF de -- resposta, comprovante). Mantido como está. -- media_files -> mídia rica (foto/vídeo) com metadados, galeria, coleções, -- logs de download/uso, controle público/privado e social. -- Mídia privada NUNCA é servida por URL direta: download via controller que -- valida permissão e faz stream. storage_path aponta p/ fora do public_html. -- ===================================================================== CREATE TABLE media_files ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, media_type ENUM('image','video','document','audio','other') NOT NULL, original_name VARCHAR(255) NOT NULL, stored_name VARCHAR(255) NOT NULL, -- nome em disco (hash) mime_type VARCHAR(120) NOT NULL, extension VARCHAR(12) NOT NULL, size_bytes BIGINT UNSIGNED NOT NULL, duration_seconds INT UNSIGNED NULL, -- vídeo/áudio width INT UNSIGNED NULL, -- imagem/vídeo height INT UNSIGNED NULL, -- metadados editoriais title VARCHAR(200) NULL, description TEXT NULL, category VARCHAR(80) NULL, source VARCHAR(80) NULL, -- origem da mídia (campo, evento...) captured_at DATETIME NULL, -- data da foto/vídeo (≠ upload) neighborhood_id BIGINT UNSIGNED NULL, region_id BIGINT UNSIGNED NULL, -- vínculos opcionais (mídia pode ser institucional = todos nulos) demand_id BIGINT UNSIGNED NULL, demand_stage VARCHAR(20) NOT NULL DEFAULT 'initial', -- estágio: initial|progress|final (migration 020) visit_id BIGINT UNSIGNED NULL, agenda_event_id BIGINT UNSIGNED NULL, document_id BIGINT UNSIGNED NULL, timeline_id BIGINT UNSIGNED NULL, citizen_id BIGINT UNSIGNED NULL, -- LGPD: cuidado redobrado -- flags de publicação/uso is_institutional TINYINT(1) NOT NULL DEFAULT 0, -- [15] separa institucional de vinculada a cidadão is_public TINYINT(1) NOT NULL DEFAULT 0, is_social_authorized TINYINT(1) NOT NULL DEFAULT 0, -- liberada p/ redes is_featured TINYINT(1) NOT NULL DEFAULT 0, -- destaque/capa de galeria is_used_by_marketing TINYINT(1) NOT NULL DEFAULT 0, -- arquivos derivados thumbnail_path VARCHAR(255) NULL, -- capa/thumbnail do vídeo storage_path VARCHAR(255) NOT NULL, -- caminho relativo do arquivo latitude DECIMAL(10,8) NULL, -- capturada no upload ou edição longitude DECIMAL(11,8) NULL, -- rastreio uploaded_by BIGINT UNSIGNED NULL, deleted_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, -- soft delete (admin restaura) PRIMARY KEY (id), KEY idx_media_tenant (tenant_id), KEY idx_media_mandate (mandate_id), KEY idx_media_type (media_type), KEY idx_media_neigh (neighborhood_id), KEY idx_media_region (region_id), KEY idx_media_demand (demand_id), KEY idx_media_demand_stage (demand_id, demand_stage), KEY idx_media_visit (visit_id), KEY idx_media_agenda (agenda_event_id), KEY idx_media_document (document_id), KEY idx_media_citizen (citizen_id), KEY idx_media_public (is_public), KEY idx_media_social (is_social_authorized), KEY idx_media_institutional (is_institutional), KEY idx_media_uploader (uploaded_by), KEY idx_media_geo (latitude, longitude), CONSTRAINT fk_media_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_media_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_media_neigh FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL, CONSTRAINT fk_media_region FOREIGN KEY (region_id) REFERENCES regions(id) ON DELETE SET NULL, CONSTRAINT fk_media_demand FOREIGN KEY (demand_id) REFERENCES demands(id) ON DELETE SET NULL, CONSTRAINT fk_media_visit FOREIGN KEY (visit_id) REFERENCES technical_visits(id) ON DELETE SET NULL, CONSTRAINT fk_media_agenda FOREIGN KEY (agenda_event_id) REFERENCES agenda_events(id) ON DELETE SET NULL, CONSTRAINT fk_media_document FOREIGN KEY (document_id) REFERENCES documents(id) ON DELETE SET NULL, CONSTRAINT fk_media_timeline FOREIGN KEY (timeline_id) REFERENCES mandate_timeline(id) ON DELETE SET NULL, CONSTRAINT fk_media_citizen FOREIGN KEY (citizen_id) REFERENCES citizens(id) ON DELETE SET NULL, CONSTRAINT fk_media_uploader FOREIGN KEY (uploaded_by) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_media_deleter FOREIGN KEY (deleted_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Coleções/galerias (campanha, evento, visita, bairro, institucional...) CREATE TABLE media_collections ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, title VARCHAR(200) NOT NULL, description TEXT NULL, type ENUM('visita','evento','demanda','prestacao_contas', 'redes_sociais','institucional','outro') NOT NULL DEFAULT 'outro', neighborhood_id BIGINT UNSIGNED NULL, cover_media_id BIGINT UNSIGNED NULL, -- capa da coleção 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, deleted_at DATETIME NULL, PRIMARY KEY (id), KEY idx_mc_tenant (tenant_id), KEY idx_mc_type (type), KEY idx_mc_neigh (neighborhood_id), CONSTRAINT fk_mc_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_mc_mandate FOREIGN KEY (mandate_id) REFERENCES mandates(id) ON DELETE SET NULL, CONSTRAINT fk_mc_neigh FOREIGN KEY (neighborhood_id) REFERENCES neighborhoods(id) ON DELETE SET NULL, CONSTRAINT fk_mc_cover FOREIGN KEY (cover_media_id) REFERENCES media_files(id) ON DELETE SET NULL, CONSTRAINT fk_mc_creator FOREIGN KEY (created_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- N:N mídia <-> coleção (uma mídia pode estar em várias coleções) CREATE TABLE media_collection_items ( collection_id BIGINT UNSIGNED NOT NULL, media_file_id BIGINT UNSIGNED NOT NULL, sort_order INT NOT NULL DEFAULT 0, added_by BIGINT UNSIGNED NULL, added_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (collection_id, media_file_id), KEY idx_mci_media (media_file_id), CONSTRAINT fk_mci_collection FOREIGN KEY (collection_id) REFERENCES media_collections(id) ON DELETE CASCADE, CONSTRAINT fk_mci_media FOREIGN KEY (media_file_id) REFERENCES media_files(id) ON DELETE CASCADE, CONSTRAINT fk_mci_user FOREIGN KEY (added_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- [12] Log de downloads (quem baixou, quando, de onde) CREATE TABLE media_download_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, media_file_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL, downloaded_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, ip_address VARCHAR(45) NULL, user_agent VARCHAR(255) NULL, PRIMARY KEY (id), KEY idx_mdl_media (media_file_id), KEY idx_mdl_user (user_id), KEY idx_mdl_when (downloaded_at), CONSTRAINT fk_mdl_media FOREIGN KEY (media_file_id) REFERENCES media_files(id) ON DELETE CASCADE, CONSTRAINT fk_mdl_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Log de uso (publicação em redes, site, documento, transparência...) CREATE TABLE media_usage_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, media_file_id BIGINT UNSIGNED NOT NULL, used_by BIGINT UNSIGNED NULL, usage_type ENUM('instagram','facebook','whatsapp','site', 'documento','transparencia','outro') NOT NULL, usage_description VARCHAR(255) NULL, used_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_mul_media (media_file_id), KEY idx_mul_type (usage_type), CONSTRAINT fk_mul_media FOREIGN KEY (media_file_id) REFERENCES media_files(id) ON DELETE CASCADE, CONSTRAINT fk_mul_user FOREIGN KEY (used_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Tags de mídia (N:N) — Bloco 3 -- --------------------------------------------------------------------- CREATE TABLE media_tags ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, name VARCHAR(60) NOT NULL, slug VARCHAR(60) NOT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_mediatag_slug_tenant (tenant_id, slug), KEY idx_mediatag_tenant (tenant_id), CONSTRAINT fk_mediatag_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE media_file_tags ( media_file_id BIGINT UNSIGNED NOT NULL, tag_id BIGINT UNSIGNED NOT NULL, PRIMARY KEY (media_file_id, tag_id), KEY idx_mft_tag (tag_id), CONSTRAINT fk_mft_media FOREIGN KEY (media_file_id) REFERENCES media_files(id) ON DELETE CASCADE, CONSTRAINT fk_mft_tag FOREIGN KEY (tag_id) REFERENCES media_tags(id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- Fila de compartilhamento WhatsApp — Bloco 3 (estrutura; SEM envio) -- --------------------------------------------------------------------- CREATE TABLE media_share_queue ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, media_file_id BIGINT UNSIGNED NOT NULL, channel ENUM('whatsapp','outro') NOT NULL DEFAULT 'whatsapp', status ENUM('selecionado','preparado','enviado','cancelado') NOT NULL DEFAULT 'selecionado', note VARCHAR(255) NULL, selected_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_msq_tenant (tenant_id), KEY idx_msq_status (status), KEY idx_msq_media (media_file_id), CONSTRAINT fk_msq_tenant FOREIGN KEY (tenant_id) REFERENCES tenants(id) ON DELETE CASCADE, CONSTRAINT fk_msq_media FOREIGN KEY (media_file_id) REFERENCES media_files(id) ON DELETE CASCADE, CONSTRAINT fk_msq_user FOREIGN KEY (selected_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1; -- ===================================================================== -- SEEDS INICIAIS -- ===================================================================== INSERT INTO tenants (id, name, slug, plan, status) VALUES (1, 'Gabinete Padrão', 'gabinete', 'mvp', 'active'); INSERT INTO mandates (id, tenant_id, councilman_name, legislature, is_active) VALUES (1, 1, 'Vereador', '2025-2028', 1); INSERT INTO roles (slug, name, description, is_system) VALUES ('admin', 'Administrador', 'Acesso total ao sistema', 1), ('vereador', 'Vereador', 'Visualiza tudo, dashboards e relatórios', 1), ('chefe_gabinete', 'Chefe de Gabinete', 'Gerencia equipe, demandas, docs e agenda',1), ('assessor', 'Assessor', 'Cadastra e atualiza demandas atribuídas', 1), ('visualizador', 'Visualizador', 'Apenas consulta informações permitidas', 1), ('equipe_rua', 'Equipe de Rua', 'Envia mídia e vincula a demandas/visitas', 1), ('marketing', 'Marketing', 'Galeria, download e uso de mídia', 1); INSERT INTO permissions (slug, name, module) VALUES ('dashboard.view','Ver dashboard','dashboard'), ('citizens.view','Ver cidadãos','citizens'), ('citizens.create','Criar cidadãos','citizens'), ('citizens.update','Editar cidadãos','citizens'), ('citizens.delete','Excluir cidadãos','citizens'), ('demands.view','Ver demandas','demands'), ('demands.create','Criar demandas','demands'), ('demands.update','Editar demandas','demands'), ('demands.assign','Atribuir demandas','demands'), ('demands.delete','Excluir demandas','demands'), ('visits.view','Ver visitas técnicas','visits'), ('visits.manage','Gerenciar visitas técnicas','visits'), ('documents.view','Ver documentos','documents'), ('documents.create','Criar documentos','documents'), ('documents.update','Editar documentos','documents'), ('templates.manage','Gerenciar modelos','documents'), ('tasks.view','Ver tarefas','tasks'), ('tasks.manage','Gerenciar tarefas','tasks'), ('agenda.view','Ver agenda','agenda'), ('agenda.manage','Gerenciar agenda','agenda'), ('team.view','Ver equipe','team'), ('team.manage','Gerenciar equipe/usuários','team'), ('reports.view','Ver relatórios','reports'), ('public.manage','Gerenciar transparência pública','public'), ('audit.view','Ver auditoria','audit'), ('settings.manage','Gerenciar configurações','settings'), ('mandates.manage','Gerenciar mandatos','mandates'), ('media.view','Ver mídia/galeria','media'), ('media.upload','Enviar mídia','media'), ('media.link','Vincular mídia a registros','media'), ('media.download','Baixar mídia','media'), ('media.manage','Gerenciar mídia (público/privado, restaurar)','media'), ('media.delete','Excluir mídia','media'), ('media.mark_used','Marcar mídia como usada/sugerir publicação','media'), ('branding.view','Ver identidade visual','branding'), ('branding.manage','Gerenciar identidade visual','branding'), ('messages.view','Ver mensagens','messages'), ('messages.create','Criar mensagens','messages'), ('messages.send','Enviar mensagens','messages'), ('messages.cancel','Cancelar mensagens','messages'), ('messages.manage_templates','Gerenciar modelos de mensagem','messages'), ('messages.view_logs','Ver logs de mensagens','messages'), ('birthdays.view','Ver aniversariantes','birthdays'), ('birthdays.message','Mensagem a aniversariantes','birthdays'); INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='admin'), id FROM permissions; INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='vereador'), id FROM permissions WHERE slug IN ('dashboard.view','citizens.view','demands.view','visits.view','documents.view', 'tasks.view','agenda.view','team.view','reports.view','audit.view'); INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='chefe_gabinete'), id FROM permissions WHERE slug IN ('dashboard.view','citizens.view','citizens.create','citizens.update', 'demands.view','demands.create','demands.update','demands.assign', 'visits.view','visits.manage', 'documents.view','documents.create','documents.update','templates.manage', 'tasks.view','tasks.manage','agenda.view','agenda.manage', 'team.view','team.manage','reports.view','public.manage'); INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='assessor'), id FROM permissions WHERE slug IN ('dashboard.view','citizens.view','citizens.create','citizens.update', 'demands.view','demands.create','demands.update', 'visits.view','visits.manage', 'documents.view','documents.create','tasks.view','tasks.manage','agenda.view'); INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='visualizador'), id FROM permissions WHERE slug IN ('dashboard.view','citizens.view','demands.view','visits.view','documents.view', 'tasks.view','agenda.view','reports.view'); -- vereador e chefe enxergam/baixam mídia INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='vereador'), id FROM permissions WHERE slug IN ('media.view','media.download'); INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='chefe_gabinete'), id FROM permissions WHERE slug IN ('media.view','media.upload','media.link','media.download', 'media.manage','media.mark_used'); -- assessor pode enviar e vincular mídia das suas demandas/visitas INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='assessor'), id FROM permissions WHERE slug IN ('media.view','media.upload','media.link','media.download'); -- [17] Equipe de Rua: envia e vincula mídia; vê o que enviou; NÃO exclui de outros. -- (a regra "só o que enviou" e "não excluir de outros" é aplicada no Service, -- além das permissões abaixo — equipe_rua não recebe media.delete/manage.) INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='equipe_rua'), id FROM permissions WHERE slug IN ('dashboard.view','demands.view','visits.view', 'media.view','media.upload','media.link','media.download'); -- [17] Marketing: galeria, download, marcar usada/sugerir publicação. -- NÃO recebe citizens.view (não vê dados pessoais sensíveis sem permissão extra). INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='marketing'), id FROM permissions WHERE slug IN ('dashboard.view','media.view','media.download','media.mark_used'); -- [v4] Mensagens/WhatsApp, identidade visual e aniversariantes -- Chefe de gabinete: gerencia mensagens, modelos, identidade e aniversariantes. INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='chefe_gabinete'), id FROM permissions WHERE slug IN ('branding.view','branding.manage', 'messages.view','messages.create','messages.send','messages.cancel', 'messages.manage_templates','messages.view_logs', 'birthdays.view','birthdays.message'); -- Assessor: vê aniversariantes e cria mensagem individual; vê mensagens. INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='assessor'), id FROM permissions WHERE slug IN ('messages.view','messages.create','birthdays.view','birthdays.message'); -- Vereador: visão de mensagens, identidade e aniversariantes (leitura). INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='vereador'), id FROM permissions WHERE slug IN ('branding.view','messages.view','messages.view_logs','birthdays.view'); -- Marketing: pode ver identidade visual/dados institucionais; NÃO gerencia mensagens sensíveis. INSERT INTO role_permissions (role_id, permission_id) SELECT (SELECT id FROM roles WHERE slug='marketing'), id FROM permissions WHERE slug IN ('branding.view'); INSERT INTO request_sources (tenant_id, slug, name, sort_order) VALUES (1,'whatsapp','WhatsApp',1), (1,'telefone','Telefone',2), (1,'presencial','Presencial',3), (1,'instagram','Instagram',4), (1,'facebook','Facebook',5), (1,'evento','Evento',6), (1,'site','Site',7), (1,'outros','Outros',99); INSERT INTO demand_categories (tenant_id, slug, name, sla_days, color, sort_order) VALUES (1,'iluminacao','Iluminação pública',15,'#f59e0b',1), (1,'asfalto','Buraco/Asfalto',30,'#78716c',2), (1,'limpeza','Limpeza urbana',10,'#22c55e',3), (1,'saude','Saúde',7,'#ef4444',4), (1,'educacao','Educação',20,'#3b82f6',5), (1,'transito','Trânsito',20,'#f97316',6), (1,'transporte','Transporte',20,'#8b5cf6',7), (1,'praca_lazer','Praça e lazer',30,'#14b8a6',8), (1,'agua_esgoto','Água/Esgoto',7,'#0ea5e9',9), (1,'seguranca','Segurança',10,'#1e3a8a',10), (1,'acessibilidade','Acessibilidade',30,'#a855f7',11), (1,'outros','Outros',30,'#6b7280',99); INSERT INTO protocol_counters (tenant_id, year, last_number) VALUES (1, 2026, 0); -- Admin inicial — e-mail: admin@admin.com senha: admin123 -- Por segurança, o admin é OBRIGADO a trocar a senha no primeiro acesso -- (must_change_password=1). Implementado na Fase 10. INSERT INTO users (tenant_id, role_id, name, email, password_hash, must_change_password, is_active) VALUES (1,(SELECT id FROM roles WHERE slug='admin'), 'Administrador','admin@admin.com', '$2y$12$.cqZShgiAe59t8.v5VFUIeslXPen91EtbMcjMmsQ/V3dz7agSQEzG',1,1); INSERT INTO mandate_users (mandate_id, user_id) VALUES (1, 1); -- [v4] Identidade visual e perfil institucional padrão INSERT INTO tenant_branding (tenant_id, primary_color, secondary_color, accent_color, public_name) VALUES (1, '#1e40af', '#0ea5e9', '#f59e0b', 'Gabinete'); INSERT INTO mandate_profile (tenant_id, mandate_id, councilman_name, public_office_name, legislature) VALUES (1, 1, 'Vereador', 'Gabinete', '2025-2028'); INSERT INTO document_templates (tenant_id, type, name, subject, body, is_active) VALUES (1,'oficio','Ofício padrão à secretaria','Solicitação referente a {{categoria}} - {{bairro}}', 'Ao(À) {{secretaria}},\n\nO Gabinete do Vereador {{vereador}} encaminha solicitação de munícipe referente a {{categoria}}, localizada em {{endereco}}, bairro {{bairro}}.\n\nDescrição: {{descricao_demanda}}\n\nProtocolo interno: {{protocolo}}\nData: {{data}}\n\nAtenciosamente,\n{{responsavel}}',1), (1,'resposta_cidadao','Atualização ao cidadão (WhatsApp)',NULL, 'Olá, {{nome_cidadao}}. Sua solicitação de protocolo {{protocolo}} foi registrada pelo gabinete e está com status: {{status}}. Assim que houver nova atualização, informaremos.',1); INSERT INTO settings (tenant_id, `key`, `value`) VALUES (1,'councilman_name','Vereador'), (1,'cabinet_name','Gabinete'), (1,'protocol_prefix','SGG'), (1,'city_name',''), (1,'primary_color','#1e40af'), -- [13] limites de upload por tipo (MB) e allowlists de extensão (1,'media_max_mb_image','15'), (1,'media_max_mb_video','200'), (1,'media_max_mb_document','25'), (1,'media_max_mb_audio','50'), (1,'media_ext_image','jpg,jpeg,png,webp'), (1,'media_ext_video','mp4,mov,webm,m4v'), (1,'media_ext_document','pdf,doc,docx,xls,xlsx'), (1,'media_ext_audio','mp3,m4a,ogg'), -- [v4] WhatsApp / Z-API (tokens ficam vazios; preencher na tela, exibição mascarada) (1,'whatsapp_provider','manual'), (1,'zapi_instance_id',''), (1,'zapi_token',''), (1,'zapi_client_token',''), (1,'default_country_code','55'), (1,'birthday_message_enabled','0'), (1,'birthday_message_time','09:00'), (1,'birthday_template_id',''); -- [v4] Modelos de mensagem WhatsApp iniciais INSERT INTO message_templates (tenant_id, name, slug, category, body, variables_allowed, is_active) VALUES (1,'Aniversário padrão','aniversario-padrao','aniversario', 'Olá, {{primeiro_nome}}! O gabinete {{nome_gabinete}} deseja um feliz aniversário e um dia muito especial!', '{{primeiro_nome}},{{nome_cidadao}},{{nome_gabinete}},{{nome_vereador}}',1), (1,'Atualização de protocolo','protocolo-atualizacao','protocolo', 'Olá, {{nome_cidadao}}. Sua solicitação de protocolo {{protocolo}} está com status: {{status_demanda}}. Assim que houver nova atualização, informaremos.', '{{nome_cidadao}},{{protocolo}},{{status_demanda}},{{bairro}}',1); -- ===================================================================== -- SEEDS BLOCO 2 — Uberlândia (regiões, bairros, secretarias) -- ===================================================================== INSERT INTO regions (tenant_id, name, description, is_active) VALUES (1,'Setor Central','Região central de Uberlândia',1), (1,'Setor Norte','Região norte',1), (1,'Setor Sul','Região sul',1), (1,'Setor Leste','Região leste',1), (1,'Setor Oeste','Região oeste',1); INSERT INTO neighborhoods (tenant_id, region_id, name, city, is_active) VALUES (1,(SELECT id FROM regions WHERE name='Setor Central' AND tenant_id=1),'Centro','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Central' AND tenant_id=1),'Fundinho','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Central' AND tenant_id=1),'Lídice','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Central' AND tenant_id=1),'Osvaldo Rezende','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Central' AND tenant_id=1),'Martins','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Sul' AND tenant_id=1),'Santa Mônica','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Sul' AND tenant_id=1),'Saraiva','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Sul' AND tenant_id=1),'Tabajaras','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Sul' AND tenant_id=1),'Cidade Jardim','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Sul' AND tenant_id=1),'Gávea','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Leste' AND tenant_id=1),'Tibery','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Leste' AND tenant_id=1),'Aparecida','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Leste' AND tenant_id=1),'Santa Rosa','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Leste' AND tenant_id=1),'Morumbi','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Norte' AND tenant_id=1),'Roosevelt','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Norte' AND tenant_id=1),'Marta Helena','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Norte' AND tenant_id=1),'Presidente Roosevelt','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Norte' AND tenant_id=1),'Jardim Brasília','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Oeste' AND tenant_id=1),'Luizote de Freitas','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Oeste' AND tenant_id=1),'Tocantins','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Oeste' AND tenant_id=1),'Planalto','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Oeste' AND tenant_id=1),'Jardim Patrícia','Uberlândia',1), (1,(SELECT id FROM regions WHERE name='Setor Oeste' AND tenant_id=1),'Guarani','Uberlândia',1); -- Coordenadas aproximadas dos bairros (Bloco 7 — para o mapa territorial) UPDATE neighborhoods SET latitude=-18.9186, longitude=-48.2772 WHERE tenant_id=1 AND name='Centro'; UPDATE neighborhoods SET latitude=-18.9132, longitude=-48.2820 WHERE tenant_id=1 AND name='Fundinho'; UPDATE neighborhoods SET latitude=-18.9160, longitude=-48.2710 WHERE tenant_id=1 AND name='Lídice'; UPDATE neighborhoods SET latitude=-18.9230, longitude=-48.2740 WHERE tenant_id=1 AND name='Osvaldo Rezende'; UPDATE neighborhoods SET latitude=-18.9300, longitude=-48.2560 WHERE tenant_id=1 AND name='Martins'; UPDATE neighborhoods SET latitude=-18.9450, longitude=-48.2650 WHERE tenant_id=1 AND name='Santa Mônica'; UPDATE neighborhoods SET latitude=-18.9080, longitude=-48.2880 WHERE tenant_id=1 AND name='Saraiva'; UPDATE neighborhoods SET latitude=-18.9089, longitude=-48.2735 WHERE tenant_id=1 AND name='Tabajaras'; UPDATE neighborhoods SET latitude=-18.9520, longitude=-48.2720 WHERE tenant_id=1 AND name='Cidade Jardim'; UPDATE neighborhoods SET latitude=-18.9400, longitude=-48.2850 WHERE tenant_id=1 AND name='Gávea'; UPDATE neighborhoods SET latitude=-18.9050, longitude=-48.2450 WHERE tenant_id=1 AND name='Tibery'; UPDATE neighborhoods SET latitude=-18.9150, longitude=-48.2400 WHERE tenant_id=1 AND name='Aparecida'; UPDATE neighborhoods SET latitude=-18.9250, longitude=-48.2350 WHERE tenant_id=1 AND name='Santa Rosa'; UPDATE neighborhoods SET latitude=-18.9350, longitude=-48.2300 WHERE tenant_id=1 AND name='Morumbi'; UPDATE neighborhoods SET latitude=-18.8850, longitude=-48.2700 WHERE tenant_id=1 AND name='Roosevelt'; UPDATE neighborhoods SET latitude=-18.8780, longitude=-48.2650 WHERE tenant_id=1 AND name='Marta Helena'; UPDATE neighborhoods SET latitude=-18.8800, longitude=-48.2780 WHERE tenant_id=1 AND name='Presidente Roosevelt'; UPDATE neighborhoods SET latitude=-18.8720, longitude=-48.2820 WHERE tenant_id=1 AND name='Jardim Brasília'; UPDATE neighborhoods SET latitude=-18.9300, longitude=-48.3050 WHERE tenant_id=1 AND name='Luizote de Freitas'; UPDATE neighborhoods SET latitude=-18.9380, longitude=-48.3100 WHERE tenant_id=1 AND name='Tocantins'; UPDATE neighborhoods SET latitude=-18.9250, longitude=-48.2980 WHERE tenant_id=1 AND name='Planalto'; UPDATE neighborhoods SET latitude=-18.9450, longitude=-48.3150 WHERE tenant_id=1 AND name='Jardim Patrícia'; UPDATE neighborhoods SET latitude=-18.9180, longitude=-48.3020 WHERE tenant_id=1 AND name='Guarani'; INSERT INTO departments (tenant_id, name, acronym, is_active) VALUES (1,'Secretaria Municipal de Obras','SEOBRAS',1), (1,'Secretaria Municipal de Trânsito e Transportes','SETTRAN',1), (1,'Secretaria Municipal de Serviços Urbanos','SESU',1), (1,'Secretaria Municipal de Saúde','SMS',1), (1,'Secretaria Municipal de Meio Ambiente','SMMA',1), (1,'DMAE - Água e Esgoto','DMAE',1); UPDATE demand_categories SET default_department_id=(SELECT id FROM departments WHERE acronym='SEOBRAS' AND tenant_id=1) WHERE tenant_id=1 AND slug IN ('asfalto'); UPDATE demand_categories SET default_department_id=(SELECT id FROM departments WHERE acronym='SESU' AND tenant_id=1) WHERE tenant_id=1 AND slug IN ('iluminacao','limpeza','praca_lazer'); UPDATE demand_categories SET default_department_id=(SELECT id FROM departments WHERE acronym='SETTRAN' AND tenant_id=1) WHERE tenant_id=1 AND slug IN ('transito','transporte'); UPDATE demand_categories SET default_department_id=(SELECT id FROM departments WHERE acronym='SMS' AND tenant_id=1) WHERE tenant_id=1 AND slug IN ('saude'); UPDATE demand_categories SET default_department_id=(SELECT id FROM departments WHERE acronym='DMAE' AND tenant_id=1) WHERE tenant_id=1 AND slug IN ('agua_esgoto'); UPDATE settings SET `value`='Uberlândia' WHERE tenant_id=1 AND `key`='city_name'; -- ===================================================================== -- Bloco 13 — Governança Administrativa (migrations 015/016/017 embutidas) -- Tabelas de status/prioridade, conversão ENUM->VARCHAR, settings.manage. -- Idempotente: seguro reaplicar. -- ===================================================================== -- ===================================================================== -- MIGRATION 015 (Bloco 13) — Tabelas administrativas de status/prioridade -- Cria 5 tabelas de apoio (slug estável = valor ENUM atual) e faz o seed -- com EXATAMENTE os valores existentes. NÃO altera as tabelas de negócio -- ainda (isso é a 016). sla_status NÃO entra (permanece ENUM calculado). -- Idempotente: cria só se não existir; seed via INSERT ... ON DUPLICATE KEY. -- ===================================================================== SET NAMES utf8mb4; CREATE TABLE IF NOT EXISTS demand_statuses ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, slug VARCHAR(30) NOT NULL, label VARCHAR(60) NOT NULL, color VARCHAR(20) NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, is_final TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_demand_status_slug (tenant_id, slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS demand_priorities ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, slug VARCHAR(30) NOT NULL, label VARCHAR(60) NOT NULL, color VARCHAR(20) NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_demand_priority_slug (tenant_id, slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS visit_statuses ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, slug VARCHAR(30) NOT NULL, label VARCHAR(60) NOT NULL, color VARCHAR(20) NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, is_final TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_visit_status_slug (tenant_id, slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS task_statuses ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, slug VARCHAR(30) NOT NULL, label VARCHAR(60) NOT NULL, color VARCHAR(20) NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, is_final TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_task_status_slug (tenant_id, slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS task_priorities ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, slug VARCHAR(30) NOT NULL, label VARCHAR(60) NOT NULL, color VARCHAR(20) NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_task_priority_slug (tenant_id, slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- SEED — slugs EXATAMENTE iguais aos ENUM atuais. INSERT INTO demand_statuses (tenant_id, slug, label, sort_order, is_final) VALUES (1,'nova','Nova',1,0), (1,'em_analise','Em análise',2,0), (1,'encaminhada','Encaminhada',3,0), (1,'em_andamento','Em andamento',4,0), (1,'aguardando_retorno','Aguardando retorno',5,0), (1,'resolvida','Resolvida',6,1), (1,'arquivada','Arquivada',7,1) ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order), is_final=VALUES(is_final); INSERT INTO demand_priorities (tenant_id, slug, label, sort_order) VALUES (1,'baixa','Baixa',1),(1,'media','Média',2),(1,'alta','Alta',3),(1,'urgente','Urgente',4) ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order); INSERT INTO visit_statuses (tenant_id, slug, label, sort_order, is_final) VALUES (1,'agendada','Agendada',1,0),(1,'realizada','Realizada',2,1), (1,'cancelada','Cancelada',3,1),(1,'remarcada','Remarcada',4,0) ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order), is_final=VALUES(is_final); INSERT INTO task_statuses (tenant_id, slug, label, sort_order, is_final) VALUES (1,'aberta','Aberta',1,0),(1,'em_andamento','Em andamento',2,0), (1,'concluida','Concluída',3,1),(1,'cancelada','Cancelada',4,1) ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order), is_final=VALUES(is_final); INSERT INTO task_priorities (tenant_id, slug, label, sort_order) VALUES (1,'baixa','Baixa',1),(1,'media','Média',2),(1,'alta','Alta',3),(1,'urgente','Urgente',4) ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order); -- ===================================================================== -- MIGRATION 016 (Bloco 13) — Converte ENUM->VARCHAR nos campos de negócio -- preservando EXATAMENTE os valores (que já são os slugs) e adiciona índices. -- NÃO mexe em sla_status (permanece ENUM calculado). -- Idempotente: usa procedure que só altera se a coluna ainda for ENUM. -- Compatível com MySQL 8 / MariaDB 10.4+. -- ===================================================================== SET NAMES utf8mb4; DROP PROCEDURE IF EXISTS sgg_enum_to_varchar; DELIMITER // CREATE PROCEDURE sgg_enum_to_varchar(IN p_table VARCHAR(64), IN p_col VARCHAR(64), IN p_default VARCHAR(30)) BEGIN DECLARE v_type VARCHAR(20); SELECT DATA_TYPE INTO v_type FROM information_schema.columns WHERE table_schema=DATABASE() AND table_name=p_table AND column_name=p_col; IF v_type = 'enum' THEN SET @s = CONCAT('ALTER TABLE `',p_table,'` MODIFY `',p_col,'` VARCHAR(30) NOT NULL DEFAULT ''',p_default,''''); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; END IF; END // DELIMITER ; CALL sgg_enum_to_varchar('demands','status','nova'); CALL sgg_enum_to_varchar('demands','priority','media'); CALL sgg_enum_to_varchar('technical_visits','status','agendada'); CALL sgg_enum_to_varchar('tasks','status','aberta'); CALL sgg_enum_to_varchar('tasks','priority','media'); DROP PROCEDURE IF EXISTS sgg_enum_to_varchar; -- Índices de apoio para joins/filtros por status (idempotentes via procedure) DROP PROCEDURE IF EXISTS sgg_add_idx16; DELIMITER // CREATE PROCEDURE sgg_add_idx16(IN p_table VARCHAR(64), IN p_idx VARCHAR(64), IN p_col VARCHAR(64)) BEGIN IF NOT EXISTS (SELECT 1 FROM information_schema.statistics WHERE table_schema=DATABASE() AND table_name=p_table AND index_name=p_idx) THEN SET @s=CONCAT('ALTER TABLE `',p_table,'` ADD KEY `',p_idx,'` (`',p_col,'`)'); PREPARE st FROM @s; EXECUTE st; DEALLOCATE PREPARE st; END IF; END // DELIMITER ; CALL sgg_add_idx16('demands','idx_demands_priority','priority'); CALL sgg_add_idx16('tasks','idx_tasks_status','status'); DROP PROCEDURE IF EXISTS sgg_add_idx16; -- NOTA: FK real (demands.status -> demand_statuses.slug) exigiria que slug fosse -- UNIQUE simples (é UNIQUE composto com tenant_id). Mantemos integridade pela -- aplicação + seed completo, evitando travar operações multi-tenant. Documentado. -- ===================================================================== -- MIGRATION 017 (Bloco 13) — Garante a permissão settings.manage e a liga -- ao papel admin (para proteger a área Administração). Idempotente. -- ===================================================================== SET NAMES utf8mb4; INSERT INTO permissions (slug, name, module) SELECT 'settings.manage', 'Gerenciar configurações', 'admin' WHERE NOT EXISTS (SELECT 1 FROM permissions WHERE slug='settings.manage'); INSERT INTO role_permissions (role_id, permission_id) SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='admin' AND p.slug='settings.manage' AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id); -- ===================================================================== -- Bloco 14 — Inteligência do Gabinete (migration 018 embutida) -- ===================================================================== -- ===================================================================== -- MIGRATION 018 (Bloco 14) — Metas do gabinete (goals) -- Estrutura mínima para metas mensais (demandas resolvidas, visitas, tarefas). -- Idempotente: cria só se não existir. -- ===================================================================== SET NAMES utf8mb4; CREATE TABLE IF NOT EXISTS goals ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, mandate_id BIGINT UNSIGNED NULL, metric VARCHAR(40) NOT NULL, -- demands_resolved | visits_done | tasks_done target_value INT NOT NULL DEFAULT 0, period_month TINYINT NOT NULL, -- 1..12 period_year SMALLINT NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, 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), UNIQUE KEY uq_goal (tenant_id, metric, period_month, period_year), KEY idx_goals_period (tenant_id, period_year, period_month) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- ===================================================================== -- BLOCO 15 — CENTRAL DE COMUNICAÇÃO E MARKETING (migration 021) -- Estrutura própria e desacoplada da demanda. Incluída aqui para -- instalações novas. Idempotente (IF NOT EXISTS / inserts guardados). -- ===================================================================== SET NAMES utf8mb4; CREATE TABLE IF NOT EXISTS communication_statuses ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, slug VARCHAR(40) NOT NULL, label VARCHAR(80) NOT NULL, color VARCHAR(20) NULL, sort_order INT NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, is_initial TINYINT(1) NOT NULL DEFAULT 0, is_final TINYINT(1) NOT NULL DEFAULT 0, 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_comm_status_slug (tenant_id, slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; INSERT INTO communication_statuses (tenant_id, slug, label, sort_order, is_active, is_initial, is_final) VALUES (1,'recebido','Recebido',1,1,1,0), (1,'em_analise','Em análise',2,1,0,0), (1,'aguardando_midia','Aguardando mídia',3,1,0,0), (1,'material_incompleto','Material incompleto',4,1,0,0), (1,'arte_em_producao','Arte em produção',5,1,0,0), (1,'aguardando_aprovacao','Aguardando aprovação',6,1,0,0), (1,'pronto_publicar','Pronto para publicar',7,1,0,0), (1,'publicado','Publicado',8,1,0,1), (1,'arquivado','Arquivado',9,1,0,1), (1,'cancelado','Cancelado',10,1,0,1) ON DUPLICATE KEY UPDATE label=VALUES(label), sort_order=VALUES(sort_order), is_initial=VALUES(is_initial), is_final=VALUES(is_final); CREATE TABLE IF NOT EXISTS communication_items ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, tenant_id BIGINT UNSIGNED NOT NULL DEFAULT 1, demand_id BIGINT UNSIGNED NOT NULL, demand_protocol VARCHAR(24) NULL, demand_title VARCHAR(200) NULL, status VARCHAR(40) NOT NULL DEFAULT 'recebido', assigned_to BIGINT UNSIGNED NULL, assigned_at DATETIME NULL, forwarded_by BIGINT UNSIGNED NULL, forwarded_at DATETIME NULL, internal_notes TEXT NULL, published_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, deleted_at DATETIME NULL, PRIMARY KEY (id), UNIQUE KEY uq_comm_demand (tenant_id, demand_id), KEY idx_comm_tenant (tenant_id), KEY idx_comm_status (status), KEY idx_comm_assigned (assigned_to), KEY idx_comm_forwarded_by (forwarded_by), KEY idx_comm_created (created_at), CONSTRAINT fk_comm_demand FOREIGN KEY (demand_id) REFERENCES demands(id) ON DELETE CASCADE, CONSTRAINT fk_comm_assigned FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_comm_forward FOREIGN KEY (forwarded_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS communication_status_history ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, item_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL, event_type VARCHAR(30) NOT NULL DEFAULT 'mudanca_status', status_from VARCHAR(40) NULL, status_to VARCHAR(40) NULL, comment TEXT NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_csh_item (item_id), KEY idx_csh_user (user_id), KEY idx_csh_created (created_at), CONSTRAINT fk_csh_item FOREIGN KEY (item_id) REFERENCES communication_items(id) ON DELETE CASCADE, CONSTRAINT fk_csh_user FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS communication_checklists ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, item_id BIGINT UNSIGNED NOT NULL, slug VARCHAR(40) NOT NULL, label VARCHAR(120) NOT NULL, is_done TINYINT(1) NOT NULL DEFAULT 0, sort_order INT NOT NULL DEFAULT 0, done_by BIGINT UNSIGNED NULL, done_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_checklist_item_slug (item_id, slug), KEY idx_checklist_item (item_id), CONSTRAINT fk_checklist_item FOREIGN KEY (item_id) REFERENCES communication_items(id) ON DELETE CASCADE, CONSTRAINT fk_checklist_user FOREIGN KEY (done_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS communication_publications ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, item_id BIGINT UNSIGNED NOT NULL, channel VARCHAR(40) NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'nao_publicado', link VARCHAR(500) NULL, published_by BIGINT UNSIGNED NULL, published_at DATETIME 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_pub_item_channel (item_id, channel), KEY idx_pub_item (item_id), KEY idx_pub_status (status), CONSTRAINT fk_pub_item FOREIGN KEY (item_id) REFERENCES communication_items(id) ON DELETE CASCADE, CONSTRAINT fk_pub_user FOREIGN KEY (published_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; CREATE TABLE IF NOT EXISTS communication_assignments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, item_id BIGINT UNSIGNED NOT NULL, assigned_to BIGINT UNSIGNED NULL, assigned_by BIGINT UNSIGNED NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_assign_item (item_id), CONSTRAINT fk_assign_item FOREIGN KEY (item_id) REFERENCES communication_items(id) ON DELETE CASCADE, CONSTRAINT fk_assign_to FOREIGN KEY (assigned_to) REFERENCES users(id) ON DELETE SET NULL, CONSTRAINT fk_assign_by FOREIGN KEY (assigned_by) REFERENCES users(id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- Permissões da comunicação INSERT INTO permissions (slug, name, module) SELECT * FROM ( SELECT 'communication.view' AS slug, 'Ver comunicação' AS name, 'communication' AS module UNION ALL SELECT 'communication.create', 'Criar itens de comunicação', 'communication' UNION ALL SELECT 'communication.update', 'Editar comunicação', 'communication' UNION ALL SELECT 'communication.assign', 'Atribuir responsável de comunicação','communication' UNION ALL SELECT 'communication.publish', 'Publicar/registrar publicação', 'communication' UNION ALL SELECT 'communication.archive', 'Arquivar comunicação', 'communication' ) AS novos WHERE NOT EXISTS (SELECT 1 FROM permissions p WHERE p.slug = novos.slug); INSERT INTO role_permissions (role_id, permission_id) SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='admin' AND p.module='communication' AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id); INSERT INTO role_permissions (role_id, permission_id) SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='chefe_gabinete' AND p.module='communication' AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id); INSERT INTO role_permissions (role_id, permission_id) SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='marketing' AND p.slug IN ('communication.view','communication.update','communication.assign','communication.publish','communication.archive') AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id); INSERT INTO role_permissions (role_id, permission_id) SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='equipe_rua' AND p.slug IN ('communication.view','communication.create') AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id); INSERT INTO role_permissions (role_id, permission_id) SELECT r.id, p.id FROM roles r CROSS JOIN permissions p WHERE r.slug='vereador' AND p.slug IN ('communication.view') AND NOT EXISTS (SELECT 1 FROM role_permissions rp WHERE rp.role_id=r.id AND rp.permission_id=p.id);