-- Database setup for AntURL - URL Shortener
-- Run this SQL script to create the database and table

-- Create database (uncomment if needed)
-- CREATE DATABASE url_shortener CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- USE url_shortener;

-- Create the urls table
CREATE TABLE IF NOT EXISTS urls (
    id INT AUTO_INCREMENT PRIMARY KEY,
    long_url TEXT NOT NULL,
    short_code VARCHAR(10) NOT NULL UNIQUE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_short_code (short_code),
    INDEX idx_created_at (created_at)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Optional: Insert some sample data for testing
-- INSERT INTO urls (long_url, short_code) VALUES 
-- ('https://www.google.com', 'abc123'),
-- ('https://www.github.com', 'def456'),
-- ('https://www.stackoverflow.com', 'ghi789');

-- Show table structure
DESCRIBE urls;

-- Display current data (if any)
SELECT * FROM urls;

-- Create the blog table
CREATE TABLE IF NOT EXISTS blog (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    image VARCHAR(255) DEFAULT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- If the blog table already exists, run this command to add the image column:
-- ALTER TABLE blog ADD COLUMN image VARCHAR(255) DEFAULT NULL AFTER content;