USE foams_db;

-- FOAMS Database Schema
-- Field Operations & Attendance Monitoring System

-- Users table for authentication and roles
CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password VARCHAR(255) NOT NULL,
    role ENUM('admin','supervisor','collector') NOT NULL,
    name VARCHAR(100),
    email VARCHAR(100),
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Workers table (Attendance enabled)
CREATE TABLE workers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name_np VARCHAR(100),
    name_en VARCHAR(100),
    gender ENUM('male','female','other'),
    phone VARCHAR(20),
    status ENUM('active','inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Attendance table (Map-based Attendance)
CREATE TABLE attendance (
    id INT AUTO_INCREMENT PRIMARY KEY,
    worker_id INT,
    date DATE,
    check_in TIME,
    check_out TIME,
    lat DOUBLE,
    lng DOUBLE,
    method ENUM('manual','gps') DEFAULT 'gps',
    entry_by INT,
    FOREIGN KEY (worker_id) REFERENCES workers(id),
    FOREIGN KEY (entry_by) REFERENCES users(id)
);

-- Activity entries table (Nadi Sarsafi)
CREATE TABLE activity_entries (
    id INT AUTO_INCREMENT PRIMARY KEY,
    entry_code VARCHAR(20) UNIQUE,
    work_location TEXT,
    start_time TIME,
    end_time TIME,
    work_length_km FLOAT,
    river_type ENUM('inside','bank'),
    plastic_wt FLOAT DEFAULT 0,
    ghaspat_qty FLOAT DEFAULT 0,
    dhulo_qty FLOAT DEFAULT 0,
    medical_wt FLOAT DEFAULT 0,
    mix_wt FLOAT DEFAULT 0,
    remarks TEXT,
    lat DOUBLE,
    lng DOUBLE,
    before_image VARCHAR(255),
    after_image VARCHAR(255),
    supervisor_status ENUM('pending','approved','rejected') DEFAULT 'pending',
    entered_by INT,
    entry_date DATE,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (entered_by) REFERENCES users(id)
);

-- Activity workers (Link attendance to activity)
CREATE TABLE activity_workers (
    activity_id INT,
    worker_id INT,
    PRIMARY KEY (activity_id, worker_id),
    FOREIGN KEY (activity_id) REFERENCES activity_entries(id),
    FOREIGN KEY (worker_id) REFERENCES workers(id)
);

-- Indexes for better performance
CREATE INDEX idx_attendance_worker_date ON attendance(worker_id, date);
CREATE INDEX idx_activity_entries_status ON activity_entries(supervisor_status);
CREATE INDEX idx_activity_entries_date ON activity_entries(entry_date);
