-- ============================================================
-- Developer Payments Table
-- PhinTech Solutions Company Ltd — System v1.0
--
-- Run this on the production MySQL database ONLY if Django
-- migrations cannot connect directly (e.g. cPanel phpMyAdmin).
--
-- IMPORTANT: Run AFTER all other migrations have been applied
-- (users_customuser table must already exist).
-- ============================================================

-- Step 1: Create the table WITHOUT the FK first (safe for any run order)
CREATE TABLE IF NOT EXISTS `developer_payments` (
  `id`                        VARCHAR(32)    NOT NULL,
  `amount`                    DECIMAL(12,2)  NOT NULL,
  `payment_type`              VARCHAR(20)    NOT NULL DEFAULT 'maintenance',
  `description`               LONGTEXT       NOT NULL,
  `reference`                 VARCHAR(100)   NOT NULL,
  `payer_phone`               VARCHAR(17)    NOT NULL,
  `status`                    VARCHAR(20)    NOT NULL DEFAULT 'pending',
  `lipia_checkout_id`         VARCHAR(200)   NULL,
  `lipia_transaction_id`      VARCHAR(200)   NULL,
  `mpesa_receipt`             VARCHAR(100)   NULL,
  `raw_initiation_response`   JSON           NULL,
  `raw_callback_data`         JSON           NULL,
  `admin_notes`               LONGTEXT       NULL,
  `failure_reason`            LONGTEXT       NULL,
  `created_at`                DATETIME(6)    NOT NULL,
  `updated_at`                DATETIME(6)    NOT NULL,
  `completed_at`              DATETIME(6)    NULL,
  `initiated_by_id`           VARCHAR(32)    NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `developer_payments_reference_uniq` (`reference`),
  KEY `developer_payments_initiated_by_id` (`initiated_by_id`),
  KEY `developer_payments_status` (`status`),
  KEY `developer_payments_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Step 2: Add FK separately (only if users_customuser already exists)
-- If this fails, the table is still usable — the FK is just for referential integrity.
ALTER TABLE `developer_payments`
  ADD CONSTRAINT `developer_payments_initiated_by_fk`
  FOREIGN KEY (`initiated_by_id`)
  REFERENCES `users_customuser` (`id`)
  ON DELETE SET NULL;

-- Step 3: Record this migration in Django's migration tracking table
INSERT IGNORE INTO `django_migrations` (`app`, `name`, `applied`)
VALUES ('payments', '0004_developer_payment', NOW());