USE guest_management;
INSERT INTO roles (role_name) SELECT 'Person To Meet' WHERE NOT EXISTS (SELECT 1 FROM roles WHERE role_name='Person To Meet');
ALTER TABLE persons_to_meet ADD COLUMN IF NOT EXISTS user_id INT NULL AFTER id;
ALTER TABLE persons_to_meet ADD COLUMN IF NOT EXISTS mobile VARCHAR(30) NULL AFTER person_name;
ALTER TABLE persons_to_meet ADD COLUMN IF NOT EXISTS email VARCHAR(150) NULL AFTER mobile;
ALTER TABLE persons_to_meet ADD COLUMN IF NOT EXISTS department VARCHAR(150) NULL AFTER email;
ALTER TABLE persons_to_meet ADD COLUMN IF NOT EXISTS designation VARCHAR(150) NULL AFTER department;
ALTER TABLE expected_visitors ADD COLUMN IF NOT EXISTS approval_status ENUM('Pending','Approved','Rejected') DEFAULT 'Pending' AFTER status;
ALTER TABLE expected_visitors ADD COLUMN IF NOT EXISTS approved_by INT NULL AFTER approval_status;
ALTER TABLE expected_visitors ADD COLUMN IF NOT EXISTS approved_at DATETIME NULL AFTER approved_by;
ALTER TABLE expected_visitors ADD COLUMN IF NOT EXISTS rejection_reason TEXT NULL AFTER approved_at;
ALTER TABLE expected_visitors ADD COLUMN IF NOT EXISTS notification_status ENUM('Pending','Sent','Read') DEFAULT 'Pending' AFTER rejection_reason;
ALTER TABLE visitors ADD COLUMN IF NOT EXISTS approval_status ENUM('Not Required','Pending','Approved','Rejected') DEFAULT 'Not Required' AFTER status;
ALTER TABLE visitors ADD COLUMN IF NOT EXISTS approved_by INT NULL AFTER approval_status;
ALTER TABLE visitors ADD COLUMN IF NOT EXISTS approved_at DATETIME NULL AFTER approved_by;
ALTER TABLE visitor_logs MODIFY action VARCHAR(100) NOT NULL;
CREATE TABLE IF NOT EXISTS notifications (
 id INT AUTO_INCREMENT PRIMARY KEY,
 user_id INT NOT NULL,
 title VARCHAR(255) NOT NULL,
 message TEXT,
 related_visitor_id INT NULL,
 related_expected_visitor_id INT NULL,
 status ENUM('Unread','Read') DEFAULT 'Unread',
 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);