-- Time Tracking Tables for OctaDigital
USE octadigital_db;

-- Time Tracking Table
CREATE TABLE IF NOT EXISTS time_tracking (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    punch_in DATETIME NOT NULL,
    punch_out DATETIME NULL,
    total_hours DECIMAL(5,2) NULL,
    date DATE NOT NULL,
    status ENUM('clocked_in', 'clocked_out', 'break') DEFAULT 'clocked_in',
    notes TEXT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff_members(id) ON DELETE CASCADE,
    INDEX idx_staff_date (staff_id, date),
    INDEX idx_date (date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Break Records Table
CREATE TABLE IF NOT EXISTS break_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    time_tracking_id INT NOT NULL,
    staff_id INT NOT NULL,
    break_start DATETIME NOT NULL,
    break_end DATETIME NULL,
    break_duration DECIMAL(4,2) NULL,
    break_type ENUM('lunch', 'short', 'other') DEFAULT 'short',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (time_tracking_id) REFERENCES time_tracking(id) ON DELETE CASCADE,
    FOREIGN KEY (staff_id) REFERENCES staff_members(id) ON DELETE CASCADE,
    INDEX idx_tracking (time_tracking_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Monthly Time Summary Table
CREATE TABLE IF NOT EXISTS monthly_time_summary (
    id INT AUTO_INCREMENT PRIMARY KEY,
    staff_id INT NOT NULL,
    year INT NOT NULL,
    month INT NOT NULL,
    total_hours DECIMAL(7,2) DEFAULT 0,
    working_days INT DEFAULT 0,
    overtime_hours DECIMAL(5,2) DEFAULT 0,
    average_hours DECIMAL(5,2) DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (staff_id) REFERENCES staff_members(id) ON DELETE CASCADE,
    UNIQUE KEY unique_staff_month (staff_id, year, month),
    INDEX idx_staff (staff_id),
    INDEX idx_year_month (year, month)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Insert sample data for testing
INSERT INTO time_tracking (staff_id, punch_in, punch_out, total_hours, date, status) VALUES
(1, '2025-12-26 09:00:00', '2025-12-26 17:30:00', 8.5, '2025-12-26', 'clocked_out'),
(1, '2025-12-25 09:15:00', '2025-12-25 18:00:00', 8.0, '2025-12-25', 'clocked_out'),
(1, '2025-12-24 09:00:00', '2025-12-24 18:00:00', 9.0, '2025-12-24', 'clocked_out'),
(1, '2025-12-23 09:30:00', '2025-12-23 17:00:00', 7.5, '2025-12-23', 'clocked_out'),
(1, '2025-12-20 09:00:00', '2025-12-20 17:00:00', 8.0, '2025-12-20', 'clocked_out');

INSERT INTO break_records (time_tracking_id, staff_id, break_start, break_end, break_duration, break_type) VALUES
(1, 1, '2025-12-26 13:00:00', '2025-12-26 14:00:00', 1.0, 'lunch'),
(2, 1, '2025-12-25 13:00:00', '2025-12-25 14:00:00', 1.0, 'lunch'),
(3, 1, '2025-12-24 13:00:00', '2025-12-24 14:00:00', 1.0, 'lunch');

INSERT INTO monthly_time_summary (staff_id, year, month, total_hours, working_days, overtime_hours, average_hours) VALUES
(1, 2025, 12, 168.5, 21, 2.5, 8.0),
(1, 2025, 11, 160.0, 20, 5.0, 8.0);
