/**
* 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)      
);

/**
* 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
    **/
);

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

/**
* The central table to store loactions
* Modules: dvr, mpr, rms, or, cr, sm
* 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, sm 
* 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: 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)
);


