-- ===================================================================== -- LeadSite AI - Esquema completo do banco de dados -- MySQL 5.7+ / MariaDB 10.3+ | Charset utf8mb4 -- Importe este arquivo antes do seed.sql -- ===================================================================== SET NAMES utf8mb4; SET FOREIGN_KEY_CHECKS = 0; SET time_zone = '+00:00'; -- --------------------------------------------------------------------- -- USUARIOS -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS users ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(150) NOT NULL, email VARCHAR(190) NOT NULL, password_hash VARCHAR(255) NOT NULL, role ENUM('admin','user') NOT NULL DEFAULT 'user', status ENUM('active','blocked','pending') NOT NULL DEFAULT 'active', email_verified_at DATETIME NULL, trial_ends_at DATETIME NULL, last_login_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_users_email (email), KEY idx_users_role (role), KEY idx_users_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- RECUPERACAO DE SENHA -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS password_resets ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_pr_user (user_id), KEY idx_pr_token (token_hash), CONSTRAINT fk_pr_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- VERIFICACAO DE E-MAIL -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS email_verifications ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, token_hash CHAR(64) NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_ev_user (user_id), KEY idx_ev_token (token_hash), CONSTRAINT fk_ev_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- TENTATIVAS DE LOGIN (protecao contra forca bruta) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS login_attempts ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, email VARCHAR(190) NOT NULL, ip_address VARCHAR(45) NOT NULL, successful TINYINT(1) NOT NULL DEFAULT 0, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_la_email_time (email, created_at), KEY idx_la_ip_time (ip_address, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- EMPRESAS (dados compartilhados por fonte; nao replicar por usuario) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS companies ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, owner_user_id BIGINT UNSIGNED NULL, -- quem cadastrou primeiro (manual) source VARCHAR(40) NOT NULL DEFAULT 'manual', -- google_places | manual | outro source_place_id VARCHAR(190) NULL, -- identificador na fonte name VARCHAR(200) NOT NULL, category VARCHAR(150) NULL, phone VARCHAR(40) NULL, website_url VARCHAR(500) NULL, address VARCHAR(500) NULL, city VARCHAR(120) NULL, state VARCHAR(120) NULL, country VARCHAR(80) NULL DEFAULT 'Brasil', latitude DECIMAL(10,7) NULL, longitude DECIMAL(10,7) NULL, rating DECIMAL(3,2) NULL, website_status ENUM('has_site','no_site_reported','site_unavailable','needs_verification') NOT NULL DEFAULT 'needs_verification', verification_status ENUM('unverified','verified','corrected') NOT NULL DEFAULT 'unverified', do_not_contact TINYINT(1) NOT NULL DEFAULT 0, last_checked_at DATETIME NULL, source_checked_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), -- Evita duplicar o mesmo estabelecimento da mesma fonte UNIQUE KEY uq_company_source (source, source_place_id), KEY idx_company_name (name), KEY idx_company_city (city, state), KEY idx_company_website_status (website_status), CONSTRAINT fk_company_owner FOREIGN KEY (owner_user_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- LEADS (vinculo entre usuario e empresa) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS leads ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, company_id BIGINT UNSIGNED NOT NULL, lead_status ENUM('new','pending_verification','demo_created','ready_to_contact', 'contacted','interested','proposal_sent','negotiating', 'converted','not_interested','do_not_contact') NOT NULL DEFAULT 'new', priority ENUM('low','normal','high') NOT NULL DEFAULT 'normal', notes TEXT NULL, last_contact_at DATETIME NULL, next_followup_at DATETIME NULL, converted_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_lead_user_company (user_id, company_id), KEY idx_lead_status (lead_status), KEY idx_lead_followup (next_followup_at), CONSTRAINT fk_lead_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_lead_company FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- HISTORICO DE ATIVIDADES DO LEAD -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS lead_activities ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, lead_id BIGINT UNSIGNED NOT NULL, user_id BIGINT UNSIGNED NULL, type VARCHAR(40) NOT NULL, -- status_change | note | contact | demo | email | whatsapp description TEXT NULL, meta_json JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_la_lead (lead_id, created_at), CONSTRAINT fk_la_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE, CONSTRAINT fk_la_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- TAREFAS / FOLLOW-UP -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS tasks ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, lead_id BIGINT UNSIGNED NULL, title VARCHAR(200) NOT NULL, description TEXT NULL, due_at DATETIME NULL, completed_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), KEY idx_task_user_due (user_id, due_at), CONSTRAINT fk_task_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_task_lead FOREIGN KEY (lead_id) REFERENCES leads (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- SITES DEMONSTRATIVOS -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS demo_sites ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NULL, -- NULL quando gerado pela landing (publico) company_id BIGINT UNSIGNED NULL, slug VARCHAR(190) NOT NULL, template VARCHAR(60) NOT NULL DEFAULT 'servicos', title VARCHAR(200) NOT NULL, content_json JSON NOT NULL, status ENUM('draft','active','disabled','expired') NOT NULL DEFAULT 'active', views INT UNSIGNED NOT NULL DEFAULT 0, is_public_demo TINYINT(1) NOT NULL DEFAULT 0, created_by_ip VARCHAR(45) NULL, expires_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_demo_slug (slug), KEY idx_demo_user (user_id), KEY idx_demo_company (company_id), CONSTRAINT fk_demo_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_demo_company FOREIGN KEY (company_id) REFERENCES companies (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- PLANOS -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS plans ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, slug VARCHAR(60) NOT NULL, name VARCHAR(120) NOT NULL, description VARCHAR(500) NULL, price DECIMAL(10,2) NOT NULL DEFAULT 0.00, billing_period ENUM('monthly','yearly','one_time') NOT NULL DEFAULT 'monthly', search_limit INT NOT NULL DEFAULT 0, -- 0 = ilimitado demo_limit INT NOT NULL DEFAULT 0, export_enabled TINYINT(1) NOT NULL DEFAULT 0, features_json JSON NULL, is_public TINYINT(1) NOT NULL DEFAULT 1, sort_order INT NOT NULL DEFAULT 0, 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_plan_slug (slug) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- ASSINATURAS -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS subscriptions ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, plan_id BIGINT UNSIGNED NOT NULL, provider VARCHAR(40) NOT NULL DEFAULT 'mercadopago', provider_subscription_id VARCHAR(190) NULL, external_reference VARCHAR(190) NOT NULL, status ENUM('pending','authorized','active','paused','cancelled','expired','rejected') NOT NULL DEFAULT 'pending', current_period_start DATETIME NULL, current_period_end DATETIME NULL, cancel_at_period_end TINYINT(1) NOT NULL DEFAULT 0, cancelled_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_sub_external_ref (external_reference), KEY idx_sub_user (user_id), KEY idx_sub_provider (provider_subscription_id), CONSTRAINT fk_sub_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_sub_plan FOREIGN KEY (plan_id) REFERENCES plans (id) ON DELETE RESTRICT ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- PAGAMENTOS -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS payments ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, subscription_id BIGINT UNSIGNED NULL, provider VARCHAR(40) NOT NULL DEFAULT 'mercadopago', provider_payment_id VARCHAR(190) NULL, external_reference VARCHAR(190) NULL, amount DECIMAL(10,2) NOT NULL DEFAULT 0.00, currency CHAR(3) NOT NULL DEFAULT 'BRL', status ENUM('pending','approved','in_process','rejected','refunded','cancelled','charged_back') NOT NULL DEFAULT 'pending', paid_at DATETIME NULL, raw_status VARCHAR(60) 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_pay_provider_id (provider, provider_payment_id), KEY idx_pay_user (user_id), KEY idx_pay_sub (subscription_id), CONSTRAINT fk_pay_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE, CONSTRAINT fk_pay_sub FOREIGN KEY (subscription_id) REFERENCES subscriptions (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- CONSUMO / USO (limites de plano) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS usage_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, action VARCHAR(60) NOT NULL, -- search | demo | export | login quantity INT NOT NULL DEFAULT 1, metadata_json JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_usage_user_action (user_id, action, created_at), CONSTRAINT fk_usage_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- EVENTOS DE WEBHOOK (idempotencia) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS webhook_events ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, provider VARCHAR(40) NOT NULL DEFAULT 'mercadopago', event_id VARCHAR(190) NOT NULL, event_type VARCHAR(120) NULL, payload_hash CHAR(64) NOT NULL, status ENUM('received','processed','failed','ignored') NOT NULL DEFAULT 'received', attempts INT NOT NULL DEFAULT 0, last_error TEXT NULL, processed_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), UNIQUE KEY uq_webhook_event (provider, event_id), KEY idx_webhook_status (status) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- AUDITORIA -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS audit_logs ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NULL, action VARCHAR(80) NOT NULL, entity_type VARCHAR(60) NULL, entity_id VARCHAR(60) NULL, ip_address VARCHAR(45) NULL, meta_json JSON NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_audit_user (user_id, created_at), KEY idx_audit_action (action), CONSTRAINT fk_audit_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- CONFIGURACOES DA PLATAFORMA (editaveis pelo painel admin) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS settings ( setting_key VARCHAR(120) NOT NULL, setting_value TEXT NULL, updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (setting_key) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- MENSAGENS DE CONTATO (landing page) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS contact_messages ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, name VARCHAR(150) NOT NULL, email VARCHAR(190) NOT NULL, subject VARCHAR(200) NULL, message TEXT NOT NULL, ip_address VARCHAR(45) NULL, status ENUM('new','read','archived') NOT NULL DEFAULT 'new', created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_cm_status (status, created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- SOLICITACOES LGPD (exclusao / correcao de dados) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS privacy_requests ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NULL, email VARCHAR(190) NOT NULL, request_type ENUM('access','correction','deletion','export') NOT NULL, details TEXT NULL, status ENUM('open','in_progress','done','denied') NOT NULL DEFAULT 'open', handled_by BIGINT UNSIGNED NULL, handled_at DATETIME NULL, created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id), KEY idx_pvr_status (status, created_at), CONSTRAINT fk_pvr_user FOREIGN KEY (user_id) REFERENCES users (id) ON DELETE SET NULL, CONSTRAINT fk_pvr_handler FOREIGN KEY (handled_by) REFERENCES users (id) ON DELETE SET NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; -- --------------------------------------------------------------------- -- RATE LIMITING -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS rate_limits ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, bucket VARCHAR(190) NOT NULL, created_at INT UNSIGNED NOT NULL, expires_at INT UNSIGNED NOT NULL, PRIMARY KEY (id), KEY idx_rl_bucket (bucket, expires_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci; SET FOREIGN_KEY_CHECKS = 1;