-- Create the database
CREATE DATABASE IF NOT EXISTS goro_platform;
USE goro_platform;

-- Activities/Gotong Royong table
CREATE TABLE activities (
    activity_id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(100) NOT NULL,
    description TEXT,
    location VARCHAR(255) NOT NULL,
    date DATE NOT NULL,
    morning_shift_start TIME,
    morning_shift_end TIME,
    evening_shift_start TIME,
    evening_shift_end TIME,
    qr_code VARCHAR(255),
    status ENUM('upcoming', 'ongoing', 'completed', 'cancelled') DEFAULT 'upcoming',
    min_participants INT DEFAULT 0,
    max_participants INT,
    created_by INT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (created_by) REFERENCES users(user_id)
);

-- Participation records
CREATE TABLE participations (
    participation_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    activity_id INT NOT NULL,
    participation_type ENUM('tenaga_fisik', 'donasi', 'pinjam_alat') NOT NULL,
    shift ENUM('pagi', 'sore') NULL,
    donation_amount DECIMAL(10,2) NULL,
    payment_proof VARCHAR(255) NULL,
    equipment_photo VARCHAR(255) NULL,
    equipment_description TEXT NULL,
    attendance_time DATETIME NULL,
    points_earned INT DEFAULT 0,
    qr_code VARCHAR(255) NULL,
    status ENUM('registered', 'attended', 'absent', 'pending_approval', 'approved', 'rejected') DEFAULT 'registered',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (activity_id) REFERENCES activities(activity_id)
);

-- Rewards catalog
CREATE TABLE rewards (
    reward_id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    points_required INT NOT NULL,
    image VARCHAR(255),
    quantity INT DEFAULT 0,
    status ENUM('active', 'inactive') DEFAULT 'active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Reward claims
CREATE TABLE reward_claims (
    claim_id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL,
    reward_id INT NOT NULL,
    points_used INT NOT NULL,
    status ENUM('pending', 'approved', 'rejected', 'claimed') DEFAULT 'pending',
    claimed_at DATETIME NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES users(user_id),
    FOREIGN KEY (reward_id) REFERENCES rewards(reward_id)
);

-- RT (Neighborhood) table for reporting
CREATE TABLE rt_data (
    rt_id INT AUTO_INCREMENT PRIMARY KEY,
    rt_number VARCHAR(10) NOT NULL,
    name VARCHAR(100),
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Scan history for admin
CREATE TABLE scan_history (
    scan_id INT AUTO_INCREMENT PRIMARY KEY,
    admin_id INT NOT NULL,
    participation_id INT NOT NULL,
    scan_time DATETIME NOT NULL,
    status ENUM('success', 'failed') NOT NULL,
    notes TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (admin_id) REFERENCES users(user_id),
    FOREIGN KEY (participation_id) REFERENCES participations(participation_id)
);

-- Insert sample RT data
INSERT INTO rt_data (rt_number, name) VALUES 
('001', 'RT 001'), 
('002', 'RT 002'), 
('003', 'RT 003');

-- Insert sample rewards
INSERT INTO rewards (name, description, points_required, image, quantity, status)
VALUES 
('Paket Sembako', 'Paket sembako lengkap untuk kebutuhan dapur', 100, 'sembako.jpg', 50, 'active'),
('Potongan Iuran', 'Potongan iuran bulanan RT', 75, 'iuran.jpg', 100, 'active'),
('Voucher Belanja', 'Voucher belanja di toko kelontong', 50, 'voucher.jpg', 30, 'active');

-- Insert sample activity
INSERT INTO activities (title, description, location, date, morning_shift_start, morning_shift_end, evening_shift_start, evening_shift_end, qr_code, status, min_participants, max_participants, created_by)
VALUES 
('Bersih Kali Ciliwung', 'Kegiatan bersih-bersih kali Ciliwung untuk mencegah banjir', 'Jembatan Kali Ciliwung', '2023-12-15', '08:00:00', '11:00:00', '14:00:00', '17:00:00', 'qr_activity_001', 'upcoming', 10, 50, 1);
