--
-- Host: localhost    Database: italvettools_bdcrm
-- ------------------------------------------------------


--
-- Table structure for table `audit_logs`
--

CREATE TABLE `audit_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `action` varchar(100) NOT NULL,
  `entity_type` varchar(100) NOT NULL,
  `entity_id` int(11) DEFAULT NULL,
  `old_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`old_values`)),
  `new_values` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`new_values`)),
  `ip_address` varchar(45) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_audit_logs_entity` (`entity_type`,`entity_id`),
  KEY `idx_audit_logs_user_id` (`user_id`),
  KEY `idx_audit_logs_created_at` (`created_at`),
  CONSTRAINT `audit_logs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=42 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `audit_logs`
--

INSERT INTO `audit_logs` VALUES
(1,1,'update','lead',28,'{\"id\":\"28\",\"event_id\":null,\"first_name\":\"Bryan Angel\",\"last_name\":\"De La Cruz Soto\",\"email\":\"bdelacruz654@gmail.com\",\"phone\":\"+51912538004\",\"company\":\"Next Boost Per\\u00fa\",\"position\":\"\",\"city\":\"Sunampe\",\"region\":\"Ica\",\"client_type\":[\"Distribuidor\",\"Veterinario\"],\"industry\":[\"Nutrici\\u00f3n Animal\",\"Sanidad Animal\"],\"classification\":\"tibio\",\"observations\":\"\",\"source\":\"manual\",\"status\":\"nuevo\",\"loss_reason_id\":null,\"is_duplicate\":\"1\",\"duplicate_of\":\"23\",\"created_by\":\"1\",\"updated_by\":\"1\",\"created_at\":\"2026-06-24 13:34:39\",\"updated_at\":\"2026-06-24 15:17:16\",\"created_by_name\":\"Super Administrador\",\"technical_profile\":{\"id\":\"3\",\"lead_id\":\"28\",\"animal_type\":null,\"herd_size\":null,\"current_supplier\":null,\"interest_area\":null,\"additional_data\":null,\"created_at\":\"2026-06-24 13:34:39\",\"updated_at\":\"2026-06-24 13:34:39\"}}','{\"id\":\"28\",\"event_id\":null,\"first_name\":\"Bryan Angel\",\"last_name\":\"De La Cruz Soto\",\"email\":\"bdelacruz654@gmail.com\",\"phone\":\"+51912538004\",\"company\":\"Next Boost Per\\u00fa\",\"position\":\"\",\"city\":\"Sunampe\",\"region\":\"Ica\",\"client_type\":[\"Distribuidor\",\"Veterinario\"],\"industry\":[\"Nutrici\\u00f3n Animal\",\"Sanidad Animal\"],\"classification\":\"frio\",\"observations\":\"\",\"source\":\"manual\",\"status\":\"nuevo\",\"loss_reason_id\":null,\"is_duplicate\":\"1\",\"duplicate_of\":\"23\",\"created_by\":\"1\",\"updated_by\":\"1\",\"created_at\":\"2026-06-24 13:34:39\",\"updated_at\":\"2026-06-24 15:29:03\",\"created_by_name\":\"Super Administrador\",\"technical_profile\":{\"id\":\"3\",\"lead_id\":\"28\",\"animal_type\":null,\"herd_size\":null,\"current_supplier\":null,\"interest_area\":null,\"additional_data\":null,\"created_at\":\"2026-06-24 13:34:39\",\"updated_at\":\"2026-06-24 13:34:39\"},\"status_history\":[],\"timeline\":[],\"products\":[]}','132.184.130.146','2026-06-24 19:29:03'),
(2,1,'login','user',1,NULL,NULL,'132.184.130.146','2026-06-24 19:48:51'),
(3,1,'login','user',1,NULL,NULL,'132.184.130.146','2026-06-24 19:53:00'),
(4,1,'login','user',1,NULL,NULL,'179.6.14.40','2026-06-24 19:53:11'),
(5,4,'login','user',4,NULL,NULL,'132.184.130.146','2026-06-24 19:53:29'),
(6,1,'login','user',1,NULL,NULL,'132.184.130.146','2026-06-24 20:02:49'),
(7,1,'update','lead',23,'{\"id\":\"23\",\"event_id\":\"7\",\"first_name\":\"Bryan\",\"last_name\":\"De la cruz\",\"email\":\"bdelacruz654@gmail.com\",\"phone\":\"+51947394676\",\"company\":\"Agromex\",\"position\":\"Gerente\",\"city\":\"chincha\",\"region\":\"Ica\",\"client_type\":[\"Distribuidor\"],\"industry\":[\"Nutrici\\u00f3n Animal\"],\"classification\":\"caliente\",\"observations\":\"ssssssssss\",\"source\":\"manual\",\"status\":\"contactado\",\"loss_reason_id\":null,\"is_duplicate\":\"1\",\"duplicate_of\":null,\"created_by\":\"4\",\"updated_by\":\"1\",\"created_at\":\"2026-06-22 23:43:40\",\"updated_at\":\"2026-06-24 12:53:06\",\"created_by_name\":\"Carlos Vendedor\",\"technical_profile\":{\"id\":\"2\",\"lead_id\":\"23\",\"animal_type\":\"Prueba\",\"herd_size\":\"100\",\"current_supplier\":\"Prueba\",\"interest_area\":\"Prueba\",\"additional_data\":null,\"created_at\":\"2026-06-22 23:43:40\",\"updated_at\":\"2026-06-23 00:44:20\"}}','{\"id\":\"23\",\"event_id\":\"7\",\"first_name\":\"Bryan\",\"last_name\":\"De la cruz\",\"email\":\"bdelacruz654@gmail.com\",\"phone\":\"+51947394676\",\"company\":\"Agromex\",\"position\":\"Gerente\",\"city\":\"chincha\",\"region\":\"Ica\",\"client_type\":[\"Distribuidor\"],\"industry\":[\"Nutrici\\u00f3n Animal\"],\"classification\":\"caliente\",\"observations\":\"12\\/05 \",\"source\":\"manual\",\"status\":\"contactado\",\"loss_reason_id\":null,\"is_duplicate\":\"1\",\"duplicate_of\":null,\"created_by\":\"4\",\"updated_by\":\"1\",\"created_at\":\"2026-06-22 23:43:40\",\"updated_at\":\"2026-06-24 16:07:30\",\"created_by_name\":\"Carlos Vendedor\",\"technical_profile\":{\"id\":\"2\",\"lead_id\":\"23\",\"animal_type\":\"Prueba\",\"herd_size\":\"100\",\"current_supplier\":\"Prueba\",\"interest_area\":\"Prueba\",\"additional_data\":null,\"created_at\":\"2026-06-22 23:43:40\",\"updated_at\":\"2026-06-23 00:44:20\"},\"status_history\":[{\"id\":\"9\",\"lead_id\":\"23\",\"previous_status\":\"nuevo\",\"new_status\":\"contactado\",\"loss_reason_id\":null,\"notes\":null,\"changed_by\":\"4\",\"created_at\":\"2026-06-23 00:37:21\",\"changed_by_name\":\"Carlos Vendedor\"}],\"timeline\":[{\"type\":\"status_change\",\"date\":\"2026-06-23 00:37:21\",\"data\":{\"from\":\"nuevo\",\"to\":\"contactado\",\"notes\":null,\"changed_by\":\"Carlos Vendedor\"}}],\"products\":[]}','179.6.14.40','2026-06-24 20:07:30'),
(8,4,'create','lead',29,NULL,NULL,'132.184.130.146','2026-06-24 20:27:39'),
(9,1,'login','user',1,NULL,NULL,'179.6.14.40','2026-06-24 20:37:17'),
(10,1,'login','user',1,NULL,NULL,'179.6.14.40','2026-06-24 20:38:03'),
(11,4,'login','user',4,NULL,NULL,'132.184.130.146','2026-06-24 20:41:51'),
(12,4,'login','user',4,NULL,NULL,'179.6.14.40','2026-06-24 20:53:13'),
(13,4,'login','user',4,NULL,NULL,'179.6.14.40','2026-06-24 20:55:00'),
(14,4,'login','user',4,NULL,NULL,'179.6.14.40','2026-06-24 21:33:07'),
(15,1,'login','user',1,NULL,NULL,'179.6.14.40','2026-06-24 21:41:16'),
(16,4,'login','user',4,NULL,NULL,'190.236.89.137','2026-06-24 22:00:33'),
(17,4,'update','lead',29,NULL,'{\"status\":\"calificado\"}','190.236.89.137','2026-06-24 22:01:15'),
(18,4,'login','user',4,NULL,NULL,'181.67.176.175','2026-06-25 14:53:39'),
(19,4,'login','user',4,NULL,NULL,'181.67.176.175','2026-06-25 15:19:51'),
(20,4,'login','user',4,NULL,NULL,'181.67.176.175','2026-06-25 15:56:59'),
(21,4,'login','user',4,NULL,NULL,'181.67.176.175','2026-06-25 16:58:24'),
(22,4,'login','user',4,NULL,NULL,'181.67.176.175','2026-06-25 17:13:00'),
(23,4,'login','user',4,NULL,NULL,'181.67.176.175','2026-06-25 18:08:59'),
(24,4,'login','user',4,NULL,NULL,'190.236.89.176','2026-06-25 19:31:47'),
(25,4,'login','user',4,NULL,NULL,'179.6.14.40','2026-06-25 21:24:39'),
(26,4,'login','user',4,NULL,NULL,'132.184.130.146','2026-06-25 22:13:02'),
(27,1,'login','user',1,NULL,NULL,'179.6.14.40','2026-06-26 14:42:36'),
(28,1,'login','user',1,NULL,NULL,'179.6.14.40','2026-07-01 20:27:45'),
(29,4,'login','user',4,NULL,NULL,'38.43.143.171','2026-07-01 20:28:42'),
(30,4,'login','user',4,NULL,NULL,'38.43.143.171','2026-07-01 20:29:43'),
(31,4,'create','lead',30,NULL,NULL,'38.43.143.171','2026-07-01 20:47:30'),
(32,1,'login','user',1,NULL,NULL,'38.43.143.171','2026-07-01 21:11:05'),
(33,4,'login','user',4,NULL,NULL,'179.6.14.40','2026-07-01 21:45:34'),
(34,4,'update','lead',29,NULL,'{\"status\":\"contactado\"}','179.6.14.40','2026-07-01 21:47:10'),
(35,4,'update','lead',29,NULL,'{\"status\":\"propuesta\"}','179.6.14.40','2026-07-01 21:47:11'),
(36,4,'login','user',4,NULL,NULL,'179.6.14.40','2026-07-01 23:45:22'),
(37,4,'login','user',4,NULL,NULL,'181.67.176.175','2026-07-03 17:47:22'),
(38,4,'login','user',4,NULL,NULL,'38.43.143.171','2026-07-03 19:11:47'),
(39,3,'login','user',3,NULL,NULL,'38.43.143.171','2026-07-04 02:47:05'),
(40,3,'login','user',3,NULL,NULL,'181.67.176.175','2026-07-07 13:59:15'),
(41,1,'login','user',1,NULL,NULL,'181.67.176.175','2026-07-08 15:05:39');

--
-- Table structure for table `backups`
--

CREATE TABLE `backups` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `file_name` varchar(300) NOT NULL,
  `file_path` varchar(500) NOT NULL,
  `type` enum('completo','parcial') NOT NULL DEFAULT 'completo',
  `size_bytes` bigint(20) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `backups_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `backups`
--

INSERT INTO `backups` VALUES
(1,'backup_20260624_092138.sql','/home/italvettools/back.italvettools.com/storage/backups/backup_20260624_092138.sql','completo',33103,1,'2026-06-24 15:21:40'),
(2,'backup_20260624_093213.sql','/home/italvettools/back.italvettools.com/storage/backups/backup_20260624_093213.sql','completo',33310,1,'2026-06-24 15:32:14'),
(3,'backup_20260624_104312.sql','/home/italvettools/back.italvettools.com/storage/backups/backup_20260624_104312.sql','completo',33729,1,'2026-06-24 16:43:14'),
(4,'backup_20260624_104318.sql','/home/italvettools/back.italvettools.com/storage/backups/backup_20260624_104318.sql','completo',33890,1,'2026-06-24 16:43:18'),
(5,'backup_20260624_123502.sql','/home/italvettools/back.italvettools.com/storage/backups/backup_20260624_123502.sql','completo',35104,1,'2026-06-24 18:35:03'),
(6,'backup_20260624_123750.sql','/home/italvettools/back.italvettools.com/storage/backups/backup_20260624_123750.sql','completo',35265,1,'2026-06-24 18:37:50'),
(7,'backup_20260624_132930.sql','/home/italvettools/back.italvettools.com/storage/backups/backup_20260624_132930.sql','completo',37632,1,'2026-06-24 19:29:31'),
(8,'backup_20260627_174023.sql','/home/italvettools/back.italvettools.com/storage/backups/backup_20260627_174023.sql','completo',41777,1,'2026-06-27 23:40:23');

--
-- Table structure for table `event_users`
--

CREATE TABLE `event_users` (
  `event_id` int(11) NOT NULL,
  `user_id` int(11) NOT NULL,
  PRIMARY KEY (`event_id`,`user_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `event_users_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `event_users_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `event_users`
--

INSERT INTO `event_users` VALUES
(6,2),
(7,4);

--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  `location` varchar(300) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `events`
--

INSERT INTO `events` VALUES
(1,'Feria QA Editada','Actualizado','2026-08-15','2026-08-20','Lima','active','2026-06-16 05:52:24','2026-06-16 05:52:33'),
(2,'Congreso QA',NULL,NULL,NULL,'Arequipa','active','2026-06-16 05:52:25','2026-06-16 05:52:25'),
(3,'Feria QA',NULL,NULL,NULL,NULL,'active','2026-06-17 02:59:38','2026-06-17 02:59:38'),
(4,'Test',NULL,NULL,NULL,NULL,'active','2026-06-17 03:01:14','2026-06-17 03:01:14'),
(6,'Evento Gestor',NULL,NULL,NULL,NULL,'active','2026-06-18 04:15:25','2026-06-18 04:15:25'),
(7,'APROHPERU 2026','Feria principal','2026-08-15','2026-08-20','Lima','active','2026-06-18 05:04:53','2026-06-18 05:04:53');

--
-- Table structure for table `lead_products`
--

CREATE TABLE `lead_products` (
  `lead_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`lead_id`,`product_id`),
  KEY `product_id` (`product_id`),
  CONSTRAINT `lead_products_ibfk_1` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `lead_products_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `products` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `lead_products`
--


--
-- Table structure for table `lead_representatives`
--

CREATE TABLE `lead_representatives` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lead_id` int(11) NOT NULL,
  `tipo_documento` varchar(20) DEFAULT NULL,
  `numero_documento` varchar(20) DEFAULT NULL,
  `nombre` varchar(250) NOT NULL,
  `cargo` varchar(200) DEFAULT NULL,
  `fecha_desde` date DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_lead_representatives_lead_id` (`lead_id`),
  CONSTRAINT `lead_representatives_ibfk_1` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci;

--
-- Dumping data for table `lead_representatives`
--


--
-- Table structure for table `lead_status_history`
--

CREATE TABLE `lead_status_history` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lead_id` int(11) NOT NULL,
  `previous_status` varchar(30) DEFAULT NULL,
  `new_status` varchar(30) NOT NULL,
  `loss_reason_id` int(11) DEFAULT NULL,
  `notes` varchar(500) DEFAULT NULL,
  `changed_by` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `lead_id` (`lead_id`),
  KEY `loss_reason_id` (`loss_reason_id`),
  KEY `changed_by` (`changed_by`),
  CONSTRAINT `lead_status_history_ibfk_1` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `lead_status_history_ibfk_2` FOREIGN KEY (`loss_reason_id`) REFERENCES `loss_reasons` (`id`) ON DELETE SET NULL,
  CONSTRAINT `lead_status_history_ibfk_3` FOREIGN KEY (`changed_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `lead_status_history`
--

INSERT INTO `lead_status_history` VALUES
(1,1,'nuevo','contactado',NULL,'Primer contacto',1,'2026-06-17 02:59:56'),
(2,1,'contactado','calificado',NULL,NULL,1,'2026-06-17 02:59:58'),
(3,1,'calificado','ganado',NULL,'Cerrado!',1,'2026-06-17 03:00:00'),
(4,2,'nuevo','propuesta',NULL,NULL,1,'2026-06-17 03:00:02'),
(5,2,'propuesta','negociacion',NULL,NULL,1,'2026-06-17 03:00:04'),
(6,4,'nuevo','contactado',NULL,NULL,1,'2026-06-17 03:01:20'),
(9,23,'nuevo','contactado',NULL,NULL,4,'2026-06-23 04:37:21'),
(10,29,'nuevo','calificado',NULL,NULL,4,'2026-06-24 22:01:15'),
(11,29,'calificado','contactado',NULL,NULL,4,'2026-07-01 21:47:10'),
(12,29,'contactado','propuesta',NULL,NULL,4,'2026-07-01 21:47:11');

--
-- Table structure for table `lead_technical_problems`
--

CREATE TABLE `lead_technical_problems` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lead_id` int(11) NOT NULL,
  `description` text NOT NULL,
  `status` enum('pendiente','resuelto') NOT NULL DEFAULT 'pendiente',
  `resolved_at` timestamp NULL DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `idx_lead_tech_problems_lead_id` (`lead_id`),
  CONSTRAINT `lead_technical_problems_ibfk_1` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `lead_technical_problems_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `lead_technical_problems`
--


--
-- Table structure for table `lead_technical_profiles`
--

CREATE TABLE `lead_technical_profiles` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lead_id` int(11) NOT NULL,
  `animal_type` varchar(100) DEFAULT NULL,
  `herd_size` varchar(100) DEFAULT NULL,
  `current_supplier` varchar(200) DEFAULT NULL,
  `interest_area` varchar(200) DEFAULT NULL,
  `additional_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`additional_data`)),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `lead_id` (`lead_id`),
  CONSTRAINT `lead_technical_profiles_ibfk_1` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `lead_technical_profiles`
--

INSERT INTO `lead_technical_profiles` VALUES
(2,23,'Prueba','100','Prueba','Prueba',NULL,'2026-06-23 03:43:40','2026-06-23 04:44:20'),
(3,28,NULL,NULL,NULL,NULL,NULL,'2026-06-24 17:34:39','2026-06-24 17:34:39'),
(4,29,NULL,NULL,NULL,NULL,NULL,'2026-06-24 20:27:39','2026-06-24 20:27:39'),
(5,30,NULL,NULL,NULL,NULL,NULL,'2026-07-01 20:47:30','2026-07-01 20:47:30');

--
-- Table structure for table `leads`
--

CREATE TABLE `leads` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_id` int(11) DEFAULT NULL,
  `first_name` varchar(150) NOT NULL,
  `last_name` varchar(150) NOT NULL,
  `email` varchar(200) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `company` varchar(200) DEFAULT NULL,
  `position` varchar(200) DEFAULT NULL,
  `city` varchar(150) DEFAULT NULL,
  `region` varchar(150) DEFAULT NULL,
  `ruc` varchar(11) DEFAULT NULL,
  `business_name` varchar(250) DEFAULT NULL,
  `address` varchar(300) DEFAULT NULL,
  `audio_file_path` varchar(500) DEFAULT NULL,
  `photo_file_path` varchar(500) DEFAULT NULL,
  `client_type` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`client_type`)),
  `industry` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`industry`)),
  `classification` varchar(20) DEFAULT NULL,
  `observations` text DEFAULT NULL,
  `source` enum('manual','scan','voice') NOT NULL DEFAULT 'manual',
  `status` enum('nuevo','contactado','calificado','propuesta','negociacion','ganado','perdido') NOT NULL DEFAULT 'nuevo',
  `loss_reason_id` int(11) DEFAULT NULL,
  `is_duplicate` tinyint(1) NOT NULL DEFAULT 0,
  `duplicate_of` int(11) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `updated_by` int(11) DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `loss_reason_id` (`loss_reason_id`),
  KEY `duplicate_of` (`duplicate_of`),
  KEY `updated_by` (`updated_by`),
  KEY `idx_leads_event_id` (`event_id`),
  KEY `idx_leads_status` (`status`),
  KEY `idx_leads_created_by` (`created_by`),
  KEY `idx_leads_email` (`email`),
  KEY `idx_leads_phone` (`phone`),
  KEY `idx_leads_ruc` (`ruc`),
  CONSTRAINT `leads_ibfk_1` FOREIGN KEY (`event_id`) REFERENCES `events` (`id`) ON DELETE CASCADE,
  CONSTRAINT `leads_ibfk_2` FOREIGN KEY (`loss_reason_id`) REFERENCES `loss_reasons` (`id`) ON DELETE SET NULL,
  CONSTRAINT `leads_ibfk_3` FOREIGN KEY (`duplicate_of`) REFERENCES `leads` (`id`) ON DELETE SET NULL,
  CONSTRAINT `leads_ibfk_4` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `leads_ibfk_5` FOREIGN KEY (`updated_by`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `leads`
--

INSERT INTO `leads` VALUES
(1,3,'Carlos Editado','Gomez','carlos@test.com','5550001','AgroMex 2.0','Gerente',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','ganado',NULL,0,NULL,1,1,'2026-06-17 02:59:42','2026-06-17 03:00:00'),
(2,3,'Maria','Lopez','maria@test.com',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','negociacion',NULL,0,NULL,1,1,'2026-06-17 02:59:44','2026-06-17 03:00:04'),
(3,3,'Copia','Gomez','carlos@test.com',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,1,1,1,NULL,'2026-06-17 02:59:45','2026-06-17 02:59:45'),
(4,4,'T','L',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','contactado',NULL,0,NULL,1,1,'2026-06-17 03:01:18','2026-06-17 03:01:20'),
(10,7,'Sin nombre','Por voz',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-18 05:36:54','2026-06-18 05:36:54'),
(11,7,'Sin nombre','Por voz',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-18 05:37:33','2026-06-18 05:37:33'),
(12,7,'Sin nombre','Por voz',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-18 05:42:30','2026-06-18 05:42:30'),
(13,7,'Brad','Delacruz',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-18 05:46:27','2026-06-18 05:46:27'),
(14,7,'Hola','Esto',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-18 05:48:26','2026-06-18 05:48:26'),
(15,7,'Brian','Por voz',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-18 05:48:45','2026-06-18 05:48:45'),
(16,7,'Carlos','De',NULL,NULL,'Agromex',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-18 05:50:45','2026-06-18 05:50:45'),
(17,7,'Sin nombre','Por voz',NULL,'55 50 2320',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-18 05:51:05','2026-06-18 05:51:05'),
(18,7,'Brian','Trabaja',NULL,NULL,'Agromex Mi Teléfono Es Nueve Veinte Uno Tres Cuatro Cuatro Tres Ocho',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-18 05:53:16','2026-06-18 05:53:16'),
(19,7,'Brian','Trabajo',NULL,'940 123 4056','Cruz',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-18 06:00:36','2026-06-18 06:00:36'),
(20,7,'Brian','Trabajador',NULL,'920 123456','Cruz',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-18 06:02:12','2026-06-18 06:02:12'),
(22,7,'Brian','De',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,'manual','nuevo',NULL,0,NULL,4,NULL,'2026-06-19 20:10:08','2026-06-19 20:10:08'),
(23,7,'Bryan','De la cruz','bdelacruz654@gmail.com','+51947394676','Agromex','Gerente','chincha','Ica',NULL,NULL,NULL,NULL,NULL,'[\"Distribuidor\"]','[\"Nutrici\\u00f3n Animal\"]','caliente','12/05 ','manual','contactado',NULL,1,NULL,4,1,'2026-06-23 03:43:40','2026-06-24 20:07:30'),
(28,NULL,'Bryan Angel','De La Cruz Soto','bdelacruz654@gmail.com','+51912538004','Next Boost Perú','','Sunampe','Ica',NULL,NULL,NULL,NULL,NULL,'[\"Distribuidor\",\"Veterinario\"]','[\"Nutrici\\u00f3n Animal\",\"Sanidad Animal\"]','frio','','manual','nuevo',NULL,1,23,1,1,'2026-06-24 17:34:39','2026-06-24 19:29:03'),
(29,7,'Bryan Angel','De La Cruz Soto','bdelacruz654@gmail.com','+51912538004','Agromex',NULL,'Sunampe','Ica',NULL,NULL,NULL,NULL,NULL,'[\"Distribuidor\"]','[\"Nutrici\\u00f3n Animal\"]',NULL,NULL,'manual','propuesta',NULL,1,23,4,4,'2026-06-24 20:27:39','2026-07-01 21:47:11'),
(30,7,'Bryan Angel','De La Cruz Soto','bdelacruz654@gmail.com','+51912538004','ITALVET S.A.C.','Prueba','Prueba','Prueba','20513909382','ITALVET S.A.C.','MZ. H LT. 17 PRO VIV AYACUCHO',NULL,NULL,'[\"Distribuidor\"]','[\"Nutrici\\u00f3n Animal\"]','caliente','Prueba','manual','nuevo',NULL,1,23,4,NULL,'2026-07-01 20:47:30','2026-07-01 20:47:30');

--
-- Table structure for table `loss_reasons`
--

CREATE TABLE `loss_reasons` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `loss_reasons`
--

INSERT INTO `loss_reasons` VALUES
(1,'Precio','El lead considero que el precio era muy alto','active','2026-06-17 03:03:47'),
(2,'Competencia','El lead eligio un competidor','active','2026-06-17 03:03:47'),
(3,'Sin interes','El lead perdio interes','active','2026-06-17 03:03:47'),
(4,'No contactable','No fue posible contactar al lead','active','2026-06-17 03:03:47'),
(5,'Presupuesto','El lead no cuenta con presupuesto','active','2026-06-17 03:03:47'),
(6,'Otro','Otro motivo no especificado','active','2026-06-17 03:03:47'),
(7,'Precio','El lead consideró que el precio era muy alto','active','2026-06-24 15:17:26'),
(8,'Competencia','El lead eligió un competidor','active','2026-06-24 15:17:26'),
(9,'Sin interés','El lead perdió interés en el producto/servicio','active','2026-06-24 15:17:26'),
(10,'No contactable','No fue posible contactar al lead','active','2026-06-24 15:17:26'),
(11,'Presupuesto','El lead no cuenta con presupuesto','active','2026-06-24 15:17:26'),
(12,'Otro','Otro motivo no especificado','active','2026-06-24 15:17:26');

--
-- Table structure for table `material_categories`
--

CREATE TABLE `material_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(150) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `material_categories`
--


--
-- Table structure for table `notes`
--

CREATE TABLE `notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lead_id` int(11) NOT NULL,
  `type` enum('texto','audio') NOT NULL DEFAULT 'texto',
  `content` text DEFAULT NULL,
  `audio_file_path` varchar(500) DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `lead_id` (`lead_id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `notes_ibfk_1` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `notes_ibfk_2` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `notes`
--


--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `type` enum('tarea_proxima','tarea_vencida','nuevo_lead','invitacion','actividad') NOT NULL,
  `title` varchar(200) NOT NULL,
  `message` text DEFAULT NULL,
  `reference_type` varchar(100) DEFAULT NULL,
  `reference_id` int(11) DEFAULT NULL,
  `is_read` tinyint(1) NOT NULL DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_notifications_user` (`user_id`,`is_read`),
  CONSTRAINT `notifications_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` VALUES
(1,4,'tarea_proxima','Nueva tarea: Llamar cliente','Tarea programada para el 2026-07-08 a las 14:00','task',3,0,'2026-07-01 20:51:18'),
(2,4,'tarea_proxima','Nueva tarea: Seguimiento','Tarea programada para el 2026-07-03 a las 10:40','task',4,0,'2026-07-01 21:48:04'),
(3,4,'tarea_proxima','Nueva tarea: Visita técnica','Tarea programada para el 2026-07-09 a las 12:50','task',5,0,'2026-07-01 23:51:00'),
(4,1,'tarea_proxima','Nueva tarea: Llamar cliente','Tarea programada para el 2026-07-08','task',6,0,'2026-07-08 15:06:02');

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `category` varchar(150) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` VALUES
(1,'v2','Upd','Nutricion','active','2026-06-17 03:14:40','2026-06-17 03:14:48');

--
-- Table structure for table `tasks`
--

CREATE TABLE `tasks` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `lead_id` int(11) NOT NULL,
  `type` enum('llamada','reunion','correo','whatsapp','seguimiento','otro') NOT NULL,
  `scheduled_date` date NOT NULL,
  `scheduled_time` time DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `farm_lat` decimal(10,7) DEFAULT NULL,
  `farm_lng` decimal(10,7) DEFAULT NULL,
  `assigned_to` int(11) NOT NULL,
  `status` enum('pendiente','completada','cancelada') NOT NULL DEFAULT 'pendiente',
  `completed_at` timestamp NULL DEFAULT NULL,
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `lead_id` (`lead_id`),
  KEY `created_by` (`created_by`),
  KEY `idx_tasks_assigned_to` (`assigned_to`),
  KEY `idx_tasks_scheduled_date` (`scheduled_date`),
  KEY `idx_tasks_status` (`status`),
  CONSTRAINT `tasks_ibfk_1` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tasks_ibfk_2` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`),
  CONSTRAINT `tasks_ibfk_3` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tasks`
--

INSERT INTO `tasks` VALUES
(1,23,'llamada','2026-06-23','00:00:00','ssssssss',NULL,NULL,4,'completada','2026-06-24 18:23:56',4,'2026-06-23 05:03:03','2026-06-24 20:23:56'),
(2,23,'llamada','2026-06-24','13:02:00','wdsfdsds',NULL,NULL,4,'completada','2026-07-01 18:59:28',4,'2026-06-24 18:02:12','2026-07-01 20:59:28'),
(3,30,'llamada','2026-07-08','14:00:00','Definir interes en espectorantes',NULL,NULL,4,'pendiente',NULL,4,'2026-07-01 20:51:18','2026-07-01 20:51:18'),
(4,29,'seguimiento','2026-07-03','10:40:00','pendiente respuesta cotizacion del 01/07',NULL,NULL,4,'pendiente',NULL,4,'2026-07-01 21:48:04','2026-07-01 21:48:04'),
(5,30,'reunion','2026-07-09','12:50:00','visita ',NULL,NULL,4,'pendiente',NULL,4,'2026-07-01 23:51:00','2026-07-01 23:51:00'),
(6,2,'llamada','2026-07-08',NULL,'',NULL,NULL,1,'pendiente',NULL,1,'2026-07-08 15:06:02','2026-07-08 15:06:02');

--
-- Table structure for table `technical_materials`
--

CREATE TABLE `technical_materials` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) NOT NULL,
  `description` text DEFAULT NULL,
  `category_id` int(11) DEFAULT NULL,
  `type` enum('pdf','video') NOT NULL,
  `file_path` varchar(500) DEFAULT NULL,
  `external_url` varchar(500) DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  KEY `idx_materials_category` (`category_id`),
  CONSTRAINT `technical_materials_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`),
  CONSTRAINT `technical_materials_ibfk_2` FOREIGN KEY (`category_id`) REFERENCES `material_categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `technical_materials`
--

INSERT INTO `technical_materials` VALUES
(3,'Prueba','',NULL,'pdf','/home/italvettools/back.italvettools.com/storage/materials/material_pdf_6a3c29b9eaf35.pdf',NULL,'active',1,'2026-06-24 19:02:17','2026-06-24 19:02:17');

--
-- Table structure for table `whatsapp_templates`
--

CREATE TABLE `whatsapp_templates` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  `content` text NOT NULL,
  `variables` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`variables`)),
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_by` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `created_by` (`created_by`),
  CONSTRAINT `whatsapp_templates_ibfk_1` FOREIGN KEY (`created_by`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `whatsapp_templates`
--

INSERT INTO `whatsapp_templates` VALUES
(1,'Saludo v2','Hola {nombre}!','[\"nombre\"]','active',1,'2026-06-17 04:36:04','2026-06-17 04:36:08');


