/**
* MySQL database structure creation table for Sahana
*/
                    /**drop tables **/



-- from GIS module

-- general tables

-- DROP TABLE IF EXISTS config;

-- drop tables from lib_uuid


-- DROP TABLE IF EXISTS loc_seq;



/**================= System and Config Tables =======================**/

/**
* The central table to store all configuration details of the base system
* and all modules
* Modules: all
* Last changed: 2-NOV-2005 - chamindra@opensource.lk 
*/
CREATE TABLE config(
    config_id BIGINT NOT NULL AUTO_INCREMENT,
    module_id VARCHAR(20), -- the directory name of the module e.g. dvr, or, mpr
	confkey VARCHAR(50) NOT NULL, -- the configuration key for the module
	value VARCHAR(100), -- the value 
    PRIMARY KEY(config_id),
	FOREIGN KEY (module_id) REFERENCES modules (module_id)
);

/**
* Field options meta table
* Give a custom list of options for each filed in this schema 
* prefixed with opt_. This is customizable then at deployment
* See the mysql-config.sql for default customizations
* Modules: dvr, mpr, or, cr, rms, gis ,cs
* Last changed: 27-OCT-2005 - chamindra@opensource.lk  
*/
CREATE TABLE field_options(
   field_name VARCHAR(100), -- a meta reference to the field_name
   option_code VARCHAR(20), -- a coded version of the value
   option_description VARCHAR(50) -- The nice name of the value 
);

/** 
 * Synchronization Related Tables
 * Modules : Framework, sync
 * Created : 22nd-Feb-2006 - janaka@opensource.lk
 * Last Change : 4th-Sep-2006  - jo@opensource.lk
 */

CREATE TABLE sync_instance (
    base_uuid VARCHAR(4) NOT NULL, -- Instance id
    owner VARCHAR(100), -- Instance owner's name
    contact TEXT, -- Contact details of the instance owner
    url VARCHAR(100) DEFAULT NULL, -- Server url if exists
    last_update TIMESTAMP NOT NULL, -- Last Time sync with the instance
    sync_count INT DEFAULT 0, -- Number of times synchronized
    PRIMARY KEY(base_uuid)
);


/**================= Security Tables ================================**/

/**
* Contains the Sahana system user details
* Modules: all
* Last changed: 27-OCT-2005 - ravindra@opensource.lk  
*/

/**
* The central table on a person, with their associated names
* Modules: dvr, mpr, rms, cr 
* Last changed: 27-OCT-2005 - chamindra@opensource.lk  
*/
CREATE TABLE person_uuid (
    p_uuid VARCHAR(60) NOT NULL, -- universally unique person id
    full_name VARCHAR(100), -- the full name (contains the family name)
    family_name VARCHAR(50), -- the family name
    l10n_name VARCHAR(100), -- localized version of name
    custom_name VARCHAR(50), -- extra name field as required
    PRIMARY KEY(p_uuid)      
);




alter table users add unique (user_name);


/**
* Contains the Sahana system user alternative login details
* Modules: all
* Last changed: 27-OCT-2005 - ravindra@opensource.lk  
*/
CREATE TABLE alt_logins (
    p_uuid VARCHAR(60) NOT NULL,  -- reference to the persons uuid
    user_name VARCHAR(100) NOT NULL,
    type VARCHAR(60) DEFAULT "openid",
    PRIMARY KEY (p_uuid),
    FOREIGN KEY (p_uuid) REFERENCES person_uuid(p_uuid)
);

/** 
* Contains the last three passwords of users,to block reusing of passwords 
* Modules: framework
* Last changed: 18-JUL-2007 - ravindra@opensource.lk
**/



/** 
* Contains the log of events(mainly login, password cracking attempts)
* Modules: framework
* Last changed: 18-JUL-2007 - ravindra@opensource.lk
**/

CREATE TABLE password_event_log (
    log_id BIGINT NOT NULL AUTO_INCREMENT,
    changed_timestamp BIGINT NOT NULL ,
    p_uuid VARCHAR(60) NOT NULL,  -- reference to the persons uuid
    user_name VARCHAR(100) NOT NULL,
    comment VARCHAR(100) NOT NULL,
    event_type INT DEFAULT 1,
    PRIMARY KEY (log_id),
    FOREIGN KEY (p_uuid) REFERENCES users(p_uuid)
);


/** 
* Contains the system user groups 
* Modules: framework
* Last changed: 2-OCT-2006 - chamindra@opensource.lk
**/
CREATE TABLE sys_user_groups (
    group_id INT NOT NULL,
    group_name VARCHAR(40) NOT NULL,
    PRIMARY KEY (group_id)
);

/** 
* Contains the mapping from a user to a system group
* Modules: framework
* Last changed: 2-OCT-2006 - chamindra@opensource.lk
**/
CREATE TABLE sys_user_to_group (
    group_id INT NOT NULL,
    p_uuid VARCHAR(60) NOT NULL,
    PRIMARY KEY(group_id,p_uuid),
    FOREIGN KEY (p_uuid) REFERENCES person_uuid(p_uuid),
    FOREIGN KEY (group_id) REFERENCES sys_user_groups(group_id) 
);
 
/** 
* Contains the data classification levels
* Modules: framework
* Last changed: 1-May-2007 - ravindra@opensource.lk
**/
CREATE TABLE sys_data_classifications(
    level_id INT NOT NULL,
    level VARCHAR(60) NOT NULL,
    PRIMARY KEY (level_id)
);


/** 
* Contains the mapping from a role to a data classification 
* Modules: framework
* Last changed: 1-May-2007 - ravindra@opensource.lk
**/
CREATE TABLE sys_group_to_data_classification (
    group_id INT NOT NULL,
    level_id INT NOT NULL,
    crud VARCHAR(4) NOT NULL,
    PRIMARY KEY (group_id,level_id),
    FOREIGN KEY (group_id) REFERENCES sys_user_groups(group_id),
    FOREIGN KEY (level_id) REFERENCES sys_data_classifications(level_id) 
);


/** 
* Contains the data classification levels
* Modules: framework
* Last changed: 1-May-2007 - ravindra@opensource.lk
**/
CREATE TABLE sys_tablefields_to_data_classification(
    table_field VARCHAR(50) NOT NULL,
    level_id INT NOT NULL,
    FOREIGN KEY (level_id) REFERENCES sys_data_classifications(level_id),
    PRIMARY KEY (table_field,level_id)
);


/** 
* specifies whether a module is enabled for a sys group or not
* Modules: framework
* Last changed: 19-AUG-2007 -ravindra@opensource.lk
**/
CREATE TABLE sys_group_to_module (
    group_id INT NOT NULL,
    module VARCHAR(60) NOT NULL,
    status VARCHAR(60) NOT NULL,
    PRIMARY KEY(group_id,module),
    FOREIGN KEY (group_id) REFERENCES sys_user_groups(group_id) 
);
 
/**================= Shared Tables ==================================**/

/**
* The phonetic search table stores the encoding (for Soundx, metafore, etc) 
* Modules: dvr, mpr, cr 
* Last changed: 27-OCT-2005 - chamindra@opensource.lk  
*/
CREATE TABLE phonetic_word(
    encode1 VARCHAR(50),
    encode2 VARCHAR(50),
    pgl_uuid VARCHAR(60)
);


/**
* Contact Information for a person, org or camp
* Modules: dvr, mpr, or, cr, rms 
* Last changed: 27-OCT-2005 - chamindra@opensource.lk  
*/
CREATE TABLE contact (
    pgoc_uuid VARCHAR(60) NOT NULL, -- be either c_uuid, p_uuid or g_uuid
    opt_contact_type VARCHAR(10), -- mobile, home phone, email, IM, etc
    contact_value VARCHAR(100), 
    PRIMARY KEY (pgoc_uuid,opt_contact_type,contact_value)
    /**only pgoc_uuid should not be primary key 
    as for a person there can be several contact types 
    and values (email and mobile) or multiple mobiles
    **/
);

/** 
 * User Preferences
 * Modules : Framework
 * Created : 28th-Mar-2006 - janaka@opensource.lk
 * Last Change : 28th-Mar-2006  - janaka@opensource.lk
 */

CREATE TABLE user_preference(
    p_uuid VARCHAR(60) NOT NULL,
    module_id VARCHAR(20) NOT NULL,
    pref_key     VARCHAR(60) NOT NULL,
    value   VARCHAR(100),
    FOREIGN KEY (p_uuid) REFERENCES person_uuid (p_uuid),
    
    PRIMARY KEY (p_uuid,module_id,pref_key)
);


/**
* Contains the the information on the sector that the org 
* group or person is involved in 
* Modules: or
* Last changed: 27-OCT-2005 - ravindra@opensource.lk  
*/
CREATE TABLE sector(
	pgoc_uuid VARCHAR(60) NOT NULL,
	opt_sector VARCHAR(100),
    PRIMARY KEY (pgoc_uuid, opt_sector)
);

/**
* Chronolology table gives a history of events on data
* Modules: all 
* Created : 28-Nov-2006 - chamindra@opensource.lk
*/
CREATE TABLE chronology(
    log_uuid VARCHAR(60) NOT NULL, -- the log identifier
    event_date DATETIME,           -- the date and time of the event
    pgoc_uuid VARCHAR(60),         -- the entity uuid that this associates to
    opt_cron_type VARCHAR(3),      -- type of event
    module VARCHAR(10),             
    action VARCHAR(10),            -- action
    user_uuid VARCHAR(60), 
    comments VARCHAR(100),         -- description of changes
    details VARCHAR(200)           -- more detailed description of the changes
); 
    

/**
* Auditor log table 
* Modules: dvr, mpr, 
* Created : 21-Dec-2005 - janaka@opensource.lk
*/
CREATE TABLE audit (
    audit_id BIGINT NOT NULL AUTO_INCREMENT,
    updated TIMESTAMP NOT NULL DEFAULT NOW(),
    x_uuid VARCHAR(60) NOT NULL,
    u_uuid VARCHAR(60) NOT NULL,
    change_type VARCHAR(3) NOT NULL,
    change_table VARCHAR(100) NOT NULL,
    change_field VARCHAR(100) NOT NULL,
    prev_val TEXT,
    new_val TEXT,
    PRIMARY KEY(audit_id)
);



/**================= Entity: Location  ===================**/

/**
* The central table to store loactions
* Modules: dvr, mpr, rms, or, cr, gis 
* Last changed: 28-OCT-2005 - janaka@opensource.lk  
*/
CREATE TABLE location(
    loc_uuid VARCHAR(60) NOT NULL, -- universally unique location id,
    parent_id VARCHAR(60) DEFAULT NULL, -- parent location id
    opt_location_type VARCHAR(10), -- location type taken from field_opts
    name VARCHAR(100) NOT NULL,
    iso_code VARCHAR(20),
    description TEXT,
    PRIMARY KEY (loc_uuid)
);

/**
* Details on the location of an entity (person, camp, organization)
* Modules: dvr, mpr, or, cr, rms, gis 
* Last changed: 27-OCT-2005 - ravindra@opensource.lk  
*/
CREATE TABLE location_details ( 
    poc_uuid VARCHAR(60) NOT NULL, -- this can be a person, camp or organization location
    location_id VARCHAR(60), -- This gives country,province,district,town - based on l10n 
    opt_person_loc_type VARCHAR(10), -- the relation this location has to the person
    address TEXT, -- the street address        
    postcode VARCHAR(30), -- or ZIP code
    long_lat VARCHAR(20), -- logatitude and latitude (GPS location)
    PRIMARY KEY (poc_uuid,location_id)
    
);

/**================= Entity: Spatial Location: GIS  ===================**/

/**
* Under Development
* The central table on GIS
* Adheres to OGC OpenGIS Geographic Information - Simple Feature Access Specifications
* Ref: http://www.opengeospatial.org/ 
* Modules: cr,or,ics 
* Last changed: 27-OCT-2005 - mifan@opensource.lk  
*/
CREATE TABLE gis_feature (
    feature_uuid VARCHAR(60) NOT NULL, -- spatial location key
    poc_uuid VARCHAR(60) NOT NULL,     -- mapped entity key
    feature_coords GEOMETRY NOT NULL,  -- coordinates of feature type
    entry_time TIMESTAMP DEFAULT now(),-- entry time for log purposes 
    PRIMARY KEY  (feature_uuid)
    /*SPATIAL KEY  (feature_coords)*/
);


/**================= Entity: Person oriented  ===================**/

/*** PERSON TABLES ***/



/**
* Many ID card numbers (or passport or driving licence) to person table
* Modules: dvr, mpr 
* Last changed: 27-OCT-2005 - chamindra@opensource.lk  
*/
CREATE TABLE identity_to_person (
    p_uuid VARCHAR(60) NOT NULL,
    serial VARCHAR(100), -- id card #, passport #, Driving License # etc
    opt_id_type VARCHAR(10), -- can be customized in the field options table
    FOREIGN KEY (p_uuid) REFERENCES person_uuid(p_uuid)
);

/**
* The main details on a person
* Modules: dvr, mpr, cr
* Created : 27-OCT-2005 - chamindra@opensource.lk  
* Last Updated : 07-Nov-2005 - janaka@opensource.lk
* Note: Removed the NOT NULL Constraint on next_kin_uuid
*/
CREATE TABLE person_details (
    p_uuid VARCHAR(60) NOT NULL,
    next_kin_uuid VARCHAR(60),
    birth_date DATE,
    opt_age_group VARCHAR(10),     -- The age group they belong too
    relation VARCHAR(50),
    opt_country VARCHAR(10),
    opt_race VARCHAR(10),
    opt_religion VARCHAR(10),
    opt_marital_status VARCHAR(10),
    opt_gender VARCHAR(10),
    occupation VARCHAR(100),
    PRIMARY KEY (p_uuid),
    FOREIGN KEY (p_uuid) REFERENCES person_uuid(p_uuid)
);

/**
* Details on the person's status
* Modules: dvr, mpr
* Last changed: 27-OCT-2005 - chamindra@opensource.lk  
*/
CREATE TABLE person_status (
    p_uuid VARCHAR(60) NOT NULL,
    isReliefWorker TINYINT, 
    opt_status VARCHAR(10), -- missing, ingured, etc. customizable
    updated TIMESTAMP DEFAULT NOW(),
    isvictim BOOL DEFAULT 1,
    PRIMARY KEY (p_uuid)
);

/**
* Physical details of a person
* Modules: dvr, mpr, cms, rms 
* Last changed: 27-OCT-2005 - chamindra@opensource.lk  
*/
CREATE TABLE person_physical (
    p_uuid VARCHAR(60) NOT NULL,
    opt_blood_type VARCHAR(10),
    height VARCHAR(10),
    weight VARCHAR(10),
    opt_eye_color VARCHAR(50),
    opt_skin_color VARCHAR(50),
    opt_hair_color VARCHAR(50),
    injuries TEXT,
    comments TEXT,
    PRIMARY KEY (p_uuid) ,
    FOREIGN KEY (p_uuid) REFERENCES person_uuid(p_uuid)
);

CREATE TABLE person_missing (
    p_uuid VARCHAR(60) NOT NULL,
    last_seen TEXT,
    last_clothing TEXT,
    comments TEXT,
    PRIMARY KEY (p_uuid) ,
    FOREIGN KEY (p_uuid) REFERENCES person_uuid(p_uuid)
);

CREATE TABLE person_deceased (
    p_uuid VARCHAR(60) NOT NULL,
    details TEXT,
    date_of_death DATE,
    location VARCHAR(20),
    place_of_death TEXT,
    comments TEXT,
    PRIMARY KEY (p_uuid),
    FOREIGN KEY (p_uuid) REFERENCES person_uuid(p_uuid),
    FOREIGN KEY (location) REFERENCES location(loc_uuid)
);

/**
* The person who reported about this person
* Modules: dvr, mpr, 
* Created : 21-Dec-2005 - janaka@opensource.lk
*/
CREATE TABLE person_to_report (
    p_uuid VARCHAR(60) NOT NULL,
    rep_uuid VARCHAR(60) NOT NULL,
    relation VARCHAR(100),
    PRIMARY KEY (p_uuid,rep_uuid),
    FOREIGN KEY (p_uuid) REFERENCES person_uuid(p_uuid),
    FOREIGN KEY (rep_uuid) REFERENCES person_uuid(p_uuid)
);


/**
* Contains the list of groups of people
* Modules: dvr, mpr
* Last changed: 26-FEB-2007 - isuru@opensource.lk  
*/
CREATE TABLE pgroup (
    g_uuid VARCHAR(60) NOT NULL, -- universally unique group id
    opt_group_type VARCHAR(10), -- type of the group
    PRIMARY KEY (g_uuid)
    
);


/**
* Contains the description of the group
* Modules: dvr, mpr
* Last changed: 26-FEB-2007 - isuru@opensource.lk  
*/
-- group-details
CREATE TABLE group_details(
g_uuid VARCHAR(60) NOT NULL, -- universally unique group id
head_uuid VARCHAR(60),
no_of_adult_males INT,
no_of_adult_females INT,
no_of_children INT, 
no_displaced INT, 
no_missing INT,
no_dead INT,
no_rehabilitated INT,
checklist TEXT,
description TEXT,
PRIMARY KEY(g_uuid),
FOREIGN KEY (head_uuid) REFERENCES person_uuid(p_uuid)

);



/**
* A person can belong to multiple groups
* Modules: dvr, mpr
* Last changed: 26-FEB-2007 - chamindra@opensource.lk  
*/
CREATE TABLE person_to_pgroup (   
    p_uuid VARCHAR(60),
    g_uuid VARCHAR(60)
);

/**================= Entity: Organizations related  ===================**/
/**
* Information about an organization is stored here
* Modules:
* Last changed: 12-OCT-2006 - ravindra@opensource.lk  
*/

CREATE TABLE org_main(
	o_uuid VARCHAR(60) NOT NULL,
    	parent_id VARCHAR(60) DEFAULT NULL,
    	name VARCHAR(100) NOT NULL ,
	opt_org_type VARCHAR(100),
	reg_no VARCHAR(100),
    	man_power VARCHAR(100),
	equipment VARCHAR(100),
	resources TEXT,
    	privacy INT(1) DEFAULT 0,
    	archived BOOL DEFAULT 0, 
 	created TIMESTAMP DEFAULT NOW(),
	PRIMARY KEY (o_uuid),
	FOREIGN KEY (parent_id) REFERENCES org_main(o_uuid)
);

/**================= Entity: Web Services related  ===================**/
/**
* Information about web service keys is stored here
* Modules:
* Last changed: 1-SEP-2007 - ravindra@opensource.lk  
*/

CREATE TABLE ws_keys(
    p_uuid VARCHAR(60) NOT NULL,
    domain VARCHAR(200) NOT NULL,
    api_key VARCHAR(60) NOT NULL,
    password VARCHAR(60) NOT NULL,
    secret VARCHAR(60) NOT NULL,
    PRIMARY KEY (p_uuid,domain,api_key),
    FOREIGN KEY (p_uuid) REFERENCES person_uuid(p_uuid)
);



/**================= Entity: Camp Tables ===========================**/

/**
* Physical Details of Camps/Shelters
* Modules: cr
* Last changed: 21-Feb-2006 - mifan@opensource.lk  
*/

CREATE TABLE camp_general (
    c_uuid VARCHAR(60) NOT NULL,
    name VARCHAR(60),
    location_id VARCHAR(20),
    opt_camp_type VARCHAR(10),
    address TEXT,
    capacity INT,
    shelters INT,
    area VARCHAR(20),
    personsPerShelter INT,
    PRIMARY KEY (c_uuid),
    FOREIGN KEY (location_id) REFERENCES location(loc_uuid)
);

/**
* Human Resource Details of Camps/Shelters
* Modules: cr
* Last changed: 21-Feb-2006 - mifan@opensource.lk  
*/
CREATE TABLE camp_reg (
    c_uuid VARCHAR(60) NOT NULL,
    admin_name VARCHAR(60),
    admin_no VARCHAR(60),
    men INT,
    women INT,
    family INT,
    children INT,
    total INT, 
    PRIMARY KEY (c_uuid)
);

/**
* Services offered by camps
* Modules: cr
* Last changed: 21-Feb-2006 - mifan@opensource.lk  
*/
CREATE TABLE camp_services (
    c_uuid VARCHAR(60) NOT NULL,
    opt_camp_service VARCHAR(50),
    value BOOL NOT NULL default 0, 
    PRIMARY KEY (c_uuid,opt_camp_service)
);

/**
* Camp to Admin Mapping
* Modules: cr
* Last changed: 21-Feb-2006 - mifan@opensource.lk  
*/
CREATE TABLE camp_admin(
    c_uuid VARCHAR(60) NOT NULL,
    contact_puuid VARCHAR(60) NOT NULL,
		PRIMARY KEY (c_uuid,contact_puuid)
);


/**
* A person/group can belong to a shelter
* Modules: dvr, mpr
* Last changed: 15-November-2007 - isuru@opensource.lk  
*/

CREATE TABLE resource_to_shelter (   
    x_uuid VARCHAR(60),
    c_uuid VARCHAR(60),


    PRIMARY KEY(x_uuid,c_uuid),
    FOREIGN KEY (c_uuid) REFERENCES camp_general(c_uuid)
);




/**================= Localization Tables ===========================**/

/**
* I18N/L10N specific tables
* Modules	: Framework, admin 
* Created 	: 08-Feb-2006 - sudheera@opensource.lk
* Last changed	: 21-Feb-2006 - sudheera@opensource.lk
*/

CREATE TABLE lc_fields (
    id BIGINT NOT NULL AUTO_INCREMENT,
    tablename VARCHAR(32) NOT NULL,
    fieldname VARCHAR(32) NOT NULL,
    PRIMARY KEY(id)
);

/**
 * DROP TABLE IF EXISTS lc_tmp_po;
 * CREATE TABLE lc_tmp_po (
 *  string TEXT,
 *  comment TEXT
 * );
 */

/**================= Resource/Multimedia Tables ===========================**/


/** 
 * Image storage table 
 * Modules : Framework, sync
 * Created : 20th-Mar-2006 - janaka@opensource.lk
 * Last Change : 26th-FEB-2007  - isuru@opensource.lk
 */
CREATE TABLE image(
    image_id BIGINT NOT NULL AUTO_INCREMENT,
    x_uuid VARCHAR(60) NOT NULL, -- universally unique person id
    image MEDIUMBLOB NOT NULL,
    image_type VARCHAR(100) NOT NULL,
    image_height INT,
    image_width INT,
    created TIMESTAMP DEFAULT NOW(),
    category varchar(32),
    PRIMARY KEY (image_id)
);


/**================ Multiple Incident Tables ========================**/
/** 
 * Incident 
 * Modules : Framework
 * Created : 28th-Mar-2006 - janaka@opensource.lk
 * Last Change : 28th-Mar-2006  - janaka@opensource.lk
 */
CREATE TABLE incident(
    incident_id BIGINT NOT NULL AUTO_INCREMENT,
    parent_id BIGINT DEFAULT NULL,
    search_id VARCHAR(60),
    name VARCHAR(60),
    FOREIGN KEY (parent_id) REFERENCES incident (incident_id),
    PRIMARY KEY (incident_id)
);

/** 
 * Resources to Incidents
 * Modules : Framework, or
 * Created : 28th-Mar-2006 - janaka@opensource.lk
 * Last Change : 28th-Mar-2006  - janaka@opensource.lk
 */

CREATE TABLE resource_to_incident(
    incident_id BIGINT NOT NULL,
    x_uuid VARCHAR(60),
    FOREIGN KEY (incident_id) REFERENCES incident (incident_id),
    PRIMARY KEY (incident_id,x_uuid)
);

/**================ PHP and 3rd Party Tables ========================**/

/**
* This is the sessions table that stores the PHP session in the database 
* Modules: all
*/
CREATE TABLE sessions(
	session_id VARCHAR(64) NOT NULL,
	sess_key VARCHAR(64) NOT NULL,
	secret VARCHAR(64) NOT NULL,
	inactive_expiry BIGINT NOT NULL,
	expiry BIGINT NOT NULL,
	data TEXT ,
	PRIMARY KEY (session_id)
);

-- OPTIMIZATION  DEVEL - @depracated ?
CREATE TABLE devel_logsql (
    created timestamp NOT NULL, 
    sql0 varchar(250) NOT NULL,
    sql1 text NOT NULL,
    params text NOT NULL,
    tracer text NOT NULL,
    timer decimal(16,6) NOT NULL
); 


/**================= TO BE REMOVED =======================**/

