-- ============================================================================
-- SepuraHome Server-Side Calculation Database Schema
-- ============================================================================
-- This is the SIMPLIFIED schema where server does all calculations
-- Device only sends total_cycles, server calculates monthly/yearly metrics
-- ============================================================================

-- Drop existing tables if recreating
-- DROP TABLE IF EXISTS daily_stats;
-- DROP TABLE IF EXISTS metrics;

-- Main metrics table - stores only raw cycle counts
CREATE TABLE IF NOT EXISTS metrics (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(50) NOT NULL UNIQUE COMMENT 'Device identifier (SEPURA_xxxxxxxx)',
    
    -- Raw data from device (only field sent by ESP32)
    total_cycles INT UNSIGNED DEFAULT 0 COMMENT 'Cumulative cycle count',
    last_cycle_time BIGINT UNSIGNED DEFAULT 0 COMMENT 'Unix timestamp of last cycle',
    
    -- Metadata
    first_seen TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT 'When device first connected',
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last sync time',
    
    -- Indexes for performance
    INDEX idx_device_id (device_id),
    INDEX idx_last_updated (last_updated)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Device metrics - server calculates monthly/yearly stats on-demand';

-- Daily stats table - for historical tracking and trend analysis
CREATE TABLE IF NOT EXISTS daily_stats (
    id INT AUTO_INCREMENT PRIMARY KEY,
    device_id VARCHAR(50) NOT NULL COMMENT 'Device identifier',
    date DATE NOT NULL COMMENT 'Date for this snapshot',
    
    -- Daily metrics
    cycles_on_date INT UNSIGNED DEFAULT 0 COMMENT 'Cycles completed on this specific date',
    total_cycles_snapshot INT UNSIGNED DEFAULT 0 COMMENT 'Cumulative total at end of this date',
    
    -- Metadata
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    
    -- Ensure one record per device per date
    UNIQUE KEY unique_device_date (device_id, date),
    
    -- Indexes for performance
    INDEX idx_device_id (device_id),
    INDEX idx_date (date),
    INDEX idx_device_date (device_id, date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
COMMENT='Daily cycle snapshots for historical tracking';

-- ============================================================================
-- Sample Data for Testing
-- ============================================================================

-- Insert test device
INSERT INTO metrics (device_id, total_cycles, last_cycle_time) 
VALUES ('SEPURA_TEST001', 100, UNIX_TIMESTAMP())
ON DUPLICATE KEY UPDATE 
    total_cycles = VALUES(total_cycles),
    last_cycle_time = VALUES(last_cycle_time);

-- Insert historical daily stats (last 30 days)
INSERT INTO daily_stats (device_id, date, total_cycles_snapshot, cycles_on_date)
VALUES 
    ('SEPURA_TEST001', DATE_SUB(CURDATE(), INTERVAL 30 DAY), 40, 2),
    ('SEPURA_TEST001', DATE_SUB(CURDATE(), INTERVAL 29 DAY), 42, 2),
    ('SEPURA_TEST001', DATE_SUB(CURDATE(), INTERVAL 28 DAY), 45, 3),
    ('SEPURA_TEST001', DATE_SUB(CURDATE(), INTERVAL 7 DAY), 80, 5),
    ('SEPURA_TEST001', DATE_SUB(CURDATE(), INTERVAL 6 DAY), 85, 5),
    ('SEPURA_TEST001', DATE_SUB(CURDATE(), INTERVAL 5 DAY), 88, 3),
    ('SEPURA_TEST001', DATE_SUB(CURDATE(), INTERVAL 1 DAY), 95, 5),
    ('SEPURA_TEST001', CURDATE(), 100, 5)
ON DUPLICATE KEY UPDATE 
    total_cycles_snapshot = VALUES(total_cycles_snapshot),
    cycles_on_date = VALUES(cycles_on_date);

-- ============================================================================
-- Useful Queries for Monitoring
-- ============================================================================

-- Get all devices with their latest stats
SELECT 
    device_id,
    total_cycles,
    FROM_UNIXTIME(last_cycle_time) as last_cycle,
    first_seen,
    last_updated
FROM metrics
ORDER BY last_updated DESC;

-- Calculate THIS MONTH for a device
SELECT 
    device_id,
    SUM(cycles_on_date) as cycles_this_month,
    ROUND(SUM(cycles_on_date) * 3.5) as food_waste_kg,
    ROUND(SUM(cycles_on_date) * 3.5 * 0.661) as co2_prevented_kg
FROM daily_stats
WHERE device_id = 'SEPURA_TEST001'
AND date >= DATE_FORMAT(NOW(), '%Y-%m-01')
GROUP BY device_id;

-- Calculate THIS YEAR for a device
SELECT 
    device_id,
    SUM(cycles_on_date) as cycles_this_year,
    ROUND(SUM(cycles_on_date) * 3.5) as food_waste_kg,
    ROUND(SUM(cycles_on_date) * 3.5 * 0.661) as co2_prevented_kg
FROM daily_stats
WHERE device_id = 'SEPURA_TEST001'
AND date >= DATE_FORMAT(NOW(), '%Y-01-01')
GROUP BY device_id;

-- Get daily trend for last 30 days
SELECT 
    date,
    cycles_on_date,
    total_cycles_snapshot
FROM daily_stats
WHERE device_id = 'SEPURA_TEST001'
AND date >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
ORDER BY date DESC;

-- Aggregate stats across all devices
SELECT 
    COUNT(DISTINCT device_id) as total_devices,
    SUM(total_cycles) as total_cycles_all_devices,
    ROUND(SUM(total_cycles) * 3.5) as total_waste_diverted_kg,
    ROUND(SUM(total_cycles) * 3.5 * 0.661) as total_co2_prevented_kg
FROM metrics;

-- Find most active devices this month
SELECT 
    d.device_id,
    SUM(d.cycles_on_date) as cycles_this_month,
    m.total_cycles,
    m.last_updated
FROM daily_stats d
JOIN metrics m ON d.device_id = m.device_id
WHERE d.date >= DATE_FORMAT(NOW(), '%Y-%m-01')
GROUP BY d.device_id, m.total_cycles, m.last_updated
ORDER BY cycles_this_month DESC
LIMIT 10;