-- Insert merchant data for Tejaswini K N
-- Database: shukria_transactions
-- Date: 2026-01-08

-- ===============================================
-- 1. INSERT INTO groups (Merchant Level)
-- ===============================================
INSERT INTO `groups` (
    `name`, 
    `code`, 
    `description`, 
    `status`, 
    `phone_number`, 
    `registered`, 
    `mcc_code`, 
    `params`, 
    `transaction_currency`, 
    `settlement_currency`,
    `inserted_at`, 
    `updated_at`
) VALUES (
    'Tejaswini K N',                           -- name (merchant_name)
    'TXN1767879629401',                        -- code (using txnRefNo as unique identifier)
    'Retail Stores - QR Merchant',             -- description
    'active',                                  -- status
    '06361241254',                             -- phone_number (contact_person_number)
    'yes',                                     -- registered
    '5999',                                    -- mcc_code (Retail Stores = 5999)
    JSON_OBJECT(
        'merchant_type', 'QR',
        'category', 'Retail Stores',
        'type_of_establishment', 'RETAIL',
        'number_of_stores', '4',
        'email', 'tejaswiniteju189@gmail.com',
        'emirate', 'banglore',
        'address_line1', 'Business Address',
        'payment_mode', 'OB-Other Bank Transfer UAE',
        'valid_from', '08/01/2026',
        'valid_fhru', '08/01/2051'
    ),                                          -- params (store original request data)
    'AED',                                     -- transaction_currency
    'AED',                                     -- settlement_currency
    NOW(),                                     -- inserted_at
    NOW()                                      -- updated_at
);

-- Get the group_id we just inserted
SET @group_id = LAST_INSERT_ID();
SELECT @group_id AS 'Created Group ID';

-- ===============================================
-- 2. INSERT INTO brands (Brand Level)
-- ===============================================
INSERT INTO `brands` (
    `code`, 
    `name`, 
    `description`, 
    `default_acquirer`, 
    `group_id`, 
    `merchant_reference_id`,
    `merchant_tag`,
    `inserted_at`, 
    `updated_at`
) VALUES (
    'TXN1767879629401',                        -- code (same as group code)
    'Tejaswini K N',                          -- name
    'Tejaswini K N - QR Brand',              -- description
    NULL,                                      -- default_acquirer
    @group_id,                                 -- group_id (from above insert)
    'TXN1767879629401',                        -- merchant_reference_id (merchant.id for API)
    NULL,                                      -- merchant_tag (for Aani - will be set after registration)
    NOW(),                                     -- inserted_at
    NOW()                                      -- updated_at
);

-- Get the brand_id we just inserted
SET @brand_id = LAST_INSERT_ID();
SELECT @brand_id AS 'Created Brand ID';

-- ===============================================
-- 3. INSERT INTO stores (Store Level)
-- ===============================================
INSERT INTO `stores` (
    `code`, 
    `name`, 
    `brand_id`, 
    `neo_merchant_id`,
    `address_id`,
    `inserted_at`, 
    `updated_at`
) VALUES (
    '48562',                                   -- code (store_id from request)
    'tejaswini',                              -- name (store_name from request)
    @brand_id,                                 -- brand_id (from above insert)
    NULL,                                      -- neo_merchant_id (can be set later if needed)
    NULL,                                      -- address_id (can be set later if address table is used)
    NOW(),                                     -- inserted_at
    NOW()                                      -- updated_at
);

-- Get the store_id we just inserted
SET @store_id = LAST_INSERT_ID();
SELECT @store_id AS 'Created Store ID';

-- ===============================================
-- 4. INSERT INTO merchant_enrollments (Enrollment Tracking)
-- ===============================================
INSERT INTO `merchant_enrollments` (
    `provider`,
    `registration_params`,
    `registration_response`,
    `inquiry_history`,
    `products`,
    `status`,
    `group_id`,
    `inserted_at`,
    `updated_at`
) VALUES (
    'aani',                                    -- provider (default to aani)
    JSON_OBJECT(
        'provider', 'aani',
        'requestId', 'TXN1767879629401',
        'services', JSON_ARRAY('DPC'),
        'merchant', JSON_OBJECT(
            'id', 'TXN1767879629401',
            'displayName', 'Tejaswini K N',
            'mcc', '5999',
            'contactInfo', JSON_OBJECT(
                'email', 'tejaswiniteju189@gmail.com',
                'phone', '06361241254'
            ),
            'address', JSON_OBJECT(
                'street', 'Business Address',
                'city', 'banglore',
                'country', 'AE'
            ),
            'legalInfo', JSON_OBJECT(
                'registrationType', 'RETAIL'
            ),
            'ownerInfo', JSON_OBJECT(
                'name', 'Tejaswini K N'
            )
        ),
        'store', JSON_OBJECT(
            'id', '48562',
            'name', 'tejaswini',
            'mcc', '5999',
            'address', JSON_OBJECT(
                'street', 'yelahanka new town\r\nBanglore',
                'city', 'banglore',
                'country', 'AE'
            )
        )
    ),                                         -- registration_params
    NULL,                                      -- registration_response (will be updated after API call)
    JSON_OBJECT(),                            -- inquiry_history (empty initially)
    JSON_ARRAY('DPC'),                        -- products (Dynamic QR Code)
    'pending',                                 -- status (pending/active/failed)
    @group_id,                                 -- group_id (from first insert)
    NOW(),                                     -- inserted_at
    NOW()                                      -- updated_at
);

SELECT @group_id AS final_group_id, @brand_id AS final_brand_id, @store_id AS final_store_id;

-- ===============================================
-- 5. VERIFICATION QUERIES
-- ===============================================
-- Verify the data was inserted correctly
SELECT 'GROUP DATA:' AS '';
SELECT * FROM `groups` WHERE id = @group_id;

SELECT 'BRAND DATA:' AS '';
SELECT * FROM `brands` WHERE id = @brand_id;

SELECT 'STORE DATA:' AS '';
SELECT * FROM `stores` WHERE id = @store_id;

SELECT 'ENROLLMENT DATA:' AS '';
SELECT * FROM `merchant_enrollments` WHERE group_id = @group_id;
