-- ============================================
-- Valentine's Day Web App - Database Schema
-- ============================================
-- Run this SQL to set up your database

-- Create the database
CREATE DATABASE IF NOT EXISTS valentine_app;
USE valentine_app;

-- ============================================
-- RESPONSES TABLE
-- ============================================
-- Stores all Valentine responses

CREATE TABLE IF NOT EXISTS responses (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    response VARCHAR(10) NOT NULL,
    timestamp DATETIME DEFAULT CURRENT_TIMESTAMP,
    ip_address VARCHAR(50),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_timestamp (timestamp),
    INDEX idx_response (response)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- ALTER STATEMENTS (if table exists)
-- ============================================
-- Uncomment and run if you need to add columns to existing table

-- ALTER TABLE responses ADD COLUMN user_agent TEXT AFTER ip_address;

-- ============================================
-- INSERT SAMPLE DATA (Optional)
-- ============================================
-- DELETE the below if you don't want sample data

INSERT INTO responses (name, response, ip_address) VALUES
('Sarah', 'YES', '192.168.1.1');

-- ============================================
-- USEFUL QUERIES
-- ============================================

-- Get all YES responses
-- SELECT * FROM responses WHERE response = 'YES' ORDER BY timestamp DESC;

-- Get total count of responses
-- SELECT COUNT(*) as total FROM responses;

-- Get responses from today
-- SELECT * FROM responses WHERE DATE(timestamp) = CURDATE();

-- Delete all responses
-- TRUNCATE TABLE responses;

-- ============================================
-- ADMIN USER TABLE (Optional - for future use)
-- ============================================

CREATE TABLE IF NOT EXISTS admin_users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================
-- END OF SCHEMA
-- ============================================
