-- Migration: add PagSeguro payment tracking tables and user payment columns

ALTER TABLE `users`
    ADD COLUMN `payment_status` ENUM('pending','paid','failed','canceled') NOT NULL DEFAULT 'pending' AFTER `reminder_enabled`,
    ADD COLUMN `is_paid` TINYINT(1) NOT NULL DEFAULT 0 AFTER `payment_status`,
    ADD COLUMN `paid_at` DATETIME NULL AFTER `is_paid`,
    ADD COLUMN `payment_reference` VARCHAR(64) DEFAULT NULL AFTER `paid_at`,
    ADD COLUMN `payment_link` VARCHAR(255) DEFAULT NULL AFTER `payment_reference`,
    ADD INDEX `idx_users_payment_status` (`payment_status`),
    ADD INDEX `idx_users_payment_reference` (`payment_reference`);

UPDATE `users`
SET `payment_status` = 'paid', `is_paid` = 1, `paid_at` = COALESCE(`paid_at`, NOW())
WHERE `payment_status` = 'pending' OR `payment_status` IS NULL;

CREATE TABLE IF NOT EXISTS `pagseguro_authorizations` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `notification_code` VARCHAR(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `authorization_code` VARCHAR(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_email` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `account_name` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `permissions_json` MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  `status_label` VARCHAR(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `raw_xml` LONGTEXT COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_authorization_notification` (`notification_code`),
  KEY `idx_authorization_code` (`authorization_code`),
  KEY `idx_authorization_email` (`account_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pagseguro_notifications` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `received_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `notification_code` VARCHAR(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `notification_type` VARCHAR(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `ip_address` VARCHAR(45) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `raw_body` MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  `http_headers` MEDIUMTEXT COLLATE utf8mb4_unicode_ci,
  `consulted` TINYINT(1) NOT NULL DEFAULT 0,
  `consult_url` VARCHAR(512) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `consult_http_code` INT DEFAULT NULL,
  `consult_response` LONGTEXT COLLATE utf8mb4_unicode_ci,
  `transaction_code` VARCHAR(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `reference` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `status_code` TINYINT DEFAULT NULL,
  `status_label` VARCHAR(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `gross_amount` DECIMAL(12,2) DEFAULT NULL,
  `paid` TINYINT(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_notification_code` (`notification_code`),
  KEY `idx_notification_received` (`received_at`),
  KEY `idx_notification_type` (`notification_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS `pagseguro_transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `transaction_code` VARCHAR(64) COLLATE utf8mb4_unicode_ci NOT NULL,
  `reference` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `type` TINYINT DEFAULT NULL,
  `status_code` TINYINT DEFAULT NULL,
  `status_label` VARCHAR(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `transaction_date` DATETIME DEFAULT NULL,
  `last_event_at` DATETIME DEFAULT NULL,
  `payment_method_type` TINYINT DEFAULT NULL,
  `payment_method_code` INT DEFAULT NULL,
  `gross_amount` DECIMAL(12,2) DEFAULT NULL,
  `discount_amount` DECIMAL(12,2) DEFAULT NULL,
  `fee_amount` DECIMAL(12,2) DEFAULT NULL,
  `net_amount` DECIMAL(12,2) DEFAULT NULL,
  `extra_amount` DECIMAL(12,2) DEFAULT NULL,
  `installment_count` INT DEFAULT NULL,
  `item_count` INT DEFAULT NULL,
  `payment_link` VARCHAR(1024) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payer_name` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payer_email` VARCHAR(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `payer_document` VARCHAR(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `paid` TINYINT(1) DEFAULT NULL,
  `raw_xml` LONGTEXT COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_transaction_code` (`transaction_code`),
  KEY `idx_transaction_status` (`status_code`),
  KEY `idx_transaction_reference` (`reference`),
  KEY `idx_transaction_email` (`payer_email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
