--
-- Host: localhost    Database: workforsrilanka
-- ------------------------------------------------------


--
-- Table structure for table `applications`
--

CREATE TABLE `applications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) NOT NULL,
  `status` enum('Applied','Reviewing','Accepted','Rejected') DEFAULT 'Applied',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `employee_nic` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `job_id` (`job_id`),
  KEY `fk_app_emp_nic` (`employee_nic`),
  CONSTRAINT `applications_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`) ON DELETE CASCADE,
  CONSTRAINT `fk_app_emp_nic` FOREIGN KEY (`employee_nic`) REFERENCES `employee_login` (`nic`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `applications`
--

INSERT INTO `applications` VALUES (2,4,'Accepted','2026-02-18 05:00:19','200231103430');

--
-- Table structure for table `customer_hires`
--

CREATE TABLE `customer_hires` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `employee_nic` varchar(20) NOT NULL,
  `status` enum('Pending','In Progress','Completed','Cancelled') DEFAULT 'Pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `customer_hires_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `customer_hires`
--

INSERT INTO `customer_hires` VALUES (1,1,'200231103412','Completed','2026-03-28 05:11:40'),(2,2,'200231103412','Completed','2026-03-30 10:31:39'),(3,2,'200231103413','Pending','2026-04-20 06:23:53'),(4,2,'200234503870','Pending','2026-04-20 06:25:21'),(5,2,'200231103430','Completed','2026-04-20 06:25:31');

--
-- Table structure for table `customer_jobs`
--

CREATE TABLE `customer_jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `category` varchar(100) NOT NULL,
  `district` varchar(100) NOT NULL,
  `description` text DEFAULT NULL,
  `status` enum('open','closed') DEFAULT 'open',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `customer_jobs_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `customer_jobs`
--


--
-- Table structure for table `customer_reviews`
--

CREATE TABLE `customer_reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) NOT NULL,
  `employee_nic` varchar(20) NOT NULL,
  `rating` int(11) DEFAULT NULL CHECK (`rating` >= 1 and `rating` <= 5),
  `comment` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  CONSTRAINT `customer_reviews_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `customer_reviews`
--

INSERT INTO `customer_reviews` VALUES (1,1,'200231103412',4,'Good','2026-03-28 05:16:54');

--
-- Table structure for table `customers`
--

CREATE TABLE `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `phone` varchar(15) NOT NULL,
  `full_name` varchar(100) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  UNIQUE KEY `phone` (`phone`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` VALUES (1,'0778897225','Pubudu Shehan','2026-03-28 05:11:15'),(2,'0778897226','Pubudu Shehan','2026-03-30 10:31:09'),(3,'0778897221','Nimesh Rupasinghe','2026-05-13 07:00:34');

--
-- Table structure for table `districts`
--

CREATE TABLE `districts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `districts`
--

INSERT INTO `districts` VALUES (4,'Ampara'),(7,'Anuradhapura'),(21,'Badulla'),(5,'Batticaloa'),(23,'Colombo'),(18,'Galle'),(24,'Gampaha'),(19,'Hambanthota'),(9,'Jaffna'),(25,'Kalutara'),(1,'Kandy'),(16,'Kegalle'),(10,'Kilinochchi'),(14,'Kurunegala'),(11,'Mannar'),(2,'Matale'),(20,'Matara'),(22,'Monaragala'),(12,'Mullaitivu'),(3,'Nuwara Eliya'),(8,'Polonnaruwa'),(15,'Puttalam'),(17,'Ratnapura'),(6,'Trincomalee'),(13,'Vavuniya');

--
-- Table structure for table `divisional_secretariats`
--

CREATE TABLE `divisional_secretariats` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `district_id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `district_id` (`district_id`),
  CONSTRAINT `divisional_secretariats_ibfk_1` FOREIGN KEY (`district_id`) REFERENCES `districts` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=337 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `divisional_secretariats`
--

INSERT INTO `divisional_secretariats` VALUES (1,1,'Akurana'),(2,1,'Delthota'),(3,1,'Doluwa'),(4,1,'Gangawata Korale'),(5,1,'Ganga Ihala Korale'),(6,1,'Harispattuwa'),(7,1,'Hatharaliyadda'),(8,1,'Kundasale'),(9,1,'Medadumbara'),(10,1,'Minipe'),(11,1,'Panvila'),(12,1,'Pasbage Korale'),(13,1,'Pathadumbara'),(14,1,'Pathahewaheta'),(15,1,'Poojapitiya'),(16,1,'Thumpane'),(17,1,'Udadumbara'),(18,1,'Udapalatha'),(19,1,'Udunuwara'),(20,1,'Yatinuwara'),(21,2,'Ambanganga Korale'),(22,2,'Dambulla'),(23,2,'Galewela'),(24,2,'Laggala-Pallegama'),(25,2,'Matale'),(26,2,'Naula'),(27,2,'Pallepola'),(28,2,'Rattota'),(29,2,'Ukuwela'),(30,2,'Wilgamuwa'),(31,2,'Yatawatta'),(32,3,'Ambagamuwa'),(33,3,'Hanguranketha'),(34,3,'Kothmale'),(35,3,'Nuwara Eliya'),(36,3,'Walapane'),(37,3,'Norwood'),(38,3,'Kothmale West'),(39,3,'Nildandahinna'),(40,3,'Thalawakale'),(41,3,'Mathurata'),(42,4,'Addalachchenai'),(43,4,'Akkaraipattu'),(44,4,'Alayadiwembu'),(45,4,'Ampara'),(46,4,'Damana'),(47,4,'Dehiattakandiya'),(48,4,'Eragama'),(49,4,'Kalmunai Muslim'),(50,4,'Kalmunai Tamil'),(51,4,'Karaitivu'),(52,4,'Lahugala'),(53,4,'Mahaoya'),(54,4,'Navithanveli'),(55,4,'Ninthavur'),(56,4,'Padiyathalawa'),(57,4,'Pothuvil'),(58,4,'Sainthamarathu'),(59,4,'Samanthurai'),(60,4,'Thirukkovil'),(61,4,'Uhana'),(62,5,'Eravur Pattu'),(63,5,'Eravur Town'),(64,5,'Kattankudy'),(65,5,'Koralai Pattu'),(66,5,'Koralai Pattu Central'),(67,5,'Koralai Pattu North'),(68,5,'Koralai Pattu South'),(69,5,'Koralai Pattu West'),(70,5,'Manmunai North'),(71,5,'Manmunai Pattu'),(72,5,'Manmunai S. and Eruvil Pattu'),(73,5,'Manmunai South West'),(74,5,'Manmunai West'),(75,5,'Porativu Pattu'),(76,6,'Gomarankadawala'),(77,6,'Kantalai'),(78,6,'Kinniya'),(79,6,'Kuchchaveli'),(80,6,'Morawewa'),(81,6,'Muttur'),(82,6,'Padavi Sri Pura'),(83,6,'Seruvila'),(84,6,'Thambalagamuwa'),(85,6,'Trincomalee'),(86,6,'Verugal'),(87,7,'Galnewa'),(88,7,'Galenbindunuwewa'),(89,7,'Horowpothana'),(90,7,'Ipalogama'),(91,7,'Kahatagasdigiliya'),(92,7,'Kebithigollewa'),(93,7,'Kekirawa'),(94,7,'Mahavilachchiya'),(95,7,'Medawachchiya'),(96,7,'Mihinthale'),(97,7,'Nachchadoowa'),(98,7,'Nochchiyagama'),(99,7,'Nuwaragam Palatha Central'),(100,7,'Nuwaragam Palatha East'),(101,7,'Padaviya'),(102,7,'Palagala'),(103,7,'Palugaswewa'),(104,7,'Rajanganaya'),(105,7,'Rambewa'),(106,7,'Thalawa'),(107,7,'Thambuttegama'),(108,7,'Thirappane'),(109,8,'Dimbulagala'),(110,8,'Elahera'),(111,8,'Hingurakgoda'),(112,8,'Lankapura'),(113,8,'Medirigiriya'),(114,8,'Thamankaduwa'),(115,8,'Welikanda'),(116,9,'Delft'),(117,9,'Island North'),(118,9,'Island South'),(119,9,'Jaffna'),(120,9,'Karainagar'),(121,9,'Nallur'),(122,9,'Thenmaradchi'),(123,9,'Vadamaradchi East'),(124,9,'Vadamaradchi North'),(125,9,'Vadamaradchi South-West'),(126,9,'Valikamam East'),(127,9,'Valikamam North'),(128,9,'Valikamam South'),(129,9,'Valikamam South-West'),(130,9,'Valikamam West'),(131,10,'Kandavalai'),(132,10,'Karachchi'),(133,10,'Pachchilaipalli'),(134,10,'Poonakary'),(135,11,'Madhu'),(136,11,'Mannar'),(137,11,'Manthai West'),(138,11,'Musalai'),(139,11,'Nanaddan'),(140,12,'Manthai East'),(141,12,'Maritimepattu'),(142,12,'Oddusuddan'),(143,12,'Puthukudiyiruppu'),(144,12,'Thunukkai'),(145,12,'Welioya'),(146,13,'Vavuniya'),(147,13,'Vavuniya North'),(148,13,'Vavuniya South'),(149,13,'Vengalacheddikulam'),(150,14,'Alawwa'),(151,14,'Ambanpola'),(152,14,'Bamunakotuwa'),(153,14,'Bingiriya'),(154,14,'Ehetuwewa'),(155,14,'Galgamuwa'),(156,14,'Ganewatta'),(157,14,'Giribawa'),(158,14,'Ibbagamuwa'),(159,14,'Katupotha'),(160,14,'Kobeigane'),(161,14,'Kotavehera'),(162,14,'Kuliyapitiya East'),(163,14,'Kuliyapitiya West'),(164,14,'Kurunegala'),(165,14,'Mahawa'),(166,14,'Mallawapitiya'),(167,14,'Maspotha'),(168,14,'Mawathagama'),(169,14,'Narammala'),(170,14,'Nikaweratiya'),(171,14,'Panduwasnuwara'),(172,14,'Pannala'),(173,14,'Polgahawela'),(174,14,'Polpithigama'),(175,14,'Rasnayakapura'),(176,14,'Rideegama'),(177,14,'Udubaddawa'),(178,14,'Wariyapola'),(179,14,'Weerambugedara'),(180,15,'Anamaduwa'),(181,15,'Arachchikattuwa'),(182,15,'Chilaw'),(183,15,'Dankotuwa'),(184,15,'Kalpitiya'),(185,15,'Karuwalagaswewa'),(186,15,'Madampe'),(187,15,'Mahakumbukkadawala'),(188,15,'Mahawewa'),(189,15,'Mundalama'),(190,15,'Nattandiya'),(191,15,'Nawagattegama'),(192,15,'Pallama'),(193,15,'Puttalam'),(194,15,'Vanathavilluwa'),(195,15,'Wennappuwa'),(196,16,'Aranayaka'),(197,16,'Bulathkohupitiya'),(198,16,'Dehiovita'),(199,16,'Deraniyagala'),(200,16,'Galigamuwa'),(201,16,'Kegalle'),(202,16,'Mawanella'),(203,16,'Rambukkana'),(204,16,'Ruwanwella'),(205,16,'Warakapola'),(206,16,'Yatiyanthota'),(207,17,'Ayagama'),(208,17,'Balangoda'),(209,17,'Eheliyagoda'),(210,17,'Elapattha'),(211,17,'Embilipitiya'),(212,17,'Godakawela'),(213,17,'Imbulpe'),(214,17,'Kahawatta'),(215,17,'Kalawana'),(216,17,'Kiriella'),(217,17,'Kolonna'),(218,17,'Kuruvita'),(219,17,'Nivithigala'),(220,17,'Opanayaka'),(221,17,'Pelmadulla'),(222,17,'Ratnapura'),(223,17,'Weligepola'),(224,18,'Akmeemana'),(225,18,'Ambalangoda'),(226,18,'Baddegama'),(227,18,'Balapitiya'),(228,18,'Benthota'),(229,18,'Bope-Poddala'),(230,18,'Elpitiya'),(231,18,'Galle'),(232,18,'Gonapinuwala'),(233,18,'Habaraduwa'),(234,18,'Hikkaduwa'),(235,18,'Imaduwa'),(236,18,'Karandeniya'),(237,18,'Nagoda'),(238,18,'Neluwa'),(239,18,'Niyagama'),(240,18,'Thawalama'),(241,18,'Welivitiya-Divithura'),(242,18,'Yakkalamulla'),(243,19,'Ambalantota'),(244,19,'Angunakolapelessa'),(245,19,'Beliatta'),(246,19,'Hambantota'),(247,19,'Katuwana'),(248,19,'Lunugamvehera'),(249,19,'Okewela'),(250,19,'Sooriyawewa'),(251,19,'Tangalle'),(252,19,'Thissamaharama'),(253,19,'Walasmulla'),(254,19,'Weeraketiya'),(255,20,'Akuressa'),(256,20,'Athuraliya'),(257,20,'Devinuwara'),(258,20,'Dickwella'),(259,20,'Hakmana'),(260,20,'Kamburupitiya'),(261,20,'Kirinda Puhulwella'),(262,20,'Kotapola'),(263,20,'Malimbada'),(264,20,'Matara'),(265,20,'Mulatiyana'),(266,20,'Pasgoda'),(267,20,'Pitabeddara'),(268,20,'Thihagoda'),(269,20,'Weligama'),(270,20,'Welipitiya'),(271,21,'Badulla'),(272,21,'Bandarawela'),(273,21,'Ella'),(274,21,'Haldummulla'),(275,21,'Hali-Ela'),(276,21,'Haputale'),(277,21,'Kandaketiya'),(278,21,'Lunugala'),(279,21,'Mahiyanganaya'),(280,21,'Meegahakivula'),(281,21,'Passara'),(282,21,'Rideemaliyadda'),(283,21,'Soranathota'),(284,21,'Uva-Paranagama'),(285,21,'Welimada'),(286,22,'Badalkumbura'),(287,22,'Bibile'),(288,22,'Buttala'),(289,22,'Katharagama'),(290,22,'Madulla'),(291,22,'Medagama'),(292,22,'Moneragala'),(293,22,'Sevanagala'),(294,22,'Siyambalanduwa'),(295,22,'Thanamalvila'),(296,22,'Wellawaya'),(297,23,'Colombo'),(298,23,'Dehiwala'),(299,23,'Homagama'),(300,23,'Kaduwela'),(301,23,'Kesbewa'),(302,23,'Kolonnawa'),(303,23,'Kotte'),(304,23,'Maharagama'),(305,23,'Moratuwa'),(306,23,'Padukka'),(307,23,'Ratmalana'),(308,23,'Seethawaka'),(309,23,'Thimbirigasyaya'),(310,24,'Attanagalla'),(311,24,'Biyagama'),(312,24,'Divulapitiya'),(313,24,'Dompe'),(314,24,'Gampaha'),(315,24,'Ja-Ela'),(316,24,'Katana'),(317,24,'Kelaniya'),(318,24,'Mahara'),(319,24,'Minuwangoda'),(320,24,'Mirigama'),(321,24,'Negombo'),(322,24,'Wattala'),(323,25,'Agalawatta'),(324,25,'Bandaragama'),(325,25,'Beruwala'),(326,25,'Bulathsinhala'),(327,25,'Dodangoda'),(328,25,'Horana'),(329,25,'Ingiriya'),(330,25,'Kalutara'),(331,25,'Madurawela'),(332,25,'Mathugama'),(333,25,'Millaniya'),(334,25,'Palindanuwara'),(335,25,'Panadura'),(336,25,'Walallavita');

--
-- Table structure for table `dme_details`
--

CREATE TABLE `dme_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `full_name` varchar(255) NOT NULL,
  `nic` varchar(20) NOT NULL,
  `district` varchar(100) NOT NULL,
  `divisional_secretariat` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `dme_details_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dme_login` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `dme_details`
--

INSERT INTO `dme_details` VALUES (1,4,'Matheesha siriwardana','200231103870','Matale','Dambulla');

--
-- Table structure for table `employee_details`
--

CREATE TABLE `employee_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `full_name` varchar(255) NOT NULL,
  `dob` date NOT NULL,
  `qualification` varchar(50) NOT NULL,
  `job_category` varchar(100) NOT NULL,
  `nic` varchar(20) NOT NULL,
  `phone` varchar(15) DEFAULT NULL,
  `address` text DEFAULT NULL,
  `district` varchar(50) DEFAULT NULL,
  `divisional_secretariat` varchar(100) DEFAULT NULL,
  `experience` text DEFAULT NULL,
  `profile_pic` varchar(255) DEFAULT NULL,
  `cv_file` varchar(255) DEFAULT NULL,
  `gn_letter` varchar(255) DEFAULT NULL,
  `gn_description` text DEFAULT NULL,
  `achievement_file` varchar(255) DEFAULT NULL,
  `achievement_description` text DEFAULT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  `employee_nic` varchar(20) DEFAULT NULL,
  `nic_front` varchar(255) DEFAULT NULL,
  `nic_back` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_emp_det_nic` (`employee_nic`),
  CONSTRAINT `fk_emp_det_nic` FOREIGN KEY (`employee_nic`) REFERENCES `employee_login` (`nic`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `employee_details`
--

INSERT INTO `employee_details` VALUES (1,'Nimesh','2002-11-06','A/L','Masons','200231103412','0778897222','No: 5/8 Koswana North, Kaikawala','Matale',NULL,'mason','profile_200231103412.JPG',NULL,NULL,NULL,NULL,NULL,'Approved','200231103412',NULL,NULL),(2,'kalana','1998-07-16','NVQ','Masons','200231103413','0778897223','','Galle',NULL,'',NULL,NULL,NULL,NULL,NULL,NULL,'Approved','200231103413',NULL,NULL),(4,'Gayashan Kavindu','2002-10-15','A/L','free lancer','200234503870','778898555','No  54 weragama kaikawala','Matale','Rattota',NULL,'profile_200234503870.jfif',NULL,NULL,NULL,NULL,NULL,'Approved','200234503870',NULL,NULL),(5,'Pubudu Shehan','2002-05-17','NVQ','Electricians','200231103430','0778897226','Police Quarters, Matale','Matale','Matale','','profile_200231103430.jfif','cv_200231103430.pdf',NULL,NULL,NULL,NULL,'Approved','200231103430',NULL,NULL);

--
-- Table structure for table `employer_details`
--

CREATE TABLE `employer_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `company_name` varchar(255) NOT NULL,
  `business_reg_no` varchar(50) NOT NULL,
  `contact_no` varchar(15) NOT NULL,
  `address` text NOT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  PRIMARY KEY (`id`),
  KEY `fk_empr_det_user` (`user_id`),
  CONSTRAINT `fk_empr_det_user` FOREIGN KEY (`user_id`) REFERENCES `employer_login` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `employer_details`
--

INSERT INTO `employer_details` VALUES (1,1,'sofa','','0778897222','matale','Approved'),(2,2,'biofoods','','0778897221','matale','Approved'),(4,4,'abans','ab123456','077777777','matale','Approved');

--
-- Table structure for table `job_categories`
--

CREATE TABLE `job_categories` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=32 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `job_categories`
--

INSERT INTO `job_categories` VALUES (13,'A/C'),(16,'Aluminium'),(2,'Carpenters'),(24,'CCTV'),(18,'Ceiling'),(20,'Chair Weavers'),(21,'Cleaners'),(12,'Concrete Slab'),(10,'Contractors'),(28,'Curtains'),(14,'Cushion Works'),(5,'Electricians'),(9,'Equipment Repairing'),(15,'Gully Bowser'),(27,'House Demolishers'),(7,'Landscaping'),(1,'Masons'),(29,'Movers'),(31,'Other'),(6,'Painters'),(30,'Pest Control'),(4,'Plumbers'),(11,'Professionals'),(22,'Rent Tools'),(19,'Repairs & Others'),(26,'Solar Panel Fixing'),(8,'Stones/ Sand/ Soil'),(3,'Tile'),(23,'Vehicle Repairs'),(25,'Welding'),(17,'Well');

--
-- Table structure for table `jobs`
--

CREATE TABLE `jobs` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employer_id` int(11) NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `category` varchar(100) NOT NULL,
  `location` varchar(100) NOT NULL,
  `status` enum('Pending','Open','Closed','Rejected') DEFAULT 'Pending',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `fk_job_employer` (`employer_id`),
  CONSTRAINT `fk_job_employer` FOREIGN KEY (`employer_id`) REFERENCES `employer_login` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `jobs`
--

INSERT INTO `jobs` VALUES (4,2,'Cleaner','Salary = 35000','Cleaners','Colombo','Open','2026-02-18 04:59:25');

--
-- Table structure for table `messages`
--

CREATE TABLE `messages` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `hire_id` int(11) NOT NULL,
  `sender_type` enum('customer','worker') NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(4) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `hire_id` (`hire_id`),
  CONSTRAINT `messages_ibfk_1` FOREIGN KEY (`hire_id`) REFERENCES `customer_hires` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `messages`
--

INSERT INTO `messages` VALUES (1,1,'worker','Hi',1,'2026-03-28 06:15:39'),(2,2,'worker','fxbfx',1,'2026-03-30 10:32:15');

--
-- Table structure for table `reviews`
--

CREATE TABLE `reviews` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `job_id` int(11) NOT NULL,
  `employer_id` int(11) NOT NULL,
  `rating` int(11) DEFAULT NULL CHECK (`rating` >= 1 and `rating` <= 5),
  `comment` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `employee_nic` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `job_id` (`job_id`),
  KEY `fk_review_employer` (`employer_id`),
  KEY `fk_review_emp_nic` (`employee_nic`),
  CONSTRAINT `fk_review_emp_nic` FOREIGN KEY (`employee_nic`) REFERENCES `employee_login` (`nic`) ON DELETE CASCADE,
  CONSTRAINT `fk_review_employer` FOREIGN KEY (`employer_id`) REFERENCES `employer_login` (`id`) ON DELETE CASCADE,
  CONSTRAINT `reviews_ibfk_1` FOREIGN KEY (`job_id`) REFERENCES `jobs` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `reviews`
--

INSERT INTO `reviews` VALUES (1,4,2,3,'Good as worker','2026-02-18 05:01:05','200231103430');

--
-- Table structure for table `worker_notifications`
--

CREATE TABLE `worker_notifications` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `employee_nic` varchar(20) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `hire_id` int(11) NOT NULL,
  `message` text NOT NULL,
  `is_read` tinyint(1) DEFAULT 0,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `customer_id` (`customer_id`),
  KEY `hire_id` (`hire_id`),
  CONSTRAINT `worker_notifications_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE CASCADE,
  CONSTRAINT `worker_notifications_ibfk_2` FOREIGN KEY (`hire_id`) REFERENCES `customer_hires` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `worker_notifications`
--

INSERT INTO `worker_notifications` VALUES (1,'200231103412',2,2,'A new customer (Pubudu Shehan) has requested to hire you. Phone: 0778897226',0,'2026-03-30 10:31:39'),(2,'200231103413',2,3,'A new customer (Matheesha siriwardana) has requested to hire you. Phone: 0778897226',0,'2026-04-20 06:23:53'),(3,'200234503870',2,4,'A new customer (Matheesha siriwardana) has requested to hire you. Phone: 0778897226',0,'2026-04-20 06:25:21'),(4,'200231103430',2,5,'A new customer (Matheesha siriwardana) has requested to hire you. Phone: 0778897226',0,'2026-04-20 06:25:31');


