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

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS notifications;
DROP TABLE IF EXISTS visitor_logs;
DROP TABLE IF EXISTS blacklist;
DROP TABLE IF EXISTS expected_visitors;
DROP TABLE IF EXISTS visitors;
DROP TABLE IF EXISTS persons_to_meet;
DROP TABLE IF EXISTS visit_purposes;
DROP TABLE IF EXISTS users;
DROP TABLE IF EXISTS locations;
DROP TABLE IF EXISTS roles;
SET FOREIGN_KEY_CHECKS=1;

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 users (
 id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(150) NOT NULL, email VARCHAR(150) NOT NULL UNIQUE, password VARCHAR(255) NOT NULL,
 role_id INT NOT NULL, location_id INT NULL, status ENUM('Active','Inactive') DEFAULT 'Active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
 FOREIGN KEY(role_id) REFERENCES roles(id), FOREIGN KEY(location_id) REFERENCES locations(id)
);

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 expected_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), expected_date DATE NOT NULL, expected_time TIME NULL,
 qr_token VARCHAR(100) UNIQUE, status ENUM('Expected','In','Out','Cancelled') DEFAULT 'Expected', approval_status ENUM('Pending','Approved','Rejected') DEFAULT 'Pending', approved_by INT NULL, approved_at DATETIME NULL, rejection_reason TEXT NULL, notification_status ENUM('Pending','Sent','Read') DEFAULT 'Pending',
 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_expected_visitors_phone ON expected_visitors(phone);
CREATE INDEX idx_expected_approval_status ON expected_visitors(approval_status);
CREATE INDEX idx_expected_person_to_meet ON expected_visitors(person_to_meet_id);
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 users(name,email,password,role_id,location_id,status) VALUES
('Group Admin','admin@example.com','$2y$12$rKPseduxeMhyox9gRkF5i.TwxEkfgPvbfGxuYWETe65HkFJRqv4E.',1,NULL,'Active'),
('Security User','security@example.com','$2y$12$rKPseduxeMhyox9gRkF5i.TwxEkfgPvbfGxuYWETe65HkFJRqv4E.',4,1,'Active'),
('Reception User','reception@example.com','$2y$12$rKPseduxeMhyox9gRkF5i.TwxEkfgPvbfGxuYWETe65HkFJRqv4E.',3,1,'Active'),
('Rajesh Sharma','rajesh@example.com','$2y$12$rKPseduxeMhyox9gRkF5i.TwxEkfgPvbfGxuYWETe65HkFJRqv4E.',5,1,'Active');
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');