--
-- Host: localhost    Database: landscaping
-- ------------------------------------------------------


--
-- Table structure for table `clients`
--

CREATE TABLE `clients` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `address` varchar(255) DEFAULT NULL,
  `contact` varchar(50) DEFAULT NULL,
  `user_id` int NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `clients`
--

INSERT INTO `clients` VALUES (1,'John Doe','123 Elm Street, Springfield','555-1234',2,'2024-11-26 21:50:50','2024-11-27 13:55:35'),(2,'Jane Smith','456 Oak Avenue, Shelbyville','555-5678',2,'2024-11-26 21:50:50','2024-11-27 13:55:35'),(3,'Acme Landscaping Co.','789 Pine Road, Capital City','555-9101',2,'2024-11-26 21:50:50','2024-11-27 13:55:35'),(4,'Daniel','14 main street','dan@msn.com',2,'2024-11-26 22:50:16','2024-11-27 13:55:35'),(5,'John Doe','123 Main St, Springfield','555-123-4567',2,'2024-11-27 13:43:55','2024-11-27 13:55:35'),(6,'Jane Smith','456 Elm St, Metropolis','555-234-5678',2,'2024-11-27 13:43:55','2024-11-27 13:55:35'),(7,'Alice Johnson','789 Oak Ave, Gotham','555-345-6789',2,'2024-11-27 13:43:55','2024-11-27 13:55:35'),(8,'Daniel','114 Old Canal Way','6177502471',2,'2024-11-27 13:51:06','2024-11-27 13:51:06');

--
-- Table structure for table `estimate_items`
--

CREATE TABLE `estimate_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `estimate_id` int NOT NULL,
  `description` varchar(255) NOT NULL,
  `quantity` int NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `total` decimal(10,2) GENERATED ALWAYS AS ((`quantity` * `unit_price`)) STORED,
  PRIMARY KEY (`id`),
  KEY `estimate_id` (`estimate_id`),
  CONSTRAINT `estimate_items_ibfk_1` FOREIGN KEY (`estimate_id`) REFERENCES `estimates` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `estimate_items`
--

INSERT INTO `estimate_items` (`id`, `estimate_id`, `description`, `quantity`, `unit_price`) VALUES (1,1,'Yes',1,97.00),(2,1,'!',1,52.00),(14,6,'Lawn mowing',1,130.00);

--
-- Table structure for table `estimates`
--

CREATE TABLE `estimates` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` int NOT NULL,
  `date` date NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `status` enum('Pending','Approved','Rejected') DEFAULT 'Pending',
  `notes` text,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `estimates_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `estimates`
--

INSERT INTO `estimates` VALUES (1,3,'2024-11-05',149.00,'Pending','This will be to do some work','2024-11-27 11:07:41','2024-11-27 13:55:35',2),(6,3,'2024-11-29',130.00,'Pending',NULL,'2024-11-27 13:01:48','2024-11-27 13:55:35',2),(7,1,'2024-11-15',500.00,'Pending',NULL,'2024-11-27 13:44:43','2024-11-27 13:55:35',2),(8,2,'2024-10-10',750.00,'Pending',NULL,'2024-11-27 13:44:43','2024-11-27 13:55:35',2),(9,3,'2024-11-25',1200.00,'Pending',NULL,'2024-11-27 13:44:43','2024-11-27 13:55:35',2);

--
-- Table structure for table `expenses`
--

CREATE TABLE `expenses` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `category` varchar(100) DEFAULT NULL,
  `date` date NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `description` text,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `expenses_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `expenses`
--

INSERT INTO `expenses` VALUES (1,2,'New','Equipment','2024-11-27',150.00,'');

--
-- Table structure for table `invoice_items`
--

CREATE TABLE `invoice_items` (
  `id` int NOT NULL AUTO_INCREMENT,
  `invoice_id` int NOT NULL,
  `description` varchar(255) NOT NULL,
  `quantity` int NOT NULL DEFAULT '1',
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` decimal(10,2) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `invoice_id` (`invoice_id`),
  CONSTRAINT `invoice_items_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `invoice_items`
--


--
-- Table structure for table `invoices`
--

CREATE TABLE `invoices` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `client_id` int NOT NULL,
  `invoice_number` varchar(50) NOT NULL,
  `issue_date` date NOT NULL,
  `due_date` date NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `status` enum('Paid','Unpaid') NOT NULL DEFAULT 'Unpaid',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `invoice_number` (`invoice_number`),
  KEY `user_id` (`user_id`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `invoices_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `invoices`
--

INSERT INTO `invoices` VALUES (1,2,4,'1000','2024-11-28','2024-11-28',150.00,'Unpaid','2024-11-27 10:54:15','2024-11-27 13:55:35');

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

CREATE TABLE `jobs` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int NOT NULL,
  `client_id` int NOT NULL,
  `name` varchar(255) NOT NULL,
  `description` text,
  `status` enum('pending','in-progress','completed') DEFAULT 'pending',
  `start_date` date DEFAULT NULL,
  `end_date` date DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `client_id` (`client_id`),
  CONSTRAINT `jobs_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`),
  CONSTRAINT `jobs_ibfk_2` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `jobs` VALUES (1,2,4,'est','test','in-progress','2024-12-08','2024-12-17'),(17,2,2,'test','test','in-progress','2024-12-11','2024-12-11');

--
-- Table structure for table `leads`
--

CREATE TABLE `leads` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `phone` varchar(20) NOT NULL,
  `email` varchar(255) NOT NULL,
  `address` varchar(255) NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `leads_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `leads`
--

INSERT INTO `leads` VALUES (8,'Daniel','6177502471','dan@gmail.com','114 old canal way',2);

--
-- Table structure for table `services`
--

CREATE TABLE `services` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `price` decimal(10,2) NOT NULL,
  `duration` varchar(50) NOT NULL,
  `user_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `services_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `services`
--

INSERT INTO `services` VALUES (1,'Lawn Mowing','Cutting Grass',50.00,'3 hours',2);

--
-- Table structure for table `tickets`
--

CREATE TABLE `tickets` (
  `id` int NOT NULL AUTO_INCREMENT,
  `client_id` int NOT NULL,
  `title` varchar(255) NOT NULL,
  `description` text NOT NULL,
  `status` enum('Open','In Progress','Closed') DEFAULT 'Open',
  `priority` enum('Low','Medium','High') DEFAULT 'Low',
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `user_id` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `client_id` (`client_id`),
  KEY `fk_user` (`user_id`),
  CONSTRAINT `fk_user` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE,
  CONSTRAINT `tickets_ibfk_1` FOREIGN KEY (`client_id`) REFERENCES `clients` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `tickets`
--

INSERT INTO `tickets` VALUES (1,1,'Lawn Mowing Request','Request for weekly lawn mowing','Open','Medium','2024-12-02 20:38:03','2024-12-02 20:38:03',2),(2,2,'Garden Cleanup Inquiry','Need assistance with garden cleanup','In Progress','High','2024-12-02 20:38:03','2024-12-02 20:38:03',2),(3,1,'Tree Trimming Service','Request for tree trimming','Open','Low','2024-12-02 20:38:03','2024-12-02 20:38:03',2),(4,2,'Fertilization Schedule','Inquiry about lawn fertilization schedule','Closed','Medium','2024-12-02 20:38:03','2024-12-02 20:38:03',2),(5,1,'Pest Control','Help with pest control for garden','Open','High','2024-12-02 20:38:03','2024-12-02 20:38:03',2),(6,2,'Soil Testing','Need soil quality test','In Progress','Medium','2024-12-02 20:38:03','2024-12-02 20:38:03',2),(7,1,'Mulching','Request for mulch installation','Open','Low','2024-12-02 20:38:03','2024-12-02 20:38:03',2),(8,2,'Irrigation System Repair','Need repair for irrigation system','Closed','High','2024-12-02 20:38:03','2024-12-02 20:38:03',2),(9,1,'Landscape Design','Inquiry about new landscape design','In Progress','High','2024-12-02 20:38:03','2024-12-02 20:38:03',2),(10,2,'Seasonal Cleanup','Need cleanup for the upcoming season','Open','Medium','2024-12-02 20:38:03','2024-12-02 20:38:03',2);


