-- Update payments table to store UPI transaction details
USE sts_v2;

-- Add new columns to payments table
ALTER TABLE payments 
ADD COLUMN base_amount DECIMAL(10,2) DEFAULT 0,
ADD COLUMN tax_amount DECIMAL(10,2) DEFAULT 0,
ADD COLUMN tax_percentage DECIMAL(5,2) DEFAULT 0,
ADD COLUMN transaction_id VARCHAR(100),
ADD COLUMN upi_transaction_id VARCHAR(100),
ADD COLUMN gateway_response JSON;

-- Add unique constraint for transaction_id
ALTER TABLE payments ADD UNIQUE KEY unique_transaction_id (transaction_id);

-- Create payment_transactions table for detailed transaction tracking
CREATE TABLE payment_transactions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  payment_id INT,
  transaction_id VARCHAR(100) UNIQUE NOT NULL,
  upi_transaction_id VARCHAR(100),
  gateway VARCHAR(50) DEFAULT 'manual',
  payment_method ENUM('cash', 'upi', 'card', 'bank-transfer') NOT NULL,
  amount DECIMAL(10,2) NOT NULL,
  base_amount DECIMAL(10,2) NOT NULL,
  tax_amount DECIMAL(10,2) DEFAULT 0,
  tax_percentage DECIMAL(5,2) DEFAULT 0,
  status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled') DEFAULT 'completed',
  gateway_response JSON,
  processed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (payment_id) REFERENCES payments(id) ON DELETE SET NULL
);

-- Show updated table structure
SELECT 'Payments table updated successfully' as Status;
SELECT 'Payment_transactions table created successfully' as Status;