-- ============================================
-- Dino Browser - Database Schema
-- Host: localhost
-- Database: ahmeuesz_dino
-- User: ahmeuesz_dino_user
-- ============================================

-- Create users table
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `firebase_uid` VARCHAR(128) NOT NULL UNIQUE,
    `email` VARCHAR(255) NOT NULL,
    `display_name` VARCHAR(100) DEFAULT NULL,
    `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `last_sync_at` DATETIME DEFAULT NULL,
    `is_active` TINYINT(1) NOT NULL DEFAULT 1,
    INDEX `idx_firebase_uid` (`firebase_uid`),
    INDEX `idx_email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create bookmarks table
CREATE TABLE IF NOT EXISTS `bookmarks` (
    `id` VARCHAR(50) NOT NULL,
    `user_id` VARCHAR(128) NOT NULL,
    `url` TEXT NOT NULL,
    `title` VARCHAR(500) NOT NULL,
    `favicon_url` TEXT DEFAULT NULL,
    `folder_id` VARCHAR(50) DEFAULT NULL,
    `position` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`, `user_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_folder_id` (`folder_id`),
    INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create bookmark folders table
CREATE TABLE IF NOT EXISTS `bookmark_folders` (
    `id` VARCHAR(50) NOT NULL,
    `user_id` VARCHAR(128) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `parent_id` VARCHAR(50) DEFAULT NULL,
    `icon` VARCHAR(10) DEFAULT '📁',
    `position` INT NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`, `user_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_parent_id` (`parent_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create history table
CREATE TABLE IF NOT EXISTS `history` (
    `id` VARCHAR(50) NOT NULL,
    `user_id` VARCHAR(128) NOT NULL,
    `url` TEXT NOT NULL,
    `title` VARCHAR(500) NOT NULL,
    `favicon_url` TEXT DEFAULT NULL,
    `visited_at` DATETIME NOT NULL,
    `visit_count` INT NOT NULL DEFAULT 1,
    PRIMARY KEY (`id`, `user_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_visited_at` (`visited_at`),
    INDEX `idx_visit_count` (`visit_count`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create saved summaries table
CREATE TABLE IF NOT EXISTS `summaries` (
    `id` VARCHAR(50) NOT NULL,
    `user_id` VARCHAR(128) NOT NULL,
    `url` TEXT NOT NULL,
    `page_title` VARCHAR(500) NOT NULL,
    `summary` LONGTEXT NOT NULL,
    `is_youtube` TINYINT(1) NOT NULL DEFAULT 0,
    `created_at` DATETIME NOT NULL,
    PRIMARY KEY (`id`, `user_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_created_at` (`created_at`),
    INDEX `idx_is_youtube` (`is_youtube`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create extensions table (metadata only - actual extension files stored locally)
CREATE TABLE IF NOT EXISTS `extensions` (
    `id` VARCHAR(50) NOT NULL,
    `user_id` VARCHAR(128) NOT NULL,
    `name` VARCHAR(255) NOT NULL,
    `version` VARCHAR(50) NOT NULL,
    `is_enabled` TINYINT(1) NOT NULL DEFAULT 1,
    `installed_at` DATETIME NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`id`, `user_id`),
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_is_enabled` (`is_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create settings table
CREATE TABLE IF NOT EXISTS `settings` (
    `user_id` VARCHAR(128) NOT NULL PRIMARY KEY,
    `settings_json` LONGTEXT NOT NULL,
    `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX `idx_updated_at` (`updated_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Create sync log table (for tracking sync operations)
CREATE TABLE IF NOT EXISTS `sync_log` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `user_id` VARCHAR(128) NOT NULL,
    `sync_type` ENUM('bookmarks', 'history', 'settings', 'summaries', 'extensions', 'full') NOT NULL,
    `items_synced` INT NOT NULL DEFAULT 0,
    `direction` ENUM('upload', 'download', 'both') NOT NULL,
    `status` ENUM('success', 'partial', 'failed') NOT NULL,
    `error_message` TEXT DEFAULT NULL,
    `synced_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
    INDEX `idx_user_id` (`user_id`),
    INDEX `idx_synced_at` (`synced_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
