CREATE TABLE IF NOT EXISTS users (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(120) NOT NULL, email VARCHAR(190) NOT NULL UNIQUE, password_hash VARCHAR(255) NOT NULL, role ENUM('member','moderator','admin','pastor') DEFAULT 'member', avatar VARCHAR(500) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS posts (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NOT NULL, body TEXT NOT NULL, image_url VARCHAR(500) NULL, youtube_id VARCHAR(50) NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS comments (id INT AUTO_INCREMENT PRIMARY KEY, post_id INT NOT NULL, user_id INT NOT NULL, body TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS reactions (id INT AUTO_INCREMENT PRIMARY KEY, post_id INT NOT NULL, user_id INT NOT NULL, type ENUM('like','amen') NOT NULL, UNIQUE KEY unique_reaction(post_id,user_id,type), FOREIGN KEY(post_id) REFERENCES posts(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE);
CREATE TABLE IF NOT EXISTS prayer_requests (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, name VARCHAR(120), email VARCHAR(190), request_text TEXT NOT NULL, visibility ENUM('pastoral','cell','community','anonymous') DEFAULT 'pastoral', status ENUM('new','praying','answered','closed') DEFAULT 'new', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS events (id INT AUTO_INCREMENT PRIMARY KEY, title VARCHAR(180) NOT NULL, slug VARCHAR(180) NOT NULL UNIQUE, description TEXT, event_date DATE NOT NULL, start_time TIME NULL, end_time TIME NULL, location VARCHAR(255), image_url VARCHAR(500), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);
CREATE TABLE IF NOT EXISTS event_registrations (id INT AUTO_INCREMENT PRIMARY KEY, event_id INT NOT NULL, user_id INT NULL, name VARCHAR(120) NOT NULL, email VARCHAR(190), phone VARCHAR(40), guests INT DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(event_id) REFERENCES events(id) ON DELETE CASCADE, FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL);
CREATE TABLE IF NOT EXISTS messages (id INT AUTO_INCREMENT PRIMARY KEY, sender_id INT NOT NULL, recipient_id INT NOT NULL, body TEXT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY(sender_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY(recipient_id) REFERENCES users(id) ON DELETE CASCADE);
INSERT IGNORE INTO events(title,slug,description,event_date,start_time,end_time,location,image_url) VALUES
('Sunday Worship Experience','sunday-worship','Worship, the Word and fellowship for the whole family.',DATE_ADD(CURDATE(),INTERVAL (7-DAYOFWEEK(CURDATE())+1) DAY),'09:00:00','13:00:00','Healing Driven Church Kasangati','assets/images/hdc-logo.jpg'),
('Friday Overnight','friday-overnight','A night of worship, prayer, teaching and encounters in God’s presence.',DATE_ADD(CURDATE(),INTERVAL ((6-DAYOFWEEK(CURDATE())+7)%7) DAY),'22:00:00','06:00:00','Healing Driven Church Kasangati','assets/images/friday-overnight.jpeg'),
('Lunch Hour Prayer','lunch-hour-prayer','Daily lunch hour prayer and intercession.',CURDATE(),'12:30:00','13:30:00','Healing Driven Church Kasangati','assets/images/hdc-logo.jpg');