-- Initial schema for Clergy Registry
CREATE TABLE IF NOT EXISTS clergy (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    clergy_rank VARCHAR(100) NOT NULL,
    metropolis_name VARCHAR(255) NOT NULL,
    email VARCHAR(255),
    status VARCHAR(50) DEFAULT 'Active',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Seed data for testing
INSERT INTO clergy (first_name, last_name, clergy_rank, metropolis_name, email) 
SELECT 'Ioannis', 'Papadopoulos', 'Archimandrite', 'Metropolis of Athens', 'ioannis.p@example.gr'
WHERE NOT EXISTS (SELECT 1 FROM clergy WHERE email = 'ioannis.p@example.gr');

INSERT INTO clergy (first_name, last_name, clergy_rank, metropolis_name, email) 
SELECT 'Dimitrios', 'Georgiou', 'Priest', 'Metropolis of Thessaloniki', 'dimitrios.g@example.gr'
WHERE NOT EXISTS (SELECT 1 FROM clergy WHERE email = 'dimitrios.g@example.gr');