-- V1__create_lookup_data_table.sql

-- Create the lookup_data table to store various lookup values.
CREATE TABLE lookup_data (
    id SERIAL PRIMARY KEY,
    lookup_category VARCHAR(255) NOT NULL,
    lookup_category_name VARCHAR(255),
    lookup_key VARCHAR(255) NOT NULL,
    lookup_label VARCHAR(255) NOT NULL,
    lookup_status hdb_common_status_enum NOT NULL DEFAULT 'active',
    UNIQUE (lookup_category, lookup_key)
);

// Create index on lookup_category and lookup_key for faster lookups
CREATE INDEX idx_lookup_category_key ON lookup_data (lookup_category, lookup_key);

-- Comments for columns:
-- id: The unique identifier for each lookup entry.
-- lookup_category: The category for a set of lookups (e.g., 'USER_ROLES', 'DOCUMENT_TYPES').
-- lookup_key: The machine-readable key for a lookup value (e.g., 'ADMIN', 'USER_AGREEMENT').
-- lookup_label: The human-readable label for a lookup value (e.g., 'Administrator', 'User Agreement').
-- lookup_status: The status of the lookup value, using the hdb_common_status_enum type.


INSERT INTO lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label)
VALUES
('BANK_NAME', 'Bank Name', 'BANK_OF_BARODA', 'Bank of Baroda'),
('BANK_NAME', 'Bank Name', 'BANK_OF_INDIA', 'Bank of India'),
('BANK_NAME', 'Bank Name', 'BANK_OF_MAHARASHTRA', 'Bank of Maharashtra'),
('BANK_NAME', 'Bank Name', 'CANARA_BANK', 'Canara Bank'),
('BANK_NAME', 'Bank Name', 'CENTRAL_BANK_OF_INDIA', 'Central Bank of India'),
('BANK_NAME', 'Bank Name', 'INDIAN_BANK', 'Indian Bank'),
('BANK_NAME', 'Bank Name', 'INDIAN_OVERSEAS_BANK', 'Indian Overseas Bank'),
('BANK_NAME', 'Bank Name', 'PUNJAB_AND_SIND_BANK', 'Punjab and Sind Bank'),
('BANK_NAME', 'Bank Name', 'PUNJAB_NATIONAL_BANK', 'Punjab National Bank'),
('BANK_NAME', 'Bank Name', 'STATE_BANK_OF_INDIA', 'State Bank of India'),
('BANK_NAME', 'Bank Name', 'UCO_BANK', 'UCO Bank'),
('BANK_NAME', 'Bank Name', 'UNION_BANK_OF_INDIA', 'Union Bank of India'),
('BANK_NAME', 'Bank Name', 'AXIS_BANK', 'Axis Bank'),
('BANK_NAME', 'Bank Name', 'BANDHAN_BANK', 'Bandhan Bank'),
('BANK_NAME', 'Bank Name', 'CSB_BANK', 'CSB Bank'),
('BANK_NAME', 'Bank Name', 'CITY_UNION_BANK', 'City Union Bank'),
('BANK_NAME', 'Bank Name', 'DCB_BANK', 'DCB Bank'),
('BANK_NAME', 'Bank Name', 'DHANLAXMI_BANK', 'Dhanlaxmi Bank'),
('BANK_NAME', 'Bank Name', 'FEDERAL_BANK', 'Federal Bank'),
('BANK_NAME', 'Bank Name', 'HDFC_BANK', 'HDFC Bank'),
('BANK_NAME', 'Bank Name', 'ICICI_BANK', 'ICICI Bank'),
('BANK_NAME', 'Bank Name', 'IDBI_BANK', 'IDBI Bank'),
('BANK_NAME', 'Bank Name', 'IDFC_FIRST_BANK', 'IDFC First Bank'),
('BANK_NAME', 'Bank Name', 'INDUSIND_BANK', 'IndusInd Bank'),
('BANK_NAME', 'Bank Name', 'KARNATAKA_BANK', 'Karnataka Bank'),
('BANK_NAME', 'Bank Name', 'KARUR_VYSYA_BANK', 'Karur Vysya Bank'),
('BANK_NAME', 'Bank Name', 'KOTAK_MAHINDRA_BANK', 'Kotak Mahindra Bank'),
('BANK_NAME', 'Bank Name', 'NAINITAL_BANK', 'Nainital Bank'),
('BANK_NAME', 'Bank Name', 'RBL_BANK', 'RBL Bank'),
('BANK_NAME', 'Bank Name', 'SOUTH_INDIAN_BANK', 'South Indian Bank'),
('BANK_NAME', 'Bank Name', 'TAMILNAD_MERCANTILE_BANK','Tamilnad Mercantile Bank'),
('BANK_NAME', 'Bank Name', 'YES_BANK','Yes Bank'),
('BANK_NAME', 'Bank Name', 'JAMMU_AND_KASHMIR_BANK_LTD','Jammu & Kashmir Bank Ltd'),
('BANK_NAME', 'Bank Name', 'OTHER', 'Other');

INSERT INTO lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label)
VALUES
('CITY_NAME', 'City', 'HYDERABAD', 'Hyderabad'),
('CITY_NAME', 'City', 'MUMBAI', 'Mumbai'),
('CITY_NAME', 'City', 'DELHI', 'Delhi'),
('CITY_NAME', 'City', 'BANGALORE', 'Bangalore'),
('CITY_NAME', 'City', 'KOLKATA', 'Kolkata'),
('CITY_NAME', 'City', 'PUNE', 'Pune'),
('CITY_NAME', 'City', 'AHMEDABAD', 'Ahmedabad'),
('CITY_NAME', 'City', 'JAIPUR', 'Jaipur'),
('CITY_NAME', 'City', 'SURAT', 'Surat'),
('CITY_NAME', 'City', 'LUCKNOW', 'Lucknow'),
('CITY_NAME', 'City', 'KANPUR', 'Kanpur'),
('CITY_NAME', 'City', 'NAGPUR', 'Nagpur'),
('CITY_NAME', 'City', 'PATNA', 'Patna'),
('CITY_NAME', 'City', 'INDORE', 'Indore'),
('CITY_NAME', 'City', 'BHOPAL', 'Bhopal'),
('CITY_NAME', 'City', 'VADODARA', 'Vadodara'),
('CITY_NAME', 'City', 'LUDHIANA', 'Ludhiana'),
('CITY_NAME', 'City', 'AGRA', 'Agra'),
('CITY_NAME', 'City', 'VARANASI', 'Varanasi'),
('CITY_NAME', 'City', 'AMRITSAR', 'Amritsar'),
('CITY_NAME', 'City', 'COIMBATORE', 'Coimbatore'),
('CITY_NAME', 'City', 'THIRUVANANTHAPURAM', 'Thiruvananthapuram'),
('CITY_NAME', 'City', 'GUWAHATI', 'Guwahati'),
('CITY_NAME', 'City', 'RANCHI', 'Ranchi'),
('CITY_NAME', 'City', 'OTHER', 'Other');

INSERT INTO lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label)
VALUES
('GENDER', 'Gender', 'OTHER', 'Other'),
('GENDER', 'Gender', 'MALE', 'Male'),
('GENDER', 'Gender', 'FEMALE', 'Female'),
('GENDER', 'Gender', 'PREFER_NOT_TO_SAY', 'Prefer not to say'),

('PAYMENT_MODE', 'Payment mode', 'ONLINE', 'Online'),
('PAYMENT_MODE', 'Payment mode', 'OFFLINE', 'Offline'),
('PAYMENT_MODE', 'Payment mode', 'CHEQUE', 'Cheque'),
('PAYMENT_MODE', 'Payment mode', 'CARD_SWIPE', 'Card swipe'),
('PAYMENT_MODE', 'Payment mode', 'CASH', 'Cash'),
('PAYMENT_MODE', 'Payment mode', 'ONLINE_RAZORPAY', 'Online (Through Razorpay)'),
('PAYMENT_MODE', 'Payment mode', 'DIRECT_BANK_TRANSFER', 'Direct bank transfer (NEFT/RTGS)'),

('TSHIRT_SIZE', 'Tshirt size', 'XS', 'XS'),
('TSHIRT_SIZE', 'Tshirt size', 'S', 'S'),
('TSHIRT_SIZE', 'Tshirt size', 'M', 'M'),
('TSHIRT_SIZE', 'Tshirt size', 'L', 'L'),
('TSHIRT_SIZE', 'Tshirt size', 'XL', 'XL'),
('TSHIRT_SIZE', 'Tshirt size', 'XXL', 'XXL'),
('TSHIRT_SIZE', 'Tshirt size', 'XXXL', 'XXXL'),

('TRAVEL_PLAN', 'Travel Plan', 'FLIGHT', 'Flight'),
('TRAVEL_PLAN', 'Travel Plan', 'OWN_TRANSPORT', 'Own transport'),
('TRAVEL_PLAN', 'Travel Plan', 'CITY_DROP', 'City drop'),
('TRAVEL_PLAN', 'Travel Plan', 'CITY_PICK_UP', 'City pick up'),

('ID_TYPE', 'Id type', 'PASSPORT', 'Passport'),
('ID_TYPE', 'Id type', 'AADHAR', 'Aadhar'),
('ID_TYPE', 'Id type', 'DRIVING_LICENSE', 'Driving license'),
('ID_TYPE', 'Id type', 'VOTER_ID', 'Voter id'),
('ID_TYPE', 'Id type', 'PAN_CARD', 'Pan card');

INSERT INTO lookup_data (lookup_category, lookup_category_name, lookup_key, lookup_label)
VALUES
('AIRLINE', 'Airline', 'EK', 'Emirates'),
('AIRLINE', 'Airline', 'EY', 'Etihad Airways'),
('AIRLINE', 'Airline', 'QR', 'Qatar Airways'),
('AIRLINE', 'Airline', 'SQ', 'Singapore Air'),
('AIRLINE', 'Airline', 'I5', 'Air Asia'),
('AIRLINE', 'Airline', 'AI', 'Air India'),
('AIRLINE', 'Airline', 'G8', 'Go First'),
('AIRLINE', 'Airline', '6E', 'IndiGo'),
('AIRLINE', 'Airline', 'SG', 'SpiceJet'),
('AIRLINE', 'Airline', 'UK', 'Vistara'),
('AIRLINE', 'Airline', 'OTHER', 'Other');


