CREATE DATABASE IF NOT EXISTS guest_management CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE guest_management;


CREATE TABLE roles (id INT AUTO_INCREMENT PRIMARY KEY, role_name VARCHAR(50) NOT NULL UNIQUE);
INSERT INTO roles(role_name) VALUES ('Group Admin'),('Location Admin'),('Reception'),('Security'),('Person To Meet');

CREATE TABLE locations (
 id INT AUTO_INCREMENT PRIMARY KEY, location_name VARCHAR(150) NOT NULL, address TEXT, city VARCHAR(100), status ENUM('Active','Inactive') DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE visit_purposes (
 id INT AUTO_INCREMENT PRIMARY KEY, location_id INT NOT NULL, purpose_name VARCHAR(150) NOT NULL, status ENUM('Active','Inactive') DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(location_id) REFERENCES locations(id)
);

CREATE TABLE persons_to_meet (
 id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, location_id INT NOT NULL, person_name VARCHAR(150) NOT NULL, mobile VARCHAR(30), email VARCHAR(150), department VARCHAR(150), designation VARCHAR(150), status ENUM('Active','Inactive') DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(location_id) REFERENCES locations(id), FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE visitors (
 id INT AUTO_INCREMENT PRIMARY KEY, location_id INT NOT NULL, visitor_name VARCHAR(150) NOT NULL, company_name VARCHAR(150), place VARCHAR(150), phone VARCHAR(30),
 person_to_meet_id INT NULL, person_to_meet_name VARCHAR(150), purpose_id INT NULL, purpose_name VARCHAR(150), vehicle_number VARCHAR(50), photo_path VARCHAR(255),
 status ENUM('In','Out','Expected','Blacklisted Alert') DEFAULT 'In', approval_status ENUM('Not Required','Pending','Approved','Rejected') DEFAULT 'Not Required', approved_by INT NULL, approved_at DATETIME NULL,
 in_time DATETIME DEFAULT CURRENT_TIMESTAMP, out_time DATETIME NULL, created_by INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(location_id) REFERENCES locations(id), FOREIGN KEY(created_by) REFERENCES users(id), FOREIGN KEY(approved_by) REFERENCES users(id) ON DELETE SET NULL
);

CREATE TABLE blacklist (
 id INT AUTO_INCREMENT PRIMARY KEY, location_id INT NULL, visitor_name VARCHAR(150), phone VARCHAR(30), company_name VARCHAR(150), reason TEXT, photo_path VARCHAR(255),
 scope ENUM('Single Location','All Locations') DEFAULT 'Single Location', status ENUM('Active','Inactive') DEFAULT 'Active', created_by INT NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(location_id) REFERENCES locations(id), FOREIGN KEY(created_by) REFERENCES users(id)
);

CREATE TABLE visitor_logs (
 id INT AUTO_INCREMENT PRIMARY KEY, visitor_id INT NULL, expected_visitor_id INT NULL, location_id INT NOT NULL,
 action VARCHAR(100) NOT NULL, action_by INT NOT NULL, action_time DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE 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,
 FOREIGN KEY(user_id) REFERENCES users(id) ON DELETE CASCADE, FOREIGN KEY(related_visitor_id) REFERENCES visitors(id) ON DELETE SET NULL, FOREIGN KEY(related_expected_visitor_id) REFERENCES expected_visitors(id) ON DELETE SET NULL
);

CREATE TABLE audit_logs (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT NULL, action VARCHAR(255), created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

CREATE INDEX idx_visitors_phone ON visitors(phone);
CREATE INDEX idx_visitors_location_phone ON visitors(location_id, phone);
CREATE INDEX idx_notifications_user_status ON notifications(user_id, status);

INSERT INTO locations(location_name,address,city) VALUES ('Head Office','Main Campus','Agra');
INSERT INTO visit_purposes(location_id,purpose_name) VALUES (1,'Meeting'),(1,'Interview'),(1,'Delivery'),(1,'Service Support');
INSERT INTO persons_to_meet(user_id,location_id,person_name,mobile,email,department,designation,status) VALUES
(4,1,'Rajesh Sharma','9999999999','rajesh@example.com','Admin','Manager','Active'),
(NULL,1,'Purchase Department',NULL,NULL,'Purchase',NULL,'Active');
