-- ============================================================
-- SasaPay + SMS Integration Tables
-- Run this in phpMyAdmin on xygbfpsg_loans database
-- ============================================================

-- 1. Raw IPN payload log
CREATE TABLE IF NOT EXISTS `sasapay_ipn_logs` (
  `id`         CHAR(32) NOT NULL,
  `raw_data`   JSON NOT NULL,
  `created_at` DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 2. STK push callback results (mirrors old stkpushresults table)
CREATE TABLE IF NOT EXISTS `sasapay_stk_results` (
  `id`                   CHAR(32) NOT NULL,
  `checkout_request_id`  VARCHAR(100) NOT NULL DEFAULT '',
  `merchant_loan_ref`    VARCHAR(100) NOT NULL DEFAULT '',
  `result_code`          VARCHAR(20)  NOT NULL,
  `result_desc`          TEXT         NOT NULL,
  `trans_amount`         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `created_at`           DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 3. B2C disbursement results (mirrors old b2cpaymentsresults table)
CREATE TABLE IF NOT EXISTS `sasapay_disbursement_results` (
  `id`                     CHAR(32) NOT NULL,
  `loan_reference`         VARCHAR(100) NOT NULL DEFAULT '',
  `sasapay_transaction_id` VARCHAR(100) NOT NULL DEFAULT '',
  `mpesa_reference`        VARCHAR(100) NOT NULL DEFAULT '',
  `result_code`            VARCHAR(20)  NOT NULL,
  `result_desc`            TEXT         NOT NULL,
  `trans_amount`           DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `merchant_balance`       DECIMAL(14,2) NOT NULL DEFAULT 0.00,
  `created_at`             DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 4. Unknown / unmatched payments (mirrors old unknown_payments table)
CREATE TABLE IF NOT EXISTS `sasapay_unknown_payments` (
  `id`         CHAR(32) NOT NULL,
  `amount`     DECIMAL(12,2) NOT NULL,
  `paid_by`    VARCHAR(200) NOT NULL DEFAULT '',
  `msisdn`     VARCHAR(20)  NOT NULL DEFAULT '',
  `bill_ref`   VARCHAR(100) NOT NULL DEFAULT '',
  `reference`  VARCHAR(100) NOT NULL DEFAULT '',
  `notes`      TEXT         NOT NULL,
  `resolved`   TINYINT(1)   NOT NULL DEFAULT 0,
  `created_at` DATETIME(6)  NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 5. SMS Log — every outgoing SMS recorded for admin review
CREATE TABLE IF NOT EXISTS `sms_logs` (
  `id`             CHAR(32)      NOT NULL,
  `sms_type`       VARCHAR(30)   NOT NULL DEFAULT 'other',
  `recipients`     TEXT          NOT NULL,
  `message`        TEXT          NOT NULL,
  `sender_id`      VARCHAR(20)   NOT NULL DEFAULT 'HavGrazuri',
  `status`         VARCHAR(20)   NOT NULL DEFAULT 'sent',
  `at_response`    JSON          NULL,
  `error_message`  TEXT          NOT NULL DEFAULT '',
  `loan_number`    VARCHAR(30)   NOT NULL DEFAULT '',
  `borrower_name`  VARCHAR(200)  NOT NULL DEFAULT '',
  `amount`         DECIMAL(12,2) NULL,
  `created_at`     DATETIME(6)   NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
  PRIMARY KEY (`id`),
  KEY `sms_logs_status` (`status`),
  KEY `sms_logs_sms_type` (`sms_type`),
  KEY `sms_logs_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 6. Record migrations in Django's migration history
INSERT IGNORE INTO `django_migrations` (`app`, `name`, `applied`)
VALUES ('payments', '0003_sasapay_models', NOW());