-- Robi CRM Database Setup SQL
-- Run this in cPanel -> phpMyAdmin

-- Create 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', 'viewer') DEFAULT 'viewer',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

-- Create leads table
CREATE TABLE IF NOT EXISTS leads (
    id INT AUTO_INCREMENT PRIMARY KEY,
    sl 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,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

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

-- Insert sample viewer user (password: viewer123)
INSERT INTO users (name, email, password, role) VALUES 
('Viewer', 'viewer@robicrm.com', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'viewer');

-- Insert all leads data
INSERT 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. Ltd.', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Running'),
(5, 'NZ Tours & Travels', 'HRM', 'ROBI', 'Rakib', '', ''),
(6, 'Infinity Holdings', 'ERP', 'ROBI', 'Rakib', '', ''),
(7, 'Radisson Blu', 'HRM', 'ROBI', 'Rakib', 'Md. Sohel Biswas', ''),
(8, 'Fortune Group', 'ERP', 'ROBI', 'Rakib', '', ''),
(9, 'Chuya Agro', 'ERP', 'ROBI', 'Rakib', 'Md. Taufiq Al Nahian', 'Follow Up'),
(10, 'Medimet', 'ERP', 'ROBI', 'Fahmiza', 'Md. Taufiq Al Nahian', 'Running'),
(11, 'BMA', 'ERP', 'ROBI', 'Fahmiza', 'Md. Sohel Biswas', 'Hold'),
(12, 'Perfect Care', 'SFA', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Hold'),
(13, 'MTB', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Hold'),
(14, 'Mesarse Morsalin Enterprise', 'ERP', 'ROBI', '', 'Md. Sohel Biswas', 'Hold'),
(15, 'Reneta', 'ERP', 'ROBI', 'Fahmiza', 'Md. Sohel Biswas', 'Hold'),
(16, 'C2C', 'ERP', 'ROBI', 'Fahmiza', 'Md. Taufiq Al Nahian', 'Hold'),
(17, 'Everest Pharma', 'ERP', 'ROBI', 'Fahmiza', 'Md. Sohel Biswas', 'Hold'),
(18, 'PHALS Ngo', 'HRM', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Follow Up'),
(19, 'Delta Life Insurance', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Hold'),
(20, 'Bio Take', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Hold'),
(21, 'Food for the Hungry', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Hold'),
(22, 'RK Consumer', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Lost'),
(23, 'Takesell', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Lost'),
(24, 'Jonota Corporation', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Lost'),
(25, 'Khulshi Mart', 'HRM', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Lost'),
(26, 'DPT', 'HRM', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Follow Up'),
(27, 'Ts Packaging', 'Accessory ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Follow Up'),
(28, 'CvC Tiles', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Hold'),
(29, 'Swish Global', 'HRM', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Follow Up'),
(30, 'Bangla Cars', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Follow Up'),
(31, 'Knit Fair', 'Garments', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Lost'),
(32, 'Ambufast', 'HRM', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Lost'),
(33, 'A & A Shoes', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Follow Up'),
(34, 'City Bank', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Hold'),
(35, 'Nagad', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Follow Up'),
(36, 'Country Fields and Farmhouse', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Won'),
(37, 'Printex Solution Ltd', 'Accessory ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Follow Up'),
(38, 'Haleon', 'HRM', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Follow Up'),
(39, 'Umama Fashion', 'HRM', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Lost'),
(40, 'Structure Eng. Ltd.', 'HRM', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Hold'),
(41, 'Rahim Afrooz', 'HRM', 'ROBI', 'Fahmiza', 'Md. Sohel Biswas', 'Lost'),
(42, 'Vista Electronics', '', 'ROBI', 'Fahmiza', 'Md. Sohel Biswas', ''),
(43, 'Mukti Cox''s', '', 'ROBI', 'Rakib', 'Md. Sohel Biswas', ''),
(44, 'Stitch BD Knitwear Ltd.', '', 'ROBI', 'Rakib', 'Md. Sohel Biswas', ''),
(45, 'Saykat Enterprise', '', 'ROBI', '', 'Md. Sohel Biswas', ''),
(46, 'Ansar VDP', '', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Follow Up'),
(47, 'Jahir Group', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', ''),
(48, 'Cellero', 'ERP', 'ROBI', 'Rakib', 'Md. Sohel Biswas', 'Running');