-- ═══════════════════════════════════════════════════════════
-- APISEVA — Set pg.paymentgateway.co.in Gateway in settings table
-- Run in phpMyAdmin → apisevax_working → SQL tab
-- ═══════════════════════════════════════════════════════════

-- Update pgapi column with new gateway credentials (JSON)
UPDATE settings SET pgapi = '{"pg_api_base":"https://merchant.razorpay.space/api/v1","pg_username":"8298201899","pg_api_key":"fee443ef0a53ef4504c71b30e3700807488d8dbc72df5d8e","pg_callback_url":"https://api.apiseva.xyz/ajax/payment_callback.php"}' WHERE id = 1;

-- Verify
SELECT id, pgapi FROM settings;

-- Create system_updates table
CREATE TABLE IF NOT EXISTS `system_updates` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `version` VARCHAR(20) NOT NULL,
    `changelog` TEXT,
    `zip_path` VARCHAR(500),
    `zip_name` VARCHAR(255),
    `sql_queries` TEXT,
    `is_active` TINYINT(1) DEFAULT 0,
    `uploaded_by` INT,
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `update_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `update_id` INT NOT NULL,
    `user_id` INT NOT NULL,
    `applied_at` DATETIME DEFAULT CURRENT_TIMESTAMP,
    `status` VARCHAR(20) DEFAULT 'success'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add missing columns
ALTER TABLE `loginusers` ADD COLUMN IF NOT EXISTS `ip_whitelist` TEXT DEFAULT NULL;
ALTER TABLE `loginusers` ADD COLUMN IF NOT EXISTS `api_created_at` DATETIME DEFAULT NULL;
ALTER TABLE `transaction` ADD COLUMN IF NOT EXISTS `balance_before` DECIMAL(15,2) DEFAULT 0;
ALTER TABLE `transaction` ADD COLUMN IF NOT EXISTS `balance_after` DECIMAL(15,2) DEFAULT 0;
ALTER TABLE `api_providers` ADD COLUMN IF NOT EXISTS `api_balance` DECIMAL(15,2) DEFAULT 0;
ALTER TABLE `paymentreq` ADD COLUMN IF NOT EXISTS `gateway_response` TEXT DEFAULT NULL;

CREATE TABLE IF NOT EXISTS `panel_activity` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` INT NOT NULL,
    `activity` VARCHAR(500) NOT NULL,
    `ip_address` VARCHAR(45),
    `created_at` DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ═══════════════════════════════════════════════════════════
-- DONE!
-- ═══════════════════════════════════════════════════════════