CREATE USER 'linkedincrawler'@'localhost' IDENTIFIED BY 'linked1nS3cret';

CREATE DATABASE linkedin_crawler_db;

GRANT ALL PRIVILEGES ON linkedin_crawler_db.* TO 'linkedincrawler'@'localhost';

USE linkedin_crawler_db;

CREATE TABLE IF NOT EXISTS `crawl_requests` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `url` TEXT NOT NULL,
  `status` VARCHAR(30) NOT NULL,
  `crawlbase_rid` VARCHAR(255) NOT NULL
);
CREATE INDEX `idx_crawl_requests_status` ON `crawl_requests` (`status`);
CREATE INDEX `idx_crawl_requests_crawlbase_rid` ON `crawl_requests` (`crawlbase_rid`);
CREATE INDEX `idx_crawl_requests_status_crawlbase_rid` ON `crawl_requests` (`status`, `crawlbase_rid`);

CREATE TABLE IF NOT EXISTS `linkedin_profiles` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `crawl_request_id` INT NOT NULL,
  `title` VARCHAR(255),
  `headline` VARCHAR(255),
  `summary` TEXT,

  FOREIGN KEY (`crawl_request_id`) REFERENCES `crawl_requests`(`id`)
);

CREATE TABLE IF NOT EXISTS `linkedin_profile_experiences` (
  `id` INT AUTO_INCREMENT PRIMARY KEY,
  `linkedin_profile_id` INT NOT NULL,
  `title` VARCHAR(255),
  `company_name` VARCHAR(255),
  `description` TEXT,
  `is_current` BIT NOT NULL DEFAULT 0,

  FOREIGN KEY (`linkedin_profile_id`) REFERENCES `linkedin_profiles`(`id`)
);