-- ============================================================
-- NOVAGENIC CRM + WHATSAPP BOTS — SCHEMA COMPLETO
-- MySQL 5.7+ / MariaDB 10.3+
-- ============================================================


-- ============================================================
-- TABLA: llm_configs
-- ============================================================
CREATE TABLE IF NOT EXISTS `llm_configs` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `module`      VARCHAR(80) NOT NULL UNIQUE COMMENT 'bot_patients, bot_doctors, engagement_patients, etc.',
  `provider`    ENUM('openai','anthropic','google') NOT NULL DEFAULT 'openai',
  `model`       VARCHAR(80) NOT NULL DEFAULT 'gpt-4o-mini',
  `temperature` DECIMAL(3,2) NOT NULL DEFAULT 0.70,
  `max_tokens`  INT NOT NULL DEFAULT 1000,
  `active`      TINYINT(1) NOT NULL DEFAULT 1,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: system_prompts
-- ============================================================
CREATE TABLE IF NOT EXISTS `system_prompts` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `module`      VARCHAR(80) NOT NULL UNIQUE,
  `name`        VARCHAR(120) NOT NULL,
  `content`     TEXT NOT NULL,
  `status`      ENUM('active','draft') NOT NULL DEFAULT 'active',
  `version`     INT NOT NULL DEFAULT 1,
  `updated_by`  INT UNSIGNED DEFAULT NULL,
  `updated_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`updated_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: patients
-- ============================================================
CREATE TABLE IF NOT EXISTS `patients` (
  `id`              INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`            VARCHAR(120) NOT NULL,
  `email`           VARCHAR(180) DEFAULT NULL,
  `phone`           VARCHAR(30)  DEFAULT NULL,
  `stage`           ENUM('purchase_confirmed','kit_shipped','kit_instructions','kit_returned','sample_received','lab_processing','results_released') NOT NULL DEFAULT 'purchase_confirmed',
  `order_id`        VARCHAR(60)  DEFAULT NULL,
  `doctor_name`     VARCHAR(120) DEFAULT NULL,
  `rep_name`        VARCHAR(120) DEFAULT NULL,
  `notes`           TEXT DEFAULT NULL,
  `sentiment`       ENUM('positive','neutral','negative','unknown') NOT NULL DEFAULT 'unknown',
  `pending_actions` JSON DEFAULT NULL,
  `last_contact`    DATE DEFAULT NULL,
  `wa_account_id`   INT UNSIGNED DEFAULT NULL,
  `created_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`      DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`wa_account_id`) REFERENCES `whatsapp_accounts`(`id`) ON DELETE SET NULL,
  INDEX idx_stage (`stage`),
  INDEX idx_rep (`rep_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: patient_messages
-- ============================================================
CREATE TABLE IF NOT EXISTS `patient_messages` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `patient_id` INT UNSIGNED NOT NULL,
  `role`       ENUM('user','assistant') NOT NULL DEFAULT 'user',
  `content`    TEXT NOT NULL,
  `channel`    ENUM('whatsapp','web') NOT NULL DEFAULT 'web',
  `wa_msg_id`  VARCHAR(100) DEFAULT NULL COMMENT 'Meta message ID',
  `timestamp`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`patient_id`) REFERENCES `patients`(`id`) ON DELETE CASCADE,
  INDEX idx_patient_ts (`patient_id`, `timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: doctor_leads
-- ============================================================
CREATE TABLE IF NOT EXISTS `doctor_leads` (
  `id`                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`                 VARCHAR(120) NOT NULL,
  `specialty`            ENUM('Cardiología','Oncología','Neurología','Psiquiatría','Medicina General','Endocrinología','Reumatología','Geriatría','Medicina Interna','Otra') DEFAULT 'Medicina General',
  `hospital`             VARCHAR(180) DEFAULT NULL,
  `phone`                VARCHAR(30)  DEFAULT NULL,
  `email`                VARCHAR(180) DEFAULT NULL,
  `city`                 VARCHAR(80)  DEFAULT NULL,
  `rep_name`             VARCHAR(120) DEFAULT NULL,
  `patient_volume`       INT DEFAULT 0,
  `previous_referrals`   INT DEFAULT 0,
  `response_rate`        DECIMAL(5,2) DEFAULT 0,
  `engagement_score`     DECIMAL(5,2) DEFAULT 0,
  `webinar_attendance`   INT DEFAULT 0,
  `content_interactions` INT DEFAULT 0,
  `ai_score`             DECIMAL(5,2) DEFAULT 0,
  `priority`             ENUM('hot','warm','cold','new') NOT NULL DEFAULT 'new',
  `conversion_probability` DECIMAL(5,2) DEFAULT 0,
  `ai_reasoning`         TEXT DEFAULT NULL,
  `last_scored`          DATETIME DEFAULT NULL,
  `tests_with_results`   INT DEFAULT 0,
  `wa_account_id`        INT UNSIGNED DEFAULT NULL,
  `created_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`wa_account_id`) REFERENCES `whatsapp_accounts`(`id`) ON DELETE SET NULL,
  INDEX idx_priority (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: doctor_messages
-- ============================================================
CREATE TABLE IF NOT EXISTS `doctor_messages` (
  `id`        INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `doctor_id` INT UNSIGNED NOT NULL,
  `role`      ENUM('user','assistant','advisor') NOT NULL DEFAULT 'user',
  `content`   TEXT NOT NULL,
  `channel`   ENUM('whatsapp','web') NOT NULL DEFAULT 'web',
  `wa_msg_id` VARCHAR(100) DEFAULT NULL,
  `timestamp` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`doctor_id`) REFERENCES `doctor_leads`(`id`) ON DELETE CASCADE,
  INDEX idx_doctor_ts (`doctor_id`, `timestamp`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: patient_leads
-- ============================================================
CREATE TABLE IF NOT EXISTS `patient_leads` (
  `id`                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `name`                 VARCHAR(120) NOT NULL,
  `email`                VARCHAR(180) DEFAULT NULL,
  `phone`                VARCHAR(30)  DEFAULT NULL,
  `doctor_source`        VARCHAR(120) DEFAULT NULL,
  `rep_name`             VARCHAR(120) DEFAULT NULL,
  `city`                 VARCHAR(80)  DEFAULT NULL,
  `invitation_date`      DATE DEFAULT NULL,
  `days_since_invitation` INT DEFAULT 0,
  `messages_sent`        INT DEFAULT 0,
  `messages_opened`      INT DEFAULT 0,
  `contact_attempts`     INT DEFAULT 0,
  `pending_payment`      TINYINT(1) DEFAULT 0,
  `payment_amount`       DECIMAL(10,2) DEFAULT 0,
  `funnel_stage`         ENUM('invited','contacted','interested','quote_sent','payment_pending','converted','lost') NOT NULL DEFAULT 'invited',
  `purchase_probability` DECIMAL(5,2) DEFAULT 0,
  `urgency_score`        DECIMAL(5,2) DEFAULT 0,
  `ai_score`             DECIMAL(5,2) DEFAULT 0,
  `recommended_followup` TEXT DEFAULT NULL,
  `ai_reasoning`         TEXT DEFAULT NULL,
  `priority`             ENUM('hot','warm','cold','new') NOT NULL DEFAULT 'new',
  `last_scored`          DATETIME DEFAULT NULL,
  `created_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_funnel (`funnel_stage`),
  INDEX idx_priority (`priority`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: first_contact_sessions
-- ============================================================
CREATE TABLE IF NOT EXISTS `first_contact_sessions` (
  `id`                   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `patient_name`         VARCHAR(120) NOT NULL,
  `email`                VARCHAR(180) DEFAULT NULL,
  `phone`                VARCHAR(30)  DEFAULT NULL,
  `doctor_source`        VARCHAR(120) DEFAULT NULL,
  `rep_name`             VARCHAR(120) DEFAULT NULL,
  `city`                 VARCHAR(80)  DEFAULT NULL,
  `channel`              ENUM('whatsapp','email','web') NOT NULL DEFAULT 'whatsapp',
  `status`               ENUM('pending','contacted','engaged','converted','lost','max_attempts') NOT NULL DEFAULT 'pending',
  `contact_attempts`     INT NOT NULL DEFAULT 0,
  `max_attempts`         INT NOT NULL DEFAULT 5,
  `last_contact_date`    DATE DEFAULT NULL,
  `next_contact_date`    DATE DEFAULT NULL,
  `engagement_score`     DECIMAL(5,2) DEFAULT 0,
  `purchase_probability` DECIMAL(5,2) DEFAULT 0,
  `ai_score`             DECIMAL(5,2) DEFAULT 0,
  `priority`             ENUM('hot','warm','cold','new') NOT NULL DEFAULT 'new',
  `sequence_step`        INT NOT NULL DEFAULT 0,
  `auto_sequence_active` TINYINT(1) NOT NULL DEFAULT 0,
  `messages_log`         JSON DEFAULT NULL,
  `notes`                TEXT DEFAULT NULL,
  `patient_lead_id`      INT UNSIGNED DEFAULT NULL,
  `created_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`           DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`patient_lead_id`) REFERENCES `patient_leads`(`id`) ON DELETE SET NULL,
  INDEX idx_status (`status`),
  INDEX idx_next_contact (`next_contact_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: doctor_contact_sessions
-- ============================================================
CREATE TABLE IF NOT EXISTS `doctor_contact_sessions` (
  `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `doctor_name`         VARCHAR(120) NOT NULL,
  `specialty`           VARCHAR(80)  DEFAULT NULL,
  `hospital`            VARCHAR(180) DEFAULT NULL,
  `email`               VARCHAR(180) DEFAULT NULL,
  `phone`               VARCHAR(30)  DEFAULT NULL,
  `city`                VARCHAR(80)  DEFAULT NULL,
  `rep_name`            VARCHAR(120) DEFAULT NULL,
  `channel`             ENUM('whatsapp','email','web') NOT NULL DEFAULT 'whatsapp',
  `status`              ENUM('pending','contacted','engaged','converted','lost','max_attempts') NOT NULL DEFAULT 'pending',
  `contact_attempts`    INT NOT NULL DEFAULT 0,
  `max_attempts`        INT NOT NULL DEFAULT 5,
  `last_contact_date`   DATE DEFAULT NULL,
  `next_contact_date`   DATE DEFAULT NULL,
  `engagement_score`    DECIMAL(5,2) DEFAULT 0,
  `referral_probability` DECIMAL(5,2) DEFAULT 0,
  `ai_score`            DECIMAL(5,2) DEFAULT 0,
  `priority`            ENUM('hot','warm','cold','new') NOT NULL DEFAULT 'new',
  `sequence_step`       INT NOT NULL DEFAULT 0,
  `auto_sequence_active` TINYINT(1) NOT NULL DEFAULT 0,
  `messages_log`        JSON DEFAULT NULL,
  `notes`               TEXT DEFAULT NULL,
  `doctor_lead_id`      INT UNSIGNED DEFAULT NULL,
  `created_at`          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`doctor_lead_id`) REFERENCES `doctor_leads`(`id`) ON DELETE SET NULL,
  INDEX idx_status (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: results_advisory_sessions
-- ============================================================
CREATE TABLE IF NOT EXISTS `results_advisory_sessions` (
  `id`                  INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `doctor_name`         VARCHAR(120) NOT NULL,
  `specialty`           VARCHAR(80)  DEFAULT NULL,
  `hospital`            VARCHAR(180) DEFAULT NULL,
  `email`               VARCHAR(180) DEFAULT NULL,
  `phone`               VARCHAR(30)  DEFAULT NULL,
  `city`                VARCHAR(80)  DEFAULT NULL,
  `rep_name`            VARCHAR(120) DEFAULT NULL,
  `patient_name`        VARCHAR(120) DEFAULT NULL,
  `test_id`             VARCHAR(80)  DEFAULT NULL,
  `tests_with_results`  INT NOT NULL DEFAULT 1,
  `results_release_date` DATE DEFAULT NULL,
  `channel`             ENUM('whatsapp','email','web') NOT NULL DEFAULT 'whatsapp',
  `status`              ENUM('pending','contacted','engaged','session_requested','session_scheduled','lost','max_attempts') NOT NULL DEFAULT 'pending',
  `contact_attempts`    INT NOT NULL DEFAULT 0,
  `max_attempts`        INT NOT NULL DEFAULT 5,
  `last_contact_date`   DATE DEFAULT NULL,
  `next_contact_date`   DATE DEFAULT NULL,
  `engagement_score`    DECIMAL(5,2) DEFAULT 0,
  `advisory_probability` DECIMAL(5,2) DEFAULT 0,
  `ai_score`            DECIMAL(5,2) DEFAULT 0,
  `priority`            ENUM('hot','warm','cold','new') NOT NULL DEFAULT 'new',
  `messages_log`        JSON DEFAULT NULL,
  `notes`               TEXT DEFAULT NULL,
  `doctor_lead_id`      INT UNSIGNED DEFAULT NULL,
  `created_at`          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at`          DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (`doctor_lead_id`) REFERENCES `doctor_leads`(`id`) ON DELETE SET NULL,
  INDEX idx_status (`status`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: events_log
-- ============================================================
CREATE TABLE IF NOT EXISTS `events_log` (
  `id`          INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `type`        ENUM('ai','sdk','rep','system') NOT NULL DEFAULT 'system',
  `module`      VARCHAR(80) NOT NULL,
  `action`      VARCHAR(120) NOT NULL,
  `params`      JSON DEFAULT NULL,
  `result`      TEXT DEFAULT NULL,
  `latency_ms`  INT DEFAULT NULL,
  `actor`       VARCHAR(120) DEFAULT NULL,
  `user_id`     INT UNSIGNED DEFAULT NULL,
  `created_at`  DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX idx_module_ts (`module`, `created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- TABLA: audit_logs
-- ============================================================
CREATE TABLE IF NOT EXISTS `audit_logs` (
  `id`         INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  `user_id`    INT UNSIGNED DEFAULT NULL,
  `action`     VARCHAR(120) NOT NULL,
  `entity`     VARCHAR(80) NOT NULL,
  `entity_id`  INT UNSIGNED DEFAULT NULL,
  `before`     JSON DEFAULT NULL,
  `after`      JSON DEFAULT NULL,
  `ip`         VARCHAR(45) DEFAULT NULL,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL,
  INDEX idx_entity (`entity`, `entity_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `llm_configs` (`module`, `provider`, `model`, `temperature`) VALUES
('bot_patients',        'openai', 'gpt-4o-mini', 0.70),
('bot_doctors',         'openai', 'gpt-4o-mini', 0.65),
('engagement_patients', 'openai', 'gpt-4o',      0.75),
('engagement_doctors',  'openai', 'gpt-4o',      0.70),
('engagement_advisory', 'openai', 'gpt-4o',      0.65),
('scoring_patients',    'openai', 'gpt-4o-mini', 0.30),
('scoring_doctors',     'openai', 'gpt-4o-mini', 0.30);

INSERT INTO `system_prompts` (`module`, `name`, `content`, `status`) VALUES
('bot_patients', 'Bot WhatsApp Pacientes',
'Eres el asistente de WhatsApp de Novagenic, empresa líder en farmacogenética. Tu objetivo es acompañar al paciente en su proceso de prueba genética de manera cálida, empática y profesional. Responde siempre en español, con mensajes cortos (máx 3 párrafos). Guía al paciente según su etapa actual. Si el paciente pregunta algo médico específico, indícale que un especialista le contactará. Nunca inventes resultados ni plazos. Finaliza siempre con una pregunta o siguiente paso claro.',
'active'),
('bot_doctors', 'Bot WhatsApp Médicos',
'Eres el asistente especializado de Novagenic para médicos. Tu objetivo es informar sobre farmacogenética de manera científica, clara y persuasiva. Habla como colega profesional, no como vendedor. Destaca el valor clínico de las pruebas para la toma de decisiones terapéuticas. Proporciona datos basados en evidencia cuando sea relevante. Facilita el proceso de referir pacientes.',
'active'),
('engagement_patients', 'Engagement Pacientes',
'Genera mensajes de WhatsApp cálidos, personalizados y profesionales para convertir leads de pacientes interesados en compradores de pruebas farmacogenéticas de Novagenic. Los mensajes deben ser naturales, no agresivos comercialmente, y educar sobre los beneficios de la prueba. Cada mensaje debe tener un llamado a la acción claro.',
'active'),
('engagement_doctors', 'Engagement Médicos',
'Genera una secuencia de mensajes para motivar a médicos a referir sus pacientes a Novagenic para pruebas farmacogenéticas. El tono debe ser científico-profesional, destacando el valor clínico y el respaldo de evidencia. Incluye datos de impacto en la práctica clínica. No seas agresivo comercialmente.',
'active'),
('engagement_advisory', 'Engagement Asesorías',
'Genera mensajes para que el médico agende una sesión de interpretación de resultados genéticos de su paciente con el equipo clínico de Novagenic. Enfatiza la importancia de la interpretación correcta, el valor para el paciente y la facilidad del proceso de agendar.',
'active');
