-- Create Database
CREATE DATABASE IF NOT EXISTS invoicedb;
USE invoicedb;

-- Set character set and collation
ALTER DATABASE invoicedb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


-- Create Customers Table
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create Invoices Table
CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT,
    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',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create Payments Table
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT,
    payment_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('credit_card', 'bank_transfer', 'cash', 'cheque') NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create Products Table
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    stock_quantity INT NOT NULL,
    minimum_stock INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create Notifications Table
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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 BOOLEAN DEFAULT FALSE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create vw_sales_analytics View
CREATE OR REPLACE VIEW vw_sales_analytics AS
SELECT 
    DATE(invoice_date) AS date,
    COUNT(*) AS invoice_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS average_value,
    SUM(CASE WHEN status = 'paid' THEN total_amount ELSE 0 END) AS collected_amount,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM invoices
GROUP BY DATE(invoice_date);

-- Create vw_customer_segments View
CREATE OR REPLACE VIEW vw_customer_segments AS
SELECT 
    CASE 
        WHEN total_sales >= 10000 THEN 'High Value'
        WHEN total_sales >= 5000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS segment,
    COUNT(*) AS customer_count,
    SUM(total_sales) AS total_revenue,
    AVG(total_sales) AS avg_revenue,
    SUM(total_sales) - LAG(SUM(total_sales)) OVER (ORDER BY MIN(created_at)) AS growth
FROM (
    SELECT 
        c.id,
        c.created_at,
        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, c.created_at
) customer_stats
GROUP BY segment;

-- Create sp_get_dashboard_stats Procedure
DELIMITER //

CREATE PROCEDURE IF NOT EXISTS 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;

    -- Top customers
    SELECT 
        c.id,
        c.customer_name,
        COUNT(i.id) AS invoice_count,
        SUM(i.total_amount) AS total_sales,
        (COUNT(CASE WHEN i.status = 'paid' THEN 1 END) / 
         COUNT(*) * 100) AS payment_rate
    FROM customers c
    LEFT JOIN invoices i ON c.id = i.customer_id
    GROUP BY c.id
    ORDER BY total_sales DESC
    LIMIT 5;

    -- Due invoices
    SELECT i.*, c.customer_name 
    FROM invoices i
    LEFT JOIN customers c ON i.customer_id = c.id
    WHERE i.status != 'paid' 
    AND i.due_date BETWEEN CURRENT_DATE AND DATE_ADD(CURRENT_DATE, INTERVAL 7 DAY)
    ORDER BY i.due_date;
END //

DELIMITER ;

-- Create sp_get_analytics_data Procedure
DELIMITER //

CREATE PROCEDURE IF NOT EXISTS sp_get_analytics_data(
    IN p_date_range VARCHAR(255),
    IN p_group_by VARCHAR(50),
    IN p_compare_with VARCHAR(50),
    IN p_metrics TEXT
)
BEGIN
    DECLARE v_start_date DATE;
    DECLARE v_end_date DATE;
    DECLARE v_compare_start_date DATE;
    DECLARE v_compare_end_date DATE;
    DECLARE v_metrics TEXT;
    DECLARE v_days_diff INT;

    -- Parse date range
    SET v_start_date = STR_TO_DATE(SUBSTRING_INDEX(p_date_range, ' - ', 1), '%Y-%m-%d');
    SET v_end_date = STR_TO_DATE(SUBSTRING_INDEX(p_date_range, ' - ', -1), '%Y-%m-%d');

    -- Calculate days difference
    SET v_days_diff = DATEDIFF(v_end_date, v_start_date) + 1;

    -- Parse compare with dates
    CASE p_compare_with
        WHEN 'previous_period' THEN
            SET v_compare_start_date = DATE_SUB(v_start_date, INTERVAL v_days_diff DAY);
            SET v_compare_end_date = DATE_SUB(v_end_date, INTERVAL v_days_diff DAY);
        WHEN 'previous_year' THEN
            SET v_compare_start_date = DATE_SUB(v_start_date, INTERVAL 1 YEAR);
            SET v_compare_end_date = DATE_SUB(v_end_date, INTERVAL 1 YEAR);
        ELSE
            SET v_compare_start_date = NULL;
            SET v_compare_end_date = NULL;
    END CASE;

    -- Parse metrics
    SET v_metrics = IFNULL(p_metrics, 'revenue');

    -- Prepare sales trend query
    SET @sales_trend_query = CONCAT(
        'SELECT ',
        CASE 
            WHEN p_group_by = 'day' THEN 'DATE(date) AS label'
            WHEN p_group_by = 'week' THEN 'YEARWEEK(date) AS label'
            WHEN p_group_by = 'month' THEN 'DATE_FORMAT(date, "%Y-%m") AS label'
        END,
        ', SUM(revenue) AS revenue, SUM(invoice_count) AS invoices, SUM(average_value) AS average_value ',
        'FROM vw_sales_analytics ',
        'WHERE date BETWEEN "', v_start_date, '" AND "', v_end_date, '" ',
        CASE 
            WHEN v_compare_start_date IS NOT NULL THEN CONCAT('UNION ALL SELECT ',
                CASE 
                    WHEN p_group_by = 'day' THEN 'DATE(date) AS label'
                    WHEN p_group_by = 'week' THEN 'YEARWEEK(date) AS label'
                    WHEN p_group_by = 'month' THEN 'DATE_FORMAT(date, "%Y-%m") AS label'
                END,
                ', SUM(revenue) AS revenue, SUM(invoice_count) AS invoices, SUM(average_value) AS average_value ',
                'FROM vw_sales_analytics ',
                'WHERE date BETWEEN "', v_compare_start_date, '" AND "', v_compare_end_date, '" ')
            ELSE ''
        END,
        'GROUP BY label '
    );

    -- Execute sales trend query
    PREPARE stmt FROM @sales_trend_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Prepare distribution query
    SET @distribution_query = 'SELECT segment, customer_count, total_revenue, avg_revenue, growth FROM vw_customer_segments';

    -- Execute distribution query
    PREPARE stmt FROM @distribution_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;

    -- Prepare metrics query
    SET @metrics_query = CONCAT(
        'SELECT ',
        CASE WHEN FIND_IN_SET('revenue', v_metrics) > 0 THEN 'SUM(CASE WHEN date BETWEEN "', v_start_date, '" AND "', v_end_date, '" THEN revenue ELSE 0 END) AS revenue, ' ELSE '' END,
        CASE WHEN FIND_IN_SET('invoices', v_metrics) > 0 THEN 'SUM(CASE WHEN date BETWEEN "', v_start_date, '" AND "', v_end_date, '" THEN invoice_count ELSE 0 END) AS invoices, ' ELSE '' END,
        CASE WHEN FIND_IN_SET('average', v_metrics) > 0 THEN 'AVG(CASE WHEN date BETWEEN "', v_start_date, '" AND "', v_end_date, '" THEN average_value ELSE 0 END) AS average_value, ' ELSE '' END,
        CASE WHEN FIND_IN_SET('revenue_growth', v_metrics) > 0 THEN '(((SUM(CASE WHEN date BETWEEN "', v_start_date, '" AND "', v_end_date, '" THEN revenue ELSE 0 END) - SUM(CASE WHEN date BETWEEN "', v_compare_start_date, '" AND "', v_compare_end_date, '" THEN revenue ELSE 0 END)) / SUM(CASE WHEN date BETWEEN "', v_compare_start_date, '" AND "', v_compare_end_date, '" THEN revenue ELSE 0 END)) * 100 AS revenue_growth, ' ELSE '' END,
        CASE WHEN FIND_IN_SET('avg_invoice_trend', v_metrics) > 0 THEN '((AVG(CASE WHEN date BETWEEN "', v_start_date, '" AND "', v_end_date, '" THEN average_value ELSE 0 END) - AVG(CASE WHEN date BETWEEN "', v_compare_start_date, '" AND "', v_compare_end_date, '" THEN average_value ELSE 0 END)) / AVG(CASE WHEN date BETWEEN "', v_compare_start_date, '" AND "', v_compare_end_date, '" THEN average_value ELSE 0 END)) * 100 AS avg_invoice_trend, ' ELSE '' END,
        CASE WHEN FIND_IN_SET('collection_rate', v_metrics) > 0 THEN '(SUM(CASE WHEN date BETWEEN "', v_start_date, '" AND "', v_end_date, '" THEN collected_amount ELSE 0 END) / SUM(CASE WHEN date BETWEEN "', v_start_date, '" AND "', v_end_date, '" THEN revenue ELSE 0 END)) * 100 AS collection_rate, ' ELSE '' END,
        CASE WHEN FIND_IN_SET('customer_growth', v_metrics) > 0 THEN '((SUM(CASE WHEN date BETWEEN "', v_start_date, '" AND "', v_end_date, '" THEN unique_customers ELSE 0 END) - SUM(CASE WHEN date BETWEEN "', v_compare_start_date, '" AND "', v_compare_end_date, '" THEN unique_customers ELSE 0 END)) / SUM(CASE WHEN date BETWEEN "', v_compare_start_date, '" AND "', v_compare_end_date, '" THEN unique_customers ELSE 0 END)) * 100 AS customer_growth' ELSE '' END,
        'FROM vw_sales_analytics'
    );

    -- Execute metrics query
    PREPARE stmt FROM @metrics_query;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
END //

DELIMITER ;

-- Create sp_get_notifications Procedure
DELIMITER //

CREATE PROCEDURE IF NOT EXISTS sp_get_notifications()
BEGIN
    -- Due Invoices
    SELECT 
        i.id,
        i.invoice_number,
        i.total_amount,
        i.due_date,
        c.customer_name,
        FALSE AS read
    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 
        NULL AS id,
        NULL AS invoice_number,
        NULL AS total_amount,
        NULL AS due_date,
        product_name AS customer_name,
        stock_quantity,
        FALSE AS read
    FROM products
    WHERE stock_quantity <= minimum_stock
    LIMIT 5;

    -- Recent Payments
    SELECT 
        NULL AS id,
        NULL AS invoice_number,
        p.amount,
        p.payment_date,
        c.customer_name,
        i.invoice_number AS invoice_number,
        FALSE AS read
    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 //

DELIMITER ;-- Create Customers Table
CREATE TABLE IF NOT EXISTS customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    phone VARCHAR(20),
    company VARCHAR(255),
    address VARCHAR(255),
    city VARCHAR(100),
    state VARCHAR(100),
    zip_code VARCHAR(20),
    notes TEXT,
    status ENUM('active', 'inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create Invoices Table
CREATE TABLE IF NOT EXISTS invoices (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_number VARCHAR(50) UNIQUE NOT NULL,
    customer_id INT,
    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',
    FOREIGN KEY (customer_id) REFERENCES customers(id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create Payments Table
CREATE TABLE IF NOT EXISTS payments (
    id INT AUTO_INCREMENT PRIMARY KEY,
    invoice_id INT,
    payment_date DATE NOT NULL,
    amount DECIMAL(10, 2) NOT NULL,
    payment_method ENUM('credit_card', 'bank_transfer', 'cash', 'cheque') NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id)
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create Products Table
CREATE TABLE IF NOT EXISTS products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    product_name VARCHAR(255) NOT NULL,
    stock_quantity INT NOT NULL,
    minimum_stock INT NOT NULL,
    price DECIMAL(10, 2) NOT NULL
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create Notifications Table
CREATE TABLE IF NOT EXISTS notifications (
    id INT AUTO_INCREMENT PRIMARY KEY,
    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 BOOLEAN DEFAULT FALSE
) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- Create vw_sales_analytics View
CREATE OR REPLACE VIEW vw_sales_analytics AS
SELECT 
    DATE(invoice_date) AS date,
    COUNT(*) AS invoice_count,
    SUM(total_amount) AS revenue,
    AVG(total_amount) AS average_value,
    SUM(CASE WHEN status = 'paid' THEN total_amount ELSE 0 END) AS collected_amount,
    COUNT(DISTINCT customer_id) AS unique_customers
FROM invoices
GROUP BY DATE(invoice_date);

-- Create vw_customer_segments View
CREATE OR REPLACE VIEW vw_customer_segments AS
SELECT 
    CASE 
        WHEN total_sales >= 10000 THEN 'High Value'
        WHEN total_sales >= 5000 THEN 'Medium Value'
        ELSE 'Low Value'
    END AS segment,
    COUNT(*) AS customer_count,
    SUM(total_sales) AS total_revenue,
    AVG(total_sales) AS avg_revenue
FROM (
    SELECT 
        c.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
) customer_stats
GROUP BY segment;

-- Create vw_customer_summary View
CREATE OR REPLACE VIEW vw_customer_summary AS
SELECT 
    c.id,
    c.customer_name,
    c.email,
    c.phone,
    c.company,
    c.address,
    c.city,
    c.state,
    c.zip_code,
    c.notes,
    c.status,
    c.created_at,
    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 collected_amount,
    SUM(CASE WHEN i.status != 'paid' THEN i.total_amount ELSE 0 END) AS outstanding_amount
FROM customers c
LEFT JOIN invoices i ON c.id = i.customer_id
GROUP BY c.id;

-- Create sp_get_dashboard_stats Procedure
DELIMITER //
CREATE 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 //
DELIMITER ;

-- Create sp_get_notifications Procedure
DELIMITER //
CREATE 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 //
DELIMITER ;
   combine both sql