--
-- Host: 127.0.0.1    Database: dev_crm
-- ------------------------------------------------------


--
-- Table structure for table `assign_enquiries`
--

CREATE TABLE `assign_enquiries` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `enquiry_id` int NOT NULL,
  `status` tinyint(1) NOT NULL DEFAULT '0',
  `date` datetime NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `assign_enquiries`
--


--
-- Table structure for table `category_level_four`
--

CREATE TABLE `category_level_four` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category_level_three_id` int DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_level_three_id` (`category_level_three_id`),
  CONSTRAINT `category_level_four_ibfk_1` FOREIGN KEY (`category_level_three_id`) REFERENCES `category_level_three` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `category_level_four`
--

INSERT INTO `category_level_four` VALUES (15,14,'October 2024'),(19,19,'December 2024'),(20,20,'November 2024'),(21,21,'October 2024'),(22,22,'October 2024'),(23,23,'September 2024'),(24,24,'September 2024'),(25,25,'December 2024');

--
-- Table structure for table `category_level_one`
--

CREATE TABLE `category_level_one` (
  `id` int NOT NULL AUTO_INCREMENT,
  `office_id` int DEFAULT NULL,
  `organization_id` int DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `organization_id` (`organization_id`),
  KEY `office_id` (`office_id`),
  CONSTRAINT `category_level_one_ibfk_1` FOREIGN KEY (`organization_id`) REFERENCES `organization` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `category_level_one_ibfk_2` FOREIGN KEY (`office_id`) REFERENCES `office` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `category_level_one`
--

INSERT INTO `category_level_one` VALUES (21,18,NULL,'Energy System'),(27,20,NULL,'Audit');

--
-- Table structure for table `category_level_three`
--

CREATE TABLE `category_level_three` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category_level_two_id` int DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_level_two_id` (`category_level_two_id`),
  CONSTRAINT `category_level_three_ibfk_1` FOREIGN KEY (`category_level_two_id`) REFERENCES `category_level_two` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=30 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `category_level_three`
--

INSERT INTO `category_level_three` VALUES (14,17,'Level 3 Diploma in Accounting and Finance'),(19,22,'Level 7 Diploma in Business Management'),(20,23,'Level 5 Diploma in Health and Social Care Management'),(21,24,'Level 7 Diploma in Information Technology'),(22,25,'Level 5 Diploma in Law'),(23,26,'Level 8 Diploma in Strategic Management and Leadership'),(24,28,'Level 7 Diploma in Leadership'),(25,29,'Level 3 Diploma Foundation in Social Care');

--
-- Table structure for table `category_level_two`
--

CREATE TABLE `category_level_two` (
  `id` int NOT NULL AUTO_INCREMENT,
  `category_level_one_id` int DEFAULT NULL,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `category_level_one_id` (`category_level_one_id`),
  CONSTRAINT `category_level_two_ibfk_1` FOREIGN KEY (`category_level_one_id`) REFERENCES `category_level_one` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=31 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `category_level_two`
--

INSERT INTO `category_level_two` VALUES (17,21,'Accounting and Finance'),(22,27,'Business Management'),(23,27,'Health and Social Care'),(24,21,'Information Systems'),(25,21,'Legal Studies'),(26,21,'Business Studies'),(28,27,'Leadership Studies'),(29,27,'Social Care');

--
-- Table structure for table `complaint_bd`
--

CREATE TABLE `complaint_bd` (
  `id` int NOT NULL AUTO_INCREMENT,
  `enquiry_id` int DEFAULT NULL,
  `to_user` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email_subject` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `email_body` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `type_of` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `status_request` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `complaint_bd`
--


--
-- Table structure for table `config_kpis`
--

CREATE TABLE `config_kpis` (
  `id` int NOT NULL AUTO_INCREMENT,
  `kpi_name` varchar(255) DEFAULT NULL,
  `description_kpis` varchar(500) DEFAULT NULL,
  `on_report` tinyint(1) DEFAULT '0',
  `report_name` varchar(500) DEFAULT NULL,
  `type_of_kpi` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `config_kpis`
--

INSERT INTO `config_kpis` VALUES (1,'CRM Utilization Rate','Measures how effectively the BDE/TSE utilizes CRM tools to manage leads, track interactions, and update client information.',0,'CRM Utilization','Judgement Based'),(2,'Lead Conversion Rate','Account Growth and Management. Percentage of leads successfully converted into paying clients',1,'Lead Conversion','System Based'),(3,'Avg. Sales Cycle','Average time from initial contact to deal closure, with an aim to reduce it',1,'Sales Cycle Length','System Based'),(4,'Follow-up Efficiency','Tracks the speed and effectiveness of follow-ups on leads and client inquiries, impacting conversion and retention. Lead Management and Pipeline Progression. Measures the number of leads in each sales funnel stage, helping balance outreach efforts and ensure a steady flow of conversions',1,'Follow-up Efficiency','System Based'),(5,'Quotation Conversion Rate','Comparing forecasted sales to actual sales to ensure alignment and improve planning accuracy',1,'Quotation Conversion','System Based'),(6,'Client Meetings','Measure ASM’s effectiveness in growing market share within the assigned area recode meetings in CRM.',1,'Client Meetings','System Based'),(8,'Partners/ Dist. Onboarding','Tracks the number of new partnerships or distribution channels established to expand sales reach.',1,'Partners/ Dist. Onboarding','System Based'),(9,'Client Training/ Awareness','Number of product demos, training sessions, or awareness events conducted.',1,'Client Training/ Awareness','System Based'),(10,'Calls Made','Tracks the daily or weekly number of outbound calls each representative makes.',1,'Calls Made','System Based'),(11,'Avg. Talk Time per Call','Measures the average duration of calls, helping assess call quality and engagement.',1,'Avg. Talk Time per Call','System Based'),(12,'Qualifying Leads','Number of leads deemed qualified after initial calls Lead Management and Pipeline Progression.',1,'Qualifying Leads','System Based'),(13,'Appointments-to-Leads Ratio','Tracks how many calls result in an appointment or follow-up meeting, signaling the tele-sales team’s success in progressing the sales pipeline',1,'Appointments-to-Leads','System Based'),(14,'Ineffective Calls Rate','Percentage of unanswered calls or dropped calls, important for maximizing contact attempts',1,'Ineffective Calls Rate','System Based'),(15,'Avg. Deal Size','Measures the average revenue per conversion, reflecting the team\'s ability to upsell or cross-sell effectively',1,'Avg. Deal Size','System Based'),(16,'Brand Visibility','Increase in brand mentions, inquiries, or engagement metrics in the regione client information.',0,'Brand Visibility','Judgement Based');

--
-- Table structure for table `config_kpis_role`
--

CREATE TABLE `config_kpis_role` (
  `id` int NOT NULL AUTO_INCREMENT,
  `weight` int DEFAULT NULL,
  `kpi_id` int DEFAULT NULL,
  `role_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `config_kpis_role`
--


--
-- Table structure for table `config_kpis_role_table`
--

CREATE TABLE `config_kpis_role_table` (
  `id` int NOT NULL AUTO_INCREMENT,
  `start_range` int DEFAULT NULL,
  `end_range` int DEFAULT NULL,
  `weight` int DEFAULT NULL,
  `kpi_role_id` int DEFAULT NULL,
  `role_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `config_kpis_role_table`
--


--
-- Table structure for table `enquiries`
--

CREATE TABLE `enquiries` (
  `id` int NOT NULL AUTO_INCREMENT,
  `enquiry_by` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `enquiry_entered_by` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `income_range` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `enquiry_date` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `username` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `cnic` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `date_of_birth` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `email` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `father_name` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `gender` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `city` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `present_address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `permanent_address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `mobile_no_one` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `mobile_no_two` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `landline` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `project` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `product` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `developer` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `price_offered` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `property_address` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `image` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `source_of_information` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `status` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `enquiry_status` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `remarks` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_one` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_two` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_three` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_four` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_five` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_six` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_seven` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_eight` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_nine` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_ten` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_eleven` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_twelve` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_thirteen` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_fourteen` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_fifteen` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_sixteen` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_seventeen` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_eighteen` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_nineteen` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `field_twenty` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `office_id` int DEFAULT NULL,
  `matured` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `cat_one_id` int DEFAULT NULL,
  `cat_two_id` int DEFAULT NULL,
  `cat_three_id` int DEFAULT NULL,
  `cat_four_id` int DEFAULT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `enquiry_status_steps` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `enquiry_matured_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `office_id` (`office_id`),
  KEY `cat_one_id` (`cat_one_id`),
  KEY `cat_two_id` (`cat_two_id`),
  KEY `cat_three_id` (`cat_three_id`),
  KEY `cat_four_id` (`cat_four_id`),
  CONSTRAINT `enquiries_ibfk_1` FOREIGN KEY (`office_id`) REFERENCES `office` (`id`) ON DELETE CASCADE,
  CONSTRAINT `enquiries_ibfk_2` FOREIGN KEY (`cat_one_id`) REFERENCES `category_level_one` (`id`) ON DELETE CASCADE,
  CONSTRAINT `enquiries_ibfk_3` FOREIGN KEY (`cat_two_id`) REFERENCES `category_level_two` (`id`) ON DELETE CASCADE,
  CONSTRAINT `enquiries_ibfk_4` FOREIGN KEY (`cat_three_id`) REFERENCES `category_level_three` (`id`) ON DELETE CASCADE,
  CONSTRAINT `enquiries_ibfk_5` FOREIGN KEY (`cat_four_id`) REFERENCES `category_level_four` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `enquiries`
--


--
-- Table structure for table `enquiry_fields`
--

CREATE TABLE `enquiry_fields` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `field_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `status` tinyint(1) NOT NULL,
  `type` varchar(11) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `show_field` tinyint(1) NOT NULL DEFAULT '0',
  `report_show` tinyint NOT NULL DEFAULT '0',
  `order_no` int DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=47 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `enquiry_fields`
--

INSERT INTO `enquiry_fields` VALUES (1,'Lead Entered By','enquiry_by',0,'Text',1,0,20),(2,'Cosmetics','enquiry_entered_by',1,'Text',1,0,10),(4,'Income Range (GBP)','income_range',0,'Drop Down',1,0,140),(5,'Visa Status','enquiry_date',0,'Drop Down',1,1,190),(6,'First Name','username',0,'Text',1,1,30),(7,'Middle Name','cnic',0,'Text',1,0,40),(8,'Date of Birth','date_of_birth',0,'Date',1,0,150),(9,'Email','email',0,'Text',1,0,100),(10,'Last Name','father_name',0,'Text',1,1,50),(11,'Gender','gender',0,'Drop Down',1,0,60),(12,'City','city',0,'Text',1,0,130),(13,'Present Address','present_address',0,'Text',1,0,120),(14,'Permanent Address','permanent_address',0,'Text',1,0,110),(15,'Mobile No. ','mobile_no_one',0,'Number',1,1,70),(16,'Mobile No. (secondary)','mobile_no_two',0,'Number',1,0,80),(17,'Landline','landline',0,'Number',1,0,90),(18,'Birth Country','project',0,'Drop Down',1,0,200),(19,'Nationality','product',1,'Text',1,0,210),(20,'Ethnic Origin','developer',0,'Drop Down',1,0,220),(21,'Course Fee (GBP)','price_offered',0,'Number',1,0,160),(22,'Religion','property_address',0,'Drop Down',1,0,230),(23,'Sexual Orientation','image',1,'Drop Down',1,0,240),(24,'Hear About us','source_of_information',0,'Drop Down',1,0,170),(25,'Passport Issue','enquiry_status',0,'Date',1,0,350),(26,'Criminal Conviction','remarks',0,'Drop Down',1,1,180),(27,'Course Title','field_one',0,'Drop Down',1,0,250),(28,'Intake','field_two',0,'Drop Down',1,0,260),(29,'Year','field_three',0,'Drop Down',1,0,270),(30,'Course Start Date','field_four',0,'Date',0,1,280),(31,'Course End Date','field_five',0,'Date',1,0,290),(32,'Place of Study/Campus','field_six',0,'Drop Down',1,0,300),(33,'Visa Required','field_seven',0,'Drop Down',1,0,310),(34,'Visa Valid From','field_eight',0,'Date',1,0,320),(35,'Visa Expiry','field_nine',0,'Date',1,0,330),(36,'Passport No.','field_ten',0,'Text',1,0,340),(37,'field_eleven','field_eleven',0,'Text',1,0,360),(38,'Contact','field_twelve',0,'Text',1,0,370),(39,'field_thirteen','field_thirteen',0,'Text',1,0,380),(40,'field_fourteen','field_fourteen',0,'Text',1,0,390),(41,'field_fifteen','field_fifteen',0,'Text',1,0,400),(42,'field_sixteen','field_sixteen',0,'Text',1,0,410),(43,'field_seventeen','field_seventeen',0,'Text',1,0,420),(44,'field_eighteen','field_eighteen',0,'Text',1,0,430),(45,'field_nineteen','field_nineteen',0,'Text',1,0,440),(46,'field_twenty','field_twenty',0,'Text',1,0,450);

--
-- Table structure for table `enquiry_fields_dropdown`
--

CREATE TABLE `enquiry_fields_dropdown` (
  `id` int NOT NULL AUTO_INCREMENT,
  `dropdown` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `field_name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=85 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `enquiry_fields_dropdown`
--

INSERT INTO `enquiry_fields_dropdown` VALUES (12,'Admission Officer','enquiry_by'),(13,'LAP Centre','enquiry_by'),(14,'Data Entry operator 1','enquiry_entered_by'),(15,'Data Entry operator 2','enquiry_entered_by'),(20,'10,000 - 20,000','income_range'),(21,'20,000 - 30,000','income_range'),(22,'30,000 - 40,000','income_range'),(25,'Social Media','source_of_information'),(26,'Friend & Family Members','source_of_information'),(30,'Matured','enquiry_status'),(31,'Dropped','enquiry_status'),(32,'Follow-up Required','enquiry_status'),(38,'Pakistan','project'),(39,'Pakistan','product'),(40,'Yes','field_seven'),(41,'No','field_seven'),(42,'Granted','enquiry_date'),(43,'Rejected','enquiry_date'),(44,'Pending','enquiry_date'),(45,'Male','gender'),(46,'Female','gender'),(47,'Prefer not to say','gender'),(48,'Islam','property_address'),(49,'Test Field 1','remarks'),(50,'Test Field 2','remarks'),(51,'Test Field 1','developer'),(52,'Test Field 2','developer'),(55,'Test course 1','field_one'),(56,'Test course 2','field_one'),(57,'Test intake 1','field_two'),(58,'Test intake 2','field_two'),(59,'2024','field_three'),(60,'2025','field_three'),(61,'Town ship','field_six'),(62,'Model Town','field_six'),(82,'A','image \r\n'),(83,'B','image \r\n'),(84,'A','image');

--
-- Table structure for table `enquiry_images`
--

CREATE TABLE `enquiry_images` (
  `id` int NOT NULL AUTO_INCREMENT,
  `image_path` text,
  `title` text,
  `enquiry_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `enquiry_images`
--


--
-- Table structure for table `enquiry_products`
--

CREATE TABLE `enquiry_products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `enquiry_id` int DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `enquiry_products`
--


--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  `id` int NOT NULL AUTO_INCREMENT,
  `event_name` varchar(50) DEFAULT NULL,
  `event_description` text,
  `event_date` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `events`
--


--
-- Table structure for table `follow_up`
--

CREATE TABLE `follow_up` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `enquiry_id` int DEFAULT NULL,
  `called_by` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `called_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `called_status` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `remarks` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci,
  `price_offered` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `followup_date` date DEFAULT NULL,
  `next_followup_date` date DEFAULT NULL,
  `created_at` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `follow_up_products` int DEFAULT NULL,
  `next_followup_type` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
  `location` json DEFAULT NULL,
  `dial_start_time` datetime DEFAULT NULL,
  `dial_end_time` datetime DEFAULT NULL,
  `call_type` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `meeting_conducted` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=54 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `follow_up`
--


--
-- Table structure for table `follow_up_products`
--

CREATE TABLE `follow_up_products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_id` int DEFAULT NULL,
  `follow_up_id` int DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `follow_up_fk_id` (`follow_up_id`),
  CONSTRAINT `follow_up_fk_id` FOREIGN KEY (`follow_up_id`) REFERENCES `follow_up` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `follow_up_products`
--


--
-- Table structure for table `marketers`
--

CREATE TABLE `marketers` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL,
  `contact` varchar(50) DEFAULT NULL,
  `business_name` varchar(50) DEFAULT NULL,
  `email` varchar(100) DEFAULT NULL,
  `created_at` date DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `marketers`
--


--
-- Table structure for table `organization`
--

CREATE TABLE `organization` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `currency` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL DEFAULT 'PKR',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `organization`
--

INSERT INTO `organization` VALUES (21,'Enfo','PKR');

--
-- Table structure for table `permissions`
--

CREATE TABLE `permissions` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role_id` int DEFAULT NULL,
  `user_tab` tinyint DEFAULT '0',
  `user_add` tinyint(1) DEFAULT NULL,
  `user_view` tinyint(1) DEFAULT NULL,
  `user_delete` tinyint(1) DEFAULT NULL,
  `user_edit` tinyint(1) DEFAULT NULL,
  `role_tab` tinyint(1) DEFAULT NULL,
  `role_add` tinyint(1) DEFAULT NULL,
  `role_delete` tinyint(1) DEFAULT NULL,
  `organization_tab` tinyint(1) DEFAULT NULL,
  `organization_add` tinyint(1) DEFAULT NULL,
  `organization_delete` tinyint(1) DEFAULT NULL,
  `office_tab` tinyint(1) DEFAULT NULL,
  `office_add` tinyint(1) DEFAULT NULL,
  `office_delete` tinyint(1) DEFAULT NULL,
  `cat_level_one_tab` tinyint(1) DEFAULT NULL,
  `cat_level_one_add` tinyint(1) DEFAULT NULL,
  `cat_level_one_delete` tinyint(1) DEFAULT NULL,
  `cat_level_two_tab` tinyint(1) DEFAULT NULL,
  `cat_level_two_add` tinyint(1) DEFAULT NULL,
  `cat_level_two_delete` tinyint(1) DEFAULT NULL,
  `cat_level_three_tab` tinyint(1) DEFAULT NULL,
  `cat_level_three_add` tinyint(1) DEFAULT NULL,
  `cat_level_three_delete` tinyint(1) DEFAULT NULL,
  `cat_level_four_tab` tinyint(1) DEFAULT NULL,
  `cat_level_four_add` tinyint(1) DEFAULT NULL,
  `cat_level_four_delete` tinyint(1) DEFAULT NULL,
  `enquiry_field_tab` tinyint(1) DEFAULT NULL,
  `enquiries_tab` tinyint(1) DEFAULT NULL,
  `enquiries_add` tinyint(1) DEFAULT NULL,
  `enquiries_delete` tinyint(1) DEFAULT NULL,
  `enquiries_edit` tinyint(1) DEFAULT NULL,
  `enquiries_view` tinyint(1) DEFAULT NULL,
  `enquiries_assign` tinyint(1) DEFAULT NULL,
  `follow_up_tab` tinyint(1) DEFAULT NULL,
  `follow_up_add` tinyint(1) DEFAULT NULL,
  `follow_up_view` tinyint(1) DEFAULT NULL,
  `customer_tab` tinyint(1) DEFAULT '0',
  `followup_report` tinyint NOT NULL DEFAULT '0',
  `enquiry_report` tinyint NOT NULL DEFAULT '0',
  `products_tab` tinyint NOT NULL DEFAULT '0',
  `products_add` tinyint NOT NULL DEFAULT '0',
  `products_edit` tinyint NOT NULL DEFAULT '0',
  `products_delete` tinyint NOT NULL DEFAULT '0',
  `cat_level_four_update` tinyint NOT NULL DEFAULT '0',
  `cat_level_three_update` tinyint NOT NULL DEFAULT '0',
  `cat_level_one_update` tinyint NOT NULL DEFAULT '0',
  `cat_level_two_update` tinyint NOT NULL DEFAULT '0',
  `organization_update` tinyint NOT NULL DEFAULT '0',
  `permissionscol` tinyint NOT NULL DEFAULT '0',
  `location_report` tinyint(1) DEFAULT NULL,
  `role_config_kpi` tinyint(1) DEFAULT NULL,
  `products_inventory` tinyint(1) DEFAULT NULL,
  `products_recevings` tinyint(1) DEFAULT NULL,
  `products_quotations` tinyint(1) DEFAULT NULL,
  `products_quotations_approval` tinyint(1) DEFAULT NULL,
  `products_sales_order` tinyint(1) DEFAULT NULL,
  `performace_kpi_report` tinyint(1) DEFAULT NULL,
  `performace_kpi` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=64 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `permissions`
--

INSERT INTO `permissions` VALUES (51,10,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,NULL,1,1,1,1,1,1,1,1),(52,12,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,NULL,1,1,1,1,1,1,NULL,NULL),(53,11,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(57,16,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,0,0,1,1,1,1,1,1,1,1,1,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(58,17,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(59,18,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(60,19,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,1,1,1,1,1,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(61,20,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,1,0,1,1,0,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(62,22,1,1,1,1,1,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL),(63,23,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,1,1,0,1,1,0,1,1,1,0,0,0,0,0,0,0,0,0,0,0,0,0,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL);

--
-- Table structure for table `product_recevings`
--

CREATE TABLE `product_recevings` (
  `id` int NOT NULL AUTO_INCREMENT,
  `quantity` int DEFAULT NULL,
  `date_recevings` text,
  `product_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `product_recevings`
--

INSERT INTO `product_recevings` VALUES (1,213,NULL,16),(2,123,'2024-11-25T14:27:24.074Z',16),(3,11,'2024-11-25T14:37:03.443Z',18),(4,35,'2025-11-25T14:37:11.136Z',18),(5,10,'2024-11-27T13:11:25.654Z',22),(6,12,'2024-12-04T09:27:03.101Z',27),(7,5,'2024-12-04T09:34:53.493Z',23);

--
-- Table structure for table `product_service_charges`
--

CREATE TABLE `product_service_charges` (
  `id` int NOT NULL AUTO_INCREMENT,
  `range_start` int DEFAULT NULL,
  `range_end` int DEFAULT NULL,
  `unit_price` int DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `unit` varchar(50) DEFAULT NULL,
  `additional_amount` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `product_service_charges`
--

INSERT INTO `product_service_charges` VALUES (1,1,50,0,25,'Kw',0),(2,51,100,400,25,'Kw',0),(3,101,1000,300,25,'Kw',20000);

--
-- Table structure for table `products`
--

CREATE TABLE `products` (
  `id` int NOT NULL AUTO_INCREMENT,
  `product_name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `details` text CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `type_of_product` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `price` int NOT NULL DEFAULT '0',
  `quantity` int DEFAULT NULL,
  `supplier` varchar(255) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=28 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `products`
--

INSERT INTO `products` VALUES (22,'Lvi1','N/A','Product','2024-11-27 09:46:52',20000,110,'Lvi1'),(23,'Lvi3','N/A','Product','2024-11-27 09:47:09',45000,101,'Lvi3'),(24,'Lvi9','N/A','Product','2024-11-27 09:47:28',90000,50,'Lvi9'),(25,'Energy Audit','N/A','Service','2024-11-27 09:47:56',15000,0,'N/A');

--
-- Table structure for table `quotations`
--

CREATE TABLE `quotations` (
  `id` int NOT NULL AUTO_INCREMENT,
  `enquiry_id` int DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `contact` varchar(255) DEFAULT NULL,
  `dated` varchar(255) DEFAULT NULL,
  `to_person` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `subject_quot` varchar(255) DEFAULT NULL,
  `analysis_of` varchar(255) DEFAULT NULL,
  `sincerely_from` varchar(255) DEFAULT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  `approval` enum('Pending','Approved') DEFAULT NULL,
  `is_sales_order` tinyint(1) DEFAULT NULL,
  `user_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `quotations`
--


--
-- Temporary view structure for view `quotations_expiry`
--

/*!50001 DROP VIEW IF EXISTS `quotations_expiry`*/;
/*!50001 CREATE VIEW `quotations_expiry` AS SELECT 
 1 AS `id`*/;

--
-- Table structure for table `quotations_items`
--

CREATE TABLE `quotations_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `quotations_id` int DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `service_category` int DEFAULT NULL,
  `quantity` int DEFAULT NULL,
  `service_units` int DEFAULT NULL,
  `service_units_price` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=196 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `quotations_items`
--

INSERT INTO `quotations_items` VALUES (10,2,24,NULL,10,NULL,NULL),(11,1,24,NULL,100,NULL,NULL),(12,1,25,NULL,NULL,25,NULL),(13,3,24,NULL,100,NULL,NULL),(14,3,25,NULL,NULL,25,NULL),(16,4,27,NULL,24,NULL,NULL),(17,4,24,NULL,1,NULL,NULL),(19,6,27,NULL,5,NULL,NULL),(23,5,27,NULL,5,NULL,NULL),(24,7,27,NULL,10,NULL,NULL),(37,8,27,NULL,24,NULL,NULL),(144,9,22,NULL,1,NULL,NULL),(145,9,23,NULL,1,NULL,NULL),(146,9,24,NULL,1,NULL,NULL),(147,9,25,NULL,NULL,50,NULL),(163,10,22,NULL,110,NULL,NULL),(164,10,23,NULL,101,NULL,NULL),(165,10,24,NULL,50,NULL,NULL),(167,11,22,NULL,110,NULL,NULL),(168,12,24,NULL,1,NULL,NULL),(193,13,24,NULL,1,NULL,NULL),(195,14,24,NULL,1,NULL,NULL);

--
-- Table structure for table `roles`
--

CREATE TABLE `roles` (
  `id` int NOT NULL AUTO_INCREMENT,
  `role` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `self_inquiries` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `roles`
--

INSERT INTO `roles` VALUES (10,'Admin',0),(12,'Admission Manager',0),(18,'Inquiry Officer',1),(19,'Admission Assistant',1),(20,'Recruitment Agent',1),(22,'Test',1),(23,'Sales Officer',1),(25,'Test',0);

--
-- Table structure for table `sales_order`
--

CREATE TABLE `sales_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `enquiry_id` int DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `contact` varchar(255) DEFAULT NULL,
  `dated` varchar(255) DEFAULT NULL,
  `to_person` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `subject_quot` varchar(255) DEFAULT NULL,
  `analysis_of` varchar(255) DEFAULT NULL,
  `sincerely_from` varchar(255) DEFAULT NULL,
  `company_name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `sales_order`
--

INSERT INTO `sales_order` VALUES (1,30,'waseem@enfo.ai','+92-310-162 1444 ','2024-11-26T19:00:00.000Z','Dimotec Engineering Company','Lahore','Quotation for Energy Efficiency analysis',' Dimotec Engineering Company','Waseem Arshad','Industrial Energy Consultant'),(2,29,'xyz@gmail.com','99999999','2024-11-26T19:00:00.000Z','ABC','Lahore','abc','aa','xyz','Energy Consultant');

--
-- Table structure for table `sales_order_items`
--

CREATE TABLE `sales_order_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `quotations_id` int DEFAULT NULL,
  `product_id` int DEFAULT NULL,
  `service_category` int DEFAULT NULL,
  `quantity` int DEFAULT NULL,
  `price` int DEFAULT NULL,
  `service_units` int DEFAULT NULL,
  `service_units_price` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `sales_order_items`
--

INSERT INTO `sales_order_items` VALUES (1,1,23,NULL,96,NULL,NULL,NULL),(2,1,25,NULL,NULL,NULL,10,NULL),(3,2,24,NULL,100,NULL,NULL,NULL),(4,2,25,NULL,NULL,NULL,110,NULL);

--
-- Table structure for table `user_kpis_manual_relation`
--

CREATE TABLE `user_kpis_manual_relation` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `kpi_id` int DEFAULT NULL,
  `weight` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `user_kpis_manual_relation`
--


--
-- Table structure for table `user_location`
--

CREATE TABLE `user_location` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `location` json DEFAULT NULL,
  `at_time` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

--
-- Dumping data for table `user_location`
--

INSERT INTO `user_location` VALUES (1,28,NULL,'2024-12-05T12:56:51.803Z');

--
-- Final view structure for view `quotations_expiry`
--

/*!50001 DROP VIEW IF EXISTS `quotations_expiry`*/;


