ALTER DATABASE pa SET TIMEZONE TO 'Australia/Victoria'; 

CREATE SEQUENCE pa_user_id_seq;
CREATE SEQUENCE pa_user_state_id_seq;
CREATE SEQUENCE face_id_seq;
CREATE SEQUENCE path_id_seq;
CREATE SEQUENCE path_type_id_seq;
CREATE SEQUENCE file_id_seq;
CREATE SEQUENCE file_type_id_seq;
CREATE SEQUENCE jobextra_id_seq;
CREATE SEQUENCE joblog_id_seq;
CREATE SEQUENCE job_id_seq;
CREATE SEQUENCE person_id_seq;
CREATE SEQUENCE refimg_id_seq;
CREATE SEQUENCE settings_id_seq;
CREATE SEQUENCE pa_job_manager_id_seq;
CREATE SEQUENCE pa_job_manager_fe_message_id_seq;
CREATE SEQUENCE face_override_type_id_seq;
CREATE SEQUENCE face_override_id_seq;
CREATE SEQUENCE query_id_seq;

-- these are hard-coded at present, not sure I can reflexively find models from API?
CREATE TABLE ai_model ( id INTEGER, name VARCHAR(24), description VARCHAR(80), CONSTRAINT pk_ai_model PRIMARY KEY(id) );
INSERT INTO ai_model VALUES ( 1, 'hog', 'normal' );
INSERT INTO ai_model VALUES ( 2, 'cnn', 'more accurate / much slower' );

CREATE TABLE settings( 
    id INTEGER, 
	base_path VARCHAR, import_path VARCHAR, storage_path VARCHAR, recycle_bin_path VARCHAR, metadata_path VARCHAR, 
	auto_rotate BOOLEAN,
    default_refimg_model INTEGER, default_scan_model INTEGER, default_threshold FLOAT,
    face_size_limit INTEGER,
    scheduled_import_scan INTEGER, scheduled_storage_scan INTEGER, 
    scheduled_bin_cleanup INTEGER, bin_cleanup_file_age INTEGER,
    job_archive_age INTEGER,
    CONSTRAINT pk_settings_id PRIMARY KEY(id),
    CONSTRAINT fk_default_refimg_model FOREIGN KEY (default_refimg_model) REFERENCES ai_model(id),
    CONSTRAINT fk_default_scan_model FOREIGN KEY (default_scan_model) REFERENCES ai_model(id) );

CREATE TABLE pa_user( 
    id INTEGER, 
    dn VARCHAR UNIQUE, 
    default_import_noo VARCHAR,
    default_storage_noo VARCHAR,
    default_search_noo VARCHAR,
    default_grouping VARCHAR(16),
    default_how_many INTEGER,
    default_size INTEGER,
    default_import_folders BOOLEAN,
    default_storage_folders BOOLEAN,
    CONSTRAINT pk_pa_user_id PRIMARY KEY(id) );

-- FIXME: NEED TO RETHINK THIS, not sure this even needs to be in the DB
CREATE TABLE pa_user_state ( id INTEGER, pa_user_dn VARCHAR(128), path_type VARCHAR(16), 
    noo VARCHAR(16), grouping VARCHAR(16), how_many INTEGER, size INTEGER, folders BOOLEAN,
    root VARCHAR, cwd VARCHAR,
    CONSTRAINT fk_pa_user_dn FOREIGN KEY (pa_user_dn) REFERENCES pa_user(dn),
    CONSTRAINT pk_pa_user_states_id PRIMARY KEY(id ) );

CREATE TABLE query ( id INTEGER, path_type VARCHAR(16), noo VARCHAR(16), grouping VARCHAR(16), q_offset INTEGER,
    entry_list VARCHAR, folders BOOLEAN, root VARCHAR, cwd VARCHAR, search_term VARCHAR, current INTEGER,
    created TIMESTAMPTZ,
    CONSTRAINT pk_query_id PRIMARY KEY(id ) );


CREATE TABLE file_type ( id INTEGER, name VARCHAR(32) UNIQUE, CONSTRAINT pk_file_type_id PRIMARY KEY(id) );

CREATE TABLE path_type ( id INTEGER, name VARCHAR(16) UNIQUE, CONSTRAINT pk_path_type_id PRIMARY KEY(id) );

CREATE TABLE path ( id INTEGER, type_id INTEGER, path_prefix VARCHAR(1024), num_files INTEGER,
    CONSTRAINT pk_path_id PRIMARY KEY(id),
    CONSTRAINT fk_path_type_type_id FOREIGN KEY (type_id) REFERENCES path_type(id) );

CREATE TABLE entry( id INTEGER, name VARCHAR(128), type_id INTEGER, exists_on_fs BOOLEAN,
    CONSTRAINT pk_entry_id PRIMARY KEY(id),
    CONSTRAINT fk_file_type_type_id FOREIGN KEY (type_id) REFERENCES file_type(id) );

CREATE TABLE file ( eid INTEGER, size_mb INTEGER, hash VARCHAR(34), thumbnail VARCHAR, faces_created_on FLOAT, last_hash_date FLOAT, last_ai_scan FLOAT, year INTEGER, month INTEGER, day INTEGER, woy INTEGER,
    CONSTRAINT pk_file_id PRIMARY KEY(eid), 
    CONSTRAINT fk_file_entry_id FOREIGN KEY (eid) REFERENCES entry(id) );

CREATE TABLE del_file ( file_eid INTEGER, orig_path_prefix VARCHAR(1024), CONSTRAINT pk_del_file_file_eid PRIMARY KEY (file_eid),
    CONSTRAINT fk_entry_id FOREIGN KEY (file_eid) REFERENCES file(eid) );

CREATE TABLE dir ( eid INTEGER, rel_path VARCHAR(256), num_files INTEGER, last_import_date FLOAT, 
    CONSTRAINT pk_dir_eid PRIMARY KEY(eid),
    CONSTRAINT fk_dir_entry_id FOREIGN KEY (eid) REFERENCES entry(id) );

CREATE TABLE path_dir_link ( PATH_ID INTEGER, DIR_EID INTEGER,
    CONSTRAINT pk_pdl_PATH_ID_DIR_EID PRIMARY KEY (PATH_ID, DIR_EID),
    CONSTRAINT fk_pdl_path_id FOREIGN KEY (path_id) REFERENCES path(id),
    CONSTRAINT fk_pdl_dir_eid FOREIGN KEY (dir_eid) REFERENCES dir(eid) );

CREATE TABLE entry_dir_link ( ENTRY_ID INTEGER, DIR_EID INTEGER,
    CONSTRAINT pk_edl_ENTRY_ID_DIR_EID PRIMARY KEY (ENTRY_ID, DIR_EID),
    CONSTRAINT fk_edl_entry_id FOREIGN KEY (entry_id) REFERENCES entry(id),
    CONSTRAINT fk_edl_dir_eid FOREIGN KEY (dir_eid) REFERENCES dir(eid) );

CREATE TABLE person ( id INTEGER DEFAULT NEXTVAL('person_id_seq'), tag VARCHAR(48), firstname VARCHAR(48), surname VARCHAR(48),
    CONSTRAINT pk_person_id PRIMARY KEY(id) );
ALTER SEQUENCE person_id_seq OWNED BY person.id;


CREATE TABLE refimg ( id INTEGER, fname VARCHAR(128), face BYTEA, orig_w INTEGER, orig_h INTEGER,
    face_top INTEGER, face_right INTEGER, face_bottom INTEGER, face_left INTEGER, created_on FLOAT, thumbnail VARCHAR, model_used INTEGER, 
    CONSTRAINT pk_refimg_id PRIMARY KEY(id),
    CONSTRAINT fk_refimg_model_used FOREIGN KEY (model_used) REFERENCES ai_model(id) );
ALTER SEQUENCE refimg_id_seq OWNED BY refimg.id;

CREATE TABLE face( id INTEGER, face BYTEA, face_top INTEGER, face_right INTEGER, face_bottom INTEGER, face_left INTEGER, 
    w INTEGER, h INTEGER, CONSTRAINT pk_face_id PRIMARY KEY(id) );

CREATE TABLE face_file_link( face_id INTEGER, file_eid INTEGER, model_used INTEGER,
    CONSTRAINT pk_ffl_face_id_file_id PRIMARY KEY(face_id, file_eid),
    CONSTRAINT fk_ffl_face_id FOREIGN KEY (face_id) REFERENCES face(id) ON DELETE CASCADE,
    CONSTRAINT fk_ffl_file_eid FOREIGN KEY (file_eid) REFERENCES file(eid),
    CONSTRAINT fk_ffl_model_used FOREIGN KEY (model_used) REFERENCES ai_model(id) );

CREATE TABLE face_refimg_link( face_id INTEGER, refimg_id INTEGER, face_distance FLOAT,
    CONSTRAINT pk_frl_face_id_refimg_id PRIMARY KEY(face_id, refimg_id), 
    CONSTRAINT fk_frl_face_id FOREIGN KEY (face_id) REFERENCES face(id) ON DELETE CASCADE,
    CONSTRAINT fk_frl_refimg_id FOREIGN KEY (refimg_id) REFERENCES refimg(id) );

CREATE TABLE face_override_type ( id INTEGER, name VARCHAR UNIQUE, CONSTRAINT pk_face_override_type_id PRIMARY KEY(id) );
INSERT INTO face_override_type VALUES ( (SELECT NEXTVAL('face_override_type_id_seq')), 'Manual match to existing person' );
INSERT INTO face_override_type VALUES ( (SELECT NEXTVAL('face_override_type_id_seq')), 'Not a face' );
INSERT INTO face_override_type VALUES ( (SELECT NEXTVAL('face_override_type_id_seq')), 'Too young' );
INSERT INTO face_override_type VALUES ( (SELECT NEXTVAL('face_override_type_id_seq')), 'Ignore face' );

-- keep non-redundant FACE because, when we rebuild data we may have a null FACE_ID, but still want to connect to this override
-- from a previous AI pass... (would happen if we delete a file and then reimport/scan it), OR, more likely we change (say) a threshold, etc.
-- any reordering of faces, generates new face_ids... (but if the face data was the same, then this override should stand)
CREATE TABLE face_no_match_override ( id INTEGER, face_id INTEGER, type_id INTEGER,
    CONSTRAINT fk_fnmo_face_id FOREIGN KEY (face_id) REFERENCES face(id),
    CONSTRAINT fk_fnmo_type FOREIGN KEY (type_id) REFERENCES face_override_type(id),
    CONSTRAINT pk_fnmo_id PRIMARY KEY(id) );

-- manual match goes to person not refimg, so on search, etc. we deal with this anomaly (via sql not ORM)
CREATE TABLE face_force_match_override ( id INTEGER, face_id INTEGER, person_id INTEGER, CONSTRAINT pk_face_force_match_override_id PRIMARY KEY(id) );

CREATE TABLE disconnected_no_match_override ( face BYTEA, type_id INTEGER, 
	CONSTRAINT fk_dnmo_type_id FOREIGN KEY (type_id) REFERENCES face_override_type(id),
	CONSTRAINT pk_dnmo_face PRIMARY KEY (face) );

CREATE TABLE disconnected_force_match_override ( face BYTEA, person_id INTEGER, 
	CONSTRAINT fk_dfmo_person_id FOREIGN KEY (person_id) REFERENCES person(id),
	CONSTRAINT pk_dfmo_face PRIMARY KEY (face) );

CREATE TABLE person_refimg_link ( person_id INTEGER, refimg_id INTEGER,
    CONSTRAINT pk_prl PRIMARY KEY(person_id, refimg_id), 
    CONSTRAINT fk_prl_person_id FOREIGN KEY (person_id) REFERENCES person(id), 
    CONSTRAINT fk_prl_refimg_id FOREIGN KEY (refimg_id) REFERENCES refimg(id), 
    CONSTRAINT u_prl_refimg_id UNIQUE(refimg_id) );

CREATE TABLE job ( 
    id INTEGER, start_time TIMESTAMPTZ, last_update TIMESTAMPTZ, name VARCHAR(64), state VARCHAR(128), 
    num_files INTEGER, current_file_num INTEGER, current_file VARCHAR(256), wait_for INTEGER, pa_job_state VARCHAR(48),
    CONSTRAINT pk_job_id PRIMARY KEY(id) );

-- used to pass / keep extra values, e.g. num_files for jobs that have sets of files, or out* for adding output from jobs that you want to pass to next job in the chain
CREATE TABLE jobextra ( id INTEGER, job_id INTEGER, name VARCHAR(32), value VARCHAR,
    CONSTRAINT pk_jobextra_id PRIMARY KEY(id), CONSTRAINT fk_jobextra_job_id FOREIGN KEY(job_id) REFERENCES job(id) );

CREATE TABLE joblog ( id INTEGER, job_id INTEGER, log_date TIMESTAMPTZ, log VARCHAR, 
    CONSTRAINT pk_jl_id PRIMARY KEY(id), CONSTRAINT fk_jl_job_id FOREIGN KEY(job_id) REFERENCES job(id) );

CREATE TABLE pa_job_manager_fe_message ( id INTEGER, job_id INTEGER, level VARCHAR(16), message VARCHAR(8192), persistent BOOLEAN, cant_close BOOLEAN,
    CONSTRAINT pk_pa_job_manager_fe_acks_id PRIMARY KEY(id),
    CONSTRAINT fk_pa_job_manager_fe_message_job_id FOREIGN KEY(job_id) REFERENCES job(id) );

CREATE TABLE amendment_type ( id INTEGER, job_name VARCHAR(64), which VARCHAR(8), what VARCHAR(32), colour VARCHAR(32),
    CONSTRAINT pk_amendment_type_id PRIMARY KEY(id) );
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 1, 'delete_files', 'icon', 'trash', 'var(--bs-danger)' );
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 2, 'restore_files', 'icon', 'trash', 'var(--bs-success)' );
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 3, 'transform_image:90', 'img', 'rot90.png', '#009EFF' );
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 4, 'transform_image:180', 'img', 'rot180.png', '#009EFF' );
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 5, 'transform_image:270', 'img', 'rot270.png', '#009EFF' );
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 6, 'transform_image:fliph', 'icon', 'flip_h', '#009EFF' );
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 7, 'transform_image:flipv', 'icon', 'flip_v', '#009EFF' );
INSERT INTO amendment_type ( id, job_name, which, what, colour ) VALUES ( 8, 'move_files', 'icon', 'folder_plus', 'var(--bs-primary)' );

CREATE TABLE entry_amendment ( amend_type INTEGER, eid INTEGER, job_id INTEGER,
    CONSTRAINT pk_entry_amendment_eid_job_id PRIMARY KEY(eid,job_id),
    CONSTRAINT fk_entry_amendment_amendment_type FOREIGN KEY(amend_type) REFERENCES amendment_type(id),
    CONSTRAINT fk_entry_amendment_job_id FOREIGN KEY(job_id) REFERENCES job(id) );

-- default data for types of paths
INSERT INTO path_type VALUES ( (SELECT NEXTVAL('path_type_id_seq')), 'Import' );
INSERT INTO path_type VALUES ( (SELECT NEXTVAL('path_type_id_seq')), 'Storage' );
INSERT INTO path_type VALUES ( (SELECT NEXTVAL('path_type_id_seq')), 'Bin' );
INSERT INTO path_type VALUES ( (SELECT NEXTVAL('path_type_id_seq')), 'Metadata' );

-- default data for types of files
INSERT INTO file_type VALUES ( (SELECT NEXTVAL('file_type_id_seq')), 'Image' );
INSERT INTO file_type VALUES ( (SELECT NEXTVAL('file_type_id_seq')), 'Video' );
INSERT INTO file_type VALUES ( (SELECT NEXTVAL('file_type_id_seq')), 'Directory' );
INSERT INTO file_type VALUES ( (SELECT NEXTVAL('file_type_id_seq')), 'Unknown' );

-- fake data only for making testing easier
--INSERT INTO person VALUES ( (SELECT NEXTVAL('person_id_seq')), 'dad', 'Damien', 'De Paoli' );
--INSERT INTO person VALUES ( (SELECT NEXTVAL('person_id_seq')), 'mum', 'Mandy', 'De Paoli' );
--INSERT INTO person VALUES ( (SELECT NEXTVAL('person_id_seq')), 'cam', 'Cameron', 'De Paoli' );
--INSERT INTO person VALUES ( (SELECT NEXTVAL('person_id_seq')), 'mich', 'Michelle', 'De Paoli' );
-- DEV(ddp):
INSERT INTO settings ( id, base_path, import_path, storage_path, recycle_bin_path, metadata_path, auto_rotate, default_refimg_model, default_scan_model, default_threshold, face_size_limit, scheduled_import_scan, scheduled_storage_scan, scheduled_bin_cleanup, bin_cleanup_file_age, job_archive_age ) VALUES ( (SELECT NEXTVAL('settings_id_seq')), '/home/ddp/src/photoassistant/', 'images_to_process/', 'photos/', '.pa_bin/', '.pa_metadata/', true, 1, 1, '0.55', 43, 1, 1, 7, 30, 3 );
-- DEV(cam):
--INSERT INTO settings ( id, base_path, import_path, storage_path, recycle_bin_path, metadata_path, auto_rotate, default_refimg_model, default_scan_model, default_threshold, face_size_limit, scheduled_import_scan, scheduled_storage_scan, scheduled_bin_cleanup, bin_cleanup_file_age, job_archive_age ) VALUES ( (select nextval('SETTINGS_ID_SEQ')), 'c:/Users/cam/Desktop/code/python/photoassistant/', 'c:\images_to_process', 'photos/', '.pa_bin/', '.pa_metadata/', TRUE, 1, 1, '0.55', 43, 1, 1, 7, 30, 3 );
-- PROD:
--INSERT INTO settings ( id, base_path, import_path, storage_path, recycle_bin_path, metadata_path, auto_rotate, default_refimg_model, default_scan_model, default_threshold, face_size_limit, scheduled_import_scan, scheduled_storage_scan, scheduled_bin_cleanup, bin_cleanup_file_age, job_archive_age ) VALUES ( (SELECT NEXTVAL('settings_id_seq')), '/export/docker/storage/', 'Camera_uploads/', 'photos/', '.pa_bin/', '.pa_metadata/', TRUE, 1, 1, '0.55', 43, 1, 1, 7, 30, 4 );