-- AEye Database Schema
-- Run this in phpMyAdmin -> SQL tab

CREATE TABLE IF NOT EXISTS `users` (
  `id` VARCHAR(30) NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `email` VARCHAR(191) NOT NULL,
  `passwordHash` VARCHAR(255) DEFAULT NULL,
  `image` VARCHAR(500) DEFAULT NULL,
  `role` ENUM('user','admin','support') DEFAULT 'user',
  `plan` ENUM('free','starter','pro') DEFAULT 'free',
  `suspended` TINYINT(1) DEFAULT 0,
  `stripeCustomerId` VARCHAR(191) DEFAULT NULL,
  `subscriptionId` VARCHAR(191) DEFAULT NULL,
  `claudeApiKey` TEXT DEFAULT NULL,
  `lastActiveAt` DATETIME DEFAULT NULL,
  `trialStartedAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `trialExpiresAt` DATETIME DEFAULT NULL,
  `trialExtendedBy` VARCHAR(30) DEFAULT NULL,
  `subscriptionEndsAt` DATETIME DEFAULT NULL,
  `subscriptionStatus` VARCHAR(20) DEFAULT 'none',
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `bots` (
  `id` VARCHAR(30) NOT NULL,
  `userId` VARCHAR(30) NOT NULL,
  `name` VARCHAR(191) NOT NULL,
  `status` ENUM('active','paused') DEFAULT 'active',
  `config` JSON DEFAULT NULL,
  `systemPrompt` TEXT DEFAULT NULL,
  `deactivatedAt` DATETIME DEFAULT NULL,
  `deactivationReason` VARCHAR(50) DEFAULT NULL,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_bots_userId` (`userId`),
  CONSTRAINT `fk_bots_user` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `knowledge_chunks` (
  `id` VARCHAR(30) NOT NULL,
  `botId` VARCHAR(30) NOT NULL,
  `source` ENUM('url','file') NOT NULL,
  `content` LONGTEXT NOT NULL,
  `embedding` JSON NOT NULL,
  `url` VARCHAR(500) DEFAULT NULL,
  `filename` VARCHAR(255) DEFAULT NULL,
  `page` INT DEFAULT NULL,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_chunks_botId` (`botId`),
  CONSTRAINT `fk_chunks_bot` FOREIGN KEY (`botId`) REFERENCES `bots`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `conversations` (
  `id` VARCHAR(30) NOT NULL,
  `botId` VARCHAR(30) NOT NULL,
  `sessionId` VARCHAR(100) NOT NULL,
  `channel` ENUM('web','whatsapp') DEFAULT 'web',
  `leadName` VARCHAR(191) DEFAULT NULL,
  `leadEmail` VARCHAR(191) DEFAULT NULL,
  `leadPhone` VARCHAR(50) DEFAULT NULL,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_conv_botId` (`botId`),
  KEY `idx_conv_sessionId` (`sessionId`),
  CONSTRAINT `fk_conv_bot` FOREIGN KEY (`botId`) REFERENCES `bots`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `messages` (
  `id` VARCHAR(30) NOT NULL,
  `conversationId` VARCHAR(30) NOT NULL,
  `role` ENUM('user','assistant') NOT NULL,
  `content` TEXT NOT NULL,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_msg_convId` (`conversationId`),
  CONSTRAINT `fk_msg_conv` FOREIGN KEY (`conversationId`) REFERENCES `conversations`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `leads` (
  `id` VARCHAR(30) NOT NULL,
  `botId` VARCHAR(30) NOT NULL,
  `conversationId` VARCHAR(30) DEFAULT NULL,
  `name` VARCHAR(191) DEFAULT NULL,
  `email` VARCHAR(191) DEFAULT NULL,
  `phone` VARCHAR(50) DEFAULT NULL,
  `company` VARCHAR(191) DEFAULT NULL,
  `budget` VARCHAR(100) DEFAULT NULL,
  `destination` ENUM('email','webhook','none') DEFAULT 'none',
  `status` ENUM('new_lead','contacted','converted') DEFAULT 'new_lead',
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_leads_botId` (`botId`),
  CONSTRAINT `fk_leads_bot` FOREIGN KEY (`botId`) REFERENCES `bots`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `admin_logs` (
  `id` VARCHAR(30) NOT NULL,
  `adminId` VARCHAR(30) NOT NULL,
  `adminEmail` VARCHAR(191) NOT NULL,
  `action` VARCHAR(100) NOT NULL,
  `target` VARCHAR(191) NOT NULL,
  `details` TEXT DEFAULT NULL,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_adminlogs_adminId` (`adminId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `announcements` (
  `id` VARCHAR(30) NOT NULL,
  `message` TEXT NOT NULL,
  `active` TINYINT(1) DEFAULT 1,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `blog_posts` (
  `id` VARCHAR(30) NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `excerpt` TEXT NOT NULL,
  `content` LONGTEXT NOT NULL,
  `featureImage` VARCHAR(500) DEFAULT NULL,
  `category` VARCHAR(100) NOT NULL,
  `color` VARCHAR(10) DEFAULT '#67FFA1',
  `readTime` VARCHAR(20) DEFAULT '5 min read',
  `status` ENUM('draft','published') DEFAULT 'draft',
  `authorId` VARCHAR(30) NOT NULL,
  `publishedAt` DATETIME DEFAULT NULL,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `slug` (`slug`),
  KEY `idx_blog_status` (`status`),
  CONSTRAINT `fk_blog_author` FOREIGN KEY (`authorId`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `blog_bot_configs` (
  `id` VARCHAR(30) NOT NULL,
  `userId` VARCHAR(30) NOT NULL,
  `feedApiKey` VARCHAR(30) NOT NULL,
  `niche` TEXT NOT NULL,
  `tone` VARCHAR(50) DEFAULT 'professional',
  `language` VARCHAR(50) DEFAULT 'English',
  `postsPerDay` INT DEFAULT 1,
  `autoGenerate` TINYINT(1) DEFAULT 0,
  `lastGeneratedAt` DATETIME DEFAULT NULL,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `userId` (`userId`),
  UNIQUE KEY `feedApiKey` (`feedApiKey`),
  CONSTRAINT `fk_blogbot_user` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

CREATE TABLE IF NOT EXISTS `generated_blogs` (
  `id` VARCHAR(30) NOT NULL,
  `userId` VARCHAR(30) NOT NULL,
  `title` VARCHAR(255) NOT NULL,
  `seoTitle` TEXT NOT NULL,
  `slug` VARCHAR(191) NOT NULL,
  `metaDescription` TEXT NOT NULL,
  `keywords` TEXT NOT NULL,
  `tags` TEXT NOT NULL,
  `excerpt` TEXT NOT NULL,
  `content` LONGTEXT NOT NULL,
  `category` VARCHAR(100) DEFAULT 'General',
  `status` ENUM('draft','published','archived') DEFAULT 'draft',
  `wordCount` INT DEFAULT 0,
  `publishedAt` DATETIME DEFAULT NULL,
  `createdAt` DATETIME DEFAULT CURRENT_TIMESTAMP,
  `updatedAt` DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_genblog_userId` (`userId`),
  KEY `idx_genblog_status` (`userId`, `status`),
  CONSTRAINT `fk_genblog_user` FOREIGN KEY (`userId`) REFERENCES `users`(`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
