-- ============================================
-- mcloud CRM - MySQL Production Schema
-- © 2026 barqcloud
-- ============================================

SET CHARACTER SET utf8mb4;
SET collation_connection = 'utf8mb4_unicode_ci';

CREATE DATABASE IF NOT EXISTS `mcloud_crm` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE `mcloud_crm`;

-- ============ Pipeline Stages ============
CREATE TABLE IF NOT EXISTS `pipeline_stages` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `stage_key` VARCHAR(50) NOT NULL UNIQUE,
    `name_ar` VARCHAR(100) NOT NULL,
    `name_en` VARCHAR(100) NOT NULL,
    `color` VARCHAR(20) DEFAULT '#4F46E5',
    `sort_order` INT DEFAULT 0,
    `active` TINYINT(1) NOT NULL DEFAULT 1
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============ Opportunities ============
CREATE TABLE IF NOT EXISTS `opportunities` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `name_ar` VARCHAR(300) NOT NULL,
    `name_en` VARCHAR(300) NOT NULL,
    `member_id` VARCHAR(50) DEFAULT '',
    `tag` VARCHAR(50) DEFAULT 'reactivation',
    `tag_ar` VARCHAR(100) DEFAULT '',
    `tag_en` VARCHAR(100) DEFAULT '',
    `expected_amount` DECIMAL(12,2) DEFAULT 0,
    `currency` VARCHAR(10) DEFAULT 'OMR',
    `stage` VARCHAR(50) DEFAULT 'pending',
    `status` ENUM('active','won','lost','cancelled') DEFAULT 'active',
    `owner_id` INT NULL,
    `expected_start_date` DATETIME NULL,
    `priority` ENUM('low','medium','high','critical') DEFAULT 'medium',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_stage` (`stage`),
    INDEX `idx_owner` (`owner_id`),
    INDEX `idx_status` (`status`),
    FOREIGN KEY (`owner_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============ Activities ============
CREATE TABLE IF NOT EXISTS `activities` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `opportunity_id` INT NOT NULL,
    `stage` VARCHAR(50) NOT NULL,
    `activity_type` ENUM('call_activity','meeting_activity') NOT NULL DEFAULT 'call_activity',
    `status` ENUM('pending','completed','cancelled','rescheduled') DEFAULT 'pending',
    `result` VARCHAR(50) DEFAULT NULL,
    `scheduled_date` DATETIME NULL,
    `start_date` DATETIME NULL,
    `end_date` DATETIME NULL,
    `created_by` INT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_opp` (`opportunity_id`),
    INDEX `idx_status` (`status`),
    FOREIGN KEY (`opportunity_id`) REFERENCES `opportunities`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============ Notes ============
CREATE TABLE IF NOT EXISTS `notes` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `opportunity_id` INT NULL,
    `user_id` INT NOT NULL,
    `content` TEXT NOT NULL,
    `note_type` ENUM('general','system','activity','important') DEFAULT 'general',
    `is_pinned` TINYINT(1) DEFAULT 0,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_opp` (`opportunity_id`),
    FOREIGN KEY (`opportunity_id`) REFERENCES `opportunities`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============ Proposals ============
CREATE TABLE IF NOT EXISTS `proposals` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `opportunity_id` INT NOT NULL,
    `title` VARCHAR(300) NOT NULL,
    `amount` DECIMAL(12,2) DEFAULT 0,
    `status` ENUM('draft','sent','accepted','rejected') DEFAULT 'draft',
    `valid_until` DATE NULL,
    `description` TEXT,
    `created_by` INT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_opp` (`opportunity_id`),
    FOREIGN KEY (`opportunity_id`) REFERENCES `opportunities`(`id`) ON DELETE CASCADE,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============ Activity Log ============
CREATE TABLE IF NOT EXISTS `activity_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NULL,
    `action` VARCHAR(100) NOT NULL,
    `detail_ar` TEXT,
    `detail_en` TEXT,
    `ip_address` VARCHAR(45) DEFAULT '',
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user` (`user_id`),
    INDEX `idx_date` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- DEFAULT DATA
-- ============================================

INSERT INTO `pipeline_stages` (`stage_key`,`name_ar`,`name_en`,`color`,`sort_order`) VALUES
('account-management','إدارة الحسابات','Account Management','#3B82F6',1),
('pending','قيد الانتظار','Pending','#EF4444',2),
('development','التطوير','Development','#06B6D4',3),
('proposal','العرض','Proposal','#F59E0B',4),
('closing','الإغلاق','Closing','#10B981',5);

-- Sample opportunities
INSERT INTO `opportunities` (`name_ar`,`name_en`,`member_id`,`tag`,`tag_ar`,`tag_en`,`expected_amount`,`currency`,`stage`,`owner_id`,`expected_start_date`) VALUES
('محمد الشرجي','Mohammed Alsharji','8840001','reactivation','إعادة التنشيط','Reactivation',39,'OMR','pending',1,'2026-01-27'),
('وليد السرمي للسيارات','Walid Al-Sarmi Auto','8840002','reactivation','إعادة التنشيط','Reactivation',79,'OMR','pending',1,'2026-01-27'),
('خالد','Khalid','8840035','reactivation','إعادة التنشيط','Reactivation',90,'OMR','pending',1,'2026-01-27'),
('يونس بن سالم السيابي','Younes Al SEEYABI','8840004','reactivation','إعادة التنشيط','Reactivation',126,'OMR','pending',1,'2026-01-27'),
('أحمد بن سعيد الراحبي','Ahmed Al-Rahbi','8840005','reactivation','إعادة التنشيط','Reactivation',79,'OMR','pending',1,'2026-01-27'),
('سعيد الريامي','Saeed Al Riyami','8840010','reactivation','إعادة التنشيط','Reactivation',120,'OMR','development',1,'2026-01-20'),
('ناصر العامري','Nasser Al Amri','8840011','new','جديد','New',250,'OMR','development',1,'2026-01-15'),
('مؤسسة الفلاح','Al Falah Trading','8840013','new','جديد','New',450,'OMR','proposal',1,'2026-01-10'),
('معرض الصقر','Al Saqr Gallery','8840015','new','جديد','New',580,'OMR','closing',1,'2026-01-05');
