-- $Id$

drop type address cascade;

create type address as (
    streetNumber        varchar,
    street              varchar,
    city                varchar,
    state               varchar,
    postalCode          varchar
);

drop type interpolated_point cascade;

create type interpolated_point as (
    point   geometry,
    percent numeric
);

create or replace function getPercentageDownLine (geometry, geometry) returns interpolated_point as $$
declare
    tmp     numeric := 0;
    percent numeric := 1;
    rec     interpolated_point;
    geom    geometry;
begin
    for i in 1..NumGeometries($2) loop
        tmp := line_locate_point(GeometryN($2, i), $1);
        if tmp < percent then
            percent := tmp;
            geom := line_interpolate_point(GeometryN($2, i), percent);
        end if;
    end loop;
    select geom, percent into rec;
    return rec; 
end;
$$ language plpgsql;

create or replace function getHouseNumber (geometry, interpolated_point, numeric, numeric, numeric, numeric) returns integer as $$
declare
    houseNumber integer := 0;
begin
    if $3 = 0 or $4 = 0 or $5 = 0 or $6 = 0 then
        return 0;
    end if; 

    if $1 &> $2.point then
        if $6 > $5 then
            houseNumber := round(($5 + (($6 - $5) * $2.percent)));
        else
            houseNumber := round(($6 + (($5 - $6) * $2.percent)));
        end if;
    else
        if $4 > $3 then
            houseNumber := round(($3 + (($4 - $3) * $2.percent)));
        else
            houseNumber := round(($4 + (($3 - $4) * $2.percent)));
        end if;
    end if;
    if houseNumber = 0 then
        if $5 = 0 and $6 = 0 and $3 > 0 and $4 > 0 then
            houseNumber := round(($3 + (($4 - $3) * ipt.percent)));
        elsif $3 = 0 and $4 = 0 and $5 > 0 and $6 > 0 then
            houseNumber := round(($5 + (($6 - $5) * ipt.percent)));
        end if;
    end if;

    return houseNumber;
end;
$$ language plpgsql;

create or replace function getAddress(numeric, numeric) returns address as $$
declare
    addr    address;
    rec     record;
    ipt     interpolated_point;
    pt      geometry;
    i       numeric;
    j       numeric;
begin
    pt := GeomFromEWKT('SRID=32767;POINT(' || $2 || ' ' || $1 || ')');

    --  LSADC code 01 is for state. This assumes only one state in the DB for now. NOTE: need to perform nearest-neighbor search on a state polygon set to get actual state value
    select fraddl, toaddl, fraddr, toaddr, zipl, zipr, placel, placer, (select name from "EntityNames" where lsadc = '01') as state, fename as fullname, wkb_geometry into rec
        from "CompleteChain" where tlid = (select tlid from "CompleteChain" where wkb_geometry && Expand(pt,0.005) and fename is not null group by tlid order by min(distance(wkb_geometry,pt)) limit 1);
    addr.street := rec.fullname;

    ipt := getPercentageDownLine(pt, Multi(rec.wkb_geometry));

    if ipt IS NULL then
        addr.streetNumber := 0;
    else
        addr.streetNumber := getHouseNumber(pt, ipt, to_number(rec.fraddl, '999999'),  to_number(rec.toaddl, '999999'),  to_number(rec.fraddr, '999999'),  to_number(rec.toaddr, '999999')); 
    end if;

    -- Problem here is that not all areas or records have placer and placel populated. It currently needs FIPS number to link back to until I can find another way to query it. 
    addr.state := rec.state;
    if pt &> ipt.point or rec.zipl is null then
        addr.city := (select name from "EntityNames" where fips = rec.placer and entity = 'P' limit 1);
        addr.postalCode := rec.zipr;
    else
        addr.city := (select name from "EntityNames" where fips = rec.placel and entity = 'P' limit 1);
        addr.postalCode := rec.zipl;
    end if;

    return addr;
end;
$$ language plpgsql;