-- Site Crawler Database Schema
CREATE DATABASE IF NOT EXISTS sitecrawler CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE sitecrawler;

CREATE TABLE IF NOT EXISTS accounts (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(150) NOT NULL,
    email VARCHAR(150) DEFAULT NULL,
    api_key VARCHAR(64) NOT NULL UNIQUE,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS sites (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    account_id INT UNSIGNED NOT NULL,
    site_url VARCHAR(500) NOT NULL,
    sitemap_url VARCHAR(500) NOT NULL,
    tags_to_fetch TEXT NOT NULL,
    status ENUM('active','inactive') NOT NULL DEFAULT 'active',
    last_crawled_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON DELETE CASCADE,
    INDEX idx_account (account_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crawled_urls (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    site_id INT UNSIGNED NOT NULL,
    url VARCHAR(700) NOT NULL,
    lastmod VARCHAR(50) DEFAULT NULL,
    http_status INT DEFAULT NULL,
    crawl_status ENUM('pending','success','failed') NOT NULL DEFAULT 'pending',
    error_message VARCHAR(500) DEFAULT NULL,
    crawled_at DATETIME DEFAULT NULL,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (site_id) REFERENCES sites(id) ON DELETE CASCADE,
    UNIQUE KEY uniq_site_url (site_id, url),
    INDEX idx_site (site_id)
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS crawled_data (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    crawled_url_id INT UNSIGNED NOT NULL,
    tag_name VARCHAR(100) NOT NULL,
    tag_value MEDIUMTEXT,
    created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (crawled_url_id) REFERENCES crawled_urls(id) ON DELETE CASCADE,
    INDEX idx_crawled_url (crawled_url_id),
    FULLTEXT KEY ft_tag_value (tag_value)
) ENGINE=InnoDB;