-- GST Notes Module Database Setup
-- This script creates the required tables for the GST Notes functionality

-- Create Credit Notes table
CREATE TABLE IF NOT EXISTS `credit_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `note_number` varchar(50) NOT NULL,
  `note_date` date NOT NULL,
  `note_type` enum('sales','purchase') NOT NULL DEFAULT 'sales',
  `party_name` varchar(255) NOT NULL,
  `party_gstin` varchar(15) DEFAULT NULL,
  `party_address` text DEFAULT NULL,
  `original_invoice_no` varchar(100) NOT NULL,
  `original_invoice_date` date NOT NULL,
  `reason` text NOT NULL,
  `taxable_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cgst_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `cgst_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `sgst_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `sgst_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `igst_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `igst_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `note_number` (`note_number`),
  INDEX `idx_note_date` (`note_date`),
  INDEX `idx_note_type` (`note_type`),
  INDEX `idx_party_name` (`party_name`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create Debit Notes table
CREATE TABLE IF NOT EXISTS `debit_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `note_number` varchar(50) NOT NULL,
  `note_date` date NOT NULL,
  `note_type` enum('sales','purchase') NOT NULL DEFAULT 'sales',
  `party_name` varchar(255) NOT NULL,
  `party_gstin` varchar(15) DEFAULT NULL,
  `party_address` text DEFAULT NULL,
  `original_invoice_no` varchar(100) NOT NULL,
  `original_invoice_date` date NOT NULL,
  `reason` text NOT NULL,
  `taxable_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `cgst_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `cgst_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `sgst_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `sgst_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `igst_rate` decimal(5,2) NOT NULL DEFAULT 0.00,
  `igst_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `total_amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `note_number` (`note_number`),
  INDEX `idx_note_date` (`note_date`),
  INDEX `idx_note_type` (`note_type`),
  INDEX `idx_party_name` (`party_name`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Insert sample data for testing (optional)
INSERT IGNORE INTO `credit_notes` (`note_number`, `note_date`, `note_type`, `party_name`, `party_gstin`, `party_address`, `original_invoice_no`, `original_invoice_date`, `reason`, `taxable_amount`, `cgst_rate`, `cgst_amount`, `sgst_rate`, `sgst_amount`, `igst_rate`, `igst_amount`, `total_amount`) VALUES
('CN-2025-001', '2025-01-15', 'sales', 'ABC Company Ltd', '24ABCDE1234F1Z5', '123 Business Street, Mumbai, Maharashtra', 'INV-2024-1234', '2024-12-15', 'Product return - damaged goods', 10000.00, 9.00, 900.00, 9.00, 900.00, 0.00, 0.00, 11800.00),
('CN-2025-002', '2025-01-18', 'purchase', 'XYZ Suppliers Pvt Ltd', '27XYZAB5678C1D2', '456 Supply Lane, Pune, Maharashtra', 'PINV-2024-5678', '2024-12-20', 'Quality issue - partial return', 5000.00, 6.00, 300.00, 6.00, 300.00, 0.00, 0.00, 5600.00);

INSERT IGNORE INTO `debit_notes` (`note_number`, `note_date`, `note_type`, `party_name`, `party_gstin`, `party_address`, `original_invoice_no`, `original_invoice_date`, `reason`, `taxable_amount`, `cgst_rate`, `cgst_amount`, `sgst_rate`, `sgst_amount`, `igst_rate`, `igst_amount`, `total_amount`) VALUES
('DN-2025-001', '2025-01-20', 'sales', 'PQR Industries', '29PQRST9012G3H4', '789 Industrial Area, Bangalore, Karnataka', 'INV-2024-2345', '2024-12-18', 'Price revision - additional charges', 3000.00, 9.00, 270.00, 9.00, 270.00, 0.00, 0.00, 3540.00),
('DN-2025-002', '2025-01-22', 'purchase', 'LMN Trading Co', '33LMNOP7890I1J2', '321 Trade Center, Delhi', 'TINV-2024-7890', '2024-12-25', 'Transportation charges not included', 1500.00, 9.00, 135.00, 9.00, 135.00, 0.00, 0.00, 1770.00);
