-- Drop old tables if they exist
DROP TABLE IF EXISTS `debit_credit_note_items`;
DROP TABLE IF EXISTS `debit_credit_notes`;

-- Create debit_credit_notes table for financial adjustments (no products)
CREATE TABLE IF NOT EXISTS `debit_credit_notes` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `company_id` int(11) NOT NULL,
  `note_number` varchar(50) NOT NULL,
  `note_date` date NOT NULL,
  `note_type` enum('debit','credit') NOT NULL COMMENT 'debit: Debit Note, credit: Credit Note',
  `ledger_type` enum('debtors','creditors','lenders','employees') NOT NULL,
  `ledger_id` int(11) NOT NULL,
  `amount` decimal(15,2) NOT NULL DEFAULT '0.00',
  `original_doc_no` varchar(50) DEFAULT NULL,
  `original_doc_date` date DEFAULT NULL,
  `reason` varchar(255) DEFAULT NULL,
  `notes` text DEFAULT NULL,
  `user_id` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `note_number_company` (`company_id`,`note_number`),
  KEY `company_id` (`company_id`),
  KEY `ledger_type_id` (`ledger_type`, `ledger_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;