-- config/createTables.sql
CREATE DATABASE IF NOT EXISTS visitor_managements;
USE visitor_managements;

-- -- Drop existing tables if they exist
DROP TABLE IF EXISTS visitor_checkins;
DROP TABLE IF EXISTS visitors;

-- Main visitors table (profile information)
CREATE TABLE visitors (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  contact_no VARCHAR(15) NULL,
  company VARCHAR(255) NULL,
  visitor_type ENUM('vip', 'customer', 'partner') NOT NULL,
  host_name VARCHAR(255) NULL, -- Optional referral/host name
  gov_id_type ENUM('pan', 'aadhar', 'passport', 'driver_license', 'national_id', 'voter_id') DEFAULT NULL,
  gov_id_image_front VARCHAR(255) NULL,
  gov_id_image_back VARCHAR(255) NULL, -- Only required for Aadhar
  user_pic VARCHAR(255) NULL,
  qr_code VARCHAR(8) UNIQUE NOT NULL,
  reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  is_deleted BOOLEAN DEFAULT FALSE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Separate table for check-ins (allows multiple visits per visitor)
CREATE TABLE visitor_checkins (
  id INT AUTO_INCREMENT PRIMARY KEY,
  visitor_id INT NOT NULL,
  purpose_of_visit VARCHAR(500) NULL,
  check_in_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  additional_notes TEXT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (visitor_id) REFERENCES visitors(id) ON DELETE CASCADE
);

-- Create indexes for performance
CREATE INDEX idx_email ON visitors(email);
CREATE INDEX idx_qr_code ON visitors(qr_code);
CREATE INDEX idx_visitor_type ON visitors(visitor_type);
CREATE INDEX idx_gov_id_type ON visitors(gov_id_type);
CREATE INDEX idx_reg_date ON visitors(reg_date);
CREATE INDEX idx_host_name ON visitors(host_name);

CREATE INDEX idx_visitor_id ON visitor_checkins(visitor_id);
CREATE INDEX idx_check_in_date ON visitor_checkins(check_in_date);

-- Create a view for easy querying of visitor with latest check-in
CREATE VIEW visitor_with_latest_checkin AS
SELECT 
    v.*,
    vc.check_in_date as latest_check_in,
    vc.purpose_of_visit as latest_purpose,
    CASE 
        WHEN vc.check_in_date IS NOT NULL THEN TRUE 
        ELSE FALSE 
    END as has_checked_in,
    (SELECT COUNT(*) FROM visitor_checkins WHERE visitor_id = v.id) as total_visits
FROM visitors v
LEFT JOIN (
    SELECT DISTINCT visitor_id,
           FIRST_VALUE(check_in_date) OVER (PARTITION BY visitor_id ORDER BY check_in_date DESC) as check_in_date,
           FIRST_VALUE(purpose_of_visit) OVER (PARTITION BY visitor_id ORDER BY check_in_date DESC) as purpose_of_visit
    FROM visitor_checkins
) vc ON v.id = vc.visitor_id
WHERE v.is_deleted = FALSE;