--
-- Host: localhost    Database: loudcannabisnear_survey
-- ------------------------------------------------------


--
-- Table structure for table `ai_analytics`
--

CREATE TABLE `ai_analytics` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `total_conversations` int(11) DEFAULT 0,
  `completed_conversations` int(11) DEFAULT 0,
  `abandoned_conversations` int(11) DEFAULT 0,
  `total_offers_presented` int(11) DEFAULT 0,
  `total_conversions` int(11) DEFAULT 0,
  `total_revenue` decimal(10,2) DEFAULT 0.00,
  `avg_conversation_duration` int(11) DEFAULT 0,
  `avg_turns_per_conversation` decimal(5,2) DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_analytics`
--


--
-- Table structure for table `ai_conversation_metadata`
--

CREATE TABLE `ai_conversation_metadata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` int(11) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `title` varchar(255) DEFAULT NULL,
  `tags` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`tags`)),
  `is_favorite` tinyint(1) DEFAULT 0,
  `is_archived` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_conversation_id` (`conversation_id`),
  CONSTRAINT `ai_conversation_metadata_ibfk_1` FOREIGN KEY (`conversation_id`) REFERENCES `ai_conversations` (`id`) ON DELETE CASCADE,
  CONSTRAINT `ai_conversation_metadata_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `ai_user_accounts` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_conversation_metadata`
--


--
-- Table structure for table `ai_conversations`
--

CREATE TABLE `ai_conversations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(255) NOT NULL,
  `conversation_id` varchar(255) DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `user_email` varchar(255) DEFAULT NULL,
  `survey_token` varchar(255) DEFAULT NULL,
  `conversation_data` longtext DEFAULT NULL,
  `current_offer_id` varchar(100) DEFAULT NULL,
  `conversation_state` enum('greeting','qualification','offer_presentation','objection_handling','conversion','completed') DEFAULT 'greeting',
  `turns_count` int(11) DEFAULT 0,
  `started_at` timestamp NULL DEFAULT current_timestamp(),
  `last_activity` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `completed_at` timestamp NULL DEFAULT NULL,
  `ended_at` timestamp NULL DEFAULT NULL,
  `status` enum('active','completed','abandoned') DEFAULT 'active',
  PRIMARY KEY (`id`),
  KEY `session_id` (`session_id`),
  KEY `survey_token` (`survey_token`),
  KEY `user_email` (`user_email`),
  KEY `status` (`status`)
) ENGINE=InnoDB AUTO_INCREMENT=38 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_conversations`
--

INSERT INTO `ai_conversations` VALUES
(1,'24faf92f9c3eb8908746e02ff50ede16','024b08dec0220feb3370eb82027f101f','Test User','test@example.com','test123',NULL,NULL,'greeting',0,'2025-09-14 18:43:48','2025-09-14 18:43:48',NULL,NULL,'active'),
(2,'c6e731fde3d621b889d5a6056f358fb4','b2329a39585a6a2177cc7bea6d3253fc','Backup Test','test@example.com','test123',NULL,NULL,'greeting',0,'2025-09-14 18:55:57','2025-09-14 18:55:57',NULL,NULL,'active'),
(3,'c6be24c78d520d88cd5976d9dc579dd8','7aa586c0e7d297e94397a9bb37449d5f','hazy piff','forexcash420@gmail.com','f7bc7759ff80be55a486ac2a25d4481a1da26bd3d31e9b393400b180e13958fe',NULL,NULL,'greeting',0,'2025-09-14 18:58:48','2025-09-14 18:58:48',NULL,NULL,'active'),
(4,'1bb6a48415be4feb73dc283c9ed75d44','42dc9b23fd37ea337799d418a8097872','Test User','test@example.com','test123',NULL,NULL,'greeting',0,'2025-09-14 19:01:13','2025-09-14 19:01:13',NULL,NULL,'active'),
(5,'65a4c9943f31b19ce743c828533f3a64','d38b351d7c432207b5e54ce10e14d88e','hazy piff','test@example.com','test123',NULL,NULL,'greeting',0,'2025-09-14 19:05:23','2025-09-14 19:05:23',NULL,NULL,'active'),
(6,'25950e2617def4c23d6342083a429e49','a700710e87face1bd96da52ea8f19b4e','Kwende Johnson','firegod420@gmail.com','f19d2b17263412fc0404f633ad12bac2006b00ec5cfeaf58978597e6eb1656fd',NULL,NULL,'greeting',0,'2025-09-14 19:06:49','2025-09-14 19:06:49',NULL,NULL,'active'),
(7,'b9d5d441ce833e1fe923221e6bf857fc','eaf4bbd20fa450a8bbc658830a953e90','Kwende Johnson','siruisman420@gmail.com','a8e868986eefe297afc57f9bbcda6575dd09c17b9a0f979a22a342cb7c6378ff',NULL,NULL,'greeting',0,'2025-09-14 19:09:01','2025-09-14 19:09:01',NULL,NULL,'active'),
(8,'8fccbd50454e613bb75482e962c87865','2b38700316f17e41a621955103ffedaf','Kwende Johnson','seashell.oro@gmail.com','92a80080b85f0022cf6b9feebc6920c74583f4c8e9dadb7bb8646528262a0eab',NULL,NULL,'greeting',0,'2025-09-14 19:26:26','2025-09-14 19:26:26',NULL,NULL,'active'),
(9,'daed81ee767718c987da5c89cc39c488','13907512d030f1e3d594bee1128d5eb1','Test User','test@example.com','test123',NULL,NULL,'greeting',0,'2025-09-14 19:30:00','2025-09-14 19:30:00',NULL,NULL,'active'),
(10,'167167e7a9f4f1ecc1dd2dd0f859bf6c','a8d6056e6f48d05de6e8e88131306fd8','Final Test','test@example.com','final_test',NULL,NULL,'greeting',0,'2025-09-14 19:52:53','2025-09-14 19:52:53',NULL,NULL,'active'),
(11,'8aa9d238eccfeea78a71e005923720da','02edec0d4b4174d98f045af5af0fdceb','Debug Test','debug@example.com','debug_test',NULL,NULL,'greeting',0,'2025-09-14 19:53:01','2025-09-14 19:53:01',NULL,NULL,'active'),
(12,'7de47ad8ad8729dfe9145ff70569ae71','f4b5a1d173c56a9b37ec483da52f15e9','Kwende Johnson','mayor420@gmail.com','6caf35208183d1094ba52da9d3a3ff3d16bc86431523bf84c123e8c6e7f789d9',NULL,NULL,'offer_presentation',0,'2025-09-15 14:04:47','2025-09-15 14:06:36',NULL,NULL,'active'),
(13,'1297db2307165860d43089e9e39d188c','4e83f75f70b2fb8d92cd3475adac1e5d','Kwende Johnson','firegod420@gmail.com','264c22fcf9f2dd7f1fdc0ccab2760e291e3bd182a63de823c9dbcb581c46beb7',NULL,NULL,'greeting',0,'2025-09-15 16:58:31','2025-09-15 16:58:31',NULL,NULL,'active'),
(14,'bef82f97140073c2fa0a6525489d0b3d','e4eede234dfde5affea0ba93db2ff773','Kwende Johnson','firegod420@gmail.com','264c22fcf9f2dd7f1fdc0ccab2760e291e3bd182a63de823c9dbcb581c46beb7',NULL,NULL,'greeting',0,'2025-09-15 17:03:20','2025-09-15 17:03:20',NULL,NULL,'active'),
(15,'2dfaa2d5943e651c74a42110a9aa2619','ae7588bee2194e617448ef9359319c2f','Kwende Johnson','forexcash420@gmail.com','be7ad17efad30670b300c0ec809b1d99bc44833fd80cdc88426974fd6597e2bc',NULL,NULL,'greeting',0,'2025-09-15 17:05:12','2025-09-15 17:05:12',NULL,NULL,'active'),
(16,'b38435cf6787817d88e2ce518f1eedc7','f1158ad571c9fcb3d3f78d79c1a89451','Kwende Johnson','forexcash420@gmail.com','be7ad17efad30670b300c0ec809b1d99bc44833fd80cdc88426974fd6597e2bc',NULL,NULL,'greeting',0,'2025-09-15 17:05:23','2025-09-15 17:05:23',NULL,NULL,'active'),
(17,'700a343e6cd2a748cf6b0544ae148942','f7702668d32441abc7c6ebd6c647244b','Kwende Johnson','forexcash420@gmail.com','be7ad17efad30670b300c0ec809b1d99bc44833fd80cdc88426974fd6597e2bc',NULL,NULL,'greeting',0,'2025-09-15 17:05:26','2025-09-15 17:05:26',NULL,NULL,'active'),
(18,'d48d4afdaecc52bf6e2032bb802050de','76a4509f0706c5aab08a6c75e6080e5f','Kwende Johnson','forexcash420@gmail.com','be7ad17efad30670b300c0ec809b1d99bc44833fd80cdc88426974fd6597e2bc',NULL,NULL,'greeting',0,'2025-09-15 17:07:53','2025-09-15 17:07:53',NULL,NULL,'active'),
(19,'ai_68c850a975b3c3.45892609',NULL,'Test User','test@example.com','test_token','{\"survey_responses\":{\"survey_data\":[],\"user_name\":\"\",\"user_email\":\"\"},\"user_profile\":{\"name\":\"Test User\",\"email\":\"test@example.com\"},\"selected_offers\":[]}',NULL,'greeting',2,'2025-09-15 17:45:13','2025-09-15 17:45:13',NULL,NULL,'active'),
(20,'ai_68c851b4c45699.64483067',NULL,'Test User','test@example.com','test_token','{\"survey_responses\":{\"survey_data\":[],\"user_name\":\"\",\"user_email\":\"\"},\"user_profile\":{\"name\":\"Test User\",\"email\":\"test@example.com\"},\"selected_offers\":[]}',NULL,'greeting',2,'2025-09-15 17:49:40','2025-09-15 17:49:40',NULL,NULL,'active'),
(21,'ai_68c8525b8384f6.61667709',NULL,'Test User','test@example.com','test_token','{\"survey_responses\":{\"survey_data\":[],\"user_name\":\"\",\"user_email\":\"\"},\"user_profile\":{\"name\":\"Test User\",\"email\":\"test@example.com\"},\"selected_offers\":[]}',NULL,'greeting',2,'2025-09-15 17:52:27','2025-09-15 17:52:27',NULL,NULL,'active'),
(22,'ai_68c85288c37178.31511814',NULL,'Test User','test@example.com','test_token','{\"survey_responses\":{\"survey_data\":[],\"user_name\":\"\",\"user_email\":\"\"},\"user_profile\":{\"name\":\"Test User\",\"email\":\"test@example.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-15 17:53:12','2025-09-15 17:53:14',NULL,NULL,'active'),
(23,'ai_68c852c5c4c222.57118007',NULL,'Test User','test@example.com','test_token','{\"survey_responses\":{\"survey_data\":[],\"user_name\":\"\",\"user_email\":\"\"},\"user_profile\":{\"name\":\"Test User\",\"email\":\"test@example.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-15 17:54:13','2025-09-15 17:54:16',NULL,NULL,'active'),
(24,'ai_68c852f44dbff1.57317483',NULL,'Production Test','test@deepleafai.com','prod_test','{\"survey_responses\":{\"survey_data\":[],\"user_name\":\"\",\"user_email\":\"\"},\"user_profile\":{\"name\":\"Production Test\",\"email\":\"test@deepleafai.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-15 17:55:00','2025-09-15 17:55:05',NULL,NULL,'active'),
(25,'ai_68c9ea4c94b0c0.09249591',NULL,'Hazy piff','mayor420@gmail.com','3e0b53b37f923dec36da66ae7882eb1b74a3e00b6baeb99f3bde5fb70eae2252','{\"survey_responses\":{\"survey_data\":{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Week\",\"\'03\'\":\"No\",\"\'04\'\":\"Struggling with debt\",\"\'05\'\":\"No, not interested\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Improve my health\\/fitness\"},\"user_name\":\"Hazy piff\",\"user_email\":\"mayor420@gmail.com\"},\"user_profile\":{\"name\":\"Hazy piff\",\"email\":\"mayor420@gmail.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-16 22:53:00','2025-09-16 22:53:06',NULL,NULL,'active'),
(26,'ai_68c9ea52c1dd86.07617309',NULL,'Hazy piff','mayor420@gmail.com','3e0b53b37f923dec36da66ae7882eb1b74a3e00b6baeb99f3bde5fb70eae2252','{\"survey_responses\":{\"survey_data\":{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Week\",\"\'03\'\":\"No\",\"\'04\'\":\"Struggling with debt\",\"\'05\'\":\"No, not interested\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Improve my health\\/fitness\"},\"user_name\":\"Hazy piff\",\"user_email\":\"mayor420@gmail.com\"},\"user_profile\":{\"name\":\"Hazy piff\",\"email\":\"mayor420@gmail.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-16 22:53:06','2025-09-16 22:53:10',NULL,NULL,'active'),
(27,'ai_68c9f3ad63f771.74131015',NULL,'Kwende Johnson','firegod420@gmail.com','72d9b70a1b6001d5277fa45478d8796694e19a3771898b67a72cfcbeb753f580','{\"survey_responses\":{\"survey_data\":{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Improve my health\\/fitness\"},\"user_name\":\"Kwende Johnson\",\"user_email\":\"firegod420@gmail.com\"},\"user_profile\":{\"name\":\"Kwende Johnson\",\"email\":\"firegod420@gmail.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-16 23:33:01','2025-09-16 23:33:05',NULL,NULL,'active'),
(28,'ai_68c9f4d96f2e35.27392343',NULL,'Kwende Johnson','firegod420@gmail.com','72d9b70a1b6001d5277fa45478d8796694e19a3771898b67a72cfcbeb753f580','{\"survey_responses\":{\"survey_data\":{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Improve my health\\/fitness\"},\"user_name\":\"Kwende Johnson\",\"user_email\":\"firegod420@gmail.com\"},\"user_profile\":{\"name\":\"Kwende Johnson\",\"email\":\"firegod420@gmail.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-16 23:38:01','2025-09-16 23:38:04',NULL,NULL,'active'),
(29,'ai_68c9f59412a853.14961255',NULL,'Kwende Johnson','firegod420@gmail.com','72d9b70a1b6001d5277fa45478d8796694e19a3771898b67a72cfcbeb753f580','{\"survey_responses\":{\"survey_data\":{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Improve my health\\/fitness\"},\"user_name\":\"Kwende Johnson\",\"user_email\":\"firegod420@gmail.com\"},\"user_profile\":{\"name\":\"Kwende Johnson\",\"email\":\"firegod420@gmail.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-16 23:41:08','2025-09-16 23:41:09',NULL,NULL,'active'),
(30,'ai_68c9f5bb413fe8.93939607',NULL,'Kwende Johnson','firegod420@gmail.com','72d9b70a1b6001d5277fa45478d8796694e19a3771898b67a72cfcbeb753f580','{\"survey_responses\":{\"survey_data\":{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Improve my health\\/fitness\"},\"user_name\":\"Kwende Johnson\",\"user_email\":\"firegod420@gmail.com\"},\"user_profile\":{\"name\":\"Kwende Johnson\",\"email\":\"firegod420@gmail.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-16 23:41:47','2025-09-16 23:41:50',NULL,NULL,'active'),
(31,'ai_68c9f5be79ab42.46802982',NULL,'Kwende Johnson','firegod420@gmail.com','72d9b70a1b6001d5277fa45478d8796694e19a3771898b67a72cfcbeb753f580','{\"survey_responses\":{\"survey_data\":{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Improve my health\\/fitness\"},\"user_name\":\"Kwende Johnson\",\"user_email\":\"firegod420@gmail.com\"},\"user_profile\":{\"name\":\"Kwende Johnson\",\"email\":\"firegod420@gmail.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-16 23:41:50','2025-09-16 23:41:55',NULL,NULL,'active'),
(32,'ai_68c9f7b630cea3.88559050',NULL,'Kwende Johnson','forexcash420@gmail.com','88f30b9aac9606258505cf2ba88557997040d6a96ce1c613fe5da150c4b3387d','{\"survey_responses\":{\"survey_data\":{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Make more money\"},\"user_name\":\"Kwende Johnson\",\"user_email\":\"forexcash420@gmail.com\"},\"user_profile\":{\"name\":\"Kwende Johnson\",\"email\":\"forexcash420@gmail.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-16 23:50:14','2025-09-16 23:50:18',NULL,NULL,'active'),
(33,'ai_68c9fa1cdedff7.86066289',NULL,'Kwende Johnson','forexcash420@gmail.com','88f30b9aac9606258505cf2ba88557997040d6a96ce1c613fe5da150c4b3387d','{\"survey_responses\":{\"survey_data\":{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Make more money\"},\"user_name\":\"Kwende Johnson\",\"user_email\":\"forexcash420@gmail.com\"},\"user_profile\":{\"name\":\"Kwende Johnson\",\"email\":\"forexcash420@gmail.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-17 00:00:28','2025-09-17 00:00:34',NULL,NULL,'active'),
(34,'ai_68c9fa6084e284.70521842',NULL,'Kwende Johnson','forexcash420@gmail.com','88f30b9aac9606258505cf2ba88557997040d6a96ce1c613fe5da150c4b3387d','{\"survey_responses\":{\"survey_data\":{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Make more money\"},\"user_name\":\"Kwende Johnson\",\"user_email\":\"forexcash420@gmail.com\"},\"user_profile\":{\"name\":\"Kwende Johnson\",\"email\":\"forexcash420@gmail.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-17 00:01:36','2025-09-17 00:01:42',NULL,NULL,'active'),
(35,'ai_68c9fcb121bcb0.43713223',NULL,'Kwende Johnson','forexcash420@gmail.com','88f30b9aac9606258505cf2ba88557997040d6a96ce1c613fe5da150c4b3387d','{\"survey_responses\":{\"survey_data\":{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Make more money\"},\"user_name\":\"Kwende Johnson\",\"user_email\":\"forexcash420@gmail.com\"},\"user_profile\":{\"name\":\"Kwende Johnson\",\"email\":\"forexcash420@gmail.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-17 00:11:29','2025-09-17 00:11:35',NULL,NULL,'active'),
(36,'ai_68ca0088d8c6b5.74524905',NULL,'Test','test@example.com','test123','{\"survey_responses\":{\"survey_data\":[],\"user_name\":\"\",\"user_email\":\"\"},\"user_profile\":{\"name\":\"Test\",\"email\":\"test@example.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-17 00:27:52','2025-09-17 00:27:56',NULL,NULL,'active'),
(37,'ai_68ca009b97ab21.77490663',NULL,'Test','test@example.com','test123','{\"survey_responses\":{\"survey_data\":[],\"user_name\":\"\",\"user_email\":\"\"},\"user_profile\":{\"name\":\"Test\",\"email\":\"test@example.com\"},\"selected_offers\":[]}',NULL,'greeting',4,'2025-09-17 00:28:11','2025-09-17 00:28:14',NULL,NULL,'active');

--
-- Table structure for table `ai_messages`
--

CREATE TABLE `ai_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` int(11) NOT NULL,
  `message_type` enum('user','ai') NOT NULL,
  `message_content` text NOT NULL,
  `message_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`message_data`)),
  `timestamp` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `conversation_id` (`conversation_id`),
  CONSTRAINT `ai_messages_ibfk_1` FOREIGN KEY (`conversation_id`) REFERENCES `ai_conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=71 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_messages`
--

INSERT INTO `ai_messages` VALUES
(1,19,'ai','Yo Test User, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.',NULL,'2025-09-15 17:45:13'),
(2,19,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-15 17:45:13'),
(3,20,'ai','Listen Test User, I broke down your survey and I see exactly what you need. No bullshit, just the real deals that match your vibe.',NULL,'2025-09-15 17:49:40'),
(4,20,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-15 17:49:40'),
(5,21,'ai','Alright Test User, I went through your answers and I know what you\'re after. Let me cut the crap and show you some opportunities that actually work.',NULL,'2025-09-15 17:52:27'),
(6,21,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-15 17:52:27'),
(7,22,'ai','Yo Test User, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.',NULL,'2025-09-15 17:53:12'),
(8,22,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-15 17:53:12'),
(9,22,'ai','I encountered an issue: Tool execution failed: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation \'=\'','{\"enhanced\": true, \"tool_results\": [{\"error\": \"Tool execution failed: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation \'=\'\"}]}','2025-09-15 17:53:14'),
(10,22,'ai','Yo Test User, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.\n\nI encountered an issue: Tool execution failed: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation \'=\'','{\"enhanced\": true, \"tool_results\": [{\"error\": \"Tool execution failed: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation \'=\'\"}]}','2025-09-15 17:53:14'),
(11,23,'ai','Listen Test User, I broke down your survey and I see exactly what you need. No bullshit, just the real deals that match your vibe.',NULL,'2025-09-15 17:54:13'),
(12,23,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-15 17:54:13'),
(13,23,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-15 17:54:16\", \"custom_message\": \"Hey there! Based on your interest in the cannabis industry, I have some fantastic opportunities for you. First, there\'s an offer for Cannabis Business Financing. This can provide specialized financing to help grow your cannabis business.\"}]}','2025-09-15 17:54:16'),
(14,23,'ai','Listen Test User, I broke down your survey and I see exactly what you need. No bullshit, just the real deals that match your vibe.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-15 17:54:16\", \"custom_message\": \"Hey there! Based on your interest in the cannabis industry, I have some fantastic opportunities for you. First, there\'s an offer for Cannabis Business Financing. This can provide specialized financing to help grow your cannabis business.\"}]}','2025-09-15 17:54:16'),
(15,24,'ai','Listen Production Test, I broke down your survey and I see exactly what you need. No bullshit, just the real deals that match your vibe.',NULL,'2025-09-15 17:55:00'),
(16,24,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-15 17:55:00'),
(17,24,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-15 17:55:05\", \"custom_message\": \"Hey there! I have some amazing offers for you. First up, we have Cannabis Business Financing. This is a specialized financing opportunity designed specifically for entrepreneurs in cannabis industry. It could be just what you need to get your venture off the ground or to take it to the next level.\"}]}','2025-09-15 17:55:05'),
(18,24,'ai','Listen Production Test, I broke down your survey and I see exactly what you need. No bullshit, just the real deals that match your vibe.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-15 17:55:05\", \"custom_message\": \"Hey there! I have some amazing offers for you. First up, we have Cannabis Business Financing. This is a specialized financing opportunity designed specifically for entrepreneurs in cannabis industry. It could be just what you need to get your venture off the ground or to take it to the next level.\"}]}','2025-09-15 17:55:05'),
(19,25,'ai','Alright Hazy piff, I went through your answers and I know what you\'re after. Let me cut the crap and show you some opportunities that actually work.',NULL,'2025-09-16 22:53:00'),
(20,25,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-16 22:53:00'),
(21,25,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 22:53:06\", \"custom_message\": \"Hello Hazy piff, based on your interest in learning more about finance and your connection with recreational use of cannabis, I have some tailored offers for you. The first one is \'Cannabis Business Financing\'. This is a specialized form-lead offer that provides financing solutions specifically for cannabis businesses. It could be a great opportunity to explore.\"}]}','2025-09-16 22:53:06'),
(22,25,'ai','Alright Hazy piff, I went through your answers and I know what you\'re after. Let me cut the crap and show you some opportunities that actually work.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 22:53:06\", \"custom_message\": \"Hello Hazy piff, based on your interest in learning more about finance and your connection with recreational use of cannabis, I have some tailored offers for you. The first one is \'Cannabis Business Financing\'. This is a specialized form-lead offer that provides financing solutions specifically for cannabis businesses. It could be a great opportunity to explore.\"}]}','2025-09-16 22:53:06'),
(23,26,'ai','Alright Hazy piff, I went through your answers and I know what you\'re after. Let me cut the crap and show you some opportunities that actually work.',NULL,'2025-09-16 22:53:06'),
(24,26,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-16 22:53:06'),
(25,26,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 22:53:10\", \"custom_message\": \"Hello Hazy piff! Based on your survey responses, I have some offers that may interest you. First is the Cannabis Business Financing. It provides specialized financing for cannabis businesses that can help in dealing with financial struggles.\"}]}','2025-09-16 22:53:10'),
(26,26,'ai','Alright Hazy piff, I went through your answers and I know what you\'re after. Let me cut the crap and show you some opportunities that actually work.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 22:53:10\", \"custom_message\": \"Hello Hazy piff! Based on your survey responses, I have some offers that may interest you. First is the Cannabis Business Financing. It provides specialized financing for cannabis businesses that can help in dealing with financial struggles.\"}]}','2025-09-16 22:53:10'),
(27,27,'ai','Yo Kwende Johnson, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.',NULL,'2025-09-16 23:33:01'),
(28,27,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-16 23:33:01'),
(29,27,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:33:05\", \"custom_message\": \"Hey there, Kwende Johnson! Based on your survey responses, I\'ve got some exciting opportunities for you. First up, we have Cannabis Business Financing. This is a great opportunity if you\'re looking to expand your ventures in the cannabis industry. It provides specialized financing for cannabis businesses, to help you get your business booming.\"}]}','2025-09-16 23:33:05'),
(30,27,'ai','Yo Kwende Johnson, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:33:05\", \"custom_message\": \"Hey there, Kwende Johnson! Based on your survey responses, I\'ve got some exciting opportunities for you. First up, we have Cannabis Business Financing. This is a great opportunity if you\'re looking to expand your ventures in the cannabis industry. It provides specialized financing for cannabis businesses, to help you get your business booming.\"}]}','2025-09-16 23:33:05'),
(31,28,'ai','Alright Kwende Johnson, I went through your answers and I know what you\'re after. Let me cut the crap and show you some opportunities that actually work.',NULL,'2025-09-16 23:38:01'),
(32,28,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-16 23:38:01'),
(33,28,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:38:04\", \"custom_message\": \"Hey Kwende Johnson, based on your interests in cannabis and recreational use, one great opportunity I found for you is the Cannabis Business Financing. It provides specialized financing for cannabis businesses which might be something you\'d want to explore. \"}]}','2025-09-16 23:38:04'),
(34,28,'ai','Alright Kwende Johnson, I went through your answers and I know what you\'re after. Let me cut the crap and show you some opportunities that actually work.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:38:04\", \"custom_message\": \"Hey Kwende Johnson, based on your interests in cannabis and recreational use, one great opportunity I found for you is the Cannabis Business Financing. It provides specialized financing for cannabis businesses which might be something you\'d want to explore. \"}]}','2025-09-16 23:38:04'),
(35,29,'ai','Listen Kwende Johnson, I broke down your survey and I see exactly what you need. No bullshit, just the real deals that match your vibe.',NULL,'2025-09-16 23:41:08'),
(36,29,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-16 23:41:08'),
(37,29,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:41:09\", \"custom_message\": \"\"}]}','2025-09-16 23:41:09'),
(38,29,'ai','Listen Kwende Johnson, I broke down your survey and I see exactly what you need. No bullshit, just the real deals that match your vibe.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:41:09\", \"custom_message\": \"\"}]}','2025-09-16 23:41:09'),
(39,30,'ai','Yo Kwende Johnson, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.',NULL,'2025-09-16 23:41:47'),
(40,30,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-16 23:41:47'),
(41,30,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:41:50\", \"custom_message\": \"\"}]}','2025-09-16 23:41:50'),
(42,30,'ai','Yo Kwende Johnson, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:41:50\", \"custom_message\": \"\"}]}','2025-09-16 23:41:50'),
(43,31,'ai','Yo Kwende Johnson, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.',NULL,'2025-09-16 23:41:50'),
(44,31,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-16 23:41:50'),
(45,31,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:41:55\", \"custom_message\": \"Hey Kwende, I\'ve got some fantastic offers for you. First up, we have Cannabis Business Financing. This could be a game-changer if you\'re considering starting or expanding your own cannabis business. It\'s a form lead opportunity that could help you secure the necessary funds to make your dreams come true.\"}]}','2025-09-16 23:41:55'),
(46,31,'ai','Yo Kwende Johnson, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:41:55\", \"custom_message\": \"Hey Kwende, I\'ve got some fantastic offers for you. First up, we have Cannabis Business Financing. This could be a game-changer if you\'re considering starting or expanding your own cannabis business. It\'s a form lead opportunity that could help you secure the necessary funds to make your dreams come true.\"}]}','2025-09-16 23:41:55'),
(47,32,'ai','Yo Kwende Johnson, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.',NULL,'2025-09-16 23:50:14'),
(48,32,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-16 23:50:14'),
(49,32,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:50:18\", \"custom_message\": \"Hey Kwende Johnson, I\'ve got some interesting opportunities for you! First up, we have Cannabis Business Financing. This is a specialized financing program tailored for cannabis businesses. It might be just what you need to grow your business to new heights.\"}]}','2025-09-16 23:50:18'),
(50,32,'ai','Yo Kwende Johnson, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-16 23:50:18\", \"custom_message\": \"Hey Kwende Johnson, I\'ve got some interesting opportunities for you! First up, we have Cannabis Business Financing. This is a specialized financing program tailored for cannabis businesses. It might be just what you need to grow your business to new heights.\"}]}','2025-09-16 23:50:18'),
(51,33,'ai','Alright Kwende Johnson, I went through your answers and I know what you\'re after. Let me cut the crap and show you some opportunities that actually work.',NULL,'2025-09-17 00:00:28'),
(52,33,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-17 00:00:28'),
(53,33,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-17 00:00:34\", \"custom_message\": \"Kwende, considering your interest in making more money, I think you might find our Cannabis Business Financing offer interesting. It provides specialized financing for cannabis businesses, which might help you expand your recreational activities into a profitable venture.\"}]}','2025-09-17 00:00:34'),
(54,33,'ai','Alright Kwende Johnson, I went through your answers and I know what you\'re after. Let me cut the crap and show you some opportunities that actually work.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-17 00:00:34\", \"custom_message\": \"Kwende, considering your interest in making more money, I think you might find our Cannabis Business Financing offer interesting. It provides specialized financing for cannabis businesses, which might help you expand your recreational activities into a profitable venture.\"}]}','2025-09-17 00:00:34'),
(55,34,'ai','Yo Kwende Johnson, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.',NULL,'2025-09-17 00:01:36'),
(56,34,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-17 00:01:36'),
(57,34,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-17 00:01:42\", \"custom_message\": \"Hey, Kwende Johnson! I noticed from your survey that you\'re very interested in the cannabis industry. I have an offer that might be perfect for you. It\'s called \'Cannabis Business Financing\'. It provides specialized financing specifically for cannabis businesses, helping you to secure the funds you need to grow. What do you think about that?\"}]}','2025-09-17 00:01:42'),
(58,34,'ai','Yo Kwende Johnson, I peeped your survey answers. You lookin\' for some real cannabis opportunities that actually fit your situation. I got the hookup.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-17 00:01:42\", \"custom_message\": \"Hey, Kwende Johnson! I noticed from your survey that you\'re very interested in the cannabis industry. I have an offer that might be perfect for you. It\'s called \'Cannabis Business Financing\'. It provides specialized financing specifically for cannabis businesses, helping you to secure the funds you need to grow. What do you think about that?\"}]}','2025-09-17 00:01:42'),
(59,35,'ai','Listen Kwende Johnson, I broke down your survey and I see exactly what you need. No bullshit, just the real deals that match your vibe.',NULL,'2025-09-17 00:11:29'),
(60,35,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-17 00:11:29'),
(61,35,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-17 00:11:35\", \"custom_message\": \"Hey there Kwende Johnson! Based on your interest in making more money and your curiosity in certain areas, I\'ve got a couple of great opportunities for you. First up, we have an offer for Cannabis Business Financing. This could be a unique avenue for you to explore, especially considering your recreational interest and potential for business expansion. What do you think?\"}]}','2025-09-17 00:11:35'),
(62,35,'ai','Listen Kwende Johnson, I broke down your survey and I see exactly what you need. No bullshit, just the real deals that match your vibe.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-17 00:11:35\", \"custom_message\": \"Hey there Kwende Johnson! Based on your interest in making more money and your curiosity in certain areas, I\'ve got a couple of great opportunities for you. First up, we have an offer for Cannabis Business Financing. This could be a unique avenue for you to explore, especially considering your recreational interest and potential for business expansion. What do you think?\"}]}','2025-09-17 00:11:35'),
(63,36,'ai','Listen Test, I broke down your survey and I see exactly what you need. No bullshit, just the real deals that match your vibe.',NULL,'2025-09-17 00:27:52'),
(64,36,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-17 00:27:52'),
(65,36,'ai','Based on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-17 00:27:56\", \"custom_message\": \"Hey there! Based on your survey responses, I\'ve got some pre-selected offers that might interest you. First up is the Cannabis Business Financing. This could provide you with specialized financing that\'s tailored to your needs in the cannabis business.\"}]}','2025-09-17 00:27:56'),
(66,36,'ai','Listen Test, I broke down your survey and I see exactly what you need. No bullshit, just the real deals that match your vibe.\n\nBased on your needs, I recommend: **Cannabis Business Financing**\n\nSpecialized financing for cannabis businesses\n\n💰 Value: $45\n📞 1-844-CANNABIS-FUND\n\nWould you like to learn more about this opportunity?','{\"enhanced\": true, \"tool_results\": [{\"offer\": {\"id\": \"Cannabis_Financing\", \"url\": \"https://example.com/financing\", \"phone\": \"1-844-CANNABIS-FUND\", \"title\": \"Cannabis Business Financing\", \"value\": 45, \"category\": \"finance\", \"is_active\": 1, \"offer_key\": \"Cannabis_Financing\", \"description\": \"Specialized financing for cannabis businesses\", \"requirements\": [\"business_owner\"], \"payout_amount\": 45, \"presentation_template\": \"Starting or growing a cannabis business? Our specialized financing options can help you secure the capital you need to succeed in this growing industry.\"}, \"presented_at\": \"2025-09-17 00:27:56\", \"custom_message\": \"Hey there! Based on your survey responses, I\'ve got some pre-selected offers that might interest you. First up is the Cannabis Business Financing. This could provide you with specialized financing that\'s tailored to your needs in the cannabis business.\"}]}','2025-09-17 00:27:56'),
(67,37,'ai','Alright Test, I went through your answers and I know what you\'re after. Let me cut the crap and show you some opportunities that actually work.',NULL,'2025-09-17 00:28:11'),
(68,37,'user','Present the pre-selected offers based on my survey.',NULL,'2025-09-17 00:28:11'),
(69,37,'ai','I encountered an issue: Tool execution failed: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation \'=\'','{\"enhanced\": true, \"tool_results\": [{\"error\": \"Tool execution failed: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation \'=\'\"}]}','2025-09-17 00:28:14'),
(70,37,'ai','Alright Test, I went through your answers and I know what you\'re after. Let me cut the crap and show you some opportunities that actually work.\n\nI encountered an issue: Tool execution failed: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation \'=\'','{\"enhanced\": true, \"tool_results\": [{\"error\": \"Tool execution failed: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8mb4_0900_ai_ci,IMPLICIT) for operation \'=\'\"}]}','2025-09-17 00:28:14');

--
-- Table structure for table `ai_offer_conversions`
--

CREATE TABLE `ai_offer_conversions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` int(11) NOT NULL,
  `offer_id` varchar(100) NOT NULL,
  `conversion_type` enum('pay_per_call','form_lead') NOT NULL,
  `conversion_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`conversion_data`)),
  `conversion_value` decimal(10,2) DEFAULT 0.00,
  `conversion_status` enum('pending','qualified','paid','rejected') DEFAULT 'pending',
  `converted_at` timestamp NULL DEFAULT current_timestamp(),
  `paid_at` timestamp NULL DEFAULT NULL,
  `payment_amount` decimal(10,2) DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `conversation_id` (`conversation_id`),
  KEY `offer_id` (`offer_id`),
  KEY `conversion_status` (`conversion_status`),
  CONSTRAINT `ai_offer_conversions_ibfk_1` FOREIGN KEY (`conversation_id`) REFERENCES `ai_conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_offer_conversions`
--


--
-- Table structure for table `ai_offer_performance`
--

CREATE TABLE `ai_offer_performance` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `offer_id` varchar(100) NOT NULL,
  `total_presentations` int(11) DEFAULT 0,
  `total_conversions` int(11) DEFAULT 0,
  `conversion_rate` decimal(5,2) DEFAULT 0.00,
  `total_revenue` decimal(10,2) DEFAULT 0.00,
  `avg_conversation_turns` decimal(5,2) DEFAULT 0.00,
  `last_updated` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `offer_id` (`offer_id`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_offer_performance`
--

INSERT INTO `ai_offer_performance` VALUES
(1,'cannabis_medical_card',0,0,0.00,0.00,0.00,'2025-09-14 19:41:06'),
(2,'cannabis_delivery_service',0,0,0.00,0.00,0.00,'2025-09-14 19:41:06'),
(3,'cannabis_events_tickets',0,0,0.00,0.00,0.00,'2025-09-14 19:41:06'),
(4,'cannabis_consultation',0,0,0.00,0.00,0.00,'2025-09-14 19:41:06'),
(5,'cannabis_dispensary_deals',0,0,0.00,0.00,0.00,'2025-09-14 19:41:06'),
(6,'cannabis_education_course',0,0,0.00,0.00,0.00,'2025-09-14 19:41:06');

--
-- Table structure for table `ai_offer_presentations`
--

CREATE TABLE `ai_offer_presentations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` int(11) NOT NULL,
  `offer_id` varchar(100) NOT NULL,
  `presentation_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`presentation_data`)),
  `presented_at` timestamp NULL DEFAULT current_timestamp(),
  `user_response` text DEFAULT NULL,
  `response_sentiment` enum('positive','neutral','negative') DEFAULT NULL,
  `conversion_attempted` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  KEY `conversation_id` (`conversation_id`),
  KEY `offer_id` (`offer_id`),
  CONSTRAINT `ai_offer_presentations_ibfk_1` FOREIGN KEY (`conversation_id`) REFERENCES `ai_conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_offer_presentations`
--

INSERT INTO `ai_offer_presentations` VALUES
(1,1,'Cannabis_Financing','{\"presented_at\":\"2025-09-15 18:05:35\"}','2025-09-15 18:05:35',NULL,NULL,0),
(2,3,'Cannabis_Financing','{\"presented_at\":\"2025-09-15 18:05:52\"}','2025-09-15 18:05:52',NULL,NULL,0),
(3,4,'Cannabis_Financing','{\"presented_at\":\"2025-09-15 18:05:54\"}','2025-09-15 18:05:54',NULL,NULL,0);

--
-- Table structure for table `ai_system_logs`
--

CREATE TABLE `ai_system_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `level` enum('DEBUG','INFO','WARNING','ERROR','CRITICAL') NOT NULL DEFAULT 'INFO',
  `message` text NOT NULL,
  `context` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`context`)),
  `session_id` varchar(255) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_level` (`level`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=113 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_system_logs`
--

INSERT INTO `ai_system_logs` VALUES
(1,'INFO','AIEngine initialized','{\"session_id\":\"ai_68c84e8f941e34.37572407\",\"persona\":\"street_budtender\"}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(2,'INFO','Memory cleared','{\"category\":null}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(3,'INFO','Added to memory','{\"key\":\"budget\",\"category\":\"preferences\",\"value\":500}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(4,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"preferences\",\"value\":\"california\"}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(5,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":25}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(6,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":16}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(7,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california\"}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(8,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"texas\"}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(9,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":16}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(10,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california\"}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(11,'INFO','Executing tool','{\"tool\":\"get_user_context\",\"parameters\":{\"include_survey\":false}}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(12,'ERROR','Tool execution failed','{\"tool\":\"get_user_context\",\"error\":\"SQLSTATE[42S22]: Column not found: 1054 Unknown column \'created_at\' in \'SELECT\'\"}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(13,'INFO','Executing tool','{\"tool\":\"invalid_tool\",\"parameters\":[]}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(14,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":16}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(15,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california\"}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(16,'WARNING','User not compliant','{\"age_compliant\":false,\"geo_compliant\":true}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(17,'INFO','Filtered offers by compliance','{\"original_count\":20,\"filtered_count\":0,\"restrictions\":[]}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(18,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":16}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(19,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california\"}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(20,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"preferences\",\"value\":\"california\"}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(21,'INFO','Extracted slots from message','{\"location\":\"california\"}','ai_68c84e8f941e34.37572407',NULL,NULL,NULL,'2025-09-15 17:36:15'),
(22,'INFO','AIEngine initialized','{\"session_id\":\"ai_68c84eb4bd6be6.69626139\",\"persona\":\"street_budtender\"}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(23,'INFO','Memory cleared','{\"category\":null}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(24,'INFO','Added to memory','{\"key\":\"budget\",\"category\":\"preferences\",\"value\":500}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(25,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"preferences\",\"value\":\"california\"}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(26,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":25}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(27,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":16}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(28,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california\"}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(29,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"texas\"}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(30,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":16}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(31,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california\"}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(32,'INFO','Executing tool','{\"tool\":\"get_user_context\",\"parameters\":{\"include_survey\":false}}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(33,'ERROR','Tool execution failed','{\"tool\":\"get_user_context\",\"error\":\"SQLSTATE[42S22]: Column not found: 1054 Unknown column \'created_at\' in \'SELECT\'\"}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(34,'INFO','Executing tool','{\"tool\":\"invalid_tool\",\"parameters\":[]}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(35,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":16}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(36,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california\"}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(37,'WARNING','User not compliant','{\"age_compliant\":false,\"geo_compliant\":true}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(38,'INFO','Filtered offers by compliance','{\"original_count\":20,\"filtered_count\":0,\"restrictions\":[]}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(39,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":16}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(40,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california\"}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(41,'INFO','Added to memory','{\"key\":\"budget\",\"category\":\"preferences\",\"value\":500}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(42,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"preferences\",\"value\":\"california\"}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(43,'INFO','Extracted slots from message','{\"budget\":500,\"location\":\"california\"}','ai_68c84eb4bd6be6.69626139',NULL,NULL,NULL,'2025-09-15 17:36:52'),
(44,'INFO','AIEngine initialized','{\"session_id\":\"ai_68c8556aa83050.17114284\",\"persona\":\"street_budtender\"}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:30'),
(45,'INFO','Pre-selected offers','{\"offers\":[],\"survey_data\":[]}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:30'),
(46,'INFO','Processing tool call','{\"tool\":\"present_offer\",\"args\":{\"offer_id\":\"Cannabis_Financing\",\"custom_message\":\"John Doe, based on your interest in cannabis, you might want to consider Cannabis Business Financing. This can provide you with the necessary funds to start or expand your venture in this budding industry.\"}}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:35'),
(47,'INFO','Executing tool','{\"tool\":\"present_offer\",\"parameters\":{\"offer_id\":\"Cannabis_Financing\",\"custom_message\":\"John Doe, based on your interest in cannabis, you might want to consider Cannabis Business Financing. This can provide you with the necessary funds to start or expand your venture in this budding industry.\"}}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:35'),
(48,'INFO','Added to memory','{\"key\":\"budget\",\"category\":\"preferences\",\"value\":500}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:36'),
(49,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"preferences\",\"value\":\"california\"}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:36'),
(50,'INFO','Extracted slots from message','{\"budget\":500,\"location\":\"california\"}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:36'),
(51,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":25}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:36'),
(52,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california\"}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:36'),
(53,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":16}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:36'),
(54,'INFO','Added to memory','{\"key\":\"age\",\"category\":\"compliance\",\"value\":16}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:36'),
(55,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california\"}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:36'),
(56,'WARNING','User not compliant','{\"age_compliant\":false,\"geo_compliant\":true}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:36'),
(57,'INFO','Filtered offers by compliance','{\"original_count\":20,\"filtered_count\":0,\"restrictions\":[]}','ai_68c8556aa83050.17114284',NULL,NULL,NULL,'2025-09-15 18:05:36'),
(58,'INFO','AIEngine initialized','{\"session_id\":\"ai_68c85578c0a509.57715229\",\"persona\":\"street_budtender\"}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(59,'INFO','Added to memory','{\"key\":\"budget\",\"category\":\"preferences\",\"value\":500}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(60,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"preferences\",\"value\":\"california, and have\"}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(61,'INFO','Extracted slots from message','{\"budget\":500,\"location\":\"california, and have\"}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(62,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(63,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(64,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(65,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(66,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(67,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(68,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(69,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(70,'INFO','Pre-selected offers','{\"offers\":[],\"survey_data\":[]}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:44'),
(71,'INFO','Processing tool call','{\"tool\":\"search_offers\",\"args\":{\"query\":\"cannabis\",\"category\":\"Medical Cannabis\",\"max_results\":3}}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(72,'INFO','Executing tool','{\"tool\":\"search_offers\",\"parameters\":{\"query\":\"cannabis\",\"category\":\"Medical Cannabis\",\"max_results\":3}}','ai_68c85578c0a509.57715229',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(73,'INFO','AIEngine initialized','{\"session_id\":\"ai_68c8557c7dbd58.15091906\",\"persona\":\"street_budtender\"}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(74,'INFO','Added to memory','{\"key\":\"budget\",\"category\":\"preferences\",\"value\":500}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(75,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"preferences\",\"value\":\"california, and have\"}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(76,'INFO','Extracted slots from message','{\"budget\":500,\"location\":\"california, and have\"}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(77,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(78,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(79,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(80,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(81,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(82,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(83,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(84,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(85,'INFO','Pre-selected offers','{\"offers\":[],\"survey_data\":[]}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:48'),
(86,'INFO','Processing tool call','{\"tool\":\"present_offer\",\"args\":{\"offer_id\":\"Cannabis_Financing\",\"custom_message\":\"Hello Sarah! Based on your preferences, I have some exciting opportunities for you. First up, there\'s \'Cannabis Business Financing\'. This is a specialized financing solution tailored to support cannabis businesses. It might be a great fit if you\'re looking into starting or expanding a business in the cannabis industry.\"}}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(87,'INFO','Executing tool','{\"tool\":\"present_offer\",\"parameters\":{\"offer_id\":\"Cannabis_Financing\",\"custom_message\":\"Hello Sarah! Based on your preferences, I have some exciting opportunities for you. First up, there\'s \'Cannabis Business Financing\'. This is a specialized financing solution tailored to support cannabis businesses. It might be a great fit if you\'re looking into starting or expanding a business in the cannabis industry.\"}}','ai_68c8557c7dbd58.15091906',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(88,'INFO','AIEngine initialized','{\"session_id\":\"ai_68c855802951f3.66347807\",\"persona\":\"street_budtender\"}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(89,'INFO','Added to memory','{\"key\":\"budget\",\"category\":\"preferences\",\"value\":500}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(90,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"preferences\",\"value\":\"california, and have\"}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(91,'INFO','Extracted slots from message','{\"budget\":500,\"location\":\"california, and have\"}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(92,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(93,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(94,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(95,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(96,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(97,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(98,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(99,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"california, and have\"}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(100,'INFO','Pre-selected offers','{\"offers\":[],\"survey_data\":[]}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:52'),
(101,'INFO','Processing tool call','{\"tool\":\"present_offer\",\"args\":{\"offer_id\":\"Cannabis_Financing\"}}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:54'),
(102,'INFO','Executing tool','{\"tool\":\"present_offer\",\"parameters\":{\"offer_id\":\"Cannabis_Financing\"}}','ai_68c855802951f3.66347807',NULL,NULL,NULL,'2025-09-15 18:05:54'),
(103,'INFO','AIEngine initialized','{\"session_id\":\"ai_68c855a534bdb3.11686406\",\"persona\":\"street_budtender\"}','ai_68c855a534bdb3.11686406',NULL,NULL,NULL,'2025-09-15 18:06:29'),
(104,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"preferences\",\"value\":\"colorado, and want to invest\"}','ai_68c855a534bdb3.11686406',NULL,NULL,NULL,'2025-09-15 18:06:29'),
(105,'INFO','Extracted slots from message','{\"location\":\"colorado, and want to invest\"}','ai_68c855a534bdb3.11686406',NULL,NULL,NULL,'2025-09-15 18:06:29'),
(106,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"colorado, and want to invest\"}','ai_68c855a534bdb3.11686406',NULL,NULL,NULL,'2025-09-15 18:06:29'),
(107,'INFO','Added to memory','{\"key\":\"location\",\"category\":\"compliance\",\"value\":\"colorado, and want to invest\"}','ai_68c855a534bdb3.11686406',NULL,NULL,NULL,'2025-09-15 18:06:29'),
(108,'WARNING','User not compliant','{\"age_compliant\":false,\"geo_compliant\":false}','ai_68c855a534bdb3.11686406',NULL,NULL,NULL,'2025-09-15 18:06:29'),
(109,'INFO','Filtered offers by compliance','{\"original_count\":20,\"filtered_count\":0,\"restrictions\":[\"all\",\"all_cannabis_offers\"]}','ai_68c855a534bdb3.11686406',NULL,NULL,NULL,'2025-09-15 18:06:29'),
(110,'INFO','AIEngine initialized','{\"session_id\":\"ai_68ca0075418267.95760876\",\"persona\":\"street_budtender\"}','ai_68ca0075418267.95760876',NULL,NULL,NULL,'2025-09-17 00:27:33'),
(111,'INFO','AIEngine initialized','{\"session_id\":\"ai_68ca009445c442.06877027\",\"persona\":\"street_budtender\"}','ai_68ca009445c442.06877027',NULL,NULL,NULL,'2025-09-17 00:28:04'),
(112,'INFO','AIEngine initialized','{\"session_id\":\"ai_68ca0182dd3673.75866124\",\"persona\":\"street_budtender\"}','ai_68ca0182dd3673.75866124',NULL,NULL,NULL,'2025-09-17 00:32:02');

--
-- Table structure for table `ai_user_accounts`
--

CREATE TABLE `ai_user_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `survey_token` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `email_verified` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `last_login` timestamp NULL DEFAULT NULL,
  `login_attempts` int(11) DEFAULT 0,
  `locked_until` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  KEY `idx_username` (`username`),
  KEY `idx_email` (`email`),
  KEY `idx_survey_token` (`survey_token`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_user_accounts`
--

INSERT INTO `ai_user_accounts` VALUES
(1,'test_patient_1757878872','test_patient_1757878872@example.com','$2y$12$oGDIDA0wuwum10SGViUvu.2e1q.givK7twxq9gTxj3TU2L3YelwPa','Test Patient','test_login_flow_1757878872',1,1,'2025-09-14 19:41:12','2025-09-14 19:41:12','2025-09-14 19:41:12',0,NULL),
(2,'login_test_1757878889','login_test_1757878889@example.com','$2y$12$J251qErW7shcd6bafC0bL.EaPOE4eMXheB7JiwIFFnXcrtczvksSO','Test User','login_test_1757878889',1,1,'2025-09-14 19:41:29','2025-09-14 19:41:30','2025-09-14 19:41:30',0,NULL);

--
-- Table structure for table `ai_user_eligibility`
--

CREATE TABLE `ai_user_eligibility` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(255) NOT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `user_email` varchar(255) DEFAULT NULL,
  `offers_completed` int(11) DEFAULT 0,
  `total_value_earned` decimal(10,2) DEFAULT 0.00,
  `eligibility_status` enum('in_progress','eligible','claimed') DEFAULT 'in_progress',
  `prize_claimed` tinyint(1) DEFAULT 0,
  `prize_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`prize_details`)),
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_id` (`session_id`),
  KEY `user_email` (`user_email`),
  KEY `eligibility_status` (`eligibility_status`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_user_eligibility`
--

INSERT INTO `ai_user_eligibility` VALUES
(1,'ai_68c8556aa83050.17114284','John Doe','john@example.com',0,0.00,'in_progress',0,NULL,'2025-09-15 18:05:30','2025-09-15 18:05:30'),
(2,'ai_68c85578c0a509.57715229','Sarah','sarah@test.com',0,0.00,'in_progress',0,NULL,'2025-09-15 18:05:44','2025-09-15 18:05:44'),
(3,'ai_68c8557c7dbd58.15091906','Sarah','sarah@test.com',0,0.00,'in_progress',0,NULL,'2025-09-15 18:05:48','2025-09-15 18:05:48'),
(4,'ai_68c855802951f3.66347807','Sarah','sarah@test.com',0,0.00,'in_progress',0,NULL,'2025-09-15 18:05:52','2025-09-15 18:05:52');

--
-- Table structure for table `ai_user_preferences`
--

CREATE TABLE `ai_user_preferences` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `preference_key` varchar(50) NOT NULL,
  `preference_value` text DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_preference` (`user_id`,`preference_key`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `ai_user_preferences_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `ai_user_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_user_preferences`
--

INSERT INTO `ai_user_preferences` VALUES
(1,1,'theme','light','2025-09-14 19:41:12','2025-09-14 19:41:12'),
(2,1,'notification_email','true','2025-09-14 19:41:12','2025-09-14 19:41:12'),
(3,1,'conversation_pagination','10','2025-09-14 19:41:12','2025-09-14 19:41:12'),
(4,2,'theme','light','2025-09-14 19:41:29','2025-09-14 19:41:29'),
(5,2,'notification_email','true','2025-09-14 19:41:29','2025-09-14 19:41:29'),
(6,2,'conversation_pagination','10','2025-09-14 19:41:29','2025-09-14 19:41:29');

--
-- Table structure for table `ai_user_qualifications`
--

CREATE TABLE `ai_user_qualifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` int(11) NOT NULL,
  `qualification_key` varchar(100) NOT NULL,
  `qualification_value` text DEFAULT NULL,
  `confidence_score` decimal(3,2) DEFAULT 0.00,
  `extracted_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `conversation_id` (`conversation_id`),
  KEY `qualification_key` (`qualification_key`),
  CONSTRAINT `ai_user_qualifications_ibfk_1` FOREIGN KEY (`conversation_id`) REFERENCES `ai_conversations` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_user_qualifications`
--


--
-- Table structure for table `ai_user_sessions`
--

CREATE TABLE `ai_user_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `session_token` varchar(255) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `expires_at` timestamp NOT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_token` (`session_token`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_session_token` (`session_token`),
  KEY `idx_expires_at` (`expires_at`),
  CONSTRAINT `ai_user_sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `ai_user_accounts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `ai_user_sessions`
--

INSERT INTO `ai_user_sessions` VALUES
(1,1,'21798e4392590a4873942f5980f44ddcfce2c4edb798e6a498a9b14eb37d661c',NULL,NULL,'2025-09-15 23:41:12','2025-09-14 19:41:12'),
(2,1,'ce2cae06a5afd2f0d89c36cd6707f01a960584e19058768f87461f854dfcd248',NULL,NULL,'2025-09-15 23:41:12','2025-09-14 19:41:12'),
(3,2,'c47d0527b82fa112c51f04e623f1e0ef0eef521eda79b722a4f215a8e96c6b0a',NULL,NULL,'2025-09-15 23:41:29','2025-09-14 19:41:29');

--
-- Table structure for table `backup_ai_user_accounts`
--

CREATE TABLE `backup_ai_user_accounts` (
  `id` int(11) NOT NULL DEFAULT 0,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `full_name` varchar(100) NOT NULL,
  `survey_token` varchar(255) DEFAULT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  `email_verified` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `last_login` timestamp NULL DEFAULT NULL,
  `login_attempts` int(11) DEFAULT 0,
  `locked_until` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `backup_ai_user_accounts`
--

INSERT INTO `backup_ai_user_accounts` VALUES
(1,'test_patient_1757878872','test_patient_1757878872@example.com','$2y$12$oGDIDA0wuwum10SGViUvu.2e1q.givK7twxq9gTxj3TU2L3YelwPa','Test Patient','test_login_flow_1757878872',1,1,'2025-09-14 19:41:12','2025-09-14 19:41:12','2025-09-14 19:41:12',0,NULL),
(2,'login_test_1757878889','login_test_1757878889@example.com','$2y$12$J251qErW7shcd6bafC0bL.EaPOE4eMXheB7JiwIFFnXcrtczvksSO','Test User','login_test_1757878889',1,1,'2025-09-14 19:41:29','2025-09-14 19:41:30','2025-09-14 19:41:30',0,NULL);

--
-- Table structure for table `backup_tbl_user`
--

CREATE TABLE `backup_tbl_user` (
  `id` int(11) NOT NULL DEFAULT 0,
  `name` varchar(100) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `status` tinyint(1) DEFAULT 1,
  `created` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `backup_tbl_user`
--

INSERT INTO `backup_tbl_user` VALUES
(1,'Administrator','admin','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',1,'2025-09-09 17:17:03');

--
-- Table structure for table `backup_tbl_users`
--

CREATE TABLE `backup_tbl_users` (
  `id` int(11) NOT NULL DEFAULT 0,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `full_name` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `status` enum('active','inactive','pending') DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `last_login` datetime DEFAULT NULL,
  `login_attempts` int(11) DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `survey_token` varchar(255) DEFAULT NULL,
  `needs_password_reset` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `backup_tbl_users`
--


--
-- Table structure for table `tbl_ai_conversations`
--

CREATE TABLE `tbl_ai_conversations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(255) NOT NULL,
  `conversation_id` varchar(255) NOT NULL,
  `user_id` int(11) DEFAULT NULL,
  `user_name` varchar(100) DEFAULT NULL,
  `user_email` varchar(255) DEFAULT NULL,
  `survey_token` varchar(255) DEFAULT NULL,
  `conversation_state` enum('greeting','qualification','offer_presentation','completion') DEFAULT 'greeting',
  `ip_address` varchar(45) DEFAULT NULL,
  `started_at` datetime DEFAULT current_timestamp(),
  `last_activity` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `completed_at` datetime DEFAULT NULL,
  `status` enum('active','completed','abandoned') DEFAULT 'active',
  PRIMARY KEY (`id`),
  UNIQUE KEY `conversation_id` (`conversation_id`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_survey_token` (`survey_token`),
  KEY `idx_user_email` (`user_email`),
  KEY `idx_status` (`status`),
  CONSTRAINT `tbl_ai_conversations_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_ai_conversations`
--

INSERT INTO `tbl_ai_conversations` VALUES
(1,'9dbee222e410e968df8b012e8d203958','8443a94734dfb915863c0686261d90bc',NULL,'Kwende Johnson','test@example.com','test123','greeting','::1','2025-09-14 18:11:44','2025-09-14 14:11:44',NULL,'active');

--
-- Table structure for table `tbl_ai_messages`
--

CREATE TABLE `tbl_ai_messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` varchar(255) NOT NULL,
  `message_type` enum('user','ai') NOT NULL,
  `message_content` text NOT NULL,
  `message_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`message_data`)),
  `created_at` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_conversation_id` (`conversation_id`),
  KEY `idx_message_type` (`message_type`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_ai_messages`
--

INSERT INTO `tbl_ai_messages` VALUES
(1,'test456','user','medical cannabis',NULL,'2025-09-14 14:07:59'),
(2,'8443a94734dfb915863c0686261d90bc','ai','Yo Kwende Johnson, I peeped your survey and I got real cannabis opportunities lined up. No bullshit, just deals that actually work.',NULL,'2025-09-14 14:11:44'),
(3,'8443a94734dfb915863c0686261d90bc','user','medical cannabis',NULL,'2025-09-14 14:11:50'),
(4,'8443a94734dfb915863c0686261d90bc','ai','Aight, you\'re lookin\' at medical cannabis, huh? Dope. But listen, it ain\'t just about smokin\' a blunt and callin\' it medicine. Different strains got different vibes, man. Indicas, sativas, hybrids - they all hit you different. Also, don\'t get scammed by folks sellin\' CBD oil like it\'s a cure-all. It\'s good shit, but it ain\'t gonna fix everything. So, what you need it for? Pain, anxiety, sleep? Talk to me, fam.',NULL,'2025-09-14 14:11:55');

--
-- Table structure for table `tbl_conversions`
--

CREATE TABLE `tbl_conversions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `conversation_id` varchar(255) NOT NULL,
  `offer_id` int(11) NOT NULL,
  `conversion_type` enum('click','call','form_submit','complete') DEFAULT 'click',
  `conversion_value` decimal(10,2) DEFAULT 0.00,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `status` enum('pending','confirmed','rejected') DEFAULT 'pending',
  `created_at` datetime DEFAULT current_timestamp(),
  `confirmed_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_conversation_id` (`conversation_id`),
  KEY `idx_offer_id` (`offer_id`),
  KEY `idx_conversion_type` (`conversion_type`),
  KEY `idx_status` (`status`),
  KEY `idx_created_at` (`created_at`),
  CONSTRAINT `tbl_conversions_ibfk_1` FOREIGN KEY (`offer_id`) REFERENCES `tbl_offers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_conversions`
--


--
-- Table structure for table `tbl_fraud_logs`
--

CREATE TABLE `tbl_fraud_logs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `event_type` enum('honeypot_triggered','time_validation_failed','rate_limit_exceeded','duplicate_attempt','invalid_proof_token','suspicious_activity') NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `session_id` varchar(200) DEFAULT NULL,
  `form_data` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`form_data`)),
  `risk_score` int(3) DEFAULT 0,
  `blocked` tinyint(1) DEFAULT 0,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_event_type` (`event_type`),
  KEY `idx_email` (`email`),
  KEY `idx_ip_address` (`ip_address`),
  KEY `idx_created_at` (`created_at`),
  KEY `idx_risk_score` (`risk_score`)
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_fraud_logs`
--

INSERT INTO `tbl_fraud_logs` VALUES
(1,'honeypot_triggered',NULL,'UNKNOWN','','8e0cb87d1986603be5c31fac1817f683','{\"name\":\"Test User\",\"email\":\"test@example.com\",\"form_started_at\":1757865711,\"proof_token\":\"test_token\",\"company_website\":\"spam-site.com\"}',100,1,'2025-09-14 16:02:01'),
(2,'time_validation_failed',NULL,'UNKNOWN','','8e0cb87d1986603be5c31fac1817f683','{\"name\":\"Test User\",\"email\":\"test@example.com\",\"form_started_at\":1757865719,\"proof_token\":\"test_token\",\"context\":{\"time_diff\":2,\"min_required\":5}}',80,1,'2025-09-14 16:02:01'),
(3,'rate_limit_exceeded','test@example.com','UNKNOWN','','8e0cb87d1986603be5c31fac1817f683','{\"context\":{\"remaining_seconds\":60}}',60,1,'2025-09-14 16:02:01'),
(4,'honeypot_triggered',NULL,'UNKNOWN','','02d04a7aa980c77cf4981d20f4107688','{\"name\":\"Test User\",\"email\":\"test@example.com\",\"form_started_at\":1757865716,\"proof_token\":\"test_token\",\"company_website\":\"spam-site.com\"}',100,1,'2025-09-14 16:02:06'),
(5,'time_validation_failed',NULL,'UNKNOWN','','02d04a7aa980c77cf4981d20f4107688','{\"name\":\"Test User\",\"email\":\"test@example.com\",\"form_started_at\":1757865724,\"proof_token\":\"test_token\",\"context\":{\"time_diff\":2,\"min_required\":5}}',80,1,'2025-09-14 16:02:06'),
(6,'rate_limit_exceeded','test@example.com','UNKNOWN','','02d04a7aa980c77cf4981d20f4107688','{\"context\":{\"remaining_seconds\":60}}',60,1,'2025-09-14 16:02:06');

--
-- Table structure for table `tbl_offers`
--

CREATE TABLE `tbl_offers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `offer_key` varchar(100) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `category` varchar(100) DEFAULT NULL,
  `url` varchar(500) DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `payout_amount` decimal(10,2) DEFAULT 0.00,
  `is_active` tinyint(1) DEFAULT 1,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `offer_key` (`offer_key`),
  KEY `idx_category` (`category`),
  KEY `idx_is_active` (`is_active`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_offers`
--

INSERT INTO `tbl_offers` VALUES
(1,'Medical_Cannabis_Card','Medical Cannabis Card','Get your medical cannabis card quickly and easily. Save money on purchases and gain legal protection.','medical','https://example.com/medical-card','1-800-CANNABIS',25.00,1,'2025-09-09 17:17:03','2025-09-09 17:17:03'),
(2,'Cannabis_Delivery_Service','Cannabis Delivery Service','Premium cannabis delivery to your door. High-quality products with fast, discreet delivery.','delivery','https://example.com/delivery','1-888-DELIVERY',15.00,1,'2025-09-09 17:17:03','2025-09-09 17:17:03'),
(3,'Cannabis_Business_Insurance','Cannabis Business Insurance','Specialized insurance coverage for cannabis businesses. Protect your investment and comply with regulations.','business','https://example.com/insurance','1-877-BIZ-INSURE',35.00,1,'2025-09-09 17:17:03','2025-09-09 17:17:03'),
(4,'Cannabis_Education_Course','Cannabis Industry Certification','Professional cannabis education and certification course. Learn about science, business, and compliance.','education','https://example.com/certification','1-866-CANNABIS-EDU',20.00,1,'2025-09-09 17:17:03','2025-09-09 17:17:03'),
(5,'Cannabis_Consultation','Free Cannabis Consultation','Personalized cannabis consultation with experts. Get advice on products and usage tailored to your needs.','consultation','https://example.com/consultation','1-855-CANNABIS-HELP',30.00,1,'2025-09-09 17:17:03','2025-09-09 17:17:03'),
(6,'Cannabis_Legal_Services','Cannabis Legal Services','Legal assistance for cannabis businesses and users. Navigate complex regulations with expert help.','legal','https://example.com/legal','1-888-CANNABIS-LAW',40.00,1,'2025-09-09 17:17:03','2025-09-09 17:17:03'),
(7,'Cannabis_Financing','Cannabis Business Financing','Specialized financing for cannabis businesses. Get the capital you need to succeed in this growing industry.','finance','https://example.com/financing','1-844-CANNABIS-FUND',45.00,1,'2025-09-09 17:17:03','2025-09-09 17:17:03'),
(8,'Cannabis_Marketing_Services','Cannabis Marketing Agency','Digital marketing services for cannabis businesses. Stand out in the competitive cannabis market.','marketing','https://example.com/marketing','1-877-GROW-CANNABIS',38.00,1,'2025-09-09 17:17:03','2025-09-09 17:17:03'),
(9,'Cannabis_Product_Wholesale','Cannabis Product Wholesale','Bulk cannabis products for businesses. Competitive pricing and reliable supply chain.','wholesale','https://example.com/wholesale','1-800-BULK-CANNABIS',50.00,1,'2025-09-09 17:17:03','2025-09-09 17:17:03'),
(10,'Cannabis_Testing_Lab','Cannabis Lab Testing Services','Professional cannabis product testing and analysis. Fast, accurate results you can trust.','testing','https://example.com/testing','1-866-TEST-CANNABIS',28.00,1,'2025-09-09 17:17:03','2025-09-09 17:17:03'),
(11,'test_medical_card_1757878872','Medical Cannabis Card Application','Apply for your medical cannabis card','medical_card','https://example.com/medical-card',NULL,25.00,1,'2025-09-14 15:41:12','2025-09-14 15:41:12'),
(12,'test_delivery_1757878872','Cannabis Delivery Service','Get cannabis delivered to your door','delivery','https://example.com/delivery',NULL,15.00,1,'2025-09-14 15:41:12','2025-09-14 15:41:12'),
(13,'Medical_CBD_Oil','Medical CBD Oil Program','High-potency CBD oil for medical conditions',NULL,'https://example.com/medical-cbd','1-888-MED-CBD',18.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(14,'Recreational_Cannabis_Subscription','Premium Cannabis Subscription','Monthly delivery of premium recreational cannabis products',NULL,'https://example.com/recreational-sub','1-877-GET-HIGH',22.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(15,'Frequent_User_Discount_Club','Cannabis VIP Club','Exclusive discounts for frequent cannabis users',NULL,'https://example.com/vip-club','1-866-VIP-CANNA',15.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(16,'Herbal_Supplements_Bundle','Natural Herbal Supplements','Premium herbal supplements and natural remedies',NULL,'https://example.com/herbal-supplements','1-855-NATURAL',12.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(17,'Debt_Relief_Consultation','Debt Relief Services','Professional debt consolidation and relief services',NULL,'https://example.com/debt-relief','1-800-DEBT-FREE',30.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(18,'Investment_Portfolio_Planning','Investment Portfolio Services','Professional investment planning and wealth management',NULL,'https://example.com/investment-planning','1-888-INVEST-NOW',35.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(19,'Crypto_Investment_Platform','Cryptocurrency Investment Platform','Easy-to-use crypto trading and investment platform',NULL,'https://example.com/crypto-platform','1-877-BUY-CRYPTO',20.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(20,'Premium_Streaming_Service','Premium Streaming Bundle','Complete entertainment streaming package',NULL,'https://example.com/premium-streaming','1-866-STREAM-PRO',15.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(21,'Adult_Entertainment_Platform','Premium Adult Content Platform','High-quality adult entertainment and dating services',NULL,'https://example.com/adult-platform','1-888-ADULT-FUN',25.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(22,'Solar_Panel_Installation','Home Solar Panel System','Complete solar panel installation and energy savings',NULL,'https://example.com/solar-panels','1-855-GO-SOLAR',40.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(23,'Health_Fitness_Program','Personal Health & Fitness Program','Comprehensive health and fitness coaching program',NULL,'https://example.com/health-program','1-877-GET-FIT',28.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(24,'Financial_Coaching','Financial Success Coaching','Personal financial coaching and wealth building strategies',NULL,'https://example.com/financial-coaching','1-866-MONEY-COACH',32.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42'),
(25,'Habit_Development_Course','Habit Mastery & Personal Development','Comprehensive personal development and habit formation course',NULL,'https://example.com/habit-course','1-888-HABIT-PRO',24.00,1,'2025-09-14 20:37:42','2025-09-14 16:37:42');

--
-- Table structure for table `tbl_survey`
--

CREATE TABLE `tbl_survey` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) DEFAULT NULL,
  `question` varchar(255) DEFAULT NULL,
  `answer` varchar(255) DEFAULT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `zipcode` varchar(10) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `status` enum('1','0','-1') DEFAULT '1',
  `session_id` varchar(200) DEFAULT NULL,
  `group_id` varchar(45) DEFAULT '0',
  `created` datetime DEFAULT current_timestamp(),
  `token` varchar(255) DEFAULT NULL,
  `survey_answers` text DEFAULT NULL,
  `email_verified` tinyint(1) DEFAULT 0,
  `email_verified_at` datetime DEFAULT NULL,
  `created_date` date GENERATED ALWAYS AS (cast(`created` as date)) STORED,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email_date_unique` (`email`,`created_date`),
  KEY `idx_email` (`email`),
  KEY `idx_token` (`token`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_created` (`created`),
  KEY `idx_status` (`status`),
  KEY `idx_email_verified` (`email_verified`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_survey`
--

INSERT INTO `tbl_survey` VALUES
(1,NULL,NULL,NULL,'Kwende Johnson','mayor420@gmail.com','22202','98.233.48.198','1','sll24p319927scom770bei7adl','0','2025-09-14 17:13:27','cf7de93e6ff1ad559d8a716a1edb1fc4a99f2cff294b27f38a6b1d1f91e9bd3d','{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"Yes, I invest\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"Yes\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Make more money\"}',0,NULL,'2025-09-14'),
(2,NULL,NULL,NULL,'Kwende Johnson','firegod420@gmail.com','22202','98.233.48.198','1','p1odl7e3t3a8715af2tg9vtllj','0','2025-09-14 18:25:17','f19d2b17263412fc0404f633ad12bac2006b00ec5cfeaf58978597e6eb1656fd','{\"\'01\'\":\"Medical\",\"\'02\'\":\"Once a Week\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Struggling with debt\",\"\'05\'\":\"No, not interested\",\"\'06\'\":\"No, I\'m good\",\"\'07\'\":\"Yes\",\"\'08\'\":\"Maybe, need more info\",\"\'09\'\":\"Improve my health\\/fitness\"}',0,NULL,'2025-09-14'),
(3,NULL,NULL,NULL,'hazy piff','forexcash420@gmail.com','22202','98.233.48.198','1','aeht0340ljttv7kj4dbgma8s7k','0','2025-09-14 18:58:47','f7bc7759ff80be55a486ac2a25d4481a1da26bd3d31e9b393400b180e13958fe','{\"\'01\'\":\"Medical\",\"\'02\'\":\"Once a Week\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, not interested\",\"\'06\'\":\"No, I\'m good\",\"\'07\'\":\"Yes\",\"\'08\'\":\"Maybe, need more info\",\"\'09\'\":\"Improve my health\\/fitness\"}',0,NULL,'2025-09-14'),
(4,NULL,NULL,NULL,'Kwende Johnson','siruisman420@gmail.com','22202','98.233.48.198','1','bsb5aed2pvgj7l543ab7uu05ti','0','2025-09-14 19:09:01','a8e868986eefe297afc57f9bbcda6575dd09c17b9a0f979a22a342cb7c6378ff','{\"\'01\'\":\"Medical\",\"\'02\'\":\"Few Times a week\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Struggling with debt\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"Yes\",\"\'08\'\":\"Maybe, need more info\",\"\'09\'\":\"Improve my health\\/fitness\"}',0,NULL,'2025-09-14'),
(5,NULL,NULL,NULL,'Kwende Johnson','seashell.oro@gmail.com','22202','98.233.48.198','1','bsb5aed2pvgj7l543ab7uu05ti','0','2025-09-14 19:26:25','92a80080b85f0022cf6b9feebc6920c74583f4c8e9dadb7bb8646528262a0eab','{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"No, I\'m good\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Improve my health\\/fitness\"}',0,NULL,'2025-09-14'),
(6,NULL,NULL,NULL,'Kwende Johnson','mayor420@gmail.com','22202','98.233.48.198','1','atm34tek584se9o9egofsmpu36','0','2025-09-15 14:04:45','6caf35208183d1094ba52da9d3a3ff3d16bc86431523bf84c123e8c6e7f789d9','{\"\'01\'\":\"Medical\",\"\'02\'\":\"Few Times a week\",\"\'03\'\":\"No\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"Yes, I invest\",\"\'06\'\":\"No, I\'m good\",\"\'07\'\":\"Yes\",\"\'08\'\":\"Maybe, need more info\",\"\'09\'\":\"Improve my health\\/fitness\"}',0,NULL,'2025-09-15'),
(7,NULL,NULL,NULL,'Kwende Johnson','firegod420@gmail.com','22202','98.233.48.198','1','atm34tek584se9o9egofsmpu36','0','2025-09-15 16:58:29','264c22fcf9f2dd7f1fdc0ccab2760e291e3bd182a63de823c9dbcb581c46beb7','{\"\'01\'\":\"Medical\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"No\",\"\'04\'\":\"Struggling with debt\",\"\'05\'\":\"No, not interested\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"Yes\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Make more money\"}',0,NULL,'2025-09-15'),
(8,NULL,NULL,NULL,'Kwende Johnson','forexcash420@gmail.com','22202','98.233.48.198','1','atm34tek584se9o9egofsmpu36','0','2025-09-15 17:05:11','be7ad17efad30670b300c0ec809b1d99bc44833fd80cdc88426974fd6597e2bc','{\"\'01\'\":\"Medical\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Struggling with debt\",\"\'05\'\":\"Yes, I invest\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Develop better habits\\/hobbies\"}',0,NULL,'2025-09-15'),
(9,NULL,NULL,NULL,'Hazy piff','mayor420@gmail.com','25401','24.126.12.197','1','fkl8ulesq6978vro18jga7v9l3','0','2025-09-16 22:52:59','3e0b53b37f923dec36da66ae7882eb1b74a3e00b6baeb99f3bde5fb70eae2252','{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Week\",\"\'03\'\":\"No\",\"\'04\'\":\"Struggling with debt\",\"\'05\'\":\"No, not interested\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Improve my health\\/fitness\"}',0,NULL,'2025-09-16'),
(10,NULL,NULL,NULL,'Kwende Johnson','firegod420@gmail.com','22202','98.233.48.198','1','qdc2grhvi4h7nkm7dq1o68ml1i','0','2025-09-16 23:33:00','72d9b70a1b6001d5277fa45478d8796694e19a3771898b67a72cfcbeb753f580','{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Improve my health\\/fitness\"}',0,NULL,'2025-09-16'),
(11,NULL,NULL,NULL,'Kwende Johnson','forexcash420@gmail.com','22202','98.233.48.198','1','fh7stav0aoiuhv32m6b8iqn82m','0','2025-09-16 23:50:12','88f30b9aac9606258505cf2ba88557997040d6a96ce1c613fe5da150c4b3387d','{\"\'01\'\":\"Recreational\",\"\'02\'\":\"Once a Month\",\"\'03\'\":\"Yes\",\"\'04\'\":\"Comfortable\",\"\'05\'\":\"No, but I\'m curious\",\"\'06\'\":\"Yes, tell me more\",\"\'07\'\":\"No\",\"\'08\'\":\"Yes, very interested\",\"\'09\'\":\"Make more money\"}',0,NULL,'2025-09-16');

--
-- Table structure for table `tbl_survey_answers`
--

CREATE TABLE `tbl_survey_answers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `question` varchar(255) NOT NULL,
  `answer` varchar(255) NOT NULL,
  `name` varchar(100) NOT NULL,
  `email` varchar(255) NOT NULL,
  `zipcode` varchar(10) DEFAULT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `status` enum('1','0','-1') DEFAULT '1',
  `session_id` varchar(200) DEFAULT NULL,
  `group_id` varchar(45) DEFAULT '0',
  `created` datetime DEFAULT current_timestamp(),
  `token` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_email` (`email`),
  KEY `idx_token` (`token`),
  KEY `idx_session_id` (`session_id`),
  KEY `idx_created` (`created`),
  KEY `idx_question` (`question`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_survey_answers`
--


--
-- Table structure for table `tbl_survey_progress`
--

CREATE TABLE `tbl_survey_progress` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `session_id` varchar(255) NOT NULL,
  `current_tab` int(11) DEFAULT 0,
  `survey_answers` longtext DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_id` (`session_id`),
  KEY `created_at` (`created_at`),
  KEY `updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `tbl_survey_progress`
--


--
-- Table structure for table `tbl_user`
--

CREATE TABLE `tbl_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `status` tinyint(1) DEFAULT 1,
  `created` datetime DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_user`
--

INSERT INTO `tbl_user` VALUES
(1,'Administrator','admin','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',1,'2025-09-14 16:57:16');

--
-- Table structure for table `tbl_user_sessions`
--

CREATE TABLE `tbl_user_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `session_token` varchar(255) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `expires_at` datetime NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_token` (`session_token`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `idx_is_active` (`is_active`),
  CONSTRAINT `tbl_user_sessions_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `tbl_users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_user_sessions`
--


--
-- Table structure for table `tbl_users`
--

CREATE TABLE `tbl_users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `full_name` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `status` enum('active','inactive','pending') DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `last_login` datetime DEFAULT NULL,
  `login_attempts` int(11) DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `survey_token` varchar(255) DEFAULT NULL,
  `needs_password_reset` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  KEY `idx_email` (`email`),
  KEY `idx_status` (`status`),
  KEY `idx_survey_token` (`survey_token`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `tbl_users`
--


--
-- Table structure for table `user_preferences`
--

CREATE TABLE `user_preferences` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `preference_key` varchar(50) NOT NULL,
  `preference_value` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_preference` (`user_id`,`preference_key`),
  KEY `idx_user_id` (`user_id`),
  CONSTRAINT `fk_user_preferences_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_preferences`
--


--
-- Table structure for table `user_sessions`
--

CREATE TABLE `user_sessions` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `session_token` varchar(255) NOT NULL,
  `ip_address` varchar(45) DEFAULT NULL,
  `user_agent` text DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `expires_at` datetime NOT NULL,
  `is_active` tinyint(1) DEFAULT 1,
  PRIMARY KEY (`id`),
  UNIQUE KEY `session_token` (`session_token`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_expires_at` (`expires_at`),
  KEY `idx_is_active` (`is_active`),
  CONSTRAINT `fk_user_sessions_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `user_sessions`
--

INSERT INTO `user_sessions` VALUES
(1,1,'69bc77d8406e5e84c21b564779d94e4889d6f91b663c0d47ea11fbe8c8d8e0e0','UNKNOWN','','2025-09-16 20:27:24','2025-09-17 01:27:24',1);

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `email` varchar(255) DEFAULT NULL,
  `full_name` varchar(100) NOT NULL,
  `password_hash` varchar(255) NOT NULL,
  `status` enum('active','inactive','pending','suspended') DEFAULT 'active',
  `user_role` enum('admin','user','ai_user','survey_user') DEFAULT 'user',
  `email_verified` tinyint(1) DEFAULT 0,
  `survey_token` varchar(255) DEFAULT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `created_at` datetime DEFAULT current_timestamp(),
  `updated_at` datetime DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `last_login` datetime DEFAULT NULL,
  `login_attempts` int(11) DEFAULT 0,
  `locked_until` datetime DEFAULT NULL,
  `needs_password_reset` tinyint(1) DEFAULT 0,
  PRIMARY KEY (`id`),
  UNIQUE KEY `username` (`username`),
  UNIQUE KEY `email` (`email`),
  UNIQUE KEY `survey_token` (`survey_token`),
  KEY `idx_status` (`status`),
  KEY `idx_user_role` (`user_role`),
  KEY `idx_email_verified` (`email_verified`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` VALUES
(1,'admin','admin@localhost','Administrator','$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi','active','admin',1,NULL,NULL,'2025-09-09 17:17:03','2025-09-16 20:27:24','2025-09-16 20:27:24',0,NULL,0),
(2,'test_patient_1757878872','test_patient_1757878872@example.com','Test Patient','$2y$12$oGDIDA0wuwum10SGViUvu.2e1q.givK7twxq9gTxj3TU2L3YelwPa','active','ai_user',1,'test_login_flow_1757878872',NULL,'2025-09-14 15:41:12','2025-09-14 15:41:12','2025-09-14 15:41:12',0,NULL,0),
(3,'login_test_1757878889','login_test_1757878889@example.com','Test User','$2y$12$J251qErW7shcd6bafC0bL.EaPOE4eMXheB7JiwIFFnXcrtczvksSO','active','ai_user',1,'login_test_1757878889',NULL,'2025-09-14 15:41:29','2025-09-14 15:41:30','2025-09-14 15:41:30',0,NULL,0);

--
-- Temporary table structure for view `v_ai_conversation_summary`
--

/*!50001 DROP VIEW IF EXISTS `v_ai_conversation_summary`*/;
/*!50001 CREATE VIEW `v_ai_conversation_summary` AS SELECT
 1 AS `id`,
  1 AS `session_id`,
  1 AS `user_name`,
  1 AS `user_email`,
  1 AS `survey_token`,
  1 AS `conversation_state`,
  1 AS `turns_count`,
  1 AS `started_at`,
  1 AS `completed_at`,
  1 AS `ended_at`,
  1 AS `status`,
  1 AS `message_count`,
  1 AS `offers_presented`,
  1 AS `conversions`,
  1 AS `total_value` */;

--
-- Temporary table structure for view `v_user_analytics`
--

/*!50001 DROP VIEW IF EXISTS `v_user_analytics`*/;
/*!50001 CREATE VIEW `v_user_analytics` AS SELECT
 1 AS `id`,
  1 AS `username`,
  1 AS `email`,
  1 AS `full_name`,
  1 AS `survey_token`,
  1 AS `created_at`,
  1 AS `last_login`,
  1 AS `total_conversations`,
  1 AS `completed_conversations`,
  1 AS `total_conversions`,
  1 AS `total_revenue` */;

--
-- Final view structure for view `v_ai_conversation_summary`
--

/*!50001 DROP VIEW IF EXISTS `v_ai_conversation_summary`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_ai_conversation_summary` AS select `c`.`id` AS `id`,`c`.`session_id` AS `session_id`,`c`.`user_name` AS `user_name`,`c`.`user_email` AS `user_email`,`c`.`survey_token` AS `survey_token`,`c`.`conversation_state` AS `conversation_state`,`c`.`turns_count` AS `turns_count`,`c`.`started_at` AS `started_at`,`c`.`completed_at` AS `completed_at`,`c`.`ended_at` AS `ended_at`,`c`.`status` AS `status`,count(`m`.`id`) AS `message_count`,count(`op`.`id`) AS `offers_presented`,count(`oc`.`id`) AS `conversions`,coalesce(sum(`oc`.`conversion_value`),0) AS `total_value` from (((`ai_conversations` `c` left join `ai_messages` `m` on(`c`.`id` = `m`.`conversation_id`)) left join `ai_offer_presentations` `op` on(`c`.`id` = `op`.`conversation_id`)) left join `ai_offer_conversions` `oc` on(`c`.`id` = `oc`.`conversation_id`)) group by `c`.`id` */;

--
-- Final view structure for view `v_user_analytics`
--

/*!50001 DROP VIEW IF EXISTS `v_user_analytics`*/;
/*!50001 CREATE ALGORITHM=UNDEFINED */
/*!50013 DEFINER=`root`@`localhost` SQL SECURITY DEFINER */
/*!50001 VIEW `v_user_analytics` AS select `ua`.`id` AS `id`,`ua`.`username` AS `username`,`ua`.`email` AS `email`,`ua`.`full_name` AS `full_name`,`ua`.`survey_token` AS `survey_token`,`ua`.`created_at` AS `created_at`,`ua`.`last_login` AS `last_login`,count(distinct `c`.`id`) AS `total_conversations`,count(distinct case when `c`.`status` = 'completed' then `c`.`id` end) AS `completed_conversations`,count(distinct `oc`.`id`) AS `total_conversions`,coalesce(sum(`oc`.`conversion_value`),0) AS `total_revenue` from ((`ai_user_accounts` `ua` left join `ai_conversations` `c` on(convert(`ua`.`survey_token` using utf8mb4) collate utf8mb4_unicode_ci = convert(`c`.`survey_token` using utf8mb4) collate utf8mb4_unicode_ci or convert(`ua`.`email` using utf8mb4) collate utf8mb4_unicode_ci = convert(`c`.`user_email` using utf8mb4) collate utf8mb4_unicode_ci)) left join `ai_offer_conversions` `oc` on(`c`.`id` = `oc`.`conversation_id`)) group by `ua`.`id` */;


