CREATE TABLE IF NOT EXISTS submissions (
    id INT AUTO_INCREMENT PRIMARY KEY,
    full_name VARCHAR(255) NOT NULL,
    id_or_passport VARCHAR(255) NOT NULL,
    id_image_path VARCHAR(255) NOT NULL,
    gender VARCHAR(50) NOT NULL,
    birth_date DATE NOT NULL,
    mobile_phone VARCHAR(50) NOT NULL,
    mailing_address TEXT NOT NULL,
    start_visit_date DATE NOT NULL,
    end_visit_date DATE NOT NULL,
    purpose_of_visit TEXT NOT NULL,
    visit_category VARCHAR(255) NOT NULL,
    visit_location_lat VARCHAR(255) NOT NULL,
    visit_location_lon VARCHAR(255) NOT NULL,
    official_letter_path VARCHAR(255),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS visitors (
    id INT AUTO_INCREMENT PRIMARY KEY,
    submission_id INT NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    id_or_passport VARCHAR(255) NOT NULL,
    id_image_path VARCHAR(255) NOT NULL,
    mobile_phone VARCHAR(50) NOT NULL,
    mailing_address TEXT NOT NULL,
    FOREIGN KEY (submission_id) REFERENCES submissions(id) ON DELETE CASCADE
);