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

USE ovee_qr;

-- Main QR Table
CREATE TABLE qr_data (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    type ENUM('text','url','file') NOT NULL,
    
    content TEXT NOT NULL, 
    -- text ya URL ya file path
    
    unique_code VARCHAR(100) NOT NULL UNIQUE,
    
    qr_image VARCHAR(255) DEFAULT NULL,
    
    scan_count INT DEFAULT 0,
    
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Optional: Analytics Table (Future Use)
CREATE TABLE qr_scans (
    id INT AUTO_INCREMENT PRIMARY KEY,
    
    qr_id INT,
    
    ip_address VARCHAR(50),
    
    user_agent TEXT,
    
    scan_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    FOREIGN KEY (qr_id) REFERENCES qr_data(id) ON DELETE CASCADE
);