database_setup.sql

-- ========================================
-- MWECAU Digital Library System Database Setup
-- ========================================
-- This script creates the database and tables for the MWECAU Digital Library System
-- Compatible with PostgreSQL and MySQL (with minor adjustments)

-- Database Creation (PostgreSQL)
-- CREATE DATABASE mwecau_library;
-- \c mwecau_library;

-- Database Creation (MySQL)
-- CREATE DATABASE mwecau_library;
-- USE mwecau_library;

-- ========================================
-- Table: visitors
-- ========================================
-- Stores information about library visitors
CREATE TABLE visitors (
    id SERIAL PRIMARY KEY,
    visitor_type VARCHAR(20) NOT NULL CHECK (visitor_type IN ('student', 'teacher', 'staff', 'guest')),
    full_name VARCHAR(100) NOT NULL,
    id_number VARCHAR(50) NOT NULL,
    contact VARCHAR(20),
    destination VARCHAR(50) NOT NULL,
    sign_in_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    sign_out_time TIMESTAMP NULL,
    status VARCHAR(20) DEFAULT 'inside' CHECK (status IN ('inside', 'signed_out'))
);

-- ========================================
-- Table: properties
-- ========================================
-- Stores property declarations for each visitor
CREATE TABLE properties (
    id SERIAL PRIMARY KEY,
    visitor_id INTEGER REFERENCES visitors(id) ON DELETE CASCADE,
    description VARCHAR(200) NOT NULL,
    serial_number VARCHAR(100),
    quantity INTEGER DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ========================================
-- Table: admin_users
-- ========================================
-- Stores admin user credentials
CREATE TABLE admin_users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_login TIMESTAMP NULL
);

-- ========================================
-- Indexes for Performance
-- ========================================
CREATE INDEX idx_visitors_status ON visitors(status);
CREATE INDEX idx_visitors_sign_in_time ON visitors(sign_in_time);
CREATE INDEX idx_visitors_id_number ON visitors(id_number);
CREATE INDEX idx_properties_visitor_id ON properties(visitor_id);
CREATE INDEX idx_admin_users_username ON admin_users(username);

-- ========================================
-- Default Admin User
-- ========================================
-- Insert default admin user (username: admin, password: admin123)
-- Password hash for 'admin123' using PHP password_hash()
INSERT INTO admin_users (username, password_hash) VALUES 
('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi');

-- ========================================
-- Sample Data (Optional)
-- ========================================
-- Uncomment below to insert sample data for testing

-- INSERT INTO visitors (visitor_type, full_name, id_number, contact, destination) VALUES
-- ('student', 'John Doe', 'ST2024001', '0712345678', 'main-library'),
-- ('teacher', 'Jane Smith', 'TC2024001', '0723456789', 'research-room'),
-- ('staff', 'Bob Johnson', 'SF2024001', '0734567890', 'computer-lab');

-- INSERT INTO properties (visitor_id, description, serial_number, quantity) VALUES
-- (1, 'Laptop', 'ABC123456', 1),
-- (1, 'Phone Charger', NULL, 1),
-- (2, 'Research Books', NULL, 3);

-- ========================================
-- Views for Common Queries
-- ========================================

-- View: Current visitors inside the library
CREATE VIEW current_visitors AS
SELECT 
    v.id,
    v.visitor_type,
    v.full_name,
    v.id_number,
    v.contact,
    v.destination,
    v.sign_in_time,
    COUNT(p.id) as property_count
FROM visitors v
LEFT JOIN properties p ON v.id = p.visitor_id
WHERE v.status = 'inside'
GROUP BY v.id, v.visitor_type, v.full_name, v.id_number, v.contact, v.destination, v.sign_in_time
ORDER BY v.sign_in_time DESC;

-- View: Daily statistics
CREATE VIEW daily_stats AS
SELECT 
    DATE(sign_in_time) as date,
    COUNT(*) as total_signins,
    COUNT(CASE WHEN visitor_type = 'student' THEN 1 END) as students,
    COUNT(CASE WHEN visitor_type = 'teacher' THEN 1 END) as teachers,
    COUNT(CASE WHEN visitor_type = 'staff' THEN 1 END) as staff,
    COUNT(CASE WHEN visitor_type = 'guest' THEN 1 END) as guests
FROM visitors
GROUP BY DATE(sign_in_time)
ORDER BY date DESC;

-- ========================================
-- Database Configuration Notes
-- ========================================
-- 
-- For PostgreSQL:
-- - Use SERIAL for auto-incrementing primary keys
-- - Use CURRENT_TIMESTAMP for default timestamps
-- - Supports CHECK constraints
-- 
-- For MySQL:
-- - Replace SERIAL with AUTO_INCREMENT
-- - Replace CURRENT_TIMESTAMP with NOW()
-- - May need to adjust CHECK constraints (use ENUM for visitor_type and status)
-- 
-- Environment Variables Required:
-- - DB_HOST: Database host (default: localhost)
-- - DB_NAME: Database name (default: mwecau_library)
-- - DB_USER: Database user (default: postgres for PostgreSQL, root for MySQL)
-- - DB_PASS: Database password
-- - DB_PORT: Database port (default: 5432 for PostgreSQL, 3306 for MySQL)
-- 
-- ========================================
-- End of Database Setup Script
-- ========================================