-- https://www.phpmyadmin.net/
--

START TRANSACTION;



--
-- Database: `zatm`
--

-- --------------------------------------------------------

--
-- Table structure for table `jupiter_live_copytrades`
--

CREATE TABLE `jupiter_live_copytrades` (
  `id` int(11) NOT NULL,
  `address_id` int(11) NOT NULL,
  `pubkey` varchar(255) DEFAULT NULL,
  `my_size` double NOT NULL,
  `trader_size` double NOT NULL,
  `total_collateral_in` double DEFAULT NULL,
  `total_collateral_out` double DEFAULT NULL,
  `pnl` double DEFAULT NULL,
  `status` enum('open','closed') NOT NULL DEFAULT 'open',
  `create_date` datetime NOT NULL,
  `close_date` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;



-- --------------------------------------------------------

--
-- Table structure for table `jupiter_live_perps`
--

CREATE TABLE `jupiter_live_perps` (
  `id` int(11) NOT NULL,
  `address` varchar(255) NOT NULL,
  `market_mint` varchar(255) NOT NULL,
  `created_time` datetime NOT NULL,
  `close_date` datetime DEFAULT NULL,
  `side` enum('long','short') NOT NULL,
  `status` enum('open','closed') NOT NULL DEFAULT 'open',
  `borrow_fees` bigint(20) NOT NULL,
  `borrow_fees_usd` double NOT NULL,
  `close_fees` bigint(20) NOT NULL,
  `close_fees_usd` double NOT NULL,
  `collateral` double NOT NULL,
  `collateral_usd` bigint(20) NOT NULL,
  `collateral_mint` varchar(255) NOT NULL,
  `entry_price` double NOT NULL,
  `leverage` double NOT NULL,
  `liquidation_price` double NOT NULL,
  `mark_price` double NOT NULL,
  `open_fees` bigint(20) NOT NULL,
  `open_fees_usd` double NOT NULL,
  `pnl_after_fees` bigint(20) NOT NULL,
  `pnl_after_fees_usd` double NOT NULL,
  `pnl_before_fees` bigint(20) NOT NULL,
  `pnl_before_fees_usd` double NOT NULL,
  `pnl_change_pct_after_fees` double NOT NULL,
  `pnl_change_pct_before_fees` double NOT NULL,
  `position_pubkey` varchar(255) NOT NULL,
  `size` double NOT NULL,
  `size_usd_delta` bigint(20) NOT NULL,
  `size_token_amount` bigint(20) NOT NULL,
  `total_fees` bigint(20) NOT NULL,
  `total_fees_usd` double NOT NULL,
  `tpsl_requests` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL,
  `updated_time` datetime NOT NULL,
  `value` double NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


--
-- Table structure for table `jupiter_live_perp_addresses`
--

CREATE TABLE `jupiter_live_perp_addresses` (
  `id` int(11) NOT NULL,
  `type` enum('Copytrader', 'Monitor') NOT NULL DEFAULT 'Monitor',
  `address` varchar(255) NOT NULL,
  `inputMint` varchar(255) NOT NULL,
  `name` varchar(255) NOT NULL,
  `active` enum('Y','N') NOT NULL DEFAULT 'N',
  `trader_size` double DEFAULT NULL,
  `my_size` double DEFAULT NULL,
  `new_trades_allowed` enum('N','Y') NOT NULL DEFAULT 'N',
  `my_public_key` varchar(128) DEFAULT NULL,
  `my_private_key` varchar(128) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

ALTER TABLE `jupiter_live_perp_addresses` ADD `last_updated_dt` DATETIME NULL AFTER `my_private_key`;
-- --------------------------------------------------------

--
-- Table structure for table `jupiter_live_perp_events`
--

CREATE TABLE `jupiter_live_perp_events` (
  `id` int(11) NOT NULL,
  `trade_id` int(11) NOT NULL,
  `event_type` enum('new_trade','size_increased','size_decreased','collateral_increased','collateral_decreased','tp_added','sl_added','tp_removed','sl_removed','trade_closed') NOT NULL,
  `event_details` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL,
  `timestamp` datetime NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


--
-- Table structure for table `jupiter_live_perp_event_log`
--

CREATE TABLE `jupiter_live_perp_event_log` (
  `id` int(11) NOT NULL,
  `event_id` int(11) NOT NULL,
  `trade_id` int(11) NOT NULL,
  `status` enum('success','error','info') NOT NULL,
  `details` text NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `jupiter_live_perp_trades`
--

CREATE TABLE `jupiter_live_perp_trades` (
  `id` int(11) NOT NULL,
  `address` varchar(255) NOT NULL,
  `mint` varchar(44) DEFAULT NULL,
  `positionName` varchar(20) DEFAULT NULL,
  `side` enum('long','short') DEFAULT NULL,
  `action` enum('Increase','Decrease') DEFAULT NULL,
  `orderType` varchar(24) DEFAULT NULL,
  `collateralUsdDelta` double DEFAULT NULL,
  `price` double DEFAULT NULL,
  `size` double DEFAULT NULL,
  `baseFee` double DEFAULT NULL,
  `borrowFee` double DEFAULT NULL,
  `liquidationFee` double DEFAULT NULL,
  `priceImpactFee` double DEFAULT NULL,
  `fee` double DEFAULT NULL,
  `pnl` double DEFAULT NULL,
  `pnlPercentage` double DEFAULT NULL,
  `txHash` varchar(128) DEFAULT NULL,
  `createdTime` datetime DEFAULT NULL,
  `updatedTime` datetime DEFAULT NULL,
  `trade_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_general_ci;


--
-- Table structure for table `jupiter_perp_api_archive`
--

CREATE TABLE `jupiter_perp_api_archive` (
  `id` int(11) NOT NULL,
  `method` varchar(10) NOT NULL,
  `action` varchar(255) NOT NULL,
  `parameters` text NOT NULL,
  `priority` int(11) NOT NULL,
  `status` varchar(20) NOT NULL,
  `result` text DEFAULT NULL,
  `http_code` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
  `processed_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- --------------------------------------------------------

--
-- Table structure for table `jupiter_perp_api_requests`
--

CREATE TABLE `jupiter_perp_api_requests` (
  `id` int(11) NOT NULL,
  `method` varchar(10) NOT NULL,
  `action` varchar(255) NOT NULL,
  `parameters` text NOT NULL,
  `priority` int(11) NOT NULL DEFAULT 3,
  `status` varchar(20) NOT NULL DEFAULT 'pending',
  `result` text DEFAULT NULL,
  `http_code` int(11) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `processed_at` timestamp NULL DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;



Create
 ALGORITHM = UNDEFINED
DEFINER=`zerotop`@`localhost` 
 SQL SECURITY DEFINER
 VIEW `jupiter_perp_trades_vw`
 AS SELECT
    `zatm`.`jupiter_live_perps`.`id` AS `id`,
    `zatm`.`jupiter_live_perps`.`address` AS `address`,
    `zatm`.`jupiter_live_perps`.`market_mint` AS `market_mint`,
    `zatm`.`jupiter_live_perps`.`created_time` AS `created_time`,
    `zatm`.`jupiter_live_perps`.`close_date` AS `close_date`,
    `zatm`.`jupiter_live_perps`.`side` AS `side`,
    `zatm`.`jupiter_live_perps`.`status` AS `status`,
    `zatm`.`jupiter_live_perps`.`borrow_fees` AS `borrow_fees`,
    `zatm`.`jupiter_live_perps`.`borrow_fees_usd` AS `borrow_fees_usd`,
    `zatm`.`jupiter_live_perps`.`close_fees` AS `close_fees`,
    `zatm`.`jupiter_live_perps`.`close_fees_usd` AS `close_fees_usd`,
    `zatm`.`jupiter_live_perps`.`collateral` AS `collateral`,
    `zatm`.`jupiter_live_perps`.`collateral_usd` AS `collateral_usd`,
    `zatm`.`jupiter_live_perps`.`collateral_mint` AS `collateral_mint`,
    `zatm`.`jupiter_live_perps`.`entry_price` AS `entry_price`,
    `zatm`.`jupiter_live_perps`.`leverage` AS `leverage`,
    `zatm`.`jupiter_live_perps`.`liquidation_price` AS `liquidation_price`,
    `zatm`.`jupiter_live_perps`.`mark_price` AS `mark_price`,
    `zatm`.`jupiter_live_perps`.`open_fees` AS `open_fees`,
    `zatm`.`jupiter_live_perps`.`open_fees_usd` AS `open_fees_usd`,
    `zatm`.`jupiter_live_perps`.`pnl_after_fees` AS `pnl_after_fees`,
    `zatm`.`jupiter_live_perps`.`pnl_after_fees_usd` AS `pnl_after_fees_usd`,
    `zatm`.`jupiter_live_perps`.`pnl_before_fees` AS `pnl_before_fees`,
    `zatm`.`jupiter_live_perps`.`pnl_before_fees_usd` AS `pnl_before_fees_usd`,
    `zatm`.`jupiter_live_perps`.`pnl_change_pct_after_fees` AS `pnl_change_pct_after_fees`,
    `zatm`.`jupiter_live_perps`.`pnl_change_pct_before_fees` AS `pnl_change_pct_before_fees`,
    `zatm`.`jupiter_live_perps`.`position_pubkey` AS `position_pubkey`,
    `zatm`.`jupiter_live_perps`.`size` AS `size`,
    `zatm`.`jupiter_live_perps`.`size_usd_delta` AS `size_usd_delta`,
    `zatm`.`jupiter_live_perps`.`size_token_amount` AS `size_token_amount`,
    `zatm`.`jupiter_live_perps`.`total_fees` AS `total_fees`,
    `zatm`.`jupiter_live_perps`.`total_fees_usd` AS `total_fees_usd`,
    `zatm`.`jupiter_live_perps`.`tpsl_requests` AS `tpsl_requests`,
    `zatm`.`jupiter_live_perps`.`updated_time` AS `updated_time`,
    `zatm`.`jupiter_live_perps`.`value` AS `value`,
    `zatm`.`jupiter_live_perp_addresses`.`active` AS `active`,
    CONCAT(
        `zatm`.`jupiter_live_perp_addresses`.`name`,
        ' ',
        SUBSTR(
            `zatm`.`jupiter_live_perps`.`address`,
            1,
            4
        ),
        '...',
        SUBSTR(
            `zatm`.`jupiter_live_perps`.`address`,
            -4,
            4
        )
    ) AS `name`
FROM
    (
        `zatm`.`jupiter_live_perps`
    JOIN `zatm`.`jupiter_live_perp_addresses`
    )
WHERE
    `zatm`.`jupiter_live_perps`.`address` = `zatm`.`jupiter_live_perp_addresses`.`address`



--
-- Indexes for dumped tables
--

--
-- Indexes for table `jupiter_live_copytrades`
--
ALTER TABLE `jupiter_live_copytrades`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `jupiter_live_perps`
--
ALTER TABLE `jupiter_live_perps`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `unique_trade` (`address`,`market_mint`,`created_time`,`side`),
  ADD KEY `idx_id` (`id`);

--
-- Indexes for table `jupiter_live_perp_addresses`
--
ALTER TABLE `jupiter_live_perp_addresses`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `jupiter_live_perp_events`
--
ALTER TABLE `jupiter_live_perp_events`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_trade_id` (`trade_id`);

--
-- Indexes for table `jupiter_live_perp_event_log`
--
ALTER TABLE `jupiter_live_perp_event_log`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `jupiter_live_perp_trades`
--
ALTER TABLE `jupiter_live_perp_trades`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `jupiter_perp_api_archive`
--
ALTER TABLE `jupiter_perp_api_archive`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `jupiter_perp_api_requests`
--
ALTER TABLE `jupiter_perp_api_requests`
  ADD PRIMARY KEY (`id`),
  ADD KEY `idx_status_priority` (`status`,`priority`,`created_at`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `jupiter_live_perps`
--
ALTER TABLE `jupiter_live_perps`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=73;

--
-- AUTO_INCREMENT for table `jupiter_live_perp_addresses`
--
ALTER TABLE `jupiter_live_perp_addresses`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=6;

--
-- AUTO_INCREMENT for table `jupiter_live_perp_events`
--
ALTER TABLE `jupiter_live_perp_events`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=246;

--
-- AUTO_INCREMENT for table `jupiter_live_perp_event_log`
--
ALTER TABLE `jupiter_live_perp_event_log`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;

--
-- AUTO_INCREMENT for table `jupiter_live_perp_trades`
--
ALTER TABLE `jupiter_live_perp_trades`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=118;

--
-- AUTO_INCREMENT for table `jupiter_perp_api_requests`
--
ALTER TABLE `jupiter_perp_api_requests`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=203333;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `jupiter_live_perp_events`
--
ALTER TABLE `jupiter_live_perp_events`
  ADD CONSTRAINT `jupiter_live_perp_events_ibfk_1` FOREIGN KEY (`trade_id`) REFERENCES `jupiter_live_perps` (`id`) ON DELETE CASCADE;
COMMIT;


