CREATE TABLE `partner_ampere_transactions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `partner_id` int(10) unsigned NOT NULL COMMENT 'Customer ID (partner type)',
  `brand_id` int(10) unsigned NULL DEFAULT NULL COMMENT 'Brand this transaction belongs to',
  `transaction_type` enum('SALE','SETTLEMENT','ADJUSTMENT','REFUND') NOT NULL,
  `delta_ampere` int(11) NOT NULL DEFAULT 0 COMMENT 'Change in ampere balance (positive = owed more, negative = paid off)',
  `rate_per_ampere` decimal(12,2) NULL DEFAULT NULL COMMENT 'Rate used in settlement (Rials/Ampere)',
  `rial_amount` bigint(20) NULL DEFAULT NULL COMMENT 'Rial equivalent for SETTLEMENT transactions',
  `balance_after` int(11) NOT NULL DEFAULT 0 COMMENT 'Account balance after this transaction',
  `voucher_id` varchar(64) NULL DEFAULT NULL,
  `description` varchar(500) NOT NULL DEFAULT '',
  `created_by` int(10) unsigned NULL DEFAULT NULL COMMENT 'Admin user ID',
  `created_at` datetime NOT NULL DEFAULT current_timestamp(),
  PRIMARY KEY (`id`),
  KEY `idx_partner_id` (`partner_id`),
  KEY `idx_brand_id` (`brand_id`),
  KEY `idx_transaction_type` (`transaction_type`),
  KEY `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE OR REPLACE VIEW `v_partner_ampere_transactions_detail` AS
  SELECT
    pat.id            AS transaction_id,
    pat.partner_id,
    pat.brand_id,
    pat.transaction_type,
    pat.delta_ampere,
    pat.rate_per_ampere,
    pat.rial_amount,
    pat.balance_after,
    pat.voucher_id,
    pat.description,
    pat.created_by,
    pat.created_at,
    b.name            AS brand_name,
    CONCAT(c.first_name, ' ', c.last_name) AS partner_name
  FROM partner_ampere_transactions pat
  LEFT JOIN brands b    ON pat.brand_id   = b.id
  LEFT JOIN customers c ON pat.partner_id = c.id;
