22

✏️ Editing: schema.sql

-- LeadCRM schema (MySQL 8)
SET NAMES utf8mb4;
SET time_zone = '+03:00';

DROP TABLE IF EXISTS sms_outbox;
DROP TABLE IF EXISTS activity_logs;
DROP TABLE IF EXISTS opportunities;
DROP TABLE IF EXISTS leads;
DROP TABLE IF EXISTS contacts;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS roles;
DROP TABLE IF EXISTS branches;

CREATE TABLE branches (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name_en VARCHAR(120) NOT NULL,
  name_ar VARCHAR(120) NOT NULL,
  city VARCHAR(80) NOT NULL,
  location_url VARCHAR(255) NULL,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE roles (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  code VARCHAR(50) UNIQUE NOT NULL,
  name_en VARCHAR(80) NOT NULL,
  name_ar VARCHAR(80) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  role_id BIGINT NOT NULL,
  branch_id BIGINT NULL,
  supervisor_user_id BIGINT NULL,
  name VARCHAR(120) NOT NULL,
  mobile VARCHAR(20) NOT NULL,
  email VARCHAR(150) NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  language_pref ENUM('en','ar') NOT NULL DEFAULT 'en',
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  FOREIGN KEY (role_id) REFERENCES roles(id),
  FOREIGN KEY (branch_id) REFERENCES branches(id),
  FOREIGN KEY (supervisor_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE contacts (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  mobile VARCHAR(20) NOT NULL,
  country_code VARCHAR(10) NOT NULL DEFAULT '+966',
  full_name VARCHAR(160) NULL,
  email VARCHAR(150) NULL,
  nationality VARCHAR(80) NULL,
  gender ENUM('male','female','unknown') NOT NULL DEFAULT 'unknown',
  city VARCHAR(80) NULL,
  notes TEXT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  UNIQUE KEY uniq_contact_mobile (country_code, mobile)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE leads (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  contact_id BIGINT NOT NULL,
  source VARCHAR(80) NULL,
  campaign VARCHAR(120) NULL,
  lead_city VARCHAR(80) NULL,
  status ENUM('new','assigned','in_progress','contacted','qualified','disqualified','closed') NOT NULL DEFAULT 'new',
  assigned_to_user_id BIGINT NULL,
  assigned_by_user_id BIGINT NULL,
  assigned_at DATETIME NULL,
  first_view_at DATETIME NULL,
  first_success_contact_at DATETIME NULL,
  qualification ENUM('unknown','qualified','disqualified') NOT NULL DEFAULT 'unknown',
  disqualify_reason VARCHAR(255) NULL,
  qualified_branch_id BIGINT NULL,
  converted_opportunity_id BIGINT NULL,
  created_by_user_id BIGINT NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  FOREIGN KEY (contact_id) REFERENCES contacts(id),
  FOREIGN KEY (assigned_to_user_id) REFERENCES users(id),
  FOREIGN KEY (assigned_by_user_id) REFERENCES users(id),
  FOREIGN KEY (qualified_branch_id) REFERENCES branches(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE opportunities (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  lead_id BIGINT NOT NULL,
  contact_id BIGINT NOT NULL,
  branch_id BIGINT NOT NULL,
  status ENUM('new','assigned','in_progress','contacted','won','lost','closed') NOT NULL DEFAULT 'new',
  assigned_to_user_id BIGINT NULL,
  assigned_by_user_id BIGINT NULL,
  assigned_at DATETIME NULL,
  first_view_at DATETIME NULL,
  first_contact_at DATETIME NULL,
  stage ENUM('stage1','stage2','stage3') NOT NULL DEFAULT 'stage1',
  stage_status VARCHAR(60) NULL,
  loss_reason VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  updated_at DATETIME NULL,
  FOREIGN KEY (lead_id) REFERENCES leads(id),
  FOREIGN KEY (contact_id) REFERENCES contacts(id),
  FOREIGN KEY (branch_id) REFERENCES branches(id),
  FOREIGN KEY (assigned_to_user_id) REFERENCES users(id),
  FOREIGN KEY (assigned_by_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE activity_logs (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  entity_type ENUM('lead','opportunity','contact','user','system') NOT NULL,
  entity_id BIGINT NOT NULL,
  action VARCHAR(60) NOT NULL,
  actor_user_id BIGINT NULL,
  meta_json JSON NULL,
  ip VARCHAR(45) NULL,
  user_agent VARCHAR(255) NULL,
  created_at DATETIME NOT NULL,
  INDEX idx_entity (entity_type, entity_id),
  FOREIGN KEY (actor_user_id) REFERENCES users(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE sms_outbox (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  to_number VARCHAR(30) NOT NULL,
  message TEXT NOT NULL,
  provider VARCHAR(50) NULL,
  status ENUM('queued','sent','failed') NOT NULL DEFAULT 'queued',
  provider_message_id VARCHAR(120) NULL,
  error_message VARCHAR(255) NULL,
  related_entity_type ENUM('lead','opportunity') NULL,
  related_entity_id BIGINT NULL,
  created_by_user_id BIGINT NULL,
  created_at DATETIME NOT NULL,
  sent_at DATETIME NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed data
INSERT INTO branches(name_en,name_ar,city,location_url,is_active,created_at) VALUES
('Riyadh Branch','فرع الرياض','Riyadh','https://maps.google.com/?q=Riyadh',1,NOW()),
('Jeddah Branch','فرع جدة','Jeddah','https://maps.google.com/?q=Jeddah',1,NOW());

INSERT INTO roles(code,name_en,name_ar) VALUES
('ADMIN','Admin','مدير النظام'),
('MARKETING','Marketing','التسويق'),
('CC_SUP','Call Center Supervisor','مشرف مركز الاتصال'),
('CC_AGENT','Call Center Agent','موظف مركز الاتصال'),
('BR_MGR','Sales Branch Manager','مدير فرع المبيعات'),
('BR_AGENT','Sales Branch Agent','مندوب مبيعات الفرع'),
('SALES_MGR','Sales Manager','مدير المبيعات'),
('GM_SALES','GM Sales','المدير العام للمبيعات');

-- Password is Password@123
-- Hash generated with PHP password_hash
INSERT INTO users(role_id,branch_id,supervisor_user_id,name,mobile,email,password_hash,language_pref,is_active,created_at) VALUES
((SELECT id FROM roles WHERE code='ADMIN'),NULL,NULL,'Admin','500000001','admin@example.com','$2y$10$h7vS4t8mGmY2L3u6h0cZ0eI3ZJp7j8bL0x5b9uP7c5mHqWz9bJb7K','en',1,NOW()),
((SELECT id FROM roles WHERE code='MARKETING'),NULL,NULL,'Marketing','500000002','marketing@example.com','$2y$10$h7vS4t8mGmY2L3u6h0cZ0eI3ZJp7j8bL0x5b9uP7c5mHqWz9bJb7K','en',1,NOW()),
((SELECT id FROM roles WHERE code='CC_SUP'),NULL,NULL,'CC Supervisor','500000003','ccsup@example.com','$2y$10$h7vS4t8mGmY2L3u6h0cZ0eI3ZJp7j8bL0x5b9uP7c5mHqWz9bJb7K','en',1,NOW()),
((SELECT id FROM roles WHERE code='CC_AGENT'),NULL,(SELECT id FROM users WHERE email='ccsup@example.com'),'CC Agent','500000004','ccagent@example.com','$2y$10$h7vS4t8mGmY2L3u6h0cZ0eI3ZJp7j8bL0x5b9uP7c5mHqWz9bJb7K','en',1,NOW()),
((SELECT id FROM roles WHERE code='BR_MGR'),(SELECT id FROM branches WHERE name_en='Riyadh Branch'),NULL,'Branch Manager','500000005','brmgr@example.com','$2y$10$h7vS4t8mGmY2L3u6h0cZ0eI3ZJp7j8bL0x5b9uP7c5mHqWz9bJb7K','en',1,NOW()),
((SELECT id FROM roles WHERE code='BR_AGENT'),(SELECT id FROM branches WHERE name_en='Riyadh Branch'),NULL,'Branch Agent','500000006','bragent@example.com','$2y$10$h7vS4t8mGmY2L3u6h0cZ0eI3ZJp7j8bL0x5b9uP7c5mHqWz9bJb7K','en',1,NOW()),
((SELECT id FROM roles WHERE code='SALES_MGR'),NULL,NULL,'Sales Manager','500000007','salesmgr@example.com','$2y$10$h7vS4t8mGmY2L3u6h0cZ0eI3ZJp7j8bL0x5b9uP7c5mHqWz9bJb7K','en',1,NOW()),
((SELECT id FROM roles WHERE code='GM_SALES'),NULL,NULL,'GM Sales','500000008','gmsales@example.com','$2y$10$h7vS4t8mGmY2L3u6h0cZ0eI3ZJp7j8bL0x5b9uP7c5mHqWz9bJb7K','en',1,NOW());

MR SKB