-- ============================================================
-- MindPad - Production database schema (single file)
-- MySQL 8.0+ / MariaDB 10.5+
-- Run once: creates database, tables, indexes, and recycle-bin views
-- ============================================================

CREATE DATABASE IF NOT EXISTS mindpad_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

USE mindpad_db;

-- ---------------------------------------------------------------------------
-- Users
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
    id VARCHAR(36) PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    name VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Folders (with soft delete + secure from start)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS folders (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    name VARCHAR(255) NOT NULL,
    parent_id VARCHAR(36) NULL,
    color ENUM('default', 'red', 'orange', 'yellow', 'green', 'blue', 'purple', 'pink') DEFAULT 'default',
    expanded BOOLEAN DEFAULT TRUE,
    secure BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    deleted_at TIMESTAMP NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (parent_id) REFERENCES folders(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_parent_id (parent_id),
    INDEX idx_deleted_at (deleted_at),
    INDEX idx_secure (secure)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Notes (files)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS notes (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    title VARCHAR(500) NOT NULL DEFAULT 'Untitled Note',
    content LONGTEXT,
    language ENUM('plaintext', 'javascript', 'typescript', 'html', 'css', 'json', 'markdown', 'sql', 'php', 'xml', 'yaml', 'tamil', 'arabic') DEFAULT 'plaintext',
    folder_id VARCHAR(36) NULL,
    favorite BOOLEAN DEFAULT FALSE,
    secure BOOLEAN DEFAULT FALSE,
    deleted_at TIMESTAMP NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (folder_id) REFERENCES folders(id) ON DELETE SET NULL,
    INDEX idx_user_id (user_id),
    INDEX idx_folder_id (folder_id),
    INDEX idx_deleted_at (deleted_at),
    INDEX idx_favorite (favorite),
    INDEX idx_secure (secure),
    INDEX idx_updated_at (updated_at),
    FULLTEXT idx_content_search (title, content)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Bookmarks
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS bookmarks (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    note_id VARCHAR(36) NOT NULL,
    line INT NOT NULL,
    name VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    FOREIGN KEY (note_id) REFERENCES notes(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_note_id (note_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Settings
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS settings (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) UNIQUE NOT NULL,
    theme ENUM('light', 'dark', 'system') DEFAULT 'dark',
    accent_color VARCHAR(50) DEFAULT 'cyan',
    font_size INT DEFAULT 14,
    font_family VARCHAR(100) DEFAULT 'JetBrains Mono',
    line_height DECIMAL(3,1) DEFAULT 1.6,
    default_language ENUM('plaintext', 'javascript', 'typescript', 'html', 'css', 'json', 'markdown', 'sql', 'php', 'xml', 'yaml', 'tamil', 'arabic') DEFAULT 'plaintext',
    word_wrap BOOLEAN DEFAULT TRUE,
    tab_size INT DEFAULT 2,
    auto_save BOOLEAN DEFAULT TRUE,
    format_on_save BOOLEAN DEFAULT FALSE,
    show_line_numbers BOOLEAN DEFAULT TRUE,
    show_minimap BOOLEAN DEFAULT FALSE,
    sidebar_width INT DEFAULT 280,
    sidebar_collapsed BOOLEAN DEFAULT FALSE,
    zen_mode BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Sessions (token blacklisting / expiry)
-- ---------------------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sessions (
    id VARCHAR(36) PRIMARY KEY,
    user_id VARCHAR(36) NOT NULL,
    token_hash VARCHAR(255) NOT NULL,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ---------------------------------------------------------------------------
-- Recycle bin views (trash) – for reporting / admin
-- ---------------------------------------------------------------------------
CREATE OR REPLACE VIEW v_trash_notes AS
SELECT id, user_id, title, folder_id, deleted_at, created_at, updated_at
FROM notes
WHERE deleted_at IS NOT NULL;

CREATE OR REPLACE VIEW v_trash_folders AS
SELECT id, user_id, name, parent_id, deleted_at, created_at, updated_at
FROM folders
WHERE deleted_at IS NOT NULL;

-- Optional: view trash counts per user (uncomment if needed)
-- CREATE OR REPLACE VIEW v_trash_counts AS
-- SELECT user_id,
--        (SELECT COUNT(*) FROM notes n WHERE n.user_id = u.id AND n.deleted_at IS NOT NULL) AS deleted_notes,
--        (SELECT COUNT(*) FROM folders f WHERE f.user_id = u.id AND f.deleted_at IS NOT NULL) AS deleted_folders
-- FROM users u;