DROP TABLE joined_legacy;
DROP TABLE legacy_copy_status_map;

BEGIN;

-- Build the status map ... by hand, which suxorz.
CREATE TABLE legacy_copy_status_map (id int, name text);
COPY legacy_copy_status_map FROM STDIN;
0	ADULT
0	AV
0	AWARDBOOKS
0	BHDESK
2	BINDERY
0	BIOGRAPHY
0	BOOKMOBILE
0	BROWSING
11	CATALOGING
1	CHECKEDOUT
0	DATALOAD
13	DISCARD
0	DISPLAY
0	EASY
0	EASY-RDR
0	FANTASY
0	FIC
0	FIXLIB
0	FOREIGNL
0	GA-CIRC
0	GENEALOGY
0	GEORGIA
0	GOV-DOC
8	HOLDS
10	ILL
10	ILS-ILL
0	INDEX
5	INPROCESS
6	INTRANSIT
0	JUV
0	LEASE
0	LG-PRNT
0	LIB-CLOSED
0	LITERACY
0	LONGOVRDUE
3	LOST
3	LOST-PAID
0	MAG
0	MAPS
4	MISSING
0	MYSTERY
0	NEW-BKS
0	NEWS
0	NONFIC
0	OFFICE
9	ON-ORDER
10	ONLOAN
0	OVERSIZED
0	PBK
0	PICTURE
0	REF
11	REPAIR
0	RESERVES
7	RESHELVING
0	ROTATING
0	SCIFI
0	SHORTSTORY
0	SPEC-COL-R
0	SPEC-COLL
0	SPECNEEDS
0	STACKS
0	STATELIBGA
0	STORAGE
0	THRILLER
0	TODDLER
0	UNAVAILBLE
0	UNKNOWN
0	VRTICLFILE
0	WEBSITE
0	WESTERN
0	YA
\.


-- First, we build shelving location
INSERT INTO asset.copy_location (name, owning_lib)
	SELECT	DISTINCT l.home_location, ou.id
	  FROM	legacy_item l JOIN actor.org_unit ou
	  		ON (l.owning_library = ou.shortname);


-- Now set their flags
UPDATE	asset.copy_location
  SET	holdable = FALSE
  WHERE	name IN ('BINDERY','DISCARD','GENEALOGY','GOV-DOC','INDEX',
		 'LIB-CLOSED','LONGOVERDUE','LOST','LOST-PAID','MAG',
		 'NEWS','ONLOAN','REF','REPAIR','SPEC-COL-R');

UPDATE	asset.copy_location
  SET	opac_visible = FALSE
  WHERE	name IN ('DATALOA','DISCARD','FIXLIB','LIB-CLOSED', 'LONGOVERDUE',
		 'LOST','LOST-PAID','STORAGE', 'UNKNOWN');


-- Now the old stat-cat stuff
INSERT INTO asset.stat_cat (owner, name) VALUES (1, 'Legacy CAT1');
INSERT INTO asset.stat_cat_entry (stat_cat, owner, value)
	SELECT	DISTINCT currval('asset.stat_cat_id_seq'::regclass), 1, cat_1
	  FROM	legacy_item;

INSERT INTO asset.stat_cat (owner, name) VALUES (1, 'Legacy CAT2');
INSERT INTO asset.stat_cat_entry (stat_cat, owner, value)
	SELECT	DISTINCT currval('asset.stat_cat_id_seq'::regclass), 1, cat_2
	  FROM	legacy_item;


-- Create a temp table to speed up CN and copy inserts
CREATE TABLE joined_legacy AS
	SELECT	i.*, c.call_num
	  FROM	legacy_item i
		JOIN legacy_callnum c USING (cat_key,call_key);

CREATE INDEX lj_cat_call_idx ON joined_legacy (cat_key,call_key);

-- Import the call numbers
-- Getting the owning lib from the first available copy on the CN
INSERT INTO asset.call_number (creator,editor,record,label,owning_lib)
	SELECT	DISTINCT 1, 1, l.cat_key , l.call_num, ou.id
	  FROM	joined_legacy l
		JOIN biblio.record_entry b ON (cat_key = b.id)
		JOIN actor.org_unit ou ON (l.owning_library = ou.shortname);



-- Import base copy data
INSERT INTO asset.copy (circ_lib,creator,editor,create_date,barcode,status,location,loan_duration,fine_level,opac_visible,price,circ_modifier,call_number, alert_message)
	SELECT	DISTINCT ou.id AS circ_lib,
		1 AS creator,
		1 AS editor,
		l.creation_date AS create_date,
		l.item_id AS barcode,
		s_map.id AS status,
		cl.id AS location,
		2 AS loan_duration,
		2 AS fine_level,
		CASE WHEN l.shadow IS TRUE THEN FALSE ELSE TRUE END AS opac_visible,
		(l.price/100::numeric)::numeric(8,2) AS price,
		l.item_type AS circ_modifier,
		cn.id AS call_number,
		pc.cnt || ' pieces' as alert_message
	  FROM	joined_legacy l
		JOIN legacy_copy_status_map s_map
			ON (s_map.name = l.current_location)
		JOIN actor.org_unit ou
			ON (l.owning_library = ou.shortname)
		JOIN asset.copy_location cl
			ON (ou.id = cl.owning_lib AND l.home_location = cl.name)
		JOIN asset.call_number cn
			ON (ou.id = cn.owning_lib AND l.cat_key = cn.record AND l.call_num = cn.label)
		LEFT JOIN legacy_piece_count pc ON (pc.barcode = l.item_id);

-- Import brief copies
INSERT INTO asset.copy (circ_lib,creator,editor,barcode,status,loan_duration,fine_level,dummy_title,dummy_author,opac_visible,circ_modifier,call_number)
	SELECT	DISTINCT ou.id AS circ_lib,
		1 AS creator,
		1 AS editor,
		b.barcode AS barcode,
		1 AS status,
		2 AS loan_duration,
		2 AS fine_level,
		b.title AS dummy_title,
		b.author AS dummy_author,
		FALSE as opac_visible,
		'BOOK' AS circ_modifier,
		-1 AS call_number
	  FROM	legacy_pre_cat b
	  	JOIN actor.org_unit ou ON (ou.shortname = b.lib);

-- Move copy notes into the notes table ... non-public
INSERT INTO asset.copy_note (owning_copy,creator,title,value)
	SELECT	cp.id,
		1,
		'Legacy Note',
		l.item_comment
	  FROM	legacy_item l
		JOIN asset.copy cp ON (cp.barcode = l.item_id)
	  WHERE	l.item_comment IS NOT NULL
		AND l.item_comment <> '';

-- Now do stat cats
INSERT INTO asset.stat_cat_entry_copy_map (stat_cat, stat_cat_entry, owning_copy)
	SELECT	sc.id,
		sce.id,
		cp.id
	  FROM	joined_legacy l
	  	JOIN asset.copy cp ON (cp.barcode = l.item_id)
		JOIN asset.stat_cat_entry sce ON (sce.value = l.cat_1)
		JOIN asset.stat_cat sc ON (sce.stat_cat = sc.id)
	  WHERE	sc.name = 'Legacy CAT1';

INSERT INTO asset.stat_cat_entry_copy_map (stat_cat, stat_cat_entry, owning_copy)
	SELECT	sc.id,
		sce.id,
		cp.id
	  FROM	joined_legacy l
	  	JOIN asset.copy cp ON (cp.barcode = l.item_id)
		JOIN asset.stat_cat_entry sce ON (sce.value = l.cat_2)
		JOIN asset.stat_cat sc ON (sce.stat_cat = sc.id)
	  WHERE	sc.name = 'Legacy CAT2';


COMMIT;

