CREATE TABLE IF NOT EXISTS lp_ttn_end_device_uplinks (
    lp_ttn_end_device_uplinks_id UUID PRIMARY KEY,
    device_id VARCHAR(255) NOT NULL,
    application_ids VARCHAR(255),
    dev_eui VARCHAR(255),
    join_eui VARCHAR(255),
    dev_addr VARCHAR(255),
    received_at_utc DATE,
    battery NUMERIC,
    latitude DOUBLE,
    longitude DOUBLE,
    created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS wifi_scan (
    wifi_scan_id UUID PRIMARY KEY,
    lp_ttn_end_device_uplinks_id UUID,
    mac VARCHAR(255),
    rssi NUMERIC,
    latitude DOUBLE NOT NULL,
    longitude DOUBLE NOT NULL,
    created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)
);

CREATE TABLE IF NOT EXISTS ttn_gateway_reception (
    ttn_gateway_reception_id UUID PRIMARY KEY,
    lp_ttn_end_device_uplinks_id UUID,
    gateway_id VARCHAR(255),
    eui VARCHAR(255),
    rssi NUMERIC,
    latitude DOUBLE,
    longitude DOUBLE,
    altitude NUMERIC,
    created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)
);

CREATE TABLE IF NOT EXISTS location (
    location_id UUID PRIMARY KEY,
    lp_ttn_end_device_uplinks_id UUID,
    wifi_latitude DOUBLE,
    wifi_longitude DOUBLE,
    gnss_latitude DOUBLE,
    gnss_longitude DOUBLE,
    ttn_gw_latitude DOUBLE,
    ttn_gw_longitude DOUBLE,
    created_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at_utc TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lp_ttn_end_device_uplinks_id) REFERENCES lp_ttn_end_device_uplinks(lp_ttn_end_device_uplinks_id)
);