-- ===========================================================================
--  Game-database bridge - run ONCE against the MySQL server after a backup.
--  Mirrors the tables the current server website relies on. Adds nothing
--  destructive; every statement is IF NOT EXISTS.
-- ===========================================================================

-- --- Coin wallets on the account row --------------------------------------
ALTER TABLE account.account ADD COLUMN IF NOT EXISTS coins  BIGINT NOT NULL DEFAULT 0;
ALTER TABLE account.account ADD COLUMN IF NOT EXISTS jcoins BIGINT NOT NULL DEFAULT 0;

-- --- Item-shop catalog (owned by the game server / in-game item shop) -----
CREATE TABLE IF NOT EXISTS player.ishop_data (
    id              INT UNSIGNED NOT NULL,
    categoryType    INT NOT NULL DEFAULT 0,
    categorySubType INT NOT NULL DEFAULT 0,
    itemVnum        INT UNSIGNED NOT NULL,
    itemPrice       BIGINT NOT NULL DEFAULT 0,
    discount        TINYINT NOT NULL DEFAULT 0,
    offerTime       DATETIME NOT NULL DEFAULT '0000-00-00 00:00:00',
    addedTime       DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    sellCount       INT NOT NULL DEFAULT 0,
    week_limit      INT NOT NULL DEFAULT 0,
    month_limit     INT NOT NULL DEFAULT 0,
    order_priority  INT NOT NULL DEFAULT 0,
    PRIMARY KEY (id),
    KEY idx_ishop_data_cat (categoryType, categorySubType)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --- Item-shop purchase log ---------------------------------------------
CREATE TABLE IF NOT EXISTS player.ishop_log (
    accountID  INT UNSIGNED NOT NULL,
    playerName VARCHAR(24) NOT NULL DEFAULT '',
    buyDate    DATETIME NOT NULL,
    buyTime    INT NOT NULL DEFAULT 0,
    ipAdress   VARCHAR(16) NOT NULL DEFAULT '',
    itemID     INT NOT NULL,
    itemVnum   INT UNSIGNED NOT NULL,
    itemCount  INT NOT NULL DEFAULT 0,
    itemPrice  BIGINT NOT NULL DEFAULT 0,
    KEY idx_ishop_log_account_item (accountID, itemID),
    KEY idx_ishop_log_buy_date (buyDate)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- --- Real-money payment history ---------------------------------------
CREATE TABLE IF NOT EXISTS log.itemshop_payments (
    id             BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    account_id     INT UNSIGNED NOT NULL DEFAULT 0,
    login          VARCHAR(30) NOT NULL DEFAULT '',
    provider       VARCHAR(32) NOT NULL DEFAULT '',
    transaction_id VARCHAR(128) NOT NULL DEFAULT '',
    amount         DECIMAL(12,2) NOT NULL DEFAULT 0.00,
    currency       CHAR(3) NOT NULL DEFAULT 'EUR',
    md_amount      BIGINT UNSIGNED NOT NULL DEFAULT 0,
    status         VARCHAR(20) NOT NULL DEFAULT 'pending',
    created_at     DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    completed_at   DATETIME NULL DEFAULT NULL,
    ip_address     VARCHAR(45) NOT NULL DEFAULT '',
    extra_data     JSON NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uq_itemshop_payment_transaction (provider, transaction_id),
    KEY idx_itemshop_payment_account (account_id),
    KEY idx_itemshop_payment_status (status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;