-- WhatsApp CRM v20 - Solicitudes de instalación por IA
CREATE TABLE IF NOT EXISTS `whatsapp_contact_intakes` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `phone` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `customer_id` bigint(20) DEFAULT NULL,
  `intake_type` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'new_installation',
  `contact_name` varchar(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `neighborhood` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `locality` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `alt_phone` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'pending_data',
  `source_message` text COLLATE utf8mb4_unicode_ci,
  `payload_json` mediumtext COLLATE utf8mb4_unicode_ci,
  `admin_notes` text COLLATE utf8mb4_unicode_ci,
  `human_required` tinyint(1) NOT NULL DEFAULT 1,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_wci_phone_created` (`phone`,`created_at`),
  KEY `idx_wci_type_status` (`intake_type`,`status`),
  KEY `idx_wci_customer` (`customer_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `intake_type` varchar(40) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'new_installation';
ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `contact_name` varchar(160) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `address` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `neighborhood` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `locality` varchar(120) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `reference` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `alt_phone` varchar(40) COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `source_message` text COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `payload_json` mediumtext COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `admin_notes` text COLLATE utf8mb4_unicode_ci DEFAULT NULL;
ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `human_required` tinyint(1) NOT NULL DEFAULT 1;
ALTER TABLE `whatsapp_contact_intakes` ADD COLUMN IF NOT EXISTS `updated_at` datetime DEFAULT NULL;
