/home/devscfvi/AblePro/journal.sql
-- phpMyAdmin SQL Dump
-- version 5.2.1
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: Mar 15, 2026 at 11:28 PM
-- Server version: 10.4.32-MariaDB
-- PHP Version: 8.2.12
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `journal`
--
-- --------------------------------------------------------
--
-- Table structure for table `budget_transactions`
--
CREATE TABLE `budget_transactions` (
`id` int(11) NOT NULL,
`transaction_type` enum('deposit','withdrawal') NOT NULL,
`amount` decimal(15,2) NOT NULL,
`description` varchar(255) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT current_timestamp(),
`user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `crypto_trades`
--
CREATE TABLE `crypto_trades` (
`id` int(11) NOT NULL,
`coin` varchar(50) NOT NULL,
`position_size` decimal(15,2) NOT NULL,
`entry_price` decimal(15,2) NOT NULL,
`stop_loss_percent` decimal(5,2) NOT NULL,
`take_profit_percent` decimal(5,2) NOT NULL,
`quantity` decimal(20,8) NOT NULL,
`sl_price` decimal(15,2) NOT NULL,
`tp_price` decimal(15,2) NOT NULL,
`loss_amount` decimal(15,2) NOT NULL,
`profit_amount` decimal(15,2) NOT NULL,
`reward_risk_ratio` decimal(5,2) NOT NULL,
`fees` decimal(10,2) DEFAULT 0.00,
`order_type` enum('market','limit') DEFAULT 'market',
`trade_category` varchar(50) DEFAULT NULL,
`emotion` varchar(50) DEFAULT NULL,
`discipline_score` int(11) DEFAULT NULL,
`mistake_type` varchar(100) DEFAULT NULL,
`lesson_learned` text DEFAULT NULL,
`pre_trade_plan` text DEFAULT NULL,
`post_trade_review` text DEFAULT NULL,
`screenshot_url` varchar(255) DEFAULT NULL,
`status` enum('pending','win','loss') DEFAULT 'pending',
`actual_exit_price` decimal(15,2) DEFAULT NULL,
`actual_pnl` decimal(15,2) DEFAULT NULL,
`comment` text DEFAULT NULL,
`created_at` timestamp NULL DEFAULT current_timestamp(),
`entry_screenshot` varchar(255) DEFAULT NULL COMMENT 'Path to entry screenshot file',
`exit_screenshot` varchar(255) DEFAULT NULL COMMENT 'Path to exit screenshot file',
`trade_mode` varchar(10) NOT NULL DEFAULT 'crypto',
`screenshots` text DEFAULT NULL,
`trade_date` date DEFAULT NULL,
`user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `crypto_trades`
--
INSERT INTO `crypto_trades` (`id`, `coin`, `position_size`, `entry_price`, `stop_loss_percent`, `take_profit_percent`, `quantity`, `sl_price`, `tp_price`, `loss_amount`, `profit_amount`, `reward_risk_ratio`, `fees`, `order_type`, `trade_category`, `emotion`, `discipline_score`, `mistake_type`, `lesson_learned`, `pre_trade_plan`, `post_trade_review`, `screenshot_url`, `status`, `actual_exit_price`, `actual_pnl`, `comment`, `created_at`, `entry_screenshot`, `exit_screenshot`, `trade_mode`, `screenshots`, `trade_date`, `user_id`) VALUES
(57, 'QCLS', 1912.00, 4.78, 0.00, 0.00, 400.00000000, 0.00, 0.00, 0.00, 14.32, 0.00, 4.08, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'win', NULL, NULL, 'Imported from IBKR (.tlg) — 2026-03-06 | P&L: +$14.32', '2026-03-09 17:26:22', NULL, NULL, 'stocks', NULL, '2026-03-06', NULL),
(58, 'GXAI', 4082.87, 2.27, 0.00, 0.00, 1800.00000000, 0.00, 0.00, 118.72, 0.00, 0.00, 18.35, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'loss', NULL, NULL, 'Imported from IBKR (.tlg) — 2026-03-05 | P&L: $-118.72', '2026-03-09 17:26:22', NULL, NULL, 'stocks', NULL, '2026-03-05', NULL),
(59, 'XWEL', 1345.00, 1.35, 0.00, 0.00, 1000.00000000, 0.00, 0.00, 0.00, 179.31, 0.00, 10.70, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'win', NULL, NULL, 'Imported from IBKR (.tlg) — 2026-02-25 | P&L: +$179.31', '2026-03-09 17:26:22', NULL, NULL, 'stocks', NULL, '2026-02-25', NULL),
(60, 'EDSA', 1918.68, 1.28, 0.00, 0.00, 1500.00000000, 0.00, 0.00, 0.00, 4.02, 0.00, 15.29, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'win', NULL, NULL, 'Imported from IBKR (.tlg) — 2026-02-24 | P&L: +$4.02', '2026-03-09 17:26:22', NULL, NULL, 'stocks', NULL, '2026-02-24', NULL),
(61, 'DRMA', 1134.00, 1.89, 0.00, 0.00, 600.00000000, 0.00, 0.00, 12.12, 0.00, 0.00, 6.12, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'loss', NULL, NULL, 'Imported from IBKR (.tlg) — 2026-02-19 | P&L: $-12.12', '2026-03-09 17:26:22', NULL, NULL, 'stocks', NULL, '2026-02-19', NULL),
(62, 'CDIO', 1025.00, 2.05, 0.00, 0.00, 500.00000000, 0.00, 0.00, 0.00, 11.12, 0.00, 5.10, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'win', NULL, NULL, 'Imported from IBKR (.tlg) — 2026-02-18 | P&L: +$11.12', '2026-03-09 17:26:22', NULL, NULL, 'stocks', NULL, '2026-02-18', NULL),
(63, 'ATOM', 135.75, 5.43, 0.00, 0.00, 25.00000000, 0.00, 0.00, 1.00, 0.00, 0.00, 2.00, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'loss', NULL, NULL, 'Imported from IBKR (.tlg) — 2026-02-17 | P&L: $-1.00', '2026-03-09 17:26:22', NULL, NULL, 'stocks', NULL, '2026-02-17', NULL),
(64, 'SVCO', 1520.64, 5.07, 0.00, 0.00, 300.00000000, 0.00, 0.00, 2.70, 0.00, 0.00, 3.06, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'loss', 5.07, -2.70, 'Imported from IBKR (.tlg) — 2026-03-13 | P&L: $-2.70 (+0.024%)', '2026-03-15 20:31:04', NULL, NULL, 'crypto', NULL, '2026-03-13', NULL),
(65, 'SVCO', 1517.40, 5.06, 0.00, 0.00, 300.00000000, 0.00, 0.00, 3.46, 0.00, 0.00, 3.06, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'loss', 5.06, -3.46, 'Imported from IBKR (.tlg) — 2026-03-13 | P&L: $-3.46 (-0.026%)', '2026-03-15 20:31:04', NULL, NULL, 'crypto', NULL, '2026-03-13', NULL),
(66, 'QCLS', 1912.00, 4.78, 0.00, 0.00, 400.00000000, 0.00, 0.00, 0.00, 14.32, 0.00, 4.08, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'win', 4.83, 14.32, 'Imported from IBKR (.tlg) — 2026-03-06 | P&L: +$14.32 (+0.962%)', '2026-03-15 20:31:04', NULL, NULL, 'crypto', NULL, '2026-03-06', NULL),
(67, 'GXAI', 1960.47, 2.18, 0.00, 0.00, 900.00000000, 0.00, 0.00, 39.15, 0.00, 0.00, 9.18, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'loss', 2.15, -39.15, 'Imported from IBKR (.tlg) — 2026-03-05 | P&L: $-39.15 (-1.529%)', '2026-03-15 20:31:04', NULL, NULL, 'crypto', NULL, '2026-03-05', NULL),
(68, 'GXAI', 2122.40, 2.36, 0.00, 0.00, 900.00000000, 0.00, 0.00, 79.58, 0.00, 0.00, 9.18, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'loss', 2.28, -79.58, 'Imported from IBKR (.tlg) — 2026-03-05 | P&L: $-79.58 (-3.317%)', '2026-03-15 20:31:04', NULL, NULL, 'crypto', NULL, '2026-03-05', NULL),
(69, 'XWEL', 1345.00, 1.35, 0.00, 0.00, 1000.00000000, 0.00, 0.00, 0.00, 179.31, 0.00, 10.70, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'win', 1.54, 179.31, 'Imported from IBKR (.tlg) — 2026-02-25 | P&L: +$179.31 (+14.126%)', '2026-03-15 20:31:04', NULL, NULL, 'crypto', NULL, '2026-02-25', NULL),
(70, 'EDSA', 1918.68, 1.28, 0.00, 0.00, 1500.00000000, 0.00, 0.00, 0.00, 4.02, 0.00, 15.29, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'win', 1.29, 4.02, 'Imported from IBKR (.tlg) — 2026-02-24 | P&L: +$4.02 (+1.007%)', '2026-03-15 20:31:04', NULL, NULL, 'crypto', NULL, '2026-02-24', NULL),
(71, 'DRMA', 1134.00, 1.89, 0.00, 0.00, 600.00000000, 0.00, 0.00, 12.12, 0.00, 0.00, 6.12, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'loss', 1.88, -12.12, 'Imported from IBKR (.tlg) — 2026-02-19 | P&L: $-12.12 (-0.529%)', '2026-03-15 20:31:04', NULL, NULL, 'crypto', NULL, '2026-02-19', NULL),
(72, 'CDIO', 1025.00, 2.05, 0.00, 0.00, 500.00000000, 0.00, 0.00, 0.00, 11.12, 0.00, 5.10, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'win', 2.08, 11.12, 'Imported from IBKR (.tlg) — 2026-02-18 | P&L: +$11.12 (+1.582%)', '2026-03-15 20:31:04', NULL, NULL, 'crypto', NULL, '2026-02-18', NULL),
(73, 'ATOM', 135.75, 5.43, 0.00, 0.00, 25.00000000, 0.00, 0.00, 1.00, 0.00, 0.00, 2.00, 'market', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'loss', 5.47, -1.00, 'Imported from IBKR (.tlg) — 2026-02-17 | P&L: $-1.00 (+0.737%)', '2026-03-15 20:31:04', NULL, NULL, 'crypto', NULL, '2026-02-17', NULL);
-- --------------------------------------------------------
--
-- Table structure for table `favorite_coins`
--
CREATE TABLE `favorite_coins` (
`id` int(11) NOT NULL,
`coins` text NOT NULL,
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `favorite_coins`
--
INSERT INTO `favorite_coins` (`id`, `coins`, `updated_at`, `user_id`) VALUES
(6, '[\"BTC\",\"ETH\",\"SOL\",\"SUI\",\"XRP\",\"ADA\",\"LINK\",\"SOMI\"]', '2026-01-30 16:35:23', NULL);
-- --------------------------------------------------------
--
-- Table structure for table `password_resets`
--
CREATE TABLE `password_resets` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`token_hash` varchar(255) NOT NULL,
`expires_at` datetime NOT NULL,
`used` tinyint(1) NOT NULL DEFAULT 0,
`created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `trade_mistakes`
--
CREATE TABLE `trade_mistakes` (
`id` int(11) NOT NULL,
`mistake_name` varchar(100) NOT NULL,
`category` varchar(50) NOT NULL,
`description` text DEFAULT NULL,
`solution` text DEFAULT NULL,
`is_active` tinyint(1) DEFAULT 1,
`created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `trade_mistakes`
--
INSERT INTO `trade_mistakes` (`id`, `mistake_name`, `category`, `description`, `solution`, `is_active`, `created_at`) VALUES
(1, 'Entered too early', 'Timing', 'Entered position before confirmation', 'Wait for confirmation signals', 1, '2025-12-02 08:18:28'),
(2, 'Entered too late', 'Timing', 'Missed optimal entry point', 'Set alerts and be more prepared', 1, '2025-12-02 08:18:28'),
(3, 'Ignored stop loss', 'Risk Management', 'Did not follow planned stop loss', 'Stick to pre-defined risk levels', 1, '2025-12-02 08:18:28'),
(4, 'Moved stop loss', 'Risk Management', 'Changed stop loss during trade', 'Never move stop loss against position', 1, '2025-12-02 08:18:28'),
(5, 'Took profit too early', 'Profit Management', 'Exited before target', 'Trust your analysis and targets', 1, '2025-12-02 08:18:28'),
(6, 'Greed - held too long', 'Psychology', 'Did not take profit at target', 'Follow the trading plan', 1, '2025-12-02 08:18:28'),
(7, 'FOMO entry', 'Psychology', 'Entered due to fear of missing out', 'Only trade planned setups', 1, '2025-12-02 08:18:28'),
(8, 'Revenge trading', 'Psychology', 'Traded to recover losses', 'Take a break after losses', 1, '2025-12-02 08:18:28'),
(9, 'Overleveraged', 'Risk Management', 'Position size too large', 'Follow position sizing rules', 1, '2025-12-02 08:18:28'),
(10, 'No clear plan', 'Planning', 'Entered without defined strategy', 'Always have entry/exit plan', 1, '2025-12-02 08:18:28'),
(11, 'Ignored trend', 'Analysis', 'Traded against main trend', 'Trade with the trend', 1, '2025-12-02 08:18:28'),
(12, 'Poor risk/reward', 'Planning', 'R:R ratio was unfavorable', 'Only take trades with R:R > 1.5', 1, '2025-12-02 08:18:28');
-- --------------------------------------------------------
--
-- Table structure for table `trade_tags`
--
CREATE TABLE `trade_tags` (
`id` int(11) NOT NULL,
`trade_id` int(11) NOT NULL,
`tag` varchar(50) NOT NULL,
`created_at` timestamp NULL DEFAULT current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
-- --------------------------------------------------------
--
-- Table structure for table `trading_budget`
--
CREATE TABLE `trading_budget` (
`id` int(11) NOT NULL,
`starting_capital` decimal(15,2) NOT NULL DEFAULT 0.00,
`current_balance` decimal(15,2) NOT NULL DEFAULT 0.00,
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `trading_budget`
--
INSERT INTO `trading_budget` (`id`, `starting_capital`, `current_balance`, `updated_at`) VALUES
(1, 0.00, 0.00, '2025-12-11 09:15:48');
-- --------------------------------------------------------
--
-- Table structure for table `trading_goals`
--
CREATE TABLE `trading_goals` (
`id` int(11) NOT NULL,
`goal_type` enum('daily','weekly','monthly','yearly') NOT NULL,
`target_profit` decimal(15,2) DEFAULT NULL,
`target_win_rate` decimal(5,2) DEFAULT NULL,
`max_daily_loss` decimal(15,2) DEFAULT NULL,
`min_risk_reward` decimal(5,2) DEFAULT NULL,
`period_start` date NOT NULL,
`period_end` date NOT NULL,
`notes` text DEFAULT NULL,
`created_at` timestamp NULL DEFAULT current_timestamp(),
`user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `trading_goals`
--
INSERT INTO `trading_goals` (`id`, `goal_type`, `target_profit`, `target_win_rate`, `max_daily_loss`, `min_risk_reward`, `period_start`, `period_end`, `notes`, `created_at`, `user_id`) VALUES
(1, 'yearly', 100000.00, 70.00, 250.00, 2.50, '2025-12-07', '2026-12-31', 'This would help built confidence to pursue trading full time. ', '2025-12-07 16:10:58', NULL);
-- --------------------------------------------------------
--
-- Table structure for table `trading_journal_entries`
--
CREATE TABLE `trading_journal_entries` (
`id` int(11) NOT NULL,
`entry_date` date NOT NULL,
`market_condition` varchar(50) DEFAULT NULL,
`overall_emotion` varchar(50) DEFAULT NULL,
`what_went_well` text DEFAULT NULL,
`what_went_wrong` text DEFAULT NULL,
`lessons_learned` text DEFAULT NULL,
`tomorrow_focus` text DEFAULT NULL,
`energy_level` int(11) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT current_timestamp(),
`user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `trading_journal_entries`
--
INSERT INTO `trading_journal_entries` (`id`, `entry_date`, `market_condition`, `overall_emotion`, `what_went_well`, `what_went_wrong`, `lessons_learned`, `tomorrow_focus`, `energy_level`, `created_at`, `user_id`) VALUES
(1, '2025-12-12', 'Volatile', 'Fearful', 'Uhmm. We found a decent momentum trade. ', 'We got out too early on MMT. The price did hit our initial TP. But we altered ours and got like 0.04 instead of 1.5%', 'If the momentum, is in the side of the coin and the you got in a decent position. Don’t be too fearful about what’s BTC doing. Although it’s also good to look at BTC Incase of any major dump. ', 'Continue to learn and grow. ', 7, '2025-12-12 19:17:43', NULL),
(3, '2025-12-15', 'Volatile', 'Greedy', '', 'We took a very bad trade. ', 'Always set your TP and SL before the trade. ', 'Learn to setup TP and SL before or after the trade immediately.', 4, '2025-12-15 14:51:47', NULL),
(4, '2026-01-21', 'Volatile', 'Fearful', '', 'You messed with the stop loss ', 'I learnt that volume is very important, Even if the structure seems good. You need to confirm there is decent trading volume. ', '', 7, '2026-01-21 15:40:25', NULL),
(5, '2026-01-28', 'Volatile', 'Excited', 'We went through Ross’s Video and got his trading plan ', 'You did not cross check the volume was the stock before getting in.', 'Before taking a trade cross check the volume. ', 'Write down the setup and go through the PDF', 7, '2026-01-28 17:54:30', NULL),
(6, '2026-01-29', NULL, NULL, 'We made 70$ in profit', 'We were getting 200$ from Feet, use limit orders. ', 'Entry is very important, should be calculated. ', 'Focus on better entry and use limit orders. Have the SELL screen opened at all times. ', NULL, '2026-01-29 16:52:48', NULL);
-- --------------------------------------------------------
--
-- Table structure for table `users`
--
CREATE TABLE `users` (
`id` int(11) NOT NULL,
`first_name` varchar(100) NOT NULL,
`last_name` varchar(100) NOT NULL,
`email` varchar(255) NOT NULL,
`password_hash` varchar(255) NOT NULL,
`trade_mode` enum('crypto','stocks') NOT NULL DEFAULT 'crypto',
`trial_ends_at` datetime DEFAULT NULL,
`subscribed` tinyint(1) NOT NULL DEFAULT 0,
`created_at` timestamp NULL DEFAULT current_timestamp(),
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `users`
--
INSERT INTO `users` (`id`, `first_name`, `last_name`, `email`, `password_hash`, `trade_mode`, `trial_ends_at`, `subscribed`, `created_at`, `updated_at`) VALUES
(1, 'Sheikh', 'Usman', 'usmansaeed91@gmail.com', '$2y$12$IZvTw4NsBzUpVctp/2h2suctw4BA4foGYy8QZb3mwLol4ANsZPznq', 'stocks', '2026-03-22 22:58:20', 0, '2026-03-15 21:58:20', '2026-03-15 21:58:20');
-- --------------------------------------------------------
--
-- Table structure for table `user_budget`
--
CREATE TABLE `user_budget` (
`id` int(11) NOT NULL,
`budget` decimal(15,2) NOT NULL,
`updated_at` timestamp NULL DEFAULT current_timestamp() ON UPDATE current_timestamp(),
`user_id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;
--
-- Dumping data for table `user_budget`
--
INSERT INTO `user_budget` (`id`, `budget`, `updated_at`, `user_id`) VALUES
(12, 5356.00, '2026-01-30 15:49:29', NULL);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v_mistake_analysis`
-- (See below for the actual view)
--
CREATE TABLE `v_mistake_analysis` (
`mistake_type` varchar(100)
,`occurrence_count` bigint(21)
,`resulted_in_loss` decimal(22,0)
,`total_loss_amount` decimal(37,2)
,`avg_loss_per_occurrence` decimal(19,6)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v_performance_by_category`
-- (See below for the actual view)
--
CREATE TABLE `v_performance_by_category` (
`trade_category` varchar(50)
,`total_trades` bigint(21)
,`wins` decimal(22,0)
,`losses` decimal(22,0)
,`win_rate` decimal(28,2)
,`net_pnl` decimal(37,2)
,`avg_rr` decimal(9,6)
);
-- --------------------------------------------------------
--
-- Stand-in structure for view `v_performance_by_coin`
-- (See below for the actual view)
--
CREATE TABLE `v_performance_by_coin` (
`coin` varchar(50)
,`total_trades` bigint(21)
,`wins` decimal(22,0)
,`losses` decimal(22,0)
,`win_rate` decimal(28,2)
,`net_pnl` decimal(37,2)
,`avg_rr` decimal(9,6)
);
-- --------------------------------------------------------
--
-- Structure for view `v_mistake_analysis`
--
DROP TABLE IF EXISTS `v_mistake_analysis`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_mistake_analysis` AS SELECT `t`.`mistake_type` AS `mistake_type`, count(0) AS `occurrence_count`, sum(case when `t`.`status` = 'loss' then 1 else 0 end) AS `resulted_in_loss`, sum(case when `t`.`status` = 'loss' then `t`.`loss_amount` else 0 end) AS `total_loss_amount`, avg(case when `t`.`status` = 'loss' then `t`.`loss_amount` else NULL end) AS `avg_loss_per_occurrence` FROM `crypto_trades` AS `t` WHERE `t`.`mistake_type` is not null GROUP BY `t`.`mistake_type` ORDER BY count(0) DESC ;
-- --------------------------------------------------------
--
-- Structure for view `v_performance_by_category`
--
DROP TABLE IF EXISTS `v_performance_by_category`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_performance_by_category` AS SELECT `crypto_trades`.`trade_category` AS `trade_category`, count(0) AS `total_trades`, sum(case when `crypto_trades`.`status` = 'win' then 1 else 0 end) AS `wins`, sum(case when `crypto_trades`.`status` = 'loss' then 1 else 0 end) AS `losses`, round(sum(case when `crypto_trades`.`status` = 'win' then 1 else 0 end) * 100.0 / nullif(sum(case when `crypto_trades`.`status` in ('win','loss') then 1 else 0 end),0),2) AS `win_rate`, sum(case when `crypto_trades`.`status` = 'win' then `crypto_trades`.`profit_amount` when `crypto_trades`.`status` = 'loss' then -`crypto_trades`.`loss_amount` else 0 end) AS `net_pnl`, avg(`crypto_trades`.`reward_risk_ratio`) AS `avg_rr` FROM `crypto_trades` WHERE `crypto_trades`.`trade_category` is not null GROUP BY `crypto_trades`.`trade_category` ;
-- --------------------------------------------------------
--
-- Structure for view `v_performance_by_coin`
--
DROP TABLE IF EXISTS `v_performance_by_coin`;
CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `v_performance_by_coin` AS SELECT `crypto_trades`.`coin` AS `coin`, count(0) AS `total_trades`, sum(case when `crypto_trades`.`status` = 'win' then 1 else 0 end) AS `wins`, sum(case when `crypto_trades`.`status` = 'loss' then 1 else 0 end) AS `losses`, round(sum(case when `crypto_trades`.`status` = 'win' then 1 else 0 end) * 100.0 / nullif(sum(case when `crypto_trades`.`status` in ('win','loss') then 1 else 0 end),0),2) AS `win_rate`, sum(case when `crypto_trades`.`status` = 'win' then `crypto_trades`.`profit_amount` when `crypto_trades`.`status` = 'loss' then -`crypto_trades`.`loss_amount` else 0 end) AS `net_pnl`, avg(`crypto_trades`.`reward_risk_ratio`) AS `avg_rr` FROM `crypto_trades` GROUP BY `crypto_trades`.`coin` ;
--
-- Indexes for dumped tables
--
--
-- Indexes for table `budget_transactions`
--
ALTER TABLE `budget_transactions`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_transaction_date` (`created_at`);
--
-- Indexes for table `crypto_trades`
--
ALTER TABLE `crypto_trades`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_created_at` (`created_at`),
ADD KEY `idx_trade_category` (`trade_category`),
ADD KEY `idx_emotion` (`emotion`),
ADD KEY `idx_status_created` (`status`,`created_at`),
ADD KEY `idx_user_id` (`user_id`);
--
-- Indexes for table `favorite_coins`
--
ALTER TABLE `favorite_coins`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `password_resets`
--
ALTER TABLE `password_resets`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_token` (`token_hash`),
ADD KEY `pr_user_fk` (`user_id`);
--
-- Indexes for table `trade_mistakes`
--
ALTER TABLE `trade_mistakes`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_category` (`category`);
--
-- Indexes for table `trade_tags`
--
ALTER TABLE `trade_tags`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_trade_id` (`trade_id`),
ADD KEY `idx_tag` (`tag`);
--
-- Indexes for table `trading_budget`
--
ALTER TABLE `trading_budget`
ADD PRIMARY KEY (`id`);
--
-- Indexes for table `trading_goals`
--
ALTER TABLE `trading_goals`
ADD PRIMARY KEY (`id`),
ADD KEY `idx_period` (`period_start`,`period_end`);
--
-- Indexes for table `trading_journal_entries`
--
ALTER TABLE `trading_journal_entries`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `idx_entry_date` (`entry_date`);
--
-- Indexes for table `users`
--
ALTER TABLE `users`
ADD PRIMARY KEY (`id`),
ADD UNIQUE KEY `idx_email` (`email`);
--
-- Indexes for table `user_budget`
--
ALTER TABLE `user_budget`
ADD PRIMARY KEY (`id`);
--
-- AUTO_INCREMENT for dumped tables
--
--
-- AUTO_INCREMENT for table `budget_transactions`
--
ALTER TABLE `budget_transactions`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=3;
--
-- AUTO_INCREMENT for table `crypto_trades`
--
ALTER TABLE `crypto_trades`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=74;
--
-- AUTO_INCREMENT for table `favorite_coins`
--
ALTER TABLE `favorite_coins`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `password_resets`
--
ALTER TABLE `password_resets`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `trade_mistakes`
--
ALTER TABLE `trade_mistakes`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
--
-- AUTO_INCREMENT for table `trade_tags`
--
ALTER TABLE `trade_tags`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT;
--
-- AUTO_INCREMENT for table `trading_budget`
--
ALTER TABLE `trading_budget`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `trading_goals`
--
ALTER TABLE `trading_goals`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `trading_journal_entries`
--
ALTER TABLE `trading_journal_entries`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;
--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2;
--
-- AUTO_INCREMENT for table `user_budget`
--
ALTER TABLE `user_budget`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;
--
-- Constraints for dumped tables
--
--
-- Constraints for table `password_resets`
--
ALTER TABLE `password_resets`
ADD CONSTRAINT `pr_user_fk` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE;
--
-- Constraints for table `trade_tags`
--
ALTER TABLE `trade_tags`
ADD CONSTRAINT `trade_tags_ibfk_1` FOREIGN KEY (`trade_id`) REFERENCES `crypto_trades` (`id`) ON DELETE CASCADE;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
