-- ============================================================================
-- Shortlinks Table - Short URL Management with OG Meta Tags
-- ============================================================================
-- Migration: 002_create_shortlinks_table.sql
-- Purpose: Store short URLs with custom Open Graph meta tags for social preview
-- ============================================================================

CREATE TABLE IF NOT EXISTS `shortlinks` (
    `id` BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,

    -- Short URL identifier (slug)
    `slug` VARCHAR(100) NOT NULL UNIQUE,

    -- Destination URL
    `destination_url` VARCHAR(2048) NOT NULL,

    -- Open Graph Meta Tags (for Facebook/social media preview)
    `og_title` VARCHAR(255) DEFAULT NULL COMMENT 'Open Graph title',
    `og_description` TEXT DEFAULT NULL COMMENT 'Open Graph description',
    `og_image` VARCHAR(2048) DEFAULT NULL COMMENT 'Open Graph image URL',
    `og_type` VARCHAR(50) DEFAULT 'website' COMMENT 'Open Graph type (website, article, etc)',

    -- Additional meta tags
    `meta_keywords` VARCHAR(500) DEFAULT NULL COMMENT 'SEO keywords',
    `meta_author` VARCHAR(255) DEFAULT NULL COMMENT 'Content author',

    -- Analytics
    `click_count` BIGINT UNSIGNED DEFAULT 0 COMMENT 'Total clicks tracked',
    `last_clicked_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'Last click timestamp',

    -- Status and lifecycle
    `status` ENUM('active', 'inactive', 'expired') NOT NULL DEFAULT 'active',
    `expires_at` TIMESTAMP NULL DEFAULT NULL COMMENT 'Expiration date (NULL = never expires)',

    -- Timestamps
    `created_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

    -- Indexes for performance
    UNIQUE KEY uk_slug (slug),
    INDEX idx_status_created (status, created_at DESC),
    INDEX idx_destination_url (destination_url(255)),
    INDEX idx_expires_at (expires_at),
    INDEX idx_click_count (click_count DESC)

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
ROW_FORMAT=DYNAMIC
COMMENT='Short URLs with Open Graph meta tags for social media preview optimization';

-- ============================================================================
-- Sample Data (Optional - uncomment to insert demo shortlinks)
-- ============================================================================

-- INSERT INTO `shortlinks` (`slug`, `destination_url`, `og_title`, `og_description`, `og_image`, `status`) VALUES
-- ('demo', 'https://example.com', 'Demo Shortlink', 'This is a demo shortlink with OG tags', 'https://example.com/image.jpg', 'active'),
-- ('promo', 'https://example.com/promo', 'Special Promo', 'Limited time offer - check it out!', 'https://example.com/promo.jpg', 'active');