-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `zuriel_invoice_system`
--

-- --------------------------------------------------------

--
-- Table structure for table `config`
--

CREATE TABLE `config` (
  `config_key` varchar(100) NOT NULL,
  `config_value` text DEFAULT NULL,
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `config`
--

INSERT INTO `config` (`config_key`, `config_value`, `updated_at`) VALUES
('COMPANY_ADDRESS', 'Shop 1-041, Area 1 Shopping Plaza, Garki Abuja', '2025-11-25 11:51:49'),
('COMPANY_EMAIL', 'zurieltechventures@gmail.com', '2025-11-25 11:51:49'),
('COMPANY_LOGO', '/zuriel-invoice-system/public/images/logo.png', '2025-11-25 11:42:40'),
('COMPANY_NAME', 'ZURIEL TECH VENTURES', '2025-11-25 11:51:49'),
('COMPANY_PHONE_1', '+234 (0) 908 444 4240', '2025-11-25 11:51:49'),
('COMPANY_PHONE_2', '+234 (0) 908 444 4140', '2025-11-25 11:51:49'),
('COMPANY_TAGLINE', 'Innovating Tomorrow, Today', '2025-11-25 11:51:49'),
('CURRENCY_NAME', 'Naira', '2025-11-25 11:51:49'),
('CURRENCY_SYMBOL_MAJOR', '₦', '2025-11-25 11:51:49'),
('CURRENCY_SYMBOL_MINOR', 'K', '2025-11-25 11:51:49'),
('HEADER_BG_COLOR', '#0066cc', '2025-11-25 11:51:49'),
('INVOICE_PREFIX', 'INV', '2025-11-25 11:51:49'),
('INVOICE_START_NUMBER', '1', '2025-11-25 11:51:49'),
('PRIMARY_COLOR', '#0066cc', '2025-11-25 11:51:49'),
('RECEIPT_PREFIX', 'RCP', '2025-11-25 11:51:49'),
('RECEIPT_START_NUMBER', '1', '2025-11-25 11:51:49');

-- --------------------------------------------------------

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

CREATE TABLE `customers` (
  `id` int(11) NOT NULL,
  `name` varchar(255) NOT NULL,
  `address` text DEFAULT NULL,
  `phone` varchar(50) DEFAULT NULL,
  `email` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `customers` (`id`, `name`, `address`, `phone`, `email`, `created_at`, `updated_at`) VALUES
(4, 'SHEMAIAH WAMBEBE YABA-SHIAKA', 'NO. 4C, ZONE D, MILLONAIRE QUATERS, BYAZHIN, KUBWA, ABUJA', '09039988198', 'apostleshem17@gmail.com', '2025-11-22 21:07:26', '2025-11-22 21:07:26');

-- --------------------------------------------------------

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

CREATE TABLE `invoices` (
  `id` int(11) NOT NULL,
  `invoice_number` varchar(50) NOT NULL,
  `customer_id` int(11) DEFAULT NULL,
  `customer_name` varchar(255) NOT NULL,
  `customer_address` text DEFAULT NULL,
  `invoice_date` date NOT NULL,
  `lpo_number` varchar(100) DEFAULT NULL,
  `subtotal` decimal(10,2) NOT NULL DEFAULT 0.00,
  `total` decimal(10,2) NOT NULL DEFAULT 0.00,
  `paid_amount` decimal(10,2) DEFAULT 0.00,
  `amount_in_words` text DEFAULT NULL,
  `invoice_type` enum('cash','credit') DEFAULT 'cash',
  `status` enum('draft','issued','paid','archived') DEFAULT 'issued',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `invoices` (`id`, `invoice_number`, `customer_id`, `customer_name`, `customer_address`, `invoice_date`, `lpo_number`, `subtotal`, `total`, `paid_amount`, `amount_in_words`, `invoice_type`, `status`, `created_at`, `updated_at`) VALUES
(1, 'INV-00001', 4, 'SHEMAIAH WAMBEBE YABA-SHIAKA', 'NO. 4C, ZONE D, MILLONAIRE QUATERS, BYAZHIN, KUBWA, ABUJA', '2025-11-22', '12089012', 250000.00, 250000.00, 250000.00, 'Two Hundred Fifty Thousand Naira Only', 'cash', 'paid', '2025-11-22 21:08:24', '2025-11-25 19:23:35'),
(5, 'INV-00002', 4, 'SHEMAIAH WAMBEBE YABA-SHIAKA', 'NO. 4C, ZONE D, MILLONAIRE QUATERS, BYAZHIN, KUBWA, ABUJA', '2025-11-25', '12089012', 250000.00, 250000.00, 0.00, 'Two Hundred Fifty Thousand Naira Only', 'cash', 'issued', '2025-11-25 19:24:26', '2025-11-25 19:24:26');

-- --------------------------------------------------------

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

CREATE TABLE `invoice_items` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `qty` int(11) NOT NULL,
  `description` text NOT NULL,
  `rate` decimal(10,2) NOT NULL,
  `amount` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `invoice_items` (`id`, `invoice_id`, `qty`, `description`, `rate`, `amount`) VALUES
(1, 1, 1, 'Mobile App Development', 250000.00, 250000.00),
(2, 5, 1, 'Mobile App Development', 250000.00, 250000.00);

-- --------------------------------------------------------

--
-- Table structure for table `login_attempts`
--

CREATE TABLE `login_attempts` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `ip_address` varchar(45) NOT NULL,
  `success` tinyint(1) DEFAULT 0,
  `attempted_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `login_attempts`
--

INSERT INTO `login_attempts` (`id`, `username`, `ip_address`, `success`, `attempted_at`) VALUES
(1, 'admin@example.com', '127.0.0.1', 0, '2025-11-22 22:14:26'),
(2, 'shemaiah', '127.0.0.1', 1, '2025-11-22 22:15:06'),
(3, 'shemaiah', '127.0.0.1', 1, '2025-11-22 22:17:46'),
(4, 'piano', '127.0.0.1', 1, '2025-11-22 22:19:11'),
(5, 'shemaiah', '127.0.0.1', 1, '2025-11-22 22:20:24'),
(6, 'shemaiah', '127.0.0.1', 1, '2025-11-25 11:19:09'),
(7, 'shemaiah', '127.0.0.1', 1, '2025-11-25 11:19:17'),
(8, 'shemaiah', '127.0.0.1', 1, '2025-11-25 11:24:05'),
(9, 'shemaiah', '127.0.0.1', 1, '2025-11-25 11:27:05'),
(10, 'shemaiah', '127.0.0.1', 1, '2025-11-25 11:37:36'),
(11, 'shemaiah', '127.0.0.1', 0, '2025-11-25 11:48:17'),
(12, 'shemaiah', '127.0.0.1', 1, '2025-11-25 11:48:22'),
(13, 'shemaiah', '127.0.0.1', 1, '2025-11-25 11:51:05'),
(14, 'shemaiah', '127.0.0.1', 1, '2025-11-25 11:51:38'),
(15, 'shemaiah', '127.0.0.1', 1, '2025-11-25 19:31:45');

-- --------------------------------------------------------

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

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `description` text NOT NULL,
  `rate` decimal(10,2) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

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

INSERT INTO `products` (`id`, `description`, `rate`, `created_at`, `updated_at`) VALUES
(1, 'Website Development', 150000.00, '2025-11-22 20:59:58', '2025-11-22 20:59:58'),
(2, 'Mobile App Development', 250000.00, '2025-11-22 20:59:58', '2025-11-22 20:59:58'),
(3, 'IT Consultation (per hour)', 15000.00, '2025-11-22 20:59:58', '2025-11-22 20:59:58'),
(4, 'Network Setup and Configuration', 75000.00, '2025-11-22 20:59:58', '2025-11-22 20:59:58'),
(5, 'Software Maintenance (monthly)', 50000.00, '2025-11-22 20:59:58', '2025-11-22 20:59:58'),
(6, 'Test Product', 1000.00, '2025-11-22 21:49:54', '2025-11-22 21:49:54');

-- --------------------------------------------------------

--
-- Table structure for table `receipts`
--

CREATE TABLE `receipts` (
  `id` int(11) NOT NULL,
  `receipt_number` varchar(50) NOT NULL,
  `invoice_id` int(11) DEFAULT NULL,
  `receipt_date` date NOT NULL,
  `received_from` varchar(255) NOT NULL,
  `amount_naira` int(11) NOT NULL,
  `amount_kobo` int(11) NOT NULL DEFAULT 0,
  `total_amount` decimal(10,2) NOT NULL,
  `payment_for` text NOT NULL,
  `payment_method` enum('cash','transfer','pos','other') NOT NULL,
  `status` enum('issued','archived') DEFAULT 'issued',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `receipts`
--

INSERT INTO `receipts` (`id`, `receipt_number`, `invoice_id`, `receipt_date`, `received_from`, `amount_naira`, `amount_kobo`, `total_amount`, `payment_for`, `payment_method`, `status`, `created_at`, `updated_at`) VALUES
(2, 'RCP-00001', 1, '2025-11-25', 'SHEMAIAH WAMBEBE YABA-SHIAKA', 250000, 0, 250000.00, 'Payment for Invoice INV-00001', 'transfer', 'issued', '2025-11-25 19:23:35', '2025-11-25 19:23:35');

--
-- Indexes for dumped tables
--

--
-- Indexes for table `config`
--
ALTER TABLE `config`
  ADD PRIMARY KEY (`config_key`);

--
-- Indexes for table `customers`
--
ALTER TABLE `customers`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `invoices`
--
ALTER TABLE `invoices`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `invoice_number` (`invoice_number`),
  ADD KEY `customer_id` (`customer_id`);

--
-- Indexes for table `invoice_items`
--
ALTER TABLE `invoice_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `invoice_id` (`invoice_id`);

--
-- Indexes for table `login_attempts`
--
ALTER TABLE `login_attempts`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_username_ip` (`username`,`ip_address`),
  ADD KEY `idx_attempted_at` (`attempted_at`);

--
-- Indexes for table `products`
--
ALTER TABLE `products`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `receipts`
--
ALTER TABLE `receipts`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `receipt_number` (`receipt_number`),
  ADD KEY `idx_invoice_id` (`invoice_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `customers`
--
ALTER TABLE `customers`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

--
-- AUTO_INCREMENT for table `invoices`
--
ALTER TABLE `invoices`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `invoice_items`
--
ALTER TABLE `invoice_items`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- AUTO_INCREMENT for table `login_attempts`
--
ALTER TABLE `login_attempts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=16;

--
-- AUTO_INCREMENT for table `products`
--
ALTER TABLE `products`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `receipts`
--
ALTER TABLE `receipts`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `invoices`
--
ALTER TABLE `invoices`
  ADD CONSTRAINT `invoices_ibfk_1` FOREIGN KEY (`customer_id`) REFERENCES `customers` (`id`) ON DELETE SET NULL;

--
-- Constraints for table `invoice_items`
--
ALTER TABLE `invoice_items`
  ADD CONSTRAINT `invoice_items_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE CASCADE;

--
-- Constraints for table `receipts`
--
ALTER TABLE `receipts`
  ADD CONSTRAINT `receipts_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`) ON DELETE SET NULL;
COMMIT;

