--
--  Copyright (C) 2001, 2002 MIT
--
--  this is free software; you can redistribute it and/or modify it under the
--  terms of the GNU General Public License as published by the Free Software
--  Foundation; either version 2 of the License, or (at your option) any later
--  version.
--
--  this is distributed in the hope that it will be useful, but WITHOUT ANY
--  WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
--  FOR A PARTICULAR PURPOSE.  See the GNU General Public License for more
--  details.
--

--
-- attachments
--
-- @author arjun (arjun@openforce.net)
-- @version $Id: attachments-create.sql,v 1.5 2017/08/07 23:48:04 gustafn Exp $
--

create table attachments_fs_root_folder_map (
    package_id                  integer
				constraint attach_fldr_map_package_id_fk
                                references apm_packages (package_id)
                                constraint attach_fldr_map_package_id_un
                                unique,
    folder_id                   integer 
				constraint attach_fldr_map_folder_id_fk
                                references fs_root_folders (folder_id),
    constraint                  attach_fldr_map_pk
                                primary key (package_id, folder_id)
);

-- RI Indexes
create index attachments_fsr_fm_folder_id_i ON attachments_fs_root_folder_map(folder_id);

create table attachments (
    object_id                   integer
				constraint attachments_object_id_fk
                                references acs_objects(object_id)
                                on delete cascade,
    item_id                     integer
				constraint attachments_item_id_fk
                                references acs_objects(object_id)
                                on delete cascade,
    approved_p                  boolean
                                default true
                                constraint attachments_approved_p_nn
                                not null,
    constraint                  attachments_pk
                                primary key (object_id, item_id)
);

-- RI Indexes 
create index attachments_item_id_idx ON attachments(item_id);