-- Attendance Periods Tracking Table
-- This table tracks which attendance periods have been created
-- Only created periods should be viewable/editable

CREATE TABLE IF NOT EXISTS attendance_periods (
    period_id INT AUTO_INCREMENT PRIMARY KEY,
    site_id INT NOT NULL,
    attendance_month VARCHAR(10) NOT NULL COMMENT 'Format: Jan-25, Feb-25, etc.',
    period_display VARCHAR(50) NOT NULL COMMENT 'Format: January 2025, February 2025',
    employee_count INT DEFAULT 0 COMMENT 'Number of employees in this period',
    is_finalized TINYINT(1) DEFAULT 0 COMMENT '1 = Period is finalized/locked',
    created_by INT DEFAULT NULL COMMENT 'User ID who created this period',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    finalized_at TIMESTAMP NULL DEFAULT NULL,
    
    -- Indexes
    UNIQUE KEY unique_site_period (site_id, attendance_month),
    KEY idx_site_month (site_id, attendance_month),
    KEY idx_created_at (created_at),
    
    -- Foreign Keys
    FOREIGN KEY (site_id) REFERENCES sites(site_id) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- Add some sample comments for clarity
ALTER TABLE attendance_periods 
COMMENT = 'Tracks all created attendance periods. Only periods in this table can be viewed/edited.';

