-- Employee Workstation Activity Tracking System
-- Database Schema Creation Script

-- Instructions:
-- 1. For SQLite: sqlite3 work_events.db < create_db.sql
-- 2. For PostgreSQL: psql -U username -d database_name -f create_db.sql

-- Drop existing table if it exists
DROP TABLE IF EXISTS work_events;

-- Create the work_events table
CREATE TABLE work_events (
    id SERIAL PRIMARY KEY,
    "user" VARCHAR(100) NOT NULL,  -- Note the double quotes
    state VARCHAR(10) NOT NULL CHECK (state IN ('working', 'stopped')),
    ts TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- Create indexes for performance optimization
CREATE INDEX idx_work_events_user ON work_events("user");
CREATE INDEX idx_work_events_ts ON work_events(ts);
CREATE INDEX idx_work_events_user_state ON work_events("user", state);

-- Optional: Sample data for testing (comment out in production)

-- Clear previous sample data if necessary before inserting new data
-- DELETE FROM work_events WHERE "user" LIKE 'User %' OR "user" LIKE 'user%';

-- Remove older generic sample data
/*
INSERT INTO work_events ("user", state, ts) VALUES
    ('user1', 'working', CURRENT_TIMESTAMP - INTERVAL '2 hours'),
    ('user1', 'stopped', CURRENT_TIMESTAMP - INTERVAL '1 hour'),
    ('user1', 'working', CURRENT_TIMESTAMP - INTERVAL '30 minutes'),
    ('user2', 'working', CURRENT_TIMESTAMP - INTERVAL '3 hours'),
    ('user2', 'stopped', CURRENT_TIMESTAMP - INTERVAL '2 hours 30 minutes'),
    ('user2', 'working', CURRENT_TIMESTAMP - INTERVAL '2 hours'),
    ('user2', 'stopped', CURRENT_TIMESTAMP - INTERVAL '1 hour');
*/

-- Example queries for reporting

-- 1. Daily activity summary per user

SELECT 
    "user",
    DATE(ts) AS day,
    COUNT(*) AS event_count,
    SUM(CASE WHEN state = 'working' THEN 1 ELSE 0 END) AS working_events
FROM work_events
GROUP BY "user", day
ORDER BY day DESC, "user";


-- 2. Weekly activity summary

SELECT 
    "user",
    date_trunc('week', ts) AS week_start,
    COUNT(*) AS event_count
FROM work_events
GROUP BY "user", week_start
ORDER BY week_start DESC, "user";


-- 3. Monthly activity summary

SELECT 
    "user",
    date_trunc('month', ts) AS month_start,
    COUNT(*) AS event_count
FROM work_events
GROUP BY "user", month_start
ORDER BY month_start DESC, "user";


-- Notes on data retention:
-- According to requirements, raw event logs should be retained for 1 year.
-- Setup a periodic cleanup job to remove old data:

-- PostgreSQL:

DELETE FROM work_events 
WHERE ts < CURRENT_TIMESTAMP - INTERVAL '1 year';


-- SQLite:
/*
DELETE FROM work_events 
WHERE ts < datetime('now', '-1 year');
*/ 

-- Enhanced Sample Data Generation (Relative to assumed date 2025-05-04)
-- Ensures Users A-E have data for Today, This Week, and This Month (April)



-- Sample data for real users: Robert, Ilia, and Nika
-- Robert: Working pattern for today, this week, and this month
INSERT INTO work_events ("user", state, ts) VALUES ('Robert', 'working', '2025-05-04 08:30:00'); -- Today
INSERT INTO work_events ("user", state, ts) VALUES ('Robert', 'stopped', '2025-05-04 12:30:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Robert', 'working', '2025-05-04 13:15:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Robert', 'stopped', '2025-05-04 17:00:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Robert', 'working', '2025-05-02 08:45:00'); -- This Week
INSERT INTO work_events ("user", state, ts) VALUES ('Robert', 'stopped', '2025-05-02 16:15:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Robert', 'working', '2025-04-15 09:00:00'); -- This Month
INSERT INTO work_events ("user", state, ts) VALUES ('Robert', 'stopped', '2025-04-15 17:30:00');

-- Ilia: Working pattern for today, this week, and this month
INSERT INTO work_events ("user", state, ts) VALUES ('Ilia', 'working', '2025-05-04 09:00:00'); -- Today
INSERT INTO work_events ("user", state, ts) VALUES ('Ilia', 'stopped', '2025-05-04 13:00:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Ilia', 'working', '2025-05-04 14:00:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Ilia', 'stopped', '2025-05-04 18:00:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Ilia', 'working', '2025-05-03 09:30:00'); -- This Week
INSERT INTO work_events ("user", state, ts) VALUES ('Ilia', 'stopped', '2025-05-03 17:30:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Ilia', 'working', '2025-04-20 08:30:00'); -- This Month
INSERT INTO work_events ("user", state, ts) VALUES ('Ilia', 'stopped', '2025-04-20 16:30:00');

-- Nika: Working pattern for today, this week, and this month
INSERT INTO work_events ("user", state, ts) VALUES ('Nika', 'working', '2025-05-04 08:15:00'); -- Today
INSERT INTO work_events ("user", state, ts) VALUES ('Nika', 'stopped', '2025-05-04 12:00:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Nika', 'working', '2025-05-04 12:45:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Nika', 'stopped', '2025-05-04 16:30:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Nika', 'working', '2025-05-01 08:30:00'); -- This Week
INSERT INTO work_events ("user", state, ts) VALUES ('Nika', 'stopped', '2025-05-01 16:45:00');
INSERT INTO work_events ("user", state, ts) VALUES ('Nika', 'working', '2025-04-10 09:15:00'); -- This Month
INSERT INTO work_events ("user", state, ts) VALUES ('Nika', 'stopped', '2025-04-10 17:15:00');

-- Enhanced SQL queries for reporting with duration calculations

-- 4. Daily working duration per user (for dashboard)
-- This calculates the total working hours per day by matching working->stopped pairs
SELECT 
    w1."user",
    DATE(w1.ts) AS day,
    SUM(
        EXTRACT(EPOCH FROM (w2.ts - w1.ts))/3600
    ) AS duration_hours,
    MIN(CASE WHEN w1.state = 'working' THEN w1.ts END) AS first_login_time
FROM 
    work_events w1
JOIN 
    work_events w2 ON w1."user" = w2."user" 
    AND w1.state = 'working' 
    AND w2.state = 'stopped'
    AND w2.ts > w1.ts
    AND NOT EXISTS (
        SELECT 1 FROM work_events w3
        WHERE w3."user" = w1."user"
        AND w3.ts > w1.ts AND w3.ts < w2.ts
    )
GROUP BY 
    w1."user", day
ORDER BY 
    day DESC, w1."user";

-- 5. Weekly working duration per user (for dashboard)
SELECT 
    w1."user",
    date_trunc('week', w1.ts) AS week_start,
    SUM(
        EXTRACT(EPOCH FROM (w2.ts - w1.ts))/3600
    ) AS duration_hours,
    MIN(CASE WHEN w1.state = 'working' THEN w1.ts END) AS first_login_time
FROM 
    work_events w1
JOIN 
    work_events w2 ON w1."user" = w2."user" 
    AND w1.state = 'working' 
    AND w2.state = 'stopped'
    AND w2.ts > w1.ts
    AND NOT EXISTS (
        SELECT 1 FROM work_events w3
        WHERE w3."user" = w1."user"
        AND w3.ts > w1.ts AND w3.ts < w2.ts
    )
GROUP BY 
    w1."user", week_start
ORDER BY 
    week_start DESC, w1."user";

-- 6. Monthly working duration per user (for dashboard)
SELECT 
    w1."user",
    date_trunc('month', w1.ts) AS month_start,
    SUM(
        EXTRACT(EPOCH FROM (w2.ts - w1.ts))/3600
    ) AS duration_hours,
    MIN(CASE WHEN w1.state = 'working' THEN w1.ts END) AS first_login_time
FROM 
    work_events w1
JOIN 
    work_events w2 ON w1."user" = w2."user" 
    AND w1.state = 'working' 
    AND w2.state = 'stopped'
    AND w2.ts > w1.ts
    AND NOT EXISTS (
        SELECT 1 FROM work_events w3
        WHERE w3."user" = w1."user"
        AND w3.ts > w1.ts AND w3.ts < w2.ts
    )
GROUP BY 
    w1."user", month_start
ORDER BY 
    month_start DESC, w1."user";

-- 7. Detailed user activity log for specific user and date range
-- This query shows all work sessions with start, end times and duration
-- Use :username and :start_date, :end_date as parameters
SELECT 
    w1."user",
    DATE(w1.ts) AS work_date,
    w1.ts AS start_time,
    w2.ts AS end_time,
    EXTRACT(EPOCH FROM (w2.ts - w1.ts))/3600 AS session_duration_hours
FROM 
    work_events w1
JOIN 
    work_events w2 ON w1."user" = w2."user" 
    AND w1.state = 'working' 
    AND w2.state = 'stopped'
    AND w2.ts > w1.ts
    AND NOT EXISTS (
        SELECT 1 FROM work_events w3
        WHERE w3."user" = w1."user"
        AND w3.ts > w1.ts AND w3.ts < w2.ts
    )
WHERE 
    w1."user" = :username
    AND DATE(w1.ts) BETWEEN :start_date AND :end_date
ORDER BY 
    w1.ts;

-- 8. Filter for specific time periods (Today, This Week, This Month)
-- Today's work duration by user
SELECT 
    w1."user",
    CURRENT_DATE AS day,
    SUM(
        EXTRACT(EPOCH FROM (w2.ts - w1.ts))/3600
    ) AS duration_hours,
    MIN(CASE WHEN w1.state = 'working' THEN w1.ts END) AS first_login_time
FROM 
    work_events w1
JOIN 
    work_events w2 ON w1."user" = w2."user" 
    AND w1.state = 'working' 
    AND w2.state = 'stopped'
    AND w2.ts > w1.ts
    AND NOT EXISTS (
        SELECT 1 FROM work_events w3
        WHERE w3."user" = w1."user"
        AND w3.ts > w1.ts AND w3.ts < w2.ts
    )
WHERE 
    DATE(w1.ts) = CURRENT_DATE
GROUP BY 
    w1."user"
ORDER BY 
    w1."user";

-- This Week's work duration by user
SELECT 
    w1."user",
    date_trunc('week', CURRENT_DATE) AS week_start,
    SUM(
        EXTRACT(EPOCH FROM (w2.ts - w1.ts))/3600
    ) AS duration_hours,
    MIN(CASE WHEN w1.state = 'working' THEN w1.ts END) AS first_login_time
FROM 
    work_events w1
JOIN 
    work_events w2 ON w1."user" = w2."user" 
    AND w1.state = 'working' 
    AND w2.state = 'stopped'
    AND w2.ts > w1.ts
    AND NOT EXISTS (
        SELECT 1 FROM work_events w3
        WHERE w3."user" = w1."user"
        AND w3.ts > w1.ts AND w3.ts < w2.ts
    )
WHERE 
    date_trunc('week', w1.ts) = date_trunc('week', CURRENT_DATE)
GROUP BY 
    w1."user"
ORDER BY 
    w1."user";

-- This Month's work duration by user
SELECT 
    w1."user",
    date_trunc('month', CURRENT_DATE) AS month_start,
    SUM(
        EXTRACT(EPOCH FROM (w2.ts - w1.ts))/3600
    ) AS duration_hours,
    MIN(CASE WHEN w1.state = 'working' THEN w1.ts END) AS first_login_time
FROM 
    work_events w1
JOIN 
    work_events w2 ON w1."user" = w2."user" 
    AND w1.state = 'working' 
    AND w2.state = 'stopped'
    AND w2.ts > w1.ts
    AND NOT EXISTS (
        SELECT 1 FROM work_events w3
        WHERE w3."user" = w1."user"
        AND w3.ts > w1.ts AND w3.ts < w2.ts
    )
WHERE 
    date_trunc('month', w1.ts) = date_trunc('month', CURRENT_DATE)
GROUP BY 
    w1."user"
ORDER BY 
    w1."user";