
-- ApexTradingBot-Pro Database Schema
-- Import this into your cPanel MySQL database

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

-- Users table
CREATE TABLE `users` (
  `id` varchar(191) NOT NULL,
  `name` varchar(191) DEFAULT NULL,
  `email` varchar(191) DEFAULT NULL,
  `emailVerified` datetime(3) DEFAULT NULL,
  `image` varchar(191) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) NOT NULL,
  `subscription` enum('FREE','BASIC','PRO','ENTERPRISE') NOT NULL DEFAULT 'FREE',
  `subscriptionStatus` varchar(191) DEFAULT NULL,
  `stripeCustomerId` varchar(191) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_key` (`email`)
);

-- Accounts table (for OAuth)
CREATE TABLE `accounts` (
  `id` varchar(191) NOT NULL,
  `userId` varchar(191) NOT NULL,
  `type` varchar(191) NOT NULL,
  `provider` varchar(191) NOT NULL,
  `providerAccountId` varchar(191) NOT NULL,
  `refresh_token` text,
  `access_token` text,
  `expires_at` int DEFAULT NULL,
  `token_type` varchar(191) DEFAULT NULL,
  `scope` varchar(191) DEFAULT NULL,
  `id_token` text,
  `session_state` varchar(191) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `accounts_provider_providerAccountId_key` (`provider`,`providerAccountId`),
  KEY `accounts_userId_fkey` (`userId`),
  CONSTRAINT `accounts_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Sessions table
CREATE TABLE `sessions` (
  `id` varchar(191) NOT NULL,
  `sessionToken` varchar(191) NOT NULL,
  `userId` varchar(191) NOT NULL,
  `expires` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sessions_sessionToken_key` (`sessionToken`),
  KEY `sessions_userId_fkey` (`userId`),
  CONSTRAINT `sessions_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Verification tokens table
CREATE TABLE `verificationtokens` (
  `identifier` varchar(191) NOT NULL,
  `token` varchar(191) NOT NULL,
  `expires` datetime(3) NOT NULL,
  UNIQUE KEY `verificationtokens_token_key` (`token`),
  UNIQUE KEY `verificationtokens_identifier_token_key` (`identifier`,`token`)
);

-- Trading bots table
CREATE TABLE `trading_bots` (
  `id` varchar(191) NOT NULL,
  `userId` varchar(191) NOT NULL,
  `name` varchar(191) NOT NULL,
  `strategy` varchar(191) NOT NULL,
  `status` enum('ACTIVE','PAUSED','STOPPED') NOT NULL DEFAULT 'PAUSED',
  `settings` json NOT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `trading_bots_userId_fkey` (`userId`),
  CONSTRAINT `trading_bots_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Trades table
CREATE TABLE `trades` (
  `id` varchar(191) NOT NULL,
  `userId` varchar(191) NOT NULL,
  `botId` varchar(191) DEFAULT NULL,
  `symbol` varchar(191) NOT NULL,
  `side` enum('BUY','SELL') NOT NULL,
  `amount` decimal(20,8) NOT NULL,
  `price` decimal(20,8) NOT NULL,
  `total` decimal(20,8) NOT NULL,
  `fee` decimal(20,8) DEFAULT NULL,
  `status` enum('PENDING','FILLED','CANCELLED','FAILED') NOT NULL DEFAULT 'PENDING',
  `exchange` varchar(191) NOT NULL,
  `executedAt` datetime(3) DEFAULT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  PRIMARY KEY (`id`),
  KEY `trades_userId_fkey` (`userId`),
  KEY `trades_botId_fkey` (`botId`),
  CONSTRAINT `trades_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `trades_botId_fkey` FOREIGN KEY (`botId`) REFERENCES `trading_bots` (`id`) ON DELETE SET NULL ON UPDATE CASCADE
);

-- API keys table (encrypted)
CREATE TABLE `api_keys` (
  `id` varchar(191) NOT NULL,
  `userId` varchar(191) NOT NULL,
  `exchange` varchar(191) NOT NULL,
  `apiKey` text NOT NULL,
  `secretKey` text NOT NULL,
  `passphrase` text DEFAULT NULL,
  `isActive` boolean NOT NULL DEFAULT true,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `api_keys_userId_fkey` (`userId`),
  CONSTRAINT `api_keys_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

-- Subscriptions table
CREATE TABLE `subscriptions` (
  `id` varchar(191) NOT NULL,
  `userId` varchar(191) NOT NULL,
  `stripeSubscriptionId` varchar(191) NOT NULL,
  `status` varchar(191) NOT NULL,
  `planId` varchar(191) NOT NULL,
  `currentPeriodStart` datetime(3) NOT NULL,
  `currentPeriodEnd` datetime(3) NOT NULL,
  `createdAt` datetime(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  `updatedAt` datetime(3) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `subscriptions_stripeSubscriptionId_key` (`stripeSubscriptionId`),
  KEY `subscriptions_userId_fkey` (`userId`),
  CONSTRAINT `subscriptions_userId_fkey` FOREIGN KEY (`userId`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
);

COMMIT;
