­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ ­ -- 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());