

-- Table for user roles (students, teachers, staff)
CREATE TABLE user_types (
    id INT PRIMARY KEY AUTO_INCREMENT,
    type_name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT
);

-- Insert default user types
INSERT INTO user_types (type_name, description) VALUES 
('student', 'Student user type'),
('teacher', 'Teacher user type'),
('staff', 'Staff member user type');

-- Table for classes
CREATE TABLE classes (
    id INT PRIMARY KEY AUTO_INCREMENT,
    class_name VARCHAR(100) NOT NULL,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table for sections (belonging to classes)
CREATE TABLE sections (
    id INT PRIMARY KEY AUTO_INCREMENT,
    section_name VARCHAR(50) NOT NULL,
    class_id INT,
    capacity INT DEFAULT 0,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL
);

-- Table for users (students, teachers, staff)
CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id VARCHAR(50) UNIQUE NOT NULL, -- Student ID / Staff ID
    name VARCHAR(100) NOT NULL,
    user_type_id INT NOT NULL,
    class_id INT NULL, -- Only for students
    section_id INT NULL, -- Only for students
    roll_number VARCHAR(20) NULL, -- Only for students
    email VARCHAR(100) NULL,
    phone VARCHAR(15) NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_type_id) REFERENCES user_types(id),
    FOREIGN KEY (class_id) REFERENCES classes(id) ON DELETE SET NULL,
    FOREIGN KEY (section_id) REFERENCES sections(id) ON DELETE SET NULL
);

-- Table for storing fingerprint templates/data
CREATE TABLE fingerprints (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id VARCHAR(50) NOT NULL,
    fingerprint_template TEXT NOT NULL, -- Encrypted fingerprint template
    finger_position ENUM('thumb_left', 'index_left', 'middle_left', 'ring_left', 'pinky_left', 
                        'thumb_right', 'index_right', 'middle_right', 'ring_right', 'pinky_right') NOT NULL,
    enrollment_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_used TIMESTAMP NULL,
    is_active BOOLEAN DEFAULT TRUE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- Table for attendance records
CREATE TABLE attendance (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id VARCHAR(50) NOT NULL,
    scan_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    device_id VARCHAR(100) DEFAULT 'MAIN_SCANNER', -- In case of multiple scanners
    verification_method ENUM('fingerprint', 'manual') DEFAULT 'fingerprint',
    is_late BOOLEAN DEFAULT FALSE,
    note TEXT NULL,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

-- Indexes for better performance
CREATE INDEX idx_attendance_user_date ON attendance(user_id, DATE(scan_time));
CREATE INDEX idx_attendance_scan_time ON attendance(scan_time DESC);
CREATE INDEX idx_users_type ON users(user_type_id);
CREATE INDEX idx_users_class ON users(class_id);
CREATE INDEX idx_users_section ON users(section_id);

-- Table for system settings
CREATE TABLE system_settings (
    id INT PRIMARY KEY AUTO_INCREMENT,
    setting_key VARCHAR(100) UNIQUE NOT NULL,
    setting_value TEXT,
    description TEXT,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Insert default system settings
INSERT INTO system_settings (setting_key, setting_value, description) VALUES 
('late_threshold_minutes', '15', 'Minutes after scheduled time to mark as late'),
('work_start_time', '09:00:00', 'Official work/school start time'),
('work_end_time', '17:00:00', 'Official work/school end time'),
('auto_logout_minutes', '30', 'Auto logout time for admin panel'),
('backup_enabled', 'true', 'Enable automatic database backups');

-- Table for audit logs
CREATE TABLE audit_logs (
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_id VARCHAR(50) NULL, -- User who performed the action (NULL for system actions)
    action VARCHAR(100) NOT NULL, -- Action performed (login, logout, register, etc.)
    table_affected VARCHAR(50) NULL, -- Which table was affected
    record_id INT NULL, -- ID of the affected record
    ip_address VARCHAR(45) NULL, -- IP address of the user
    user_agent TEXT NULL, -- Browser/user agent info
    timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    details TEXT -- Additional details about the action
);

-- View to get daily attendance summary
CREATE VIEW daily_attendance_summary AS
SELECT 
    u.name,
    u.user_id,
    ut.type_name,
    c.class_name,
    s.section_name,
    DATE(a.scan_time) as attendance_date,
    MIN(a.scan_time) as first_scan,
    MAX(a.scan_time) as last_scan,
    COUNT(*) as total_scans
FROM attendance a
JOIN users u ON a.user_id = u.user_id
JOIN user_types ut ON u.user_type_id = ut.id
LEFT JOIN classes c ON u.class_id = c.id
LEFT JOIN sections s ON u.section_id = s.id
GROUP BY u.user_id, DATE(a.scan_time);

-- View to get monthly attendance report
CREATE VIEW monthly_attendance_report AS
SELECT 
    u.name,
    u.user_id,
    ut.type_name,
    c.class_name,
    s.section_name,
    u.roll_number,
    YEAR(a.scan_time) as year,
    MONTH(a.scan_time) as month,
    COUNT(*) as days_present,
    ROUND(
        (COUNT(*) * 100.0 / 
         DAY(LAST_DAY(CONCAT(YEAR(a.scan_time), '-', MONTH(a.scan_time), '-01')))), 2
    ) as attendance_percentage
FROM attendance a
JOIN users u ON a.user_id = u.user_id
JOIN user_types ut ON u.user_type_id = ut.id
LEFT JOIN classes c ON u.class_id = c.id
LEFT JOIN sections s ON u.section_id = s.id
WHERE a.scan_time >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
GROUP BY u.user_id, YEAR(a.scan_time), MONTH(a.scan_time);

-- Sample data for demonstration
-- Adding sample classes
INSERT INTO classes (class_name, description) VALUES 
('Grade 1', 'First grade students'),
('Grade 2', 'Second grade students'),
('Grade 3', 'Third grade students'),
('Grade 4', 'Fourth grade students'),
('Grade 5', 'Fifth grade students'),
('Grade 6', 'Sixth grade students'),
('Grade 7', 'Seventh grade students'),
('Grade 8', 'Eighth grade students'),
('Grade 9', 'Ninth grade students'),
('Grade 10', 'Tenth grade students'),
('Grade 11', 'Eleventh grade students'),
('Grade 12', 'Twelfth grade students');

-- Adding sample sections
INSERT INTO sections (section_name, class_id) VALUES 
('A', 1), ('B', 1), ('C', 1),
('A', 2), ('B', 2),
('A', 3), ('B', 3), ('C', 3),
('A', 4), ('B', 4),
('A', 5), ('B', 5), ('C', 5),
('A', 6), ('B', 6),
('A', 7), ('B', 7), ('C', 7),
('A', 8), ('B', 8),
('A', 9), ('B', 9), ('C', 9),
('A', 10), ('B', 10),
('Science', 11), ('Commerce', 11), ('Arts', 11),
('Science', 12), ('Commerce', 12), ('Arts', 12);