44 lines
2.0 KiB
SQL
44 lines
2.0 KiB
SQL
CREATE TABLE IF NOT EXISTS visitor_verifications (
|
|
id VARCHAR(36) PRIMARY KEY,
|
|
created_at TIMESTAMP NOT NULL DEFAULT now(),
|
|
verification_method VARCHAR(10) NOT NULL,
|
|
target VARCHAR(120) NOT NULL,
|
|
code_hash VARCHAR(64) NOT NULL,
|
|
expires_at TIMESTAMP NOT NULL,
|
|
verified_at TIMESTAMP,
|
|
verification_token VARCHAR(64)
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_visitor_verifications_token ON visitor_verifications (verification_token);
|
|
|
|
CREATE TABLE IF NOT EXISTS visitor_applications (
|
|
id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
|
|
created_at TIMESTAMP NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMP NOT NULL DEFAULT now(),
|
|
status VARCHAR(20) NOT NULL DEFAULT 'SUBMITTED',
|
|
visitor_name VARCHAR(80) NOT NULL,
|
|
company VARCHAR(120),
|
|
contact VARCHAR(40),
|
|
email VARCHAR(120),
|
|
vehicle_no VARCHAR(20),
|
|
zone_name VARCHAR(80),
|
|
room_zone VARCHAR(80),
|
|
purpose VARCHAR(255) NOT NULL,
|
|
purpose_code VARCHAR(40),
|
|
purpose_detail VARCHAR(255),
|
|
work_name VARCHAR(255),
|
|
watcher2_name VARCHAR(80),
|
|
watcher2_team VARCHAR(80),
|
|
watcher2_contact VARCHAR(60),
|
|
visit_from TIMESTAMP NOT NULL,
|
|
visit_to TIMESTAMP NOT NULL,
|
|
verification_method VARCHAR(10) NOT NULL,
|
|
verification_target VARCHAR(120) NOT NULL,
|
|
verification_id VARCHAR(36),
|
|
verified_at TIMESTAMP,
|
|
imported_visit_request_id BIGINT,
|
|
imported_at TIMESTAMP
|
|
);
|
|
CREATE INDEX IF NOT EXISTS idx_visitor_applications_status ON visitor_applications (status, created_at DESC);
|
|
CREATE INDEX IF NOT EXISTS idx_visitor_applications_contact ON visitor_applications (contact);
|
|
CREATE INDEX IF NOT EXISTS idx_visitor_applications_email ON visitor_applications (email);
|