-- Create database
CREATE DATABASE IF NOT EXISTS crm_database;
USE crm_database;

-- Profiles table (Admin and Salesmen)
CREATE TABLE IF NOT EXISTS profiles (
  id VARCHAR(36) PRIMARY KEY,
  role ENUM('admin', 'salesman') NOT NULL,
  full_name VARCHAR(255) NOT NULL,
  contact_number VARCHAR(50) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  password VARCHAR(255) NOT NULL,
  designation VARCHAR(255),
  photo_url TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Customers table
CREATE TABLE IF NOT EXISTS customers (
  id VARCHAR(36) PRIMARY KEY,
  full_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NOT NULL,
  contact_number VARCHAR(50) NOT NULL,
  address TEXT,
  city VARCHAR(100),
  state VARCHAR(100),
  photo_url TEXT,
  resume_url TEXT,
  assigned_to VARCHAR(36),
  status ENUM('new', 'contacted', 'meeting_scheduled', 'converted', 'rejected') DEFAULT 'new',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (assigned_to) REFERENCES profiles(id) ON DELETE SET NULL
);

-- Remarks table
CREATE TABLE IF NOT EXISTS remarks (
  id VARCHAR(36) PRIMARY KEY,
  customer_id VARCHAR(36) NOT NULL,
  salesman_id VARCHAR(36) NOT NULL,
  remark_text TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  FOREIGN KEY (salesman_id) REFERENCES profiles(id) ON DELETE CASCADE
);

-- Meetings table
CREATE TABLE IF NOT EXISTS meetings (
  id VARCHAR(36) PRIMARY KEY,
  customer_id VARCHAR(36) NOT NULL,
  salesman_id VARCHAR(36) NOT NULL,
  meeting_date DATETIME NOT NULL,
  discussion_notes TEXT NOT NULL,
  outcome TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  FOREIGN KEY (salesman_id) REFERENCES profiles(id) ON DELETE CASCADE
);

-- Follow-ups table
CREATE TABLE IF NOT EXISTS follow_ups (
  id VARCHAR(36) PRIMARY KEY,
  customer_id VARCHAR(36) NOT NULL,
  salesman_id VARCHAR(36) NOT NULL,
  scheduled_date DATETIME NOT NULL,
  purpose TEXT NOT NULL,
  completed BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE,
  FOREIGN KEY (salesman_id) REFERENCES profiles(id) ON DELETE CASCADE
);

-- Job Categories table
CREATE TABLE IF NOT EXISTS job_categories (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(255) NOT NULL,
  description TEXT,
  image_url TEXT,
  job_count INT DEFAULT 0,
  is_active BOOLEAN DEFAULT TRUE,
  display_order INT DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Create indexes for better performance
CREATE INDEX idx_customers_assigned_to ON customers(assigned_to);
CREATE INDEX idx_customers_status ON customers(status);
CREATE INDEX idx_remarks_customer_id ON remarks(customer_id);
CREATE INDEX idx_meetings_customer_id ON meetings(customer_id);
CREATE INDEX idx_follow_ups_customer_id ON follow_ups(customer_id);
CREATE INDEX idx_follow_ups_completed ON follow_ups(completed);
CREATE INDEX idx_job_categories_active ON job_categories(is_active);
CREATE INDEX idx_job_categories_order ON job_categories(display_order);

-- Insert default admin user (password: admin123)
INSERT INTO profiles (id, role, full_name, contact_number, email, password, designation)
VALUES (
  UUID(),
  'admin',
  'Admin User',
  '+91 1234567890',
  'admin@hireloomglobal.com',
  '$2a$10$vI8aWBnW3fID.ZQ4/zo1G.q1lRps.9cGLcZEiGDMVr5yUP1KUOYTa',
  'Administrator'
);

-- Insert sample job categories
INSERT INTO job_categories (title, description, job_count, display_order) VALUES
('Software Development', 'Full-stack developers, mobile app developers, and software engineers', 150, 1),
('Data Science & Analytics', 'Data scientists, machine learning engineers, and business analysts', 85, 2),
('Digital Marketing', 'SEO specialists, content marketers, and social media managers', 120, 3),
('Design & Creative', 'UI/UX designers, graphic designers, and creative directors', 95, 4),
('Sales & Business Development', 'Sales executives, account managers, and business development professionals', 110, 5),
('Human Resources', 'HR managers, recruiters, and talent acquisition specialists', 70, 6),
('Finance & Accounting', 'Accountants, financial analysts, and finance managers', 65, 7),
('Customer Support', 'Customer service representatives and support specialists', 80, 8),
('Operations & Management', 'Project managers, operations managers, and team leads', 90, 9),
('Healthcare', 'Nurses, doctors, medical assistants, and healthcare professionals', 100, 10),
('Education & Training', 'Teachers, trainers, and educational coordinators', 75, 11),
('Engineering', 'Mechanical, electrical, civil engineers and technical specialists', 105, 12);