-- CREATE 'linea' role with 'linea' password
--CREATE ROLE linea LOGIN
--  ENCRYPTED PASSWORD 'md58f1e8102379267e742e375283dc9b22e'
--  NOSUPERUSER NOINHERIT NOCREATEDB NOCREATEROLE;


-- BUILD Linea21 DB script - UTF-8 format
--CREATE DATABASE linea
-- WITH OWNER = linea
--     ENCODING = 'UTF8'
--    TABLESPACE = pg_default;


--
-- Table structure for table bibliography_resources
--

DROP TABLE IF EXISTS bibliography_resources;
CREATE TABLE bibliography_resources (
  bibliores_id SERIAL,
  bibliores_author varchar(200) NOT NULL default '',
  bibliores_title varchar(255) NOT NULL default '',
  bibliores_sub_title varchar(255) NOT NULL default '',
  bibliores_editor varchar(255) NOT NULL default '',
  bibliores_date varchar(100) NOT NULL default '',
  bibliores_range int NOT NULL default '0',
  bibliores_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  bibliores_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  bibliores_validity char(1) NOT NULL default 'Y', -- 'Y','N'
  PRIMARY KEY  (bibliores_id)
) ;


--
-- Table structure for table category_user
--

DROP TABLE IF EXISTS category_user;
CREATE TABLE category_user (
  catus_id SERIAL,
  catus_name varchar(100) NOT NULL default '',
  catus_description varchar(255) NOT NULL default '',
  catus_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  catus_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (catus_id)
) ;

--
-- Dumping data for table category_user
--

INSERT INTO category_user (catus_id, catus_name, catus_description, catus_date_crea, catus_last_modify) VALUES (1, E'administrateur', E'administrateur du forum - dispose de tous les droits', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO category_user (catus_id, catus_name, catus_description, catus_date_crea, catus_last_modify) VALUES (2, E'utilisateur privilegié', E'utilisateur privilegié', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO category_user (catus_id, catus_name, catus_description, catus_date_crea, catus_last_modify) VALUES (3, E'utilisateur simple', E'utilisateur simple', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);


-- --------------------------------------------------------

--
-- Table structure for table email_collector
--

DROP TABLE IF EXISTS email_collector;
CREATE TABLE email_collector (
  emailcol_id SERIAL,
  emailcol_email varchar(200) NOT NULL default '',
  emailcol_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  emailcol_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  emailcol_validity char(1) NOT NULL default 'Y', --'Y','N'
  PRIMARY KEY  (emailcol_id)
) ;


-- --------------------------------------------------------
--
-- Table structure for table j_biblio
--

DROP TABLE IF EXISTS j_biblio;
CREATE TABLE j_biblio (
  j_root_id int NOT NULL default '0',
  j_biblio_id int NOT NULL default '0',
  j_type char(1) NOT NULL default 'P' --'P','W'
) ;


-- --------------------------------------------------------
--
-- Table structure for table j_link
--

DROP TABLE IF EXISTS j_link;
CREATE TABLE j_link (
  j_root_id int NOT NULL default '0',
  j_link_id int NOT NULL default '0',
  j_type char(1) NOT NULL default 'P' --'P', 'W'
) ;


-- --------------------------------------------------------
--
-- Table structure for table j_multi
--

DROP TABLE IF EXISTS j_multi;
CREATE TABLE j_multi (
  j_root_id int NOT NULL default '0',
  j_multi_id int NOT NULL default '0',
  j_type char(1) NOT NULL default 'P' --'P', 'W'
) ;


-- --------------------------------------------------------
--
-- Table structure for table j_parts
--

DROP TABLE IF EXISTS j_parts;
CREATE TABLE j_parts (
  j_root_id int NOT NULL default '0',
  j_parts_id int NOT NULL default '0',
  j_type char(1) NOT NULL default 'P' --'P', 'W'
) ;


-- --------------------------------------------------------
--
-- Table structure for table j_work_users
--

DROP TABLE IF EXISTS j_work_users;
CREATE TABLE j_work_users (
  jwu_id SERIAL,
  jwu_workshop_id int NOT NULL default '0',
  jwu_user_id int NOT NULL default '0',
  jwu_user_right char(1) NOT NULL default 'U', -- 'A','O','U'
  PRIMARY KEY  (jwu_id)
) ;


-- --------------------------------------------------------
--
-- Table structure for table level
--

DROP TABLE IF EXISTS level;
CREATE TABLE level (
  level_id SERIAL,
  level_name varchar(200) NOT NULL default '',
  level_description text NOT NULL default '',
  level_comment text NOT NULL default '',
  level_visual_identity varchar(100) NOT NULL default '',
  level_range int NOT NULL default '0',
  level_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  level_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  level_statut char(1) NOT NULL default 'P', --'P','D','E'
  PRIMARY KEY  (level_id)
)  ;

--
-- Dumping data for table level
--

INSERT INTO level (level_id, level_name, level_description, level_comment, level_visual_identity, level_range, level_date_crea, level_last_modify, level_statut) VALUES (DEFAULT, E'jeune public', E'premiers pas en matière d\'environnement, développement durable', '', '', 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'P');
INSERT INTO level (level_id, level_name, level_description, level_comment, level_visual_identity, level_range, level_date_crea, level_last_modify, level_statut) VALUES (DEFAULT, E'débutant', E'connaissance succintes sur les sujets d\'environnement, développement durable', '', '', 2, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'P');
INSERT INTO level (level_id, level_name, level_description, level_comment, level_visual_identity, level_range, level_date_crea, level_last_modify, level_statut) VALUES (DEFAULT, E'intérmediaire', E'initiés aux problématiques du développement durable', '', '', 3, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'P');
INSERT INTO level (level_id, level_name, level_description, level_comment, level_visual_identity, level_range, level_date_crea, level_last_modify, level_statut) VALUES (DEFAULT, E'expert', E'vous avez des connaissances solides sur le sujet et maitrisez les enjeux', '', '', 4, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'P');

-- --------------------------------------------------------

--
-- Table structure for table link_resources
--

DROP TABLE IF EXISTS link_resources;
CREATE TABLE link_resources (
  linkres_id SERIAL,
  linkres_text text NOT NULL,
  linkres_mask varchar(200) NOT NULL default '',
  linkres_range int NOT NULL default '0',
  linkres_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  linkres_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  linkres_validity char(1) NOT NULL default 'Y', --'Y', 'N'
  PRIMARY KEY  (linkres_id)
) ;


-- --------------------------------------------------------
--
-- Table structure for table multimedia_resources
--

DROP TABLE IF EXISTS multimedia_resources;
CREATE TABLE multimedia_resources (
  multires_id SERIAL,
  multires_name varchar(200) NOT NULL default '',
  multires_description text NOT NULL,
  multires_media_uri varchar(255) NOT NULL default '',
  multires_range int NOT NULL default '0',
  multires_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  multires_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  multires_validity char(1) NOT NULL default 'Y', --'Y', 'N'
  PRIMARY KEY  (multires_id)
) ;


-- --------------------------------------------------------
--
-- Table structure for table news
--

DROP TABLE IF EXISTS news;
CREATE TABLE news (
  news_id SERIAL,
  news_title varchar(200) NOT NULL default '',
  news_header text NOT NULL,
  news_body text NOT NULL,
  news_theme int NOT NULL default '0',
  news_scale int NOT NULL default '0',
  news_level int NOT NULL default '0',
  news_template int NOT NULL default '0',
  news_photo_uri varchar(255) NOT NULL default '',
  news_range int NOT NULL default '0',
  news_posted_by int NOT NULL default '0',
  news_published_date timestamp NOT NULL default CURRENT_TIMESTAMP,
  news_perempt_date timestamp NOT NULL default CURRENT_TIMESTAMP,
  news_is_national char(1) NOT NULL default 'N', -- 'Y','N'
  news_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  news_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  news_statut varchar(2) NOT NULL default 'D', --'D','P','AA','PA','E'
  PRIMARY KEY  (news_id) --,
  --FULLTEXT KEY news_index (news_title,news_header,news_body)
) ;


-- --------------------------------------------------------
--
-- Table structure for table newsletter
--

DROP TABLE IF EXISTS newsletter;
CREATE TABLE newsletter (
  newsletter_id SERIAL,
  newsletter_title varchar(200) default NULL,
  newsletter_body text NOT NULL,
  newsletter_posted_by int NOT NULL default '0',
  newsletter_published_date timestamp NOT NULL default CURRENT_TIMESTAMP,
  newsletter_date_crea timestamp default CURRENT_TIMESTAMP,
  newsletter_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  newsletter_statut char(1) NOT NULL default 'D', --'D','W','P','E'
  PRIMARY KEY  (newsletter_id) --,
  --FULLTEXT KEY newsletter_index (newsletter_title,newsletter_body)
) ;


-- --------------------------------------------------------
--
-- Table structure for table profile
--

DROP TABLE IF EXISTS profile;
CREATE TABLE profile (
  profile_id SERIAL,
  profile_email varchar(200) NOT NULL default '',
  profile_email_display char(1) NOT NULL default 'Y', --'Y', 'N'
  profile_city varchar(100) NOT NULL default '',
  profile_birthdate date NOT NULL default '0001-01-01',
  profile_leisures varchar(150) NOT NULL default '',
  profile_job varchar(150) NOT NULL default '',
  profile_avatar varchar(255) NOT NULL default '',
  profile_quotation varchar(150) NOT NULL default '',
  profile_signature varchar(200) NOT NULL default '',
  profile_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  profile_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (profile_id)
)  ;

--
-- Dumping data for table profile
--

INSERT INTO profile (profile_id, profile_email, profile_email_display, profile_city, profile_birthdate, profile_leisures, profile_job, profile_avatar, profile_quotation, profile_signature, profile_date_crea, profile_last_modify) VALUES (DEFAULT, 'change_your@address.com', 'N', '', '0001-01-01', E'', E'administrateur linea21', E'', E'', E'', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- --------------------------------------------------------

--
-- Table structure for table publication
--

DROP TABLE IF EXISTS publication;
CREATE TABLE publication (
  publi_id SERIAL,
  publi_title varchar(200) NOT NULL default '',
  publi_resume text NOT NULL,
  publi_theme int NOT NULL default '0',
  publi_scale int NOT NULL default '0',
  publi_level int NOT NULL default '0',
  publi_posted_by int NOT NULL default '0',
  publi_published_date timestamp NOT NULL default CURRENT_TIMESTAMP,
  publi_comment text NOT NULL,
  publi_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  publi_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  publi_statut varchar(2) NOT NULL default 'D', --'D','P','AA','PA','E'
  PRIMARY KEY  (publi_id) --,
  --FULLTEXT KEY publi_index (publi_title,publi_resume)
) ;



--
-- Table structure for table publication_contents
--

DROP TABLE IF EXISTS publication_contents;
CREATE TABLE publication_contents (
  publicon_id SERIAL,
  publicon_title varchar(200) NOT NULL default '',
  publicon_body text NOT NULL,
  publicon_template int NOT NULL default '0',
  publicon_photos_uri varchar(255) NOT NULL default '',
  publicon_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  publicon_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  publicon_validity char(1) NOT NULL default 'Y', --'Y', 'N'
  PRIMARY KEY  (publicon_id) --,
  --FULLTEXT KEY publication_contents_index (publicon_title,publicon_body)
) ;


-- --------------------------------------------------------
--
-- Table structure for table rights
--

DROP TABLE IF EXISTS rights;
CREATE TABLE rights (
  rights_id SERIAL,
  rights_dashboard char(1) NOT NULL default 'U', --'A','O','U'
  rights_workshop char(1) NOT NULL default 'U', --'A','O','U'
  rights_project char(1) NOT NULL default 'U', -- 'A', 'U'
  rights_publication char(1) NOT NULL default 'U', --'A','O','U'
  rights_news char(1) NOT NULL default 'U', --'A','O','U'
  rights_yellowpages char(1) NOT NULL default 'U', --'A','O','U'
  rights_theme char(1) NOT NULL default 'U', --'A','U'
  rights_scale char(1) NOT NULL default 'U', --'A','U'
  rights_level char(1) NOT NULL default 'U', --'A','U'
  rights_category_user char(1) NOT NULL default 'U', --'A','U'
  rights_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  rights_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (rights_id)
)  ;

--
-- Dumping data for table rights
--

INSERT INTO rights (rights_id, rights_dashboard, rights_workshop, rights_project, rights_publication, rights_news, rights_yellowpages, rights_theme, rights_scale, rights_level, rights_category_user, rights_date_crea, rights_last_modify) VALUES (DEFAULT, 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', 'A', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);


-- --------------------------------------------------------
--
-- Table structure for table scale
--

DROP TABLE IF EXISTS scale;
CREATE TABLE scale (
  scale_id SERIAL,
  scale_denomination varchar(200) NOT NULL default '',
  scale_surface real NOT NULL default '0',
  scale_inhabitantsnumber bigint NOT NULL default '0',
  scale_root int NOT NULL default '0',
  scale_comment text NOT NULL default '',
  scale_dependencies int NOT NULL default '0',
  scale_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  scale_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  scale_statut char(1) NOT NULL default 'P', --'P','D','E','I'
  PRIMARY KEY  (scale_id)
)  ;

--
-- Dumping data for table scale
--

INSERT INTO scale (scale_id, scale_denomination, scale_surface, scale_inhabitantsnumber, scale_root, scale_comment, scale_dependencies, scale_date_crea, scale_last_modify, scale_statut) VALUES (DEFAULT, E'Echelle racine', 0, 0, 0, E'', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'P');

-- --------------------------------------------------------


-- --------------------------------------------------------
--
-- Table structure for table sd_theme
--

DROP TABLE IF EXISTS sd_theme;
CREATE TABLE sd_theme (
  sdtheme_id SERIAL,
  sdtheme_name varchar(150) NOT NULL default '',
  sdtheme_description text NOT NULL,
  sdtheme_range int NOT NULL default '0',
  sdtheme_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  sdtheme_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (sdtheme_id)
)  ;

--
-- Dumping data for table sd_theme
--

INSERT INTO sd_theme (sdtheme_id, sdtheme_name, sdtheme_description, sdtheme_range, sdtheme_date_crea, sdtheme_last_modify) VALUES (DEFAULT, E'social', E'', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sd_theme (sdtheme_id, sdtheme_name, sdtheme_description, sdtheme_range, sdtheme_date_crea, sdtheme_last_modify) VALUES (DEFAULT, E'économique', E'', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sd_theme (sdtheme_id, sdtheme_name, sdtheme_description, sdtheme_range, sdtheme_date_crea, sdtheme_last_modify) VALUES (DEFAULT, E'environnement', E'', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sd_theme (sdtheme_id, sdtheme_name, sdtheme_description, sdtheme_range, sdtheme_date_crea, sdtheme_last_modify) VALUES (DEFAULT, E'gouvernance', E'', 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);

-- --------------------------------------------------------

--
-- Table structure for table sdi_evaluation
--

DROP TABLE IF EXISTS sdi_evaluation;
CREATE TABLE sdi_evaluation (
  sdie_id SERIAL,
  sdie_scale_compare int NOT NULL default '0',
  sdie_fiability int NOT NULL default '0',
  sdie_accessibility int NOT NULL default '0',
  sdie_lisibility int NOT NULL default '0',
  sdie_relevance int NOT NULL default '0',
  sdie_global_performance int NOT NULL default '0',
  sdie_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  sdie_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (sdie_id)
) ;


-- --------------------------------------------------------
--
-- Table structure for table sdi_info
--

DROP TABLE IF EXISTS sdi_info;
CREATE TABLE sdi_info (
  sdii_id SERIAL,
  sdii_name varchar(255) NOT NULL default '',
  sdii_description text NOT NULL,
  sdii_comment text NOT NULL,
  sdii_theme int NOT NULL default '0',
  sdii_range int NOT NULL default '0',
  sdii_goal text NOT NULL,
  sdii_consulting text NOT NULL,
  sdii_unit varchar(100) NOT NULL default '',
  sdii_max_value real NOT NULL default '0',
  sdii_min_value real NOT NULL default '0',
  sdii_threshold_value real NOT NULL default '0',
  sdii_threshold_relative char(1) NOT NULL default 'Y', --'Y', 'N'
  sdii_frequency int NOT NULL default '0',
  sdii_provider int NOT NULL default '0',
  sdii_evaluation int NOT NULL default '0',
  sdii_reglementation int NOT NULL default '0',
  sdii_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  sdii_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  sdii_statut char(1) NOT NULL default 'P', --'P','D','E'
  PRIMARY KEY  (sdii_id) --,
  --FULLTEXT KEY sdi_info_index (sdii_name,sdii_description,sdii_goal,sdii_consulting)
) ;



-- --------------------------------------------------------
--
-- Table structure for table sdi_provider
--

DROP TABLE IF EXISTS sdi_provider;
CREATE TABLE sdi_provider (
  sdip_id SERIAL,
  sdip_name varchar(255) NOT NULL default '',
  sdip_service varchar(255) NOT NULL default '',
  sdip_description text NOT NULL default '',
  sdip_incharge varchar(150) NOT NULL default '',
  sdip_address text NOT NULL default '',
  sdip_phone varchar(10) NOT NULL default '',
  sdip_fax varchar(12) NOT NULL default '',
  sdip_email varchar(200) NOT NULL default '',
  sdip_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  sdip_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (sdip_id)
) ;


-- --------------------------------------------------------
--
-- Table structure for table sdi_reglementation
--

DROP TABLE IF EXISTS sdi_reglementation;
CREATE TABLE sdi_reglementation (
  sdir_id SERIAL,
  sdir_title varchar(255) NOT NULL default '',
  sdir_body text NOT NULL,
  sdir_referer_uri text NOT NULL,
  sdir_mask_uri varchar(255) NOT NULL default '',
  sdir_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  sdir_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (sdir_id)
) ;


-- --------------------------------------------------------
--
-- Table structure for table sdi_value
--

DROP TABLE IF EXISTS sdi_value;
CREATE TABLE sdi_value (
  sdiv_id SERIAL,
  sdiv_value real NOT NULL default '0',
  sdiv_sdi_info int NOT NULL default '0',
  sdiv_scale int NOT NULL default '0',
  sdiv_date_published timestamp NOT NULL default CURRENT_TIMESTAMP,
  sdiv_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  sdiv_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  sdiv_statut char(1) NOT NULL default 'P', --'P','D','E'
  PRIMARY KEY  (sdiv_id)
) ;



-- --------------------------------------------------------
--
-- Table structure for table theme
--

DROP TABLE IF EXISTS theme;
CREATE TABLE theme (
  theme_id SERIAL,
  theme_name varchar(200) NOT NULL default '',
  theme_visual_uri varchar(255) NOT NULL default '',
  theme_sd_theme int NOT NULL default '0',
  theme_desc_project text NOT NULL default '',
  theme_desc_dashboard text NOT NULL default '',
  theme_desc_publication text NOT NULL default '',
  theme_desc_news text NOT NULL default '',
  theme_desc_workshop text NOT NULL default '',
  theme_range_project int NOT NULL default '0',
  theme_range_dashboard int NOT NULL default '0',
  theme_range_publication int NOT NULL default '0',
  theme_range_news int NOT NULL default '0',
  theme_range_workshop int NOT NULL default '0',
  theme_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  theme_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  theme_statut char(1) NOT NULL default 'P', --'P','D','E'
  PRIMARY KEY  (theme_id)
) ;


--
-- Dumping data for table theme
--

INSERT INTO theme (theme_id, theme_name, theme_visual_uri, theme_sd_theme, theme_desc_dashboard, theme_desc_project, theme_desc_publication, theme_desc_news, theme_desc_workshop, theme_range_dashboard, theme_range_project, theme_range_publication, theme_range_news, theme_range_workshop, theme_date_crea, theme_last_modify, theme_statut) VALUES 
(DEFAULT, E'sous thème à modifier', '', 1, E'description', E'description', E'description', E'description', E'description', 0, 0, 0, 0, 0, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'P');

-- --------------------------------------------------------
--
-- Table structure for table user
--

DROP TABLE IF EXISTS luser;
CREATE TABLE luser (
  user_id SERIAL,
  user_login varchar(20) NOT NULL default '',
  user_password varchar(20) NOT NULL default '',
  user_community int NOT NULL default '0',
  user_category int NOT NULL default '0',
  user_rights int NOT NULL default '0',
  user_profile int NOT NULL default '0',
  user_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  user_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  user_validity char(1) NOT NULL default 'Y', --'D','Y','N'
  PRIMARY KEY  (user_id) --,
) ;

--
-- Dumping data for table user
--

INSERT INTO luser (user_id, user_login, user_password, user_community, user_category, user_rights, user_profile, user_date_crea, user_last_modify, user_validity) VALUES (DEFAULT, 'admin', 'ldz8xiJGiGs4U', 1, 1, 1, 1, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'Y');


-- --------------------------------------------------------
--
-- Table structure for table workshop
--

DROP TABLE IF EXISTS workshop;
CREATE TABLE workshop (
  workshop_id SERIAL,
  workshop_denomination varchar(255) NOT NULL default '',
  workshop_resume text NOT NULL default '',
  workshop_theme int NOT NULL default '0',
  workshop_comment text NOT NULL,
  workshop_level int NOT NULL default '0',
  workshop_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  workshop_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  workshop_statut varchar(2) NOT NULL default 'D', --'D','P','AA','PA','E'
  PRIMARY KEY  (workshop_id) --,
  --FULLTEXT KEY workshop_index (workshop_denomination,workshop_resume)
) ;


-- --------------------------------------------------------
--
-- Table structure for table workshop_calendar
--

DROP TABLE IF EXISTS workshop_calendar;
CREATE TABLE workshop_calendar (
  workcal_id SERIAL,
  workcal_task_date timestamp NOT NULL default CURRENT_TIMESTAMP,
  workcal_task varchar(200) NOT NULL default '',
  workcal_task_details text NOT NULL,
  workcal_workshop_id int NOT NULL default '0',
  workcal_posted_by int NOT NULL default '0',
  workcal_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  workcal_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  workcal_validity char(1) NOT NULL default 'Y', --'Y', 'N'
  PRIMARY KEY  (workcal_id) --,
  --FULLTEXT KEY workshop_cal_index (workcal_task,workcal_task_details)
) ;


-- --------------------------------------------------------
--
-- Table structure for table workshop_com
--

DROP TABLE IF EXISTS workshop_com;
CREATE TABLE workshop_com (
  workcom_id SERIAL,
  workcom_subject varchar(200) NOT NULL default '',
  workcom_body text NOT NULL,
  workcom_parent int NOT NULL default '0',
  workcom_workshop_id int NOT NULL default '0',
  workcom_user_id int NOT NULL default '0',
  workcom_last_user_id int NOT NULL default '0',
  workcom_viewed int NOT NULL default '0',
  workcom_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  workcom_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  workcom_statut char(1) NOT NULL default 'P', --'P','C','E'
  PRIMARY KEY  (workcom_id) --,
  --FULLTEXT KEY workshop_com_index (workcom_subject,workcom_body)
) ;


-- --------------------------------------------------------
--
-- Table structure for table workshop_report
--

DROP TABLE IF EXISTS workshop_report;
CREATE TABLE workshop_report (
  workrep_id SERIAL,
  workrep_title varchar(200) NOT NULL default '',
  workrep_resume text NOT NULL,
  workrep_workshop_id int NOT NULL default '0',
  workrep_published_date timestamp NOT NULL default CURRENT_TIMESTAMP,
  workrep_comment text NOT NULL default '',
  workrep_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  workrep_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  workrep_statut varchar(2) NOT NULL default 'D', --'D','P','AA','PA','E'
  PRIMARY KEY  (workrep_id) --,
  --FULLTEXT KEY workshop_report_index (workrep_title,workrep_resume)
) ;


-- --------------------------------------------------------
--
-- Table structure for table workshop_report_contents
--

DROP TABLE IF EXISTS workshop_report_contents;
CREATE TABLE workshop_report_contents (
  workrepcon_id SERIAL,
  workrepcon_title varchar(100) NOT NULL default '',
  workrepcon_body text NOT NULL,
  workrepcon_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  workrepcon_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  workrepcon_validity char(1) NOT NULL default 'Y', --'Y', 'N'
  PRIMARY KEY  (workrepcon_id) --,
  --FULLTEXT KEY workshop_report_contents_index (workrepcon_title,workrepcon_body)
) ;


-- --------------------------------------------------------
--
-- Table structure for table yellowpages
--

DROP TABLE IF EXISTS yellowpages;
CREATE TABLE yellowpages (
  yellowp_id SERIAL,
  yellowp_name varchar(150) NOT NULL default '',
  yellowp_activity varchar(255) NOT NULL default '',
  yellowp_theme int NOT NULL default '0',
  yellowp_street varchar(200) NOT NULL default '',
  yellowp_postal_code varchar(6) NOT NULL default '',
  yellowp_city varchar(100) NOT NULL default '',
  yellowp_country varchar(100) NOT NULL default '',
  yellowp_phone varchar(12) NOT NULL default '',
  yellowp_fax varchar(12) NOT NULL default '',
  yellowp_email varchar(150) NOT NULL default '',
  yellowp_website varchar(150) NOT NULL default '',
  yellowp_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  yellowp_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  yellowp_statut varchar(2) NOT NULL default 'D', --'D','P','AA','E'
  PRIMARY KEY  (yellowp_id)
) ;

-- Patch v1.2

-- 
-- Creating and populating the **Priority** table
--
CREATE TABLE priority (
  priority_id SERIAL,
  priority_name varchar(255) NOT NULL,
  priority_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  priority_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  PRIMARY KEY  (priority_id)
);


INSERT INTO priority VALUES (1, E'basse', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO priority VALUES (2, E'moyenne', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO priority VALUES (3, E'haute', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);


-- 
-- Creating the **Project** table
--
CREATE TABLE project (
  project_id SERIAL,
  project_parent_id int default '0',
  project_priority_id int default '0',
  project_name varchar(255) NOT NULL,
  project_scale_id int NOT NULL,
  project_description text,
  project_body text NOT NULL,
  project_begin_date timestamp default NULL,
  project_estimated_date timestamp default NULL,
  project_end_date timestamp default NULL,
  project_posted_by int NOT NULL,
  project_published_date timestamp NOT NULL default CURRENT_TIMESTAMP,
  project_date_crea timestamp NOT NULL default CURRENT_TIMESTAMP,
  project_last_modify timestamp NOT NULL default CURRENT_TIMESTAMP,
  project_statut varchar(2) NOT NULL default 'D', -- 'D','P','AA','PA','E'
  PRIMARY KEY  (project_id)
) ;

