-- Migration: Add Down Payment (DP) field to sales table
-- Date: 2025-01-07
-- Description: Adds optional down_payment field to track down payments in sales

USE mobile_management;

-- Add down_payment column to sales table
ALTER TABLE sales 
ADD COLUMN down_payment DECIMAL(10, 2) DEFAULT 0.00 COMMENT 'Down Payment amount (optional)' 
AFTER emi_amount;

-- Add index for down_payment queries
CREATE INDEX idx_down_payment ON sales(down_payment);

-- Update comment on sales table
ALTER TABLE sales COMMENT = 'Sales transactions with finance and down payment support';

-- Verification query
SELECT 
    COLUMN_NAME, 
    COLUMN_TYPE, 
    IS_NULLABLE, 
    COLUMN_DEFAULT, 
    COLUMN_COMMENT 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE TABLE_SCHEMA = 'mobile_management' 
    AND TABLE_NAME = 'sales' 
    AND COLUMN_NAME = 'down_payment';


