--
-- packages/places/sql/pl_address-create.sql
--
-- @author jmileham@arsdigita.com
-- @creation-date 2001-01-19
-- @cvs-id $Id: pl_address-create.sql,v 1.1.1.1 2001/04/20 20:51:14 donb Exp $
--


  
--------------------
-- ADDRESS OBJECT --
--------------------

begin
 acs_object_type.create_type (
   supertype => 'place',
   object_type => 'pl_address',
   pretty_name => 'Address',
   pretty_plural => 'Addresses',
   table_name => 'pl_addresses',
   id_column => 'address_id',
   package_name => 'pl_address',
   name_method => 'pl_address.name'
 );
end;
/
show errors

---------------------
-- ADDRESSES TABLE --
---------------------

create table pl_addresses (
	address_id		not null
				constraint pl_addresses_address_id_fk
				references places (place_id)
				constraint pl_addresses_pk primary key,
	address_name		varchar2(200),
	line_one		varchar2(200),
	line_two		varchar2(200),
	line_three		varchar2(200),
	line_four		varchar2(200),
	municipality_name	varchar2(200),
	postal_code		varchar2(50)
);
-- NOTE: city and postal code will probably be modeled as places in the near future.

-- The pl_addresses_table has an insert trigger defined further below.

-------------------------------
-- ADDRESSES DENORMALIZATION --
-------------------------------

-- denormalization for speeding up the pl_addresses_info and 
-- pl_addresses_complete views.  DO NOT READ OR WRITE TO THIS TABLE DIRECTLY.
create table pl_address_superplace_index (
    address_id             constraint pl_asi_address_id_fk
                           references places(place_id)
                           on delete cascade
                           constraint pl_asi_address_id_pk
                           primary key,
    region_id              constraint pl_asi_region_id_fk
                           references pl_regions(region_id),
    country_id             constraint pl_asi_country_id_fk
                           references pl_countries(country_id),
    postal_code_id         references pl_postal_codes(postal_code_id)
);


---------------------
-- ADDRESS PACKAGE --
---------------------

create or replace package pl_address
as

 function new (
  address_id		in pl_addresses.address_id%TYPE default null,
  object_type		in acs_objects.object_type%TYPE
			   default 'pl_address',
  creation_date		in acs_objects.creation_date%TYPE
			   default sysdate,
  creation_user		in acs_objects.creation_user%TYPE
			   default null,
  creation_ip		in acs_objects.creation_ip%TYPE
			   default null,
  context_id		in acs_objects.context_id%TYPE
			   default null,
  latitude		in places.latitude%TYPE
			   default null,
  longitude		in places.longitude%TYPE
			   default null,
  address_name		in pl_addresses.address_name%TYPE
			   default null,
  line_one		in pl_addresses.line_one%TYPE
			   default null,
  line_two		in pl_addresses.line_two%TYPE
			   default null,
  line_three		in pl_addresses.line_three%TYPE
			   default null,
  line_four		in pl_addresses.line_four%TYPE
			   default null,
  municipality_name	in pl_addresses.municipality_name%TYPE
			   default null,
  postal_code		in pl_addresses.postal_code%TYPE
			   default null,
  superplace_id		in acs_rels.object_id_one%TYPE
			   default null,
  locatee_id		in acs_rels.object_id_two%TYPE
			default null,
  use_context		in location_rels.use_context%TYPE
			default null,
  before_rel_id		in location_rels.rel_id%TYPE
			default null
 ) return pl_addresses.address_id%TYPE;

 procedure delete (
  address_id		in pl_addresses.address_id%TYPE
 );

 function name (
  address_id		in pl_addresses.address_id%TYPE
 ) return pl_addresses.address_name%TYPE;

 function violation (
  postal_code_id	in pl_postal_codes.postal_code_id%TYPE default null,
  region_id		in pl_regions.region_id%TYPE default null,
  country_id		in pl_countries.country_id%TYPE default null
 ) return varchar2;

 -- This is primarily intended as a private member function, but
 -- it does what you'd expect, so go ahead and use it if you'd
 -- like.  Note that not all arguments are truly validated at the
 -- moment... this is to allow for future extension without change
 -- in API.

 -- ** This throws an exception if the hierarchy is
 -- ** invalid, so don't use it for validation... Use pl_address.violation
 -- ** instead if you're not sure.

 function smallest_superplace_id (
  postal_code_id	in pl_addresses.postal_code%TYPE default null,
  region_id		in pl_regions.region_id%TYPE default null,
  country_id		in pl_countries.iso%TYPE default null
 ) return acs_rels.object_id_one%TYPE;

 -- This function is designed to allow 1 step hierarchy
 -- validation, address creation (as a subplace
 -- if possible) and (optional) locatee association.

 -- ** Don't use this if you aren't sure that the place
 -- ** place hierarchy is valid, cause it will throw an exception.
 -- ** Use pl_address.violation for that.

 function new_intl (
  address_id		in pl_addresses.address_id%TYPE default null,
  object_type		in acs_objects.object_type%TYPE
			   default 'pl_address',
  creation_date		in acs_objects.creation_date%TYPE
			   default sysdate,
  creation_user		in acs_objects.creation_user%TYPE
			   default null,
  creation_ip		in acs_objects.creation_ip%TYPE
			   default null,
  context_id		in acs_objects.context_id%TYPE
			   default null,
  latitude		in places.latitude%TYPE
			   default null,
  longitude		in places.longitude%TYPE
			   default null,
  address_name		in pl_addresses.address_name%TYPE
			   default null,
  line_one		in pl_addresses.line_one%TYPE
			   default null,
  line_two		in pl_addresses.line_two%TYPE
			   default null,
  line_three		in pl_addresses.line_three%TYPE
			   default null,
  line_four		in pl_addresses.line_four%TYPE
			   default null,
  municipality_name	in pl_addresses.municipality_name%TYPE
			   default null,
  postal_code		in pl_addresses.postal_code%TYPE
			   default null,
  postal_code_id	in pl_postal_codes.postal_code_id%TYPE
			   default null,
  region_id		in pl_regions.region_id%TYPE
			   default null,
  country_id		in pl_countries.country_id%TYPE
			   default null,
  locatee_id		in acs_rels.object_id_two%TYPE
			default null,
  use_context		in location_rels.use_context%TYPE
			default null,
  before_rel_id		in location_rels.rel_id%TYPE
			default null
 ) return pl_addresses.address_id%TYPE;

 procedure update_intl (
  address_id		in pl_addresses.address_id%TYPE,
  creation_user		in acs_objects.creation_user%TYPE
			   default null,
  creation_ip		in acs_objects.creation_ip%TYPE
			   default null,
  address_name		in pl_addresses.address_name%TYPE default null,
  line_one		in pl_addresses.line_one%TYPE default null,
  line_two		in pl_addresses.line_two%TYPE default null,
  line_three		in pl_addresses.line_three%TYPE default null,
  line_four		in pl_addresses.line_four%TYPE default null,
  municipality_name	in pl_addresses.municipality_name%TYPE default null,
  postal_code		in pl_addresses.postal_code%TYPE default null,
  postal_code_id	in pl_postal_codes.postal_code_id%TYPE default null,
  region_id		in pl_regions.region_id%TYPE default null,
  country_id		in pl_countries.country_id%TYPE default null
 );

 procedure denormalize_superplaces (
  --/** 
  --    Denormalizes region_id, country_id, and postal_code_id into the
  --    pl_address_superplace_index table (used to optimize views that
  --    need to get all information about an address in one query -- Oracle
  --    really hated all the outer joins involved, but this denormalization
  --    elimnates the need for some expensive outer joins).
  --
  --    This proc is registered as a callback whenever subplace_rels.new or 
  --    subplace_rels.delete is used to set or clear an address's superplace.
  --    (It is "registered" by setting place_types.callback_set_superplace).
  -- 
  --    @author Oumi Mehrotra (oumi@arsdigita.com)
  --    @creation-date 2001-03-19
  -- 
  --*/
  address_id            in pl_addresses.address_id%TYPE default null
 );

end pl_address;
/
show errors




create or replace package body pl_address
as

 function new (
  address_id		in pl_addresses.address_id%TYPE default null,
  object_type		in acs_objects.object_type%TYPE
			   default 'pl_address',
  creation_date		in acs_objects.creation_date%TYPE
			   default sysdate,
  creation_user		in acs_objects.creation_user%TYPE
			   default null,
  creation_ip		in acs_objects.creation_ip%TYPE
			   default null,
  context_id		in acs_objects.context_id%TYPE
			   default null,
  latitude		in places.latitude%TYPE
			   default null,
  longitude		in places.longitude%TYPE
			   default null,
  address_name		in pl_addresses.address_name%TYPE
			   default null,
  line_one		in pl_addresses.line_one%TYPE
			   default null,
  line_two		in pl_addresses.line_two%TYPE
			   default null,
  line_three		in pl_addresses.line_three%TYPE
			   default null,
  line_four		in pl_addresses.line_four%TYPE
			   default null,
  municipality_name	in pl_addresses.municipality_name%TYPE
			   default null,
  postal_code		in pl_addresses.postal_code%TYPE
			   default null,
  superplace_id		in acs_rels.object_id_one%TYPE
			   default null,
  locatee_id		in acs_rels.object_id_two%TYPE
			default null,
  use_context		in location_rels.use_context%TYPE
			default null,
  before_rel_id		in location_rels.rel_id%TYPE
			default null
 ) return pl_addresses.address_id%TYPE
 is
  v_address_id pl_addresses.address_id%TYPE;
 begin
  v_address_id :=
   place.new(place_id => address_id,
	     object_type => object_type,
	     creation_date => creation_date,
	     creation_user => creation_user,
	     creation_ip => creation_ip,
	     context_id => context_id,
	     latitude => latitude,
	     longitude => longitude,
	     superplace_id => superplace_id,
	     locatee_id => locatee_id,
             use_context => use_context,
             before_rel_id => before_rel_id);

  insert into pl_addresses
   (address_id, address_name,
    line_one, line_two, line_three, line_four,
    municipality_name, postal_code)
  values
   (v_address_id, new.address_name,
    new.line_one, new.line_two, new.line_three, new.line_four,
    new.municipality_name, new.postal_code);

  return v_address_id;
 end new;

 procedure delete (
  address_id		in pl_addresses.address_id%TYPE
 )
 is
 begin

  place.delete(address_id);

 end delete;

 function name (
  address_id		in pl_addresses.address_id%TYPE
 ) return pl_addresses.address_name%TYPE
 is
  v_address_name pl_addresses.address_name%TYPE;
 begin

  select nvl(address_name,'Address '||address_id)
    into v_address_name
    from pl_addresses
   where address_id = name.address_id;
  return v_address_name;

 end name;

 function violation (
  postal_code_id	in pl_postal_codes.postal_code_id%TYPE default null,
  region_id		in pl_regions.region_id%TYPE default null,
  country_id		in pl_countries.country_id%TYPE default null
 ) return varchar2
 is
   complaint varchar2(4000);
 begin
   if country_id is not null and region_id is not null then
     complaint := subplace_rel.violation(place_id => country_id, subplace_id => region_id);
     if complaint is not null then
       return complaint;
     end if;
   end if;
   if region_id is not null and postal_code_id is not null then
     complaint := subplace_rel.violation(place_id => region_id, subplace_id => postal_code_id);
     if complaint is not null then
       return complaint;
     end if;
   end if;
   if country_id is not null and postal_code_id is not null then
     complaint := subplace_rel.violation(place_id => country_id, subplace_id => postal_code_id);
     if complaint is not null then
       return complaint;
     end if;
   end if;
   return null;
 end violation;

 function smallest_superplace_id (
  postal_code_id	in pl_addresses.postal_code%TYPE default null,
  region_id		in pl_regions.region_id%TYPE default null,
  country_id		in pl_countries.iso%TYPE default null
 ) return acs_rels.object_id_one%TYPE
 is
   superplace_id		acs_rels.object_id_one%TYPE;
   invalid_place_hierarchy	exception;
 begin

   if pl_address.violation(postal_code_id => postal_code_id,
		           region_id => region_id,
		           country_id => country_id) is not null then
     raise invalid_place_hierarchy;
   end if;

   select nvl(postal_code_id,
	      nvl(region_id,
		  country_id))
     into superplace_id
     from dual;
   return superplace_id;
 exception
   when invalid_place_hierarchy then
     raise_application_error (-20001,
       'Place hierarchy is invalid.');
 end smallest_superplace_id;



 function new_intl (
  address_id		in pl_addresses.address_id%TYPE default null,
  object_type		in acs_objects.object_type%TYPE
			   default 'address',
  creation_date		in acs_objects.creation_date%TYPE
			   default sysdate,
  creation_user		in acs_objects.creation_user%TYPE
			   default null,
  creation_ip		in acs_objects.creation_ip%TYPE
			   default null,
  context_id		in acs_objects.context_id%TYPE
			   default null,
  latitude		in places.latitude%TYPE
			   default null,
  longitude		in places.longitude%TYPE
			   default null,
  address_name		in pl_addresses.address_name%TYPE
			   default null,
  line_one		in pl_addresses.line_one%TYPE
			   default null,
  line_two		in pl_addresses.line_two%TYPE
			   default null,
  line_three		in pl_addresses.line_three%TYPE
			   default null,
  line_four		in pl_addresses.line_four%TYPE
			   default null,
  municipality_name	in pl_addresses.municipality_name%TYPE
			   default null,
  postal_code		in pl_addresses.postal_code%TYPE
			   default null,
  postal_code_id	in pl_postal_codes.postal_code_id%TYPE
			   default null,
  region_id		in pl_regions.region_id%TYPE
			   default null,
  country_id		in pl_countries.country_id%TYPE
			   default null,
  locatee_id		in acs_rels.object_id_two%TYPE
			default null,
  use_context		in location_rels.use_context%TYPE
			default null,
  before_rel_id		in location_rels.rel_id%TYPE
			default null
 ) return pl_addresses.address_id%TYPE
 is
   v_address_id pl_addresses.address_id%TYPE;
   multiple_postal_code_types exception;
 begin
  if postal_code is not null and postal_code_id is not null then
    raise multiple_postal_code_types;
  end if;
  v_address_id :=
   pl_address.new(address_id => address_id,
                  object_type => object_type,
	          creation_date => creation_date,
	          creation_user => creation_user,
	          creation_ip => creation_ip,
	          context_id => context_id,
	          latitude => latitude,
	          address_name => address_name,
	          line_one => line_one,
	          line_two => line_two,
	          line_three => line_three,
	          line_four => line_four,
	          municipality_name => municipality_name,
	          postal_code => postal_code,
	          superplace_id => pl_address.smallest_superplace_id(postal_code_id => postal_code_id,
                                                                          region_id => region_id,
                                                                         country_id => country_id),
                  locatee_id => locatee_id,
                  use_context => use_context,
	          before_rel_id => before_rel_id);

   return v_address_id;
 exception
   when multiple_postal_code_types then
     raise_application_error (-20002,
       'Values for were provided for both the free-text postal_code and the referenced postal_code_id.');
 end new_intl;


 procedure update_intl (
  address_id		in pl_addresses.address_id%TYPE,
  creation_user		in acs_objects.creation_user%TYPE
			   default null,
  creation_ip		in acs_objects.creation_ip%TYPE
			   default null,
  address_name		in pl_addresses.address_name%TYPE default null,
  line_one		in pl_addresses.line_one%TYPE default null,
  line_two		in pl_addresses.line_two%TYPE default null,
  line_three		in pl_addresses.line_three%TYPE default null,
  line_four		in pl_addresses.line_four%TYPE default null,
  municipality_name	in pl_addresses.municipality_name%TYPE default null,
  postal_code		in pl_addresses.postal_code%TYPE default null,
  postal_code_id	in pl_postal_codes.postal_code_id%TYPE default null,
  region_id		in pl_regions.region_id%TYPE default null,
  country_id		in pl_countries.country_id%TYPE default null
 )
 is
  v_rel_id subplace_rels.rel_id%TYPE;
  place_id_old place_subplace_map.place_id%TYPE;
  cursor place_id_c is
   select place_id
     from place_subplace_map psm
    where subplace_id = update_intl.address_id
      and container_id = place_id;
  multiple_postal_code_types exception;
 begin
   if postal_code is not null and postal_code_id is not null then
     raise multiple_postal_code_types;
   end if;
   update pl_addresses set address_name = update_intl.address_name,
			line_one = update_intl.line_one,
			line_two = update_intl.line_two,
			line_three = update_intl.line_three,
			line_four = update_intl.line_four,
			municipality_name = update_intl.municipality_name,
			postal_code = update_intl.postal_code
	          where address_id = update_intl.address_id;
  
   open place_id_c;
   fetch place_id_c into place_id_old;
   if place_id_c%NOTFOUND then
     place_id_old := null;
   end if;
   close place_id_c;

  -- This always recreates the relation regardless of whether
  -- the subplace has changed since last time.
  -- This behavior could be changed later if required.  Nothing
  -- should be keying off of the subplace_rels table, though
  -- so it should be fine.  If somebody tries to move a subtype
  -- of subplace_rel, then it'll act unexpectedly, but that's
  -- only 'cause they'd be doing something unexpected and should
  -- probably implement their own move function.

  v_rel_id :=
   subplace_rel.move(subplace_id => address_id,
		     place_id_old => place_id_old,
		     place_id_new => smallest_superplace_id(postal_code_id => postal_code_id,
							    region_id => region_id,
							    country_id => country_id));
 exception
   when multiple_postal_code_types then
     raise_application_error (-20002,
       'Values for were provided for both the free-text postal_code and the referenced postal_code_id.');
 end update_intl;


 procedure denormalize_superplaces (
  --/** 
  --    Denormalizes region_id, country_id, and postal_code_id into the
  --    pl_address_superplace_index table (used to optimize views that
  --    need to get all information about an address in one query -- Oracle
  --    really hated all the outer joins involved, but this denormalization
  --    elimnates the need for some expensive outer joins).
  --
  --    This proc is registered as a callback whenever subplace_rels.new or 
  --    subplace_rels.delete is used to set or clear an address's superplace.
  --    (It is "registered" by setting place_types.callback_set_superplace).
  -- 
  --    @author Oumi Mehrotra (oumi@arsdigita.com)
  --    @creation-date 2001-03-19
  -- 
  --*/
  address_id            in pl_addresses.address_id%TYPE default null
 )
 is
   v_address_exists_p integer;
   v_region_id      pl_regions.region_id%TYPE;
   v_country_id     pl_countries.country_id%TYPE;
   v_postal_code_id pl_postal_codes.postal_code_id%TYPE;
 begin

       select rg.region_id, cn.country_id, pc.postal_code_id
         into v_region_id, v_country_id, v_postal_code_id
         from places p,
	      (select rgi.region_id,
		      psm.subplace_id
	         from pl_regions rgi,
		      place_subplace_map psm
	        where rgi.region_id = psm.place_id) rg,
              (select cni.country_id,
	              psm.subplace_id
                 from pl_countries cni,
                      place_subplace_map psm
                where cni.country_id = psm.place_id) cn,
	      (select pci.postal_code_id,
		      psm.subplace_id
	         from pl_postal_codes pci,
		      place_subplace_map psm
	        where pci.postal_code_id = psm.place_id) pc
        where p.place_id = rg.subplace_id(+)
          and p.place_id = cn.subplace_id(+)
          and p.place_id = pc.subplace_id(+)
          and p.place_id = denormalize_superplaces.address_id;

      update pl_address_superplace_index set
        region_id = v_region_id,
        country_id = v_country_id,
        postal_code_id = v_postal_code_id
      where address_id = denormalize_superplaces.address_id;

      if SQL%NOTFOUND then
	    insert into pl_address_superplace_index
            (address_id, region_id, country_id, postal_code_id)
            values
            (address_id, v_region_id, v_country_id, v_postal_code_id);
      end if;


 end denormalize_superplaces;

end pl_address;
/
show errors

----------------------------
-- ADDRESS INSERT TRIGGER --
----------------------------

-- trigger on insert to populate the denormalization table
create or replace trigger pl_addresses_in_tr
after insert on pl_addresses
for each row
declare
  denorm_exists_p integer;
begin

  select count(*) into denorm_exists_p
  from pl_address_superplace_index 
  where address_id = :new.address_id;

  if denorm_exists_p = 0 then
      insert into pl_address_superplace_index
      (address_id, region_id, country_id, postal_code_id)
      values
      (:new.address_id, null, null, null);
  end if;

end pl_addresses_in_tr;
/
show errors

-------------------------
-- PLACE TYPE CALLBACK --
-------------------------

-- set up the address callback for maintainind pl_address_superplace_index
insert into place_types
(place_type, callback_set_superplace)
values
('pl_address', 'pl_address.denormalize_superplaces');

commit;


-------------------
-- ADDRESS VIEWS --
-------------------

create or replace view pl_address_superplace_map as
select address_id, region_id, country_id, postal_code_id
from pl_address_superplace_index;

-- This view contains all info in pl_addresses and 
-- pl_address_superplace_map (the denormalized extension of pl_addresses),
-- and also the postal_code.  It is a lighter weight version of 
-- pl_addresses_complete; this view doesn't look up region_name/abbrev or
-- country_name/iso, because that information can be cached in the 
-- application layer.  It includes postal_code, because postal_codes should
-- not be cached in the application layer.
create or replace view pl_addresses_info as
  select ad.address_id,
	 ad.address_name,
	 ad.line_one,
	 ad.line_two,
	 ad.line_three,
	 ad.line_four,
	 ad.municipality_name,
	 nvl(pc.postal_code, ad.postal_code) as postal_code,
         ad.postal_code as free_postal_code,
         asm.postal_code_id,
	 asm.region_id,
	 asm.country_id
    from pl_addresses ad,
         pl_address_superplace_map asm,
         pl_postal_codes pc
   where ad.address_id = asm.address_id
     and asm.postal_code_id = pc.postal_code_id(+);

create or replace view pl_addresses_complete as
  select ai.address_id,
	 ai.address_name,
	 ai.line_one,
	 ai.line_two,
	 ai.line_three,
	 ai.line_four,
	 ai.municipality_name,
	 ai.region_id,
	 ai.country_id,
         ai.postal_code,
         ai.free_postal_code,
         ai.postal_code_id,
	 rg.region_name,
         rg.region_ps_abbrev,
	 cn.country_name,
         cn.iso as country_iso
    from pl_addresses_info ai,
         (select r.region_id, r.region_name, us.usps_abbrev as region_ps_abbrev
          from pl_regions r,
               pl_us_states us
          where r.region_id = us.state_id(+)) rg,
         pl_countries cn
   where ai.region_id = rg.region_id(+)
     and ai.country_id = cn.country_id(+);

create or replace view pl_addresses_located as
  select ac.*,
	 plm.rel_id,
	 plm.locatee_id,
	 plm.use_context,
	 plm.sort_key
    from pl_addresses_info ac,
	 place_locatee_map_complete plm
   where ac.address_id = plm.place_id;

