-- Robi CRM Database Setup - Run this in phpMyAdmin

-- Users table
CREATE TABLE IF NOT EXISTS users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin', 'manager', 'salesperson', 'viewer') DEFAULT 'viewer',
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Organizations table
CREATE TABLE IF NOT EXISTS organizations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sl INT DEFAULT NULL,
    name VARCHAR(255) NOT NULL,
    industry VARCHAR(100) DEFAULT NULL,
    website VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(50) DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    address TEXT DEFAULT NULL,
    description TEXT DEFAULT NULL,
    assigned_to INT DEFAULT NULL,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_by INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Contacts table
CREATE TABLE IF NOT EXISTS contacts (
    id INT AUTO_INCREMENT PRIMARY KEY,
    organization_id INT DEFAULT NULL,
    first_name VARCHAR(100) NOT NULL,
    last_name VARCHAR(100) DEFAULT NULL,
    email VARCHAR(255) DEFAULT NULL,
    phone VARCHAR(50) DEFAULT NULL,
    position VARCHAR(100) DEFAULT NULL,
    department VARCHAR(100) DEFAULT NULL,
    is_primary ENUM('yes', 'no') DEFAULT 'no',
    notes TEXT DEFAULT NULL,
    created_by INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Leads table
CREATE TABLE IF NOT EXISTS leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sl INT DEFAULT NULL,
    organization_id INT DEFAULT NULL,
    contact_id INT DEFAULT NULL,
    company VARCHAR(255) NOT NULL,
    solution VARCHAR(100) DEFAULT NULL,
    source VARCHAR(50) DEFAULT 'ROBI',
    salesperson VARCHAR(100) DEFAULT NULL,
    call_handler VARCHAR(100) DEFAULT NULL,
    status VARCHAR(50) DEFAULT '',
    remarks TEXT DEFAULT NULL,
    estimated_value DECIMAL(15,2) DEFAULT NULL,
    probability INT DEFAULT 50,
    next_followup DATE DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Projects table
CREATE TABLE IF NOT EXISTS projects (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sl INT DEFAULT NULL,
    lead_id INT DEFAULT NULL,
    project_name VARCHAR(255) NOT NULL,
    client_name VARCHAR(255),
    solution VARCHAR(100) DEFAULT NULL,
    source VARCHAR(50) DEFAULT 'ROBI',
    salesperson VARCHAR(100) DEFAULT NULL,
    call_handler VARCHAR(100) DEFAULT NULL,
    status VARCHAR(50) DEFAULT '',
    start_date DATE,
    end_date DATE,
    progress INT DEFAULT 0,
    budget DECIMAL(15,2) DEFAULT NULL,
    remarks TEXT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Activities table
CREATE TABLE IF NOT EXISTS activities (
    id INT AUTO_INCREMENT PRIMARY KEY,
    activity_type ENUM('call', 'meeting', 'email', 'task', 'note') NOT NULL,
    subject VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    related_to_type ENUM('lead', 'contact', 'organization', 'project') DEFAULT NULL,
    related_to_id INT DEFAULT NULL,
    assigned_to INT DEFAULT NULL,
    due_date DATETIME DEFAULT NULL,
    completed ENUM('yes', 'no') DEFAULT 'no',
    completed_at DATETIME DEFAULT NULL,
    created_by INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Tasks table
CREATE TABLE IF NOT EXISTS tasks (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    related_to_type ENUM('lead', 'contact', 'organization', 'project', 'milestone') DEFAULT NULL,
    related_to_id INT DEFAULT NULL,
    assigned_to INT DEFAULT NULL,
    priority ENUM('low', 'medium', 'high', 'urgent') DEFAULT 'medium',
    status ENUM('pending', 'in_progress', 'completed', 'cancelled') DEFAULT 'pending',
    due_date DATE DEFAULT NULL,
    completed_at DATETIME DEFAULT NULL,
    created_by INT DEFAULT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Milestones table
CREATE TABLE IF NOT EXISTS milestones (
    id INT AUTO_INCREMENT PRIMARY KEY,
    project_id INT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT DEFAULT NULL,
    due_date DATE DEFAULT NULL,
    status ENUM('pending', 'in_progress', 'completed') DEFAULT 'pending',
    completion_date DATE DEFAULT NULL,
    order_index INT DEFAULT 0,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Comments table
CREATE TABLE IF NOT EXISTS comments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type ENUM('lead', 'contact', 'organization', 'project', 'task', 'milestone') NOT NULL,
    entity_id INT NOT NULL,
    user_id INT NOT NULL,
    comment_text TEXT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Attachments table
CREATE TABLE IF NOT EXISTS attachments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entity_type ENUM('lead', 'contact', 'organization', 'project', 'task', 'milestone') NOT NULL,
    entity_id INT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_type VARCHAR(100) DEFAULT NULL,
    file_size INT DEFAULT NULL,
    uploaded_by INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Settings table
CREATE TABLE IF NOT EXISTS settings (
    id INT AUTO_INCREMENT PRIMARY KEY,
    setting_type VARCHAR(50) NOT NULL,
    setting_key VARCHAR(100) NOT NULL,
    setting_value VARCHAR(255) NOT NULL,
    sort_order INT DEFAULT 0,
    is_active ENUM('yes', 'no') DEFAULT 'yes',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY unique_type_key (setting_type, setting_key)
);

-- Insert default admin user (password: admin123)
INSERT IGNORE INTO users (name, email, password, role, status) VALUES 
('Admin', 'admin@robicrm.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin', 'active');

-- Insert default settings
INSERT IGNORE INTO settings (setting_type, setting_key, setting_value, sort_order) VALUES
('lead_status', 'new', 'New', 1),
('lead_status', 'contacted', 'Contacted', 2),
('lead_status', 'qualified', 'Qualified', 3),
('lead_status', 'won', 'Won', 4),
('lead_status', 'lost', 'Lost', 5),
('lead_status', 'follow_up', 'Follow Up', 6),
('lead_status', 'hold', 'Hold', 7),
('lead_source', 'robi', 'ROBI', 1),
('lead_source', 'website', 'Website', 2),
('lead_source', 'referral', 'Referral', 3),
('lead_source', 'other', 'Other', 4),
('solution', 'erp', 'ERP', 1),
('solution', 'hrm', 'HRM', 2),
('solution', 'sfa', 'SFA', 3),
('solution', 'crm', 'CRM', 4),
('solution', 'other', 'Other', 5),
('industry', 'manufacturing', 'Manufacturing', 1),
('industry', 'retail', 'Retail', 2),
('industry', 'finance', 'Finance & Banking', 3),
('industry', 'healthcare', 'Healthcare', 4),
('industry', 'other', 'Other', 5),
('project_status', 'planning', 'Planning', 1),
('project_status', 'in_progress', 'In Progress', 2),
('project_status', 'completed', 'Completed', 3),
('project_status', 'on_hold', 'On Hold', 4),
('task_priority', 'low', 'Low', 1),
('task_priority', 'medium', 'Medium', 2),
('task_priority', 'high', 'High', 3),
('task_priority', 'urgent', 'Urgent', 4),
('activity_type', 'call', 'Call', 1),
('activity_type', 'meeting', 'Meeting', 2),
('activity_type', 'email', 'Email', 3),
('activity_type', 'task', 'Task', 4),
('activity_type', 'note', 'Note', 5);

-- Insert sample leads
INSERT IGNORE INTO leads (sl, company, solution, source, salesperson, call_handler, status) VALUES
(1, 'Steel Vision', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Won'),
(2, 'AIBL', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', ''),
(3, 'Unison Homoeo Lab', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Running'),
(4, 'City Bank', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Follow Up'),
(5, 'Medimet', 'ERP', 'ROBI', 'Fahmiza', 'Md. Taufiq Al Nahian', 'Running');