-- =====================================================
-- Outbound Caller App - MySQL Schema
-- Engine: InnoDB | Charset: utf8mb4 | Collation: utf8mb4_general_ci
-- =====================================================

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- -----------------------------------------------------
-- Table: users
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `users` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(100) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `password_hash` VARCHAR(255) NOT NULL,
  `full_name` VARCHAR(255) NOT NULL,
  `role` ENUM('admin', 'agent') NOT NULL DEFAULT 'agent',
  `status` TINYINT NOT NULL DEFAULT 1,
  `created_at` DATETIME NULL DEFAULT NULL,
  `updated_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_username_unique` (`username`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- -----------------------------------------------------
-- Table: categories
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `categories` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NULL DEFAULT NULL,
  `updated_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- -----------------------------------------------------
-- Table: documents
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `documents` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `category_id` INT UNSIGNED NULL DEFAULT NULL,
  `title` VARCHAR(255) NOT NULL,
  `description` TEXT NULL DEFAULT NULL,
  `type` ENUM('pdf', 'docx', 'txt', 'image', 'video', 'link') NOT NULL DEFAULT 'pdf',
  `file_path` VARCHAR(500) NULL DEFAULT NULL,
  `file_size` BIGINT UNSIGNED NULL DEFAULT NULL,
  `mime_type` VARCHAR(255) NULL DEFAULT NULL,
  `url` VARCHAR(500) NULL DEFAULT NULL,
  `content_text` LONGTEXT NULL DEFAULT NULL,
  `summary` TEXT NULL DEFAULT NULL,
  `embedding_status` TINYINT NOT NULL DEFAULT 0,
  `created_at` DATETIME NULL DEFAULT NULL,
  `updated_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `documents_category_id_index` (`category_id`),
  CONSTRAINT `documents_category_id_foreign` FOREIGN KEY (`category_id`) REFERENCES `categories` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- -----------------------------------------------------
-- Table: document_chunks
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `document_chunks` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `document_id` INT UNSIGNED NOT NULL,
  `chunk_index` INT NOT NULL DEFAULT 0,
  `content_text` LONGTEXT NOT NULL,
  `embedding` LONGTEXT NULL DEFAULT NULL,
  `created_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `document_chunks_document_id_index` (`document_id`),
  CONSTRAINT `document_chunks_document_id_foreign` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- -----------------------------------------------------
-- Table: lead_statuses
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lead_statuses` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(100) NOT NULL,
  `color` VARCHAR(50) NULL DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `created_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- -----------------------------------------------------
-- Table: leads
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `leads` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `lead_number` VARCHAR(50) NOT NULL,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NULL DEFAULT NULL,
  `phone` VARCHAR(50) NULL DEFAULT NULL,
  `company_name` VARCHAR(255) NULL DEFAULT NULL,
  `license_number` VARCHAR(100) NULL DEFAULT NULL,
  `status_id` INT UNSIGNED NULL DEFAULT NULL,
  `assigned_to` INT UNSIGNED NULL DEFAULT NULL,
  `created_at` DATETIME NULL DEFAULT NULL,
  `updated_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `leads_lead_number_unique` (`lead_number`),
  KEY `leads_status_id_index` (`status_id`),
  KEY `leads_assigned_to_index` (`assigned_to`),
  CONSTRAINT `leads_status_id_foreign` FOREIGN KEY (`status_id`) REFERENCES `lead_statuses` (`id`) ON DELETE SET NULL,
  CONSTRAINT `leads_assigned_to_foreign` FOREIGN KEY (`assigned_to`) REFERENCES `users` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- -----------------------------------------------------
-- Table: lead_custom_fields
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lead_custom_fields` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `field_name` VARCHAR(100) NOT NULL,
  `field_label` VARCHAR(255) NOT NULL,
  `field_type` ENUM('text', 'textarea', 'select', 'number', 'email', 'date') NOT NULL DEFAULT 'text',
  `options_json` TEXT NULL DEFAULT NULL,
  `sort_order` INT NOT NULL DEFAULT 0,
  `required` TINYINT NOT NULL DEFAULT 0,
  `created_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- -----------------------------------------------------
-- Table: lead_custom_values
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lead_custom_values` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `lead_id` INT UNSIGNED NOT NULL,
  `field_id` INT UNSIGNED NOT NULL,
  `field_value` TEXT NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `lead_custom_values_lead_id_field_id_unique` (`lead_id`, `field_id`),
  KEY `lead_custom_values_field_id_index` (`field_id`),
  CONSTRAINT `lead_custom_values_lead_id_foreign` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `lead_custom_values_field_id_foreign` FOREIGN KEY (`field_id`) REFERENCES `lead_custom_fields` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- -----------------------------------------------------
-- Table: lead_notes
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `lead_notes` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `lead_id` INT UNSIGNED NOT NULL,
  `user_id` INT UNSIGNED NOT NULL,
  `note_text` TEXT NOT NULL,
  `created_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `lead_notes_lead_id_index` (`lead_id`),
  KEY `lead_notes_user_id_index` (`user_id`),
  CONSTRAINT `lead_notes_lead_id_foreign` FOREIGN KEY (`lead_id`) REFERENCES `leads` (`id`) ON DELETE CASCADE,
  CONSTRAINT `lead_notes_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- -----------------------------------------------------
-- Table: chatbot_configs
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `chatbot_configs` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `config_key` VARCHAR(255) NOT NULL,
  `config_value` TEXT NULL DEFAULT NULL,
  `updated_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `chatbot_configs_config_key_unique` (`config_key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- -----------------------------------------------------
-- Table: chat_history
-- -----------------------------------------------------
CREATE TABLE IF NOT EXISTS `chat_history` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` INT UNSIGNED NOT NULL,
  `document_id` INT UNSIGNED NULL DEFAULT NULL,
  `message_role` ENUM('system', 'user', 'assistant') NOT NULL,
  `message_text` LONGTEXT NOT NULL,
  `created_at` DATETIME NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `chat_history_user_id_index` (`user_id`),
  KEY `chat_history_document_id_index` (`document_id`),
  CONSTRAINT `chat_history_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `chat_history_document_id_foreign` FOREIGN KEY (`document_id`) REFERENCES `documents` (`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- =====================================================
-- SEED DATA
-- =====================================================

-- -----------------------------------------------------
-- Seed: users
-- Password hashes generated with PHP password_hash() using PASSWORD_DEFAULT
-- admin123 => $2y$10$...
-- agent123 => $2y$10$...
-- -----------------------------------------------------
INSERT INTO `users` (`id`, `username`, `email`, `password_hash`, `full_name`, `role`, `status`, `created_at`, `updated_at`) VALUES
(1, 'admin', 'admin@example.com', '$2y$10$EixZaYVK1fsbw1ZfbX3OXePaWxn96p36WQoeG6Lruj3vjPGga31lW', 'System Admin', 'admin', 1, NOW(), NOW()),
(2, 'agent', 'agent@example.com', '$2y$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p92ldGxad68LJZdL17lhWy', 'Test Agent', 'agent', 1, NOW(), NOW());

-- -----------------------------------------------------
-- Seed: lead_statuses
-- -----------------------------------------------------
INSERT INTO `lead_statuses` (`id`, `name`, `color`, `sort_order`, `created_at`) VALUES
(1, 'New',          '#3b82f6', 1, NOW()),
(2, 'Contacted',    '#f59e0b', 2, NOW()),
(3, 'Interested',   '#10b981', 3, NOW()),
(4, 'Not Interested', '#ef4444', 4, NOW()),
(5, 'Closed',       '#6b7280', 5, NOW());

-- -----------------------------------------------------
-- Seed: categories
-- -----------------------------------------------------
INSERT INTO `categories` (`id`, `name`, `description`, `sort_order`, `created_at`, `updated_at`) VALUES
(1, 'General',        'General information and company documents.',               1, NOW(), NOW()),
(2, 'Product Info',   'Product specifications, features, and pricing details.',  2, NOW(), NOW()),
(3, 'Sales Scripts',  'Call scripts, rebuttals, and objection handling guides.', 3, NOW(), NOW()),
(4, 'Compliance',     'Compliance documents, legal notices, and regulations.',   4, NOW(), NOW()),
(5, 'FAQs',           'Frequently asked questions and quick reference answers.', 5, NOW(), NOW());

SET FOREIGN_KEY_CHECKS = 1;
