-- ============================================================
-- SUPPLIER LEDGER
-- ============================================================
CREATE TABLE IF NOT EXISTS `supplier_ledger` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `supplier_id` INT UNSIGNED NOT NULL,
  `transaction_type` ENUM('purchase','payment','return','adjustment','opening_balance') NOT NULL,
  `reference_id` INT UNSIGNED NULL COMMENT 'ID of related purchase/payment/return',
  `reference_no` VARCHAR(100) NULL COMMENT 'Purchase no, payment ref, etc.',
  `debit` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Amount owed to supplier (purchase)',
  `credit` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Amount paid to supplier (payment)',
  `balance` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Running balance after this transaction',
  `description` TEXT,
  `transaction_date` DATE NOT NULL,
  `user_id` INT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `supplier_id` (`supplier_id`),
  KEY `transaction_date` (`transaction_date`),
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`supplier_id`) REFERENCES `suppliers`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================================
-- CUSTOMER LEDGER
-- ============================================================
CREATE TABLE IF NOT EXISTS `customer_ledger` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `tenant_id` INT UNSIGNED NOT NULL,
  `customer_id` INT UNSIGNED NOT NULL,
  `transaction_type` ENUM('sale','payment','return','adjustment','opening_balance') NOT NULL,
  `reference_id` INT UNSIGNED NULL COMMENT 'ID of related sale/payment/return',
  `reference_no` VARCHAR(100) NULL COMMENT 'Invoice no, payment ref, etc.',
  `debit` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Amount owed by customer (sale)',
  `credit` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Amount received from customer (payment)',
  `balance` DECIMAL(15,2) DEFAULT 0.00 COMMENT 'Running balance after this transaction',
  `description` TEXT,
  `transaction_date` DATE NOT NULL,
  `user_id` INT UNSIGNED,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `tenant_id` (`tenant_id`),
  KEY `customer_id` (`customer_id`),
  KEY `transaction_date` (`transaction_date`),
  FOREIGN KEY (`tenant_id`) REFERENCES `tenants`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`customer_id`) REFERENCES `customers`(`id`) ON DELETE CASCADE,
  FOREIGN KEY (`user_id`) REFERENCES `users`(`id`) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
