-- =====================================================
-- SUPPLIER RETURNS V2 - Complete Fresh Setup
-- =====================================================
-- Features:
-- 1. Returns not tied to specific purchase invoice  
-- 2. Pick items from stock (any batch) like POS
-- 3. Items removed from stock when returned
-- 4. Supplier return balance tracking (pending/resolved)
-- 5. Manual settlement tracking
-- 6. Original purchase invoices stay unchanged
-- =====================================================

-- =====================================================
-- 1. Drop existing tables (in correct order due to FKs)
-- =====================================================
DROP TABLE IF EXISTS `supplier_return_settlements`;
DROP TABLE IF EXISTS `supplier_return_items`;
DROP TABLE IF EXISTS `supplier_returns`;

-- =====================================================
-- 2. Create supplier_returns table
-- =====================================================
CREATE TABLE `supplier_returns` (
    `supplier_return_id` INT(11) NOT NULL AUTO_INCREMENT,
    `supplier_id` INT(11) NOT NULL,
    `purchase_id` INT(11) NULL COMMENT 'Optional - not required in v2',
    `supplier_return_date` DATE NOT NULL,
    `supplier_return_total_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `supplier_return_notes` TEXT NULL,
    `supplier_return_status` ENUM('pending', 'partial', 'resolved') NOT NULL DEFAULT 'pending',
    `supplier_return_resolved_amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    `supplier_return_created_by` INT(11) NULL,
    `supplier_return_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`supplier_return_id`),
    INDEX `idx_sr_supplier` (`supplier_id`),
    INDEX `idx_sr_purchase` (`purchase_id`),
    INDEX `idx_sr_date` (`supplier_return_date`),
    INDEX `idx_sr_status` (`supplier_return_status`),
    FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`supplier_id`),
    FOREIGN KEY (`purchase_id`) REFERENCES `purchases`(`purchase_id`) ON DELETE SET NULL,
    FOREIGN KEY (`supplier_return_created_by`) REFERENCES `admins`(`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- 3. Create supplier_return_items table
-- =====================================================
CREATE TABLE `supplier_return_items` (
    `supplier_return_item_id` INT(11) NOT NULL AUTO_INCREMENT,
    `supplier_return_id` INT(11) NOT NULL,
    `item_id` INT(11) NOT NULL,
    `purchase_item_id` INT(11) NOT NULL COMMENT 'Which batch the item came from',
    `supplier_return_item_quantity` DECIMAL(10,2) NOT NULL,
    `supplier_return_item_original_cost` DECIMAL(12,2) NOT NULL COMMENT 'Original purchase cost price',
    `supplier_return_item_return_price` DECIMAL(12,2) NOT NULL COMMENT 'Price returning to supplier',
    `supplier_return_item_line_total` DECIMAL(12,2) NOT NULL,
    `supplier_return_item_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`supplier_return_item_id`),
    INDEX `idx_sri_return` (`supplier_return_id`),
    INDEX `idx_sri_item` (`item_id`),
    INDEX `idx_sri_purchase_item` (`purchase_item_id`),
    FOREIGN KEY (`supplier_return_id`) REFERENCES `supplier_returns`(`supplier_return_id`) ON DELETE CASCADE,
    FOREIGN KEY (`item_id`) REFERENCES `items`(`item_id`),
    FOREIGN KEY (`purchase_item_id`) REFERENCES `purchase_items`(`purchase_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- 4. Create supplier_return_settlements table
-- =====================================================
CREATE TABLE `supplier_return_settlements` (
    `settlement_id` INT(11) NOT NULL AUTO_INCREMENT,
    `supplier_id` INT(11) NOT NULL,
    `supplier_return_id` INT(11) NULL COMMENT 'NULL for general settlement across multiple returns',
    `settlement_amount` DECIMAL(12,2) NOT NULL,
    `settlement_date` DATE NOT NULL,
    `settlement_type` ENUM('cash', 'credit', 'deduction', 'other') NOT NULL DEFAULT 'cash' COMMENT 'How supplier settled: cash refund, credit note, deducted from next order, etc',
    `settlement_reference` VARCHAR(100) NULL COMMENT 'Reference number, credit note #, etc',
    `settlement_notes` TEXT NULL,
    `settlement_created_by` INT(11) NULL,
    `settlement_created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (`settlement_id`),
    INDEX `idx_settlement_supplier` (`supplier_id`),
    INDEX `idx_settlement_return` (`supplier_return_id`),
    INDEX `idx_settlement_date` (`settlement_date`),
    FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`supplier_id`),
    FOREIGN KEY (`supplier_return_id`) REFERENCES `supplier_returns`(`supplier_return_id`) ON DELETE SET NULL,
    FOREIGN KEY (`settlement_created_by`) REFERENCES `admins`(`admin_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- =====================================================
-- 5. Create view for supplier return balance
-- =====================================================
CREATE OR REPLACE VIEW `v_supplier_return_balance` AS
SELECT 
    s.supplier_id,
    s.supplier_name,
    s.supplier_phone,
    COUNT(DISTINCT sr.supplier_return_id) as total_returns,
    COUNT(DISTINCT CASE WHEN sr.supplier_return_status = 'pending' THEN sr.supplier_return_id END) as pending_returns,
    COUNT(DISTINCT CASE WHEN sr.supplier_return_status = 'resolved' THEN sr.supplier_return_id END) as resolved_returns,
    COALESCE(SUM(sri.supplier_return_item_line_total), 0) as total_return_value,
    COALESCE(settlements.total_settled, 0) as total_settled,
    (COALESCE(SUM(sri.supplier_return_item_line_total), 0) - COALESCE(settlements.total_settled, 0)) as balance_due
FROM suppliers s
LEFT JOIN supplier_returns sr ON s.supplier_id = sr.supplier_id
LEFT JOIN supplier_return_items sri ON sr.supplier_return_id = sri.supplier_return_id
LEFT JOIN (
    SELECT supplier_id, SUM(settlement_amount) as total_settled
    FROM supplier_return_settlements
    GROUP BY supplier_id
) settlements ON s.supplier_id = settlements.supplier_id
GROUP BY s.supplier_id, s.supplier_name, s.supplier_phone, settlements.total_settled;

-- =====================================================
-- 6. Create view for individual return details
-- =====================================================
CREATE OR REPLACE VIEW `v_supplier_return_details` AS
SELECT 
    sr.supplier_return_id,
    sr.supplier_id,
    s.supplier_name,
    sr.purchase_id,
    sr.supplier_return_date,
    sr.supplier_return_total_amount,
    sr.supplier_return_notes,
    sr.supplier_return_status,
    sr.supplier_return_resolved_amount,
    sr.supplier_return_created_by,
    a.admin_full_name as created_by_name,
    sr.supplier_return_created_at,
    COUNT(sri.supplier_return_item_id) as item_count,
    COALESCE(SUM(sri.supplier_return_item_quantity), 0) as total_qty_returned,
    (sr.supplier_return_total_amount - sr.supplier_return_resolved_amount) as remaining_balance
FROM supplier_returns sr
LEFT JOIN suppliers s ON sr.supplier_id = s.supplier_id
LEFT JOIN admins a ON sr.supplier_return_created_by = a.admin_id
LEFT JOIN supplier_return_items sri ON sr.supplier_return_id = sri.supplier_return_id
GROUP BY sr.supplier_return_id, sr.supplier_id, s.supplier_name, sr.purchase_id,
         sr.supplier_return_date, sr.supplier_return_total_amount, sr.supplier_return_notes,
         sr.supplier_return_status, sr.supplier_return_resolved_amount,
         sr.supplier_return_created_by, a.admin_full_name, sr.supplier_return_created_at;

-- =====================================================
-- 7. Create view for return items with full details
-- =====================================================
CREATE OR REPLACE VIEW `v_supplier_return_item_details` AS
SELECT 
    sri.supplier_return_item_id,
    sri.supplier_return_id,
    sr.supplier_id,
    s.supplier_name,
    sr.supplier_return_date,
    sr.supplier_return_status,
    sri.item_id,
    i.item_scientific_name,
    i.item_brand,
    i.item_barcode,
    sri.purchase_item_id,
    pi.purchase_item_batch_number,
    pi.purchase_item_expiry_date,
    pi.purchase_item_cost_price as purchase_cost_price,
    sri.supplier_return_item_quantity,
    sri.supplier_return_item_original_cost,
    sri.supplier_return_item_return_price,
    sri.supplier_return_item_line_total,
    sri.supplier_return_item_created_at
FROM supplier_return_items sri
JOIN supplier_returns sr ON sri.supplier_return_id = sr.supplier_return_id
LEFT JOIN suppliers s ON sr.supplier_id = s.supplier_id
LEFT JOIN items i ON sri.item_id = i.item_id
LEFT JOIN purchase_items pi ON sri.purchase_item_id = pi.purchase_item_id;

-- =====================================================
-- 8. Create/Update view for purchase item tracking
-- =====================================================
CREATE OR REPLACE VIEW `v_purchase_item_tracking` AS
SELECT 
    pi.purchase_item_id,
    pi.purchase_id,
    p.purchase_notes as purchase_supplier_invoice_number,
    p.purchase_date,
    s.supplier_id,
    s.supplier_name,
    pi.item_id,
    i.item_scientific_name,
    i.item_brand,
    i.item_barcode,
    it.item_type_name,
    pi.purchase_item_batch_number,
    pi.purchase_item_quantity as qty_purchased,
    pi.purchase_item_remaining_quantity as qty_remaining,
    -- Sold = purchased - remaining - returned to supplier
    (pi.purchase_item_quantity - pi.purchase_item_remaining_quantity - COALESCE(sr_totals.qty_returned_to_supplier, 0)) as qty_sold,
    COALESCE(sr_totals.qty_returned_to_supplier, 0) as qty_returned_to_supplier,
    pi.purchase_item_cost_price,
    pi.purchase_item_sell_price,
    (pi.purchase_item_quantity * pi.purchase_item_cost_price) as total_cost_value,
    (pi.purchase_item_remaining_quantity * pi.purchase_item_cost_price) as remaining_cost_value,
    pi.purchase_item_expiry_date,
    pi.purchase_item_is_active,
    pi.purchase_item_created_at
FROM purchase_items pi
LEFT JOIN purchases p ON pi.purchase_id = p.purchase_id
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id
LEFT JOIN items i ON pi.item_id = i.item_id
LEFT JOIN item_types it ON i.item_type_id = it.item_type_id
LEFT JOIN (
    SELECT 
        purchase_item_id,
        SUM(supplier_return_item_quantity) as qty_returned_to_supplier
    FROM supplier_return_items
    GROUP BY purchase_item_id
) sr_totals ON pi.purchase_item_id = sr_totals.purchase_item_id;

-- =====================================================
-- 9. Create/Update view for purchase invoice summary
-- =====================================================
CREATE OR REPLACE VIEW `v_purchase_summary` AS
SELECT 
    p.purchase_id,
    p.purchase_notes as purchase_supplier_invoice_number,
    p.supplier_id,
    s.supplier_name,
    p.purchase_date,
    p.purchase_total_amount,
    p.purchase_notes,
    COUNT(DISTINCT pi.purchase_item_id) as total_items,
    SUM(pi.purchase_item_quantity) as total_qty_purchased,
    SUM(pi.purchase_item_remaining_quantity) as total_qty_remaining,
    (SUM(pi.purchase_item_quantity - pi.purchase_item_remaining_quantity) - COALESCE(sr_totals.total_qty_returned_to_supplier, 0)) as total_qty_sold,
    COALESCE(sr_totals.total_qty_returned_to_supplier, 0) as total_qty_returned_to_supplier,
    COALESCE(sr_totals.total_return_amount, 0) as total_return_amount,
    COALESCE(sp_totals.total_paid, 0) as total_paid,
    (p.purchase_total_amount - COALESCE(sp_totals.total_paid, 0) - COALESCE(sr_totals.total_return_amount, 0)) as balance_due,
    p.purchase_created_by,
    a.admin_full_name as created_by_name,
    p.purchase_created_at
FROM purchases p
LEFT JOIN suppliers s ON p.supplier_id = s.supplier_id
LEFT JOIN purchase_items pi ON p.purchase_id = pi.purchase_id
LEFT JOIN admins a ON p.purchase_created_by = a.admin_id
LEFT JOIN (
    SELECT 
        sri.purchase_item_id,
        SUM(sri.supplier_return_item_quantity) as total_qty_returned_to_supplier,
        SUM(sri.supplier_return_item_line_total) as total_return_amount
    FROM supplier_return_items sri
    GROUP BY sri.purchase_item_id
) sr_totals ON pi.purchase_item_id = sr_totals.purchase_item_id
LEFT JOIN (
    SELECT 
        purchase_id,
        SUM(supplier_payment_amount) as total_paid
    FROM supplier_payments
    GROUP BY purchase_id
) sp_totals ON p.purchase_id = sp_totals.purchase_id
GROUP BY p.purchase_id, p.purchase_notes, p.supplier_id, s.supplier_name, 
         p.purchase_date, p.purchase_total_amount,
         sp_totals.total_paid, p.purchase_created_by, a.admin_full_name, p.purchase_created_at
ORDER BY p.purchase_id DESC;

-- =====================================================
-- DONE! Restart backend after running this migration
-- =====================================================