-- Use your existing database (replace 'qdeuftqy_ekerk' with your actual database name)
USE `qdeuftqy_ekerk`;

-- Users table for authentication
CREATE TABLE IF NOT EXISTS `users` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `username` VARCHAR(50) UNIQUE NOT NULL,
    `password` VARCHAR(255) NOT NULL,
    `email` VARCHAR(100),
    `full_name` VARCHAR(100),
    `role` ENUM('admin', 'user') DEFAULT 'user',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Households table
CREATE TABLE IF NOT EXISTS `households` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `surname` VARCHAR(100) NOT NULL,
    `marriage_anniversary` DATE,
    `address` TEXT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Members table (linked to households)
CREATE TABLE IF NOT EXISTS `members` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `household_id` INT NOT NULL,
    `first_name` VARCHAR(50) NOT NULL,
    `last_name` VARCHAR(50) NOT NULL,
    `email` VARCHAR(100),
    `cellphone` VARCHAR(20),
    `birth_date` DATE,
    `baptism_date` DATE,
    `membership_status` ENUM('active', 'inactive', 'visitor') DEFAULT 'active',
    `relationship` ENUM('spouse', 'child', 'other') DEFAULT 'other',
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    `updated_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (`household_id`) REFERENCES `households`(`id`) ON DELETE CASCADE
);

-- Calendar events table
CREATE TABLE IF NOT EXISTS `calendar_events` (
    `id` INT AUTO_INCREMENT PRIMARY KEY,
    `title` VARCHAR(255) NOT NULL,
    `description` TEXT,
    `event_date` DATE NOT NULL,
    `event_type` ENUM('birthday', 'anniversary', 'church_event', 'other') NOT NULL,
    `household_id` INT,
    `member_id` INT,
    `created_by` INT,
    `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (`household_id`) REFERENCES `households`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`member_id`) REFERENCES `members`(`id`) ON DELETE SET NULL,
    FOREIGN KEY (`created_by`) REFERENCES `users`(`id`)
);

-- Insert default admin user (password: admin123)
INSERT IGNORE INTO `users` (`username`, `password`, `email`, `full_name`, `role`) 
VALUES ('admin', '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi', 'admin@ekerp.com', 'System Administrator', 'admin');