when my create.php as below <?php
require_once '../../config/config.php';
require_once '../../config/database.php';
require_once '../../includes/header.php';
?>

<!-- Include jQuery -->
<script src="https://code.jquery.com/jquery-3.6.0.min.js"></script>

<div class="row">
    <div class="col-md-12">
        <div class="card">
            <div class="card-header">
                <h4>Create New Invoice</h4>
            </div>
            <div class="card-body">
                <form id="createInvoiceForm">
                    <!-- Invoice Header -->
                    <div class="row mb-4">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Customer</label>
                                <select class="form-select" name="customer_id" required>
                                    <option value="">Select Customer</option>
                                </select>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="mb-3">
                                <label class="form-label">Invoice Date</label>
                                <input type="date" class="form-control" name="invoice_date" required>
                            </div>
                        </div>
                        <div class="col-md-3">
                            <div class="mb-3">
                                <label class="form-label">Due Date</label>
                                <input type="date" class="form-control" name="due_date" required>
                            </div>
                        </div>
                    </div>

                    <!-- Invoice Items -->
                    <div class="row mb-4">
                        <div class="col-md-12">
                            <h5>Invoice Items</h5>
                            <table class="table" id="invoiceItems">
                                <thead>
                                    <tr>
                                        <th>Product</th>
                                        <th>Quantity</th>
                                        <th>Unit Price</th>
                                        <th>Total</th>
                                        <th>Action</th>
                                    </tr>
                                </thead>
                                <tbody>
                                    <tr id="itemRow0">
                                        <td>
                                            <select class="form-select product-select" name="items[0][product_id]" required>
                                                <option value="">Select Product</option>
                                            </select>
                                        </td>
                                        <td>
                                            <input type="number" class="form-control quantity" name="items[0][quantity]" min="1" value="1">
                                        </td>
                                        <td>
                                            <input type="number" class="form-control unit-price" name="items[0][unit_price]" readonly>
                                        </td>
                                        <td>
                                            <input type="number" class="form-control item-total" readonly>
                                        </td>
                                        <td>
                                            <button type="button" class="btn btn-danger btn-sm remove-item">
                                                <i class="fas fa-trash"></i>
                                            </button>
                                        </td>
                                    </tr>
                                </tbody>
                            </table>
                            <button type="button" class="btn btn-success" id="addItem">
                                <i class="fas fa-plus"></i> Add Item
                            </button>
                        </div>
                    </div>

                    <!-- Invoice Totals -->
                    <div class="row">
                        <div class="col-md-6">
                            <div class="mb-3">
                                <label class="form-label">Notes</label>
                                <textarea class="form-control" name="notes" rows="3"></textarea>
                            </div>
                        </div>
                        <div class="col-md-6">
                            <div class="card">
                                <div class="card-body">
                                    <div class="row mb-2">
                                        <label class="col-sm-4 col-form-label">Subtotal:</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" id="subtotal" readonly>
                                        </div>
                                    </div>
                                    <div class="row mb-2">
                                        <label class="col-sm-4 col-form-label">Tax Rate (%):</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" name="tax_rate" value="0" min="0" max="100">
                                        </div>
                                    </div>
                                    <div class="row mb-2">
                                        <label class="col-sm-4 col-form-label">Tax Amount:</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" id="taxAmount" readonly>
                                        </div>
                                    </div>
                                    <div class="row">
                                        <label class="col-sm-4 col-form-label">Total:</label>
                                        <div class="col-sm-8">
                                            <input type="number" class="form-control" id="total" readonly>
                                        </div>
                                    </div>
                                </div>
                            </div>
                        </div>
                    </div>

                    <div class="row mt-4">
                        <div class="col-md-12">
                            <button type="submit" class="btn btn-primary">
                                <i class="fas fa-save"></i> Save Invoice
                            </button>
                            <a href="index.php" class="btn btn-secondary">
                                <i class="fas fa-times"></i> Cancel
                            </a>
                        </div>
                    </div>
                </form>
            </div>
        </div>
    </div>
</div>

<!-- Add JavaScript for invoice handling -->
<script src="<?php echo SITE_URL; ?>/assets/js/invoice-create.js"></script>
and my database tables as below -- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Mar 08, 2025 at 05:55 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `invoicedb`
--

DELIMITER $$
--
-- Procedures
--
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_create_invoice_from_recurring` (IN `p_recurring_id` INT)   BEGIN
    -- Logic to generate an invoice from a recurring invoice process.
    DECLARE v_customer_id INT;
    DECLARE v_total_amount DECIMAL(10,2);
    
    SELECT customer_id, total_amount INTO v_customer_id, v_total_amount 
    FROM recurring_invoices WHERE id = p_recurring_id;
    
    INSERT INTO invoices (
        invoice_number, customer_id, invoice_date, due_date,
        subtotal, total_amount
    ) VALUES (
        CONCAT('INV', DATE_FORMAT(NOW(), '%Y%m%d'), '-', p_recurring_id),
        v_customer_id,
        CURDATE(),
        DATE_ADD(CURDATE(), INTERVAL 30 DAY),
        v_total_amount,
        v_total_amount
    );
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_dashboard_stats` ()   BEGIN
    -- Monthly sales
    SELECT COALESCE(SUM(total_amount), 0) AS monthly_sales
    FROM invoices 
    WHERE MONTH(invoice_date) = MONTH(CURRENT_DATE) 
    AND YEAR(invoice_date) = YEAR(CURRENT_DATE);
    
    -- Outstanding amount
    SELECT COALESCE(SUM(total_amount), 0) AS outstanding_amount
    FROM invoices 
    WHERE status != 'paid' AND status != 'cancelled';
    
    -- Collection rate
    SELECT 
        (SUM(CASE WHEN status = 'paid' THEN total_amount ELSE 0 END) / 
         SUM(total_amount) * 100) AS collection_rate
    FROM invoices 
    WHERE status != 'cancelled';

    -- Pending invoices
    SELECT COUNT(*) AS pending_invoices
    FROM invoices 
    WHERE status = 'pending';

    -- Recent invoices
    SELECT i.*, c.customer_name 
    FROM invoices i 
    LEFT JOIN customers c ON i.customer_id = c.id 
    ORDER BY i.invoice_date DESC 
    LIMIT 5;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_get_notifications` ()   BEGIN
    -- Due Invoices
    SELECT 
        i.id,
        i.invoice_number,
        i.total_amount,
        i.due_date,
        c.customer_name
    FROM invoices i
    LEFT JOIN customers c ON i.customer_id = c.id
    WHERE i.status != 'paid' 
    AND i.due_date <= DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY)
    ORDER BY i.due_date
    LIMIT 5;

    -- Low Stock Alerts
    SELECT 
        product_name,
        stock_quantity
    FROM products
    WHERE stock_quantity <= minimum_stock
    LIMIT 5;

    -- Recent Payments
    SELECT 
        p.amount,
        p.payment_date,
        c.customer_name,
        i.invoice_number
    FROM payments p
    LEFT JOIN invoices i ON p.invoice_id = i.id
    LEFT JOIN customers c ON i.customer_id = c.id
    ORDER BY p.payment_date DESC
    LIMIT 5;
END$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_update_recurring_invoices` ()   BEGIN
    -- Logic to update the next date for active recurring invoices.
    UPDATE recurring_invoices 
    SET next_date = CASE frequency
        WHEN 'weekly' THEN DATE_ADD(next_date, INTERVAL 7 DAY)
        WHEN 'monthly' THEN DATE_ADD(next_date, INTERVAL 1 MONTH)
        WHEN 'quarterly' THEN DATE_ADD(next_date, INTERVAL 3 MONTH)
        WHEN 'yearly' THEN DATE_ADD(next_date, INTERVAL 1 YEAR)
    END
    WHERE status = 'active' AND next_date <= CURDATE();
END$$

DELIMITER ;

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

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

CREATE TABLE `customers` (
  `id` int(11) NOT NULL,
  `customer_name` varchar(255) NOT NULL,
  `email` varchar(255) NOT NULL,
  `phone` varchar(20) DEFAULT NULL,
  `company` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL,
  `city` varchar(100) DEFAULT NULL,
  `state` varchar(100) DEFAULT NULL,
  `zip_code` varchar(20) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('active','inactive') NOT NULL DEFAULT 'active',
  `created_at` datetime DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `customers` (`id`, `customer_name`, `email`, `phone`, `company`, `address`, `city`, `state`, `zip_code`, `notes`, `status`, `created_at`) VALUES
(1, 'John Doe', 'john@example.com', '123-456-7890', 'ACME Corporation', '123 Elm St.', 'Springfield', 'IL', '62701', 'Valued customer', 'active', '2025-03-08 17:42:20'),
(2, 'Jane Smith', 'jane@example.com', '987-654-3210', 'XYZ Inc.', '456 Maple Ave.', 'Lincoln', 'NE', '68505', 'Frequent buyer', 'active', '2025-03-08 17:42:20'),
(3, 'Tom Brown', 'tom@example.com', '555-123-4567', NULL, '789 Oak St.', 'Peoria', 'IL', '61614', 'New client', 'active', '2025-03-08 17:42:20'),
(4, 'Alice Johnson', 'alice@example.com', '444-567-8901', 'Widgets LLC', '321 Pine St.', 'Champaign', 'IL', '61820', 'First-time order', 'active', '2025-03-08 17:42:20'),
(5, 'Bob Jones', 'bob@example.com', '888-777-6666', 'Gadgets Co.', '654 Cedar Blvd.', 'Aurora', 'IL', '60505', 'High value account', 'inactive', '2025-03-08 17:42:20');

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

--
-- 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,
  `invoice_date` date NOT NULL,
  `due_date` date NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `status` enum('pending','paid','cancelled') NOT NULL DEFAULT 'pending'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `invoices` (`id`, `invoice_number`, `customer_id`, `invoice_date`, `due_date`, `total_amount`, `status`) VALUES
(1, 'INV-1001', 1, '2023-08-01', '2023-08-15', 300.00, 'pending'),
(2, 'INV-1002', 2, '2023-08-02', '2023-08-16', 1500.00, 'paid'),
(3, 'INV-1003', 1, '2023-08-03', '2023-08-17', 400.00, 'pending'),
(4, 'INV-1004', 3, '2023-08-04', '2023-08-18', 600.00, 'paid'),
(5, 'INV-1005', 4, '2023-08-05', '2023-08-19', 250.00, 'cancelled'),
(6, 'INV-1006', 2, '2023-08-06', '2023-08-20', 750.00, 'pending'),
(7, 'INV-1007', 5, '2023-08-07', '2023-08-21', 1200.00, 'paid');

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

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

CREATE TABLE `invoice_items` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `unit_price` decimal(10,2) NOT NULL,
  `total_price` 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`, `product_id`, `quantity`, `unit_price`, `total_price`) VALUES
(1, 1, 1, 3, 100.00, 300.00),
(2, 1, 2, 2, 150.00, 300.00),
(3, 2, 1, 5, 100.00, 500.00),
(4, 2, 3, 3, 200.00, 600.00),
(5, 3, 2, 7, 150.00, 1050.00),
(6, 4, 3, 8, 200.00, 1600.00);

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

--
-- Table structure for table `invoice_templates`
--

CREATE TABLE `invoice_templates` (
  `id` int(11) NOT NULL,
  `template_name` varchar(100) NOT NULL,
  `customer_type` enum('all','corporate','individual') NOT NULL DEFAULT 'all',
  `description` text DEFAULT NULL,
  `payment_terms` int(11) DEFAULT 30,
  `tax_rate` decimal(5,2) DEFAULT 0.00,
  `notes` text DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `invoice_templates`
--

INSERT INTO `invoice_templates` (`id`, `template_name`, `customer_type`, `description`, `payment_terms`, `tax_rate`, `notes`, `created_at`) VALUES
(1, 'Standard Template', 'all', 'A standard template for most customers.', 30, 5.00, 'Use this template for regular customers.', '2025-03-08 13:21:35');

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

--
-- Table structure for table `notifications`
--

CREATE TABLE `notifications` (
  `id` int(11) NOT NULL,
  `type` enum('invoice_due','low_stock','payment_received') NOT NULL,
  `message` text NOT NULL,
  `amount` decimal(10,2) DEFAULT NULL,
  `date` datetime DEFAULT current_timestamp(),
  `read_status` tinyint(1) DEFAULT 0
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `notifications`
--

INSERT INTO `notifications` (`id`, `type`, `message`, `amount`, `date`, `read_status`) VALUES
(1, 'invoice_due', 'Invoice INV-1001 is due on 2023-08-15.', 300.00, '2025-03-08 17:42:52', 0),
(2, 'low_stock', 'Stock is low for Gadget B.', NULL, '2025-03-08 17:42:52', 0),
(3, 'payment_received', 'Payment received for invoice INV-1002.', 1500.00, '2025-03-08 17:42:52', 0);

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

--
-- Table structure for table `payments`
--

CREATE TABLE `payments` (
  `id` int(11) NOT NULL,
  `invoice_id` int(11) DEFAULT NULL,
  `payment_date` date NOT NULL,
  `amount` decimal(10,2) NOT NULL,
  `payment_method` enum('credit_card','bank_transfer','cash','cheque') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `payments`
--

INSERT INTO `payments` (`id`, `invoice_id`, `payment_date`, `amount`, `payment_method`) VALUES
(1, 2, '2023-08-10', 1500.00, 'credit_card'),
(2, 4, '2023-08-11', 600.00, 'bank_transfer'),
(3, 3, '2023-08-13', 400.00, 'cash'),
(4, 7, '2023-08-15', 1200.00, 'cheque');

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

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

CREATE TABLE `products` (
  `id` int(11) NOT NULL,
  `product_name` varchar(255) NOT NULL,
  `description` text DEFAULT NULL,
  `stock_quantity` int(11) NOT NULL,
  `minimum_stock` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

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

INSERT INTO `products` (`id`, `product_name`, `description`, `stock_quantity`, `minimum_stock`, `price`) VALUES
(1, 'Widget A', NULL, 100, 20, 15.00),
(2, 'Widget B', NULL, 50, 10, 25.00),
(3, 'Gadget A', NULL, 200, 50, 35.00),
(4, 'Gadget B', NULL, 10, 5, 55.00),
(5, 'Gadget C', NULL, 30, 15, 45.00);

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

--
-- Table structure for table `recurring_invoices`
--

CREATE TABLE `recurring_invoices` (
  `id` int(11) NOT NULL,
  `customer_id` int(11) NOT NULL,
  `frequency` enum('weekly','monthly','quarterly','yearly') NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date DEFAULT NULL,
  `next_date` date NOT NULL,
  `total_amount` decimal(10,2) NOT NULL,
  `notes` text DEFAULT NULL,
  `status` enum('active','paused','completed') DEFAULT 'active',
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `recurring_invoices`
--

INSERT INTO `recurring_invoices` (`id`, `customer_id`, `frequency`, `start_date`, `end_date`, `next_date`, `total_amount`, `notes`, `status`, `created_at`) VALUES
(1, 1, 'monthly', '2023-01-01', NULL, '2023-02-01', 525.00, 'Monthly invoice for Acme Corp', 'active', '2025-03-08 13:21:35'),
(2, 2, 'weekly', '2023-01-01', NULL, '2023-01-08', 300.00, 'Weekly invoice for Beta LLC', 'active', '2025-03-08 13:21:35');

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

--
-- Table structure for table `recurring_items`
--

CREATE TABLE `recurring_items` (
  `id` int(11) NOT NULL,
  `recurring_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `recurring_items`
--

INSERT INTO `recurring_items` (`id`, `recurring_id`, `product_id`, `quantity`, `price`) VALUES
(1, 1, 1, 4, 100.00),
(2, 1, 2, 2, 150.00),
(3, 2, 1, 3, 100.00);

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

--
-- Table structure for table `template_items`
--

CREATE TABLE `template_items` (
  `id` int(11) NOT NULL,
  `template_id` int(11) NOT NULL,
  `product_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  `price` decimal(10,2) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `template_items`
--

INSERT INTO `template_items` (`id`, `template_id`, `product_id`, `quantity`, `price`) VALUES
(1, 1, 1, 2, 100.00),
(2, 1, 3, 1, 200.00);

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

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `id` int(11) NOT NULL,
  `username` varchar(50) NOT NULL,
  `password` varchar(255) NOT NULL,
  `email` varchar(100) NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

--
-- Dumping data for table `users`
--

INSERT INTO `users` (`id`, `username`, `password`, `email`, `created_at`) VALUES
(8, 'admin', 'Ana@4016', 'admin@example.com', '2025-03-08 13:21:35'),
(9, 'johndoe', '$2y$10$hashedpassword', 'john@example.com', '2025-03-08 13:21:35'),
(10, 'janedoe', '$2y$10$hashedpassword', 'jane@example.com', '2025-03-08 13:21:35');

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

--
-- Stand-in structure for view `vw_customer_segments`
-- (See below for the actual view)
--
CREATE TABLE `vw_customer_segments` (
`segment` varchar(12)
,`customer_count` bigint(21)
,`total_revenue` decimal(54,2)
,`avg_revenue` decimal(36,6)
);

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

--
-- Stand-in structure for view `vw_customer_summary`
-- (See below for the actual view)
--
CREATE TABLE `vw_customer_summary` (
`id` int(11)
,`customer_name` varchar(255)
,`email` varchar(255)
,`phone` varchar(20)
,`company` varchar(255)
,`address` varchar(255)
,`city` varchar(100)
,`state` varchar(100)
,`zip_code` varchar(20)
,`total_invoices` bigint(21)
,`total_sales` decimal(32,2)
,`avg_invoice_value` decimal(14,6)
,`outstanding_amount` decimal(32,2)
,`status` enum('active','inactive')
);

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

--
-- Stand-in structure for view `vw_product_performance`
-- (See below for the actual view)
--
CREATE TABLE `vw_product_performance` (
`id` int(11)
,`product_name` varchar(255)
,`units_sold` decimal(32,0)
,`total_sold` decimal(42,2)
,`invoice_count` bigint(21)
);

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

--
-- Stand-in structure for view `vw_sales_analytics`
-- (See below for the actual view)
--
CREATE TABLE `vw_sales_analytics` (
`date` date
,`invoice_count` bigint(21)
,`revenue` decimal(32,2)
,`average_value` decimal(14,6)
,`collected_amount` decimal(32,2)
,`unique_customers` bigint(21)
);

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

--
-- Stand-in structure for view `vw_sales_summary`
-- (See below for the actual view)
--
CREATE TABLE `vw_sales_summary` (
`total_invoices` bigint(21)
,`total_revenue` decimal(32,2)
,`avg_invoice_value` decimal(14,6)
);

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

--
-- Structure for view `vw_customer_segments`
--
DROP TABLE IF EXISTS `vw_customer_segments`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_customer_segments`  AS SELECT CASE WHEN `customer_stats`.`total_sales` >= 10000 THEN 'High Value' WHEN `customer_stats`.`total_sales` >= 5000 THEN 'Medium Value' ELSE 'Low Value' END AS `segment`, count(0) AS `customer_count`, sum(`customer_stats`.`total_sales`) AS `total_revenue`, avg(`customer_stats`.`total_sales`) AS `avg_revenue` FROM (select `c`.`id` AS `id`,coalesce(sum(`i`.`total_amount`),0) AS `total_sales` from (`customers` `c` left join `invoices` `i` on(`c`.`id` = `i`.`customer_id`)) group by `c`.`id`) AS `customer_stats` GROUP BY CASE WHEN `customer_stats`.`total_sales` >= 10000 THEN 'High Value' WHEN `customer_stats`.`total_sales` >= 5000 THEN 'Medium Value' ELSE 'Low Value' END ;

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

--
-- Structure for view `vw_customer_summary`
--
DROP TABLE IF EXISTS `vw_customer_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_customer_summary`  AS SELECT `c`.`id` AS `id`, `c`.`customer_name` AS `customer_name`, `c`.`email` AS `email`, `c`.`phone` AS `phone`, `c`.`company` AS `company`, `c`.`address` AS `address`, `c`.`city` AS `city`, `c`.`state` AS `state`, `c`.`zip_code` AS `zip_code`, count(`i`.`id`) AS `total_invoices`, sum(`i`.`total_amount`) AS `total_sales`, avg(`i`.`total_amount`) AS `avg_invoice_value`, sum(case when `i`.`status` <> 'paid' then `i`.`total_amount` else 0 end) AS `outstanding_amount`, `c`.`status` AS `status` FROM (`customers` `c` left join `invoices` `i` on(`c`.`id` = `i`.`customer_id`)) GROUP BY `c`.`id` ;

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

--
-- Structure for view `vw_product_performance`
--
DROP TABLE IF EXISTS `vw_product_performance`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_product_performance`  AS SELECT `p`.`id` AS `id`, `p`.`product_name` AS `product_name`, sum(`ii`.`quantity`) AS `units_sold`, sum(`ii`.`unit_price` * `ii`.`quantity`) AS `total_sold`, count(distinct `i`.`id`) AS `invoice_count` FROM ((`products` `p` left join `invoice_items` `ii` on(`p`.`id` = `ii`.`product_id`)) left join `invoices` `i` on(`ii`.`invoice_id` = `i`.`id`)) GROUP BY `p`.`id` ;

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

--
-- Structure for view `vw_sales_analytics`
--
DROP TABLE IF EXISTS `vw_sales_analytics`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_sales_analytics`  AS SELECT cast(`invoices`.`invoice_date` as date) AS `date`, count(0) AS `invoice_count`, sum(`invoices`.`total_amount`) AS `revenue`, avg(`invoices`.`total_amount`) AS `average_value`, sum(case when `invoices`.`status` = 'paid' then `invoices`.`total_amount` else 0 end) AS `collected_amount`, count(distinct `invoices`.`customer_id`) AS `unique_customers` FROM `invoices` GROUP BY cast(`invoices`.`invoice_date` as date) ;

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

--
-- Structure for view `vw_sales_summary`
--
DROP TABLE IF EXISTS `vw_sales_summary`;

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `vw_sales_summary`  AS SELECT count(`i`.`id`) AS `total_invoices`, sum(`i`.`total_amount`) AS `total_revenue`, avg(`i`.`total_amount`) AS `avg_invoice_value` FROM `invoices` AS `i` WHERE `i`.`status` = 'paid' ;

--
-- Indexes for dumped tables
--

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

--
-- 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`),
  ADD KEY `product_id` (`product_id`);

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

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

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

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

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

--
-- Indexes for table `recurring_items`
--
ALTER TABLE `recurring_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `recurring_id` (`recurring_id`),
  ADD KEY `product_id` (`product_id`);

--
-- Indexes for table `template_items`
--
ALTER TABLE `template_items`
  ADD PRIMARY KEY (`id`),
  ADD KEY `template_id` (`template_id`),
  ADD KEY `product_id` (`product_id`);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `username` (`username`),
  ADD UNIQUE KEY `email` (`email`);

--
-- AUTO_INCREMENT for dumped tables
--

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

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

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

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

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

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

--
-- Constraints for dumped tables
--

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

--
-- Constraints for table `payments`
--
ALTER TABLE `payments`
  ADD CONSTRAINT `payments_ibfk_1` FOREIGN KEY (`invoice_id`) REFERENCES `invoices` (`id`);
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 make a invoice-create.js and api.php