
 *) mail_archive table has new column attempt which counts failed attempts
    to send email.
    SQL: ALTER TABLE mail_archive ADD COLUMN attempt smallint NOT NULL DEFAULT 0;

 *) Email templates conserned with invoicing have english translations now.
    SQL: UPDATE mail_templates SET template = '...' WHERE id = 17;
    SQL: UPDATE mail_templates SET template = '...' WHERE id = 18;
    SQL: UPDATE mail_templates SET template = '...' WHERE id = 19;

 version 1.4

 *) adding role

ALTER TABLE domain_contact_map ADD role integer not null default 1;
ALTER TABLE domain_contact_map DROP constraint domain_contact_map_pkey;
ALTER TABLE domain_contact_map ADD PRIMARY KEY(domainid,contactid,role);
ALTER TABLE domain_contact_map_history ADD role integer not null default 1;
ALTER TABLE domain_contact_map_history DROP constraint domain_contact_map_history_pkey;
ALTER TABLE domain_contact_map_history ADD PRIMARY KEY(historyid,domainid,contactid,role);

-- 1.5->1.6

CREATE TABLE epp_info_buffer_content (
	id INTEGER NOT NULL, 
	registrar_id INTEGER NOT NULL REFERENCES registrar (id), 
	object_id INTEGER NOT NULL REFERENCES object_registry (id), 
	PRIMARY KEY (id,registrar_id)
);

CREATE TABLE epp_info_buffer (
	registrar_id INTEGER NOT NULL REFERENCES registrar (id),
	current INTEGER, 
	FOREIGN KEY (registrar_id, current) REFERENCES epp_info_buffer_content (registrar_id, id),
  PRIMARY KEY (registrar_id)	
);

INSERT INTO enum_action (  status , id )  VALUES(  'Info'  , 1104 );
INSERT INTO enum_action (  status , id )  VALUES(  'GetInfoResults' ,  1105 );
    
DROP INDEX object_registry_name_idx;
CREATE INDEX object_registry_upper_name_1_idx 
 ON object_registry (UPPER(name)) WHERE type=1;
CREATE INDEX object_registry_upper_name_2_idx 
 ON object_registry (UPPER(name)) WHERE type=2;
CREATE INDEX object_registry_name_3_idx 
 ON object_registry  (NAME) WHERE type=3;
CREATE INDEX domain_registrant_idx ON Domain (registrant);
CREATE INDEX domain_nsset_idx ON Domain (nsset);
CREATE INDEX object_upid_idx ON "object" (upid);
CREATE INDEX object_clid_idx ON "object" (clid);
DROP index domain_id_idx;
CREATE INDEX genzone_domain_history_domain_hid_idx 
  ON genzone_domain_history (domain_hid);
CREATE INDEX genzone_domain_history_domain_id_idx 
  ON genzone_domain_history (domain_id);
CREATE INDEX host_ipaddr_map_hostid_idx ON host_ipaddr_map (hostid);
CREATE INDEX host_ipaddr_map_nssetid_idx ON host_ipaddr_map (nssetid);

DROP INDEX object_id_idx;
DROP INDEX contact_id_idx;
DROP INDEX nsset_id_idx;

CREATE INDEX nsset_contact_map_contactid_idx ON nsset_contact_map (ContactID);
CREATE INDEX domain_contact_map_contactid_idx ON domain_contact_map (ContactID);

ALTER TABLE contact 
  ADD DiscloseVAT boolean DEFAULT False,
  ADD DiscloseIdent boolean DEFAULT False,
  ADD DiscloseNotifyEmail boolean DEFAULT False,
  ALTER telephone TYPE varchar(64),
  ALTER fax TYPE varchar(64),
  ALTER ssn TYPE varchar(64);
ALTER TABLE contact_history
  ADD DiscloseVAT boolean DEFAULT False,
  ADD DiscloseIdent boolean DEFAULT False,
  ADD DiscloseNotifyEmail boolean DEFAULT False,
  ALTER telephone TYPE varchar(64),
  ALTER fax TYPE varchar(64),
  ALTER ssn TYPE varchar(64);
  
ALTER TABLE invoice
  ALTER zone SET NOT NULL,
  ALTER taxdate SET NOT NULL,
  ALTER prefix SET NOT NULL,
  ALTER prefix TYPE bigint,
  ALTER prefix_type SET NOT NULL,
  ALTER price SET NOT NULL,
  ALTER vat SET NOT NULL,
  ALTER total SET NOT NULL,
  ALTER totalvat SET NOT NULL;
  
ALTER TABLE invoice_prefix
  ALTER prefix TYPE bigint;

ALTER TABLE registrarinvoice
  ALTER zone SET NOT NULL,
  ALTER fromdate SET NOT NULL;

ALTER TABLE domain_contact_map DROP constraint domain_contact_map_pkey;
ALTER TABLE domain_contact_map ADD PRIMARY KEY(domainid,contactid);
ALTER TABLE domain_contact_map_history DROP constraint domain_contact_map_history_pkey;
ALTER TABLE domain_contact_map_history ADD PRIMARY KEY(historyid,domainid,contactid);
    
INSERT INTO enum_ssntype  VALUES(6 , 'BIRTHDAY' , 'day of birth');

INSERT INTO enum_country (id,country) VALUES ( 'YU' , 'YUGOSLAVIA' );
INSERT INTO enum_country (id,country) VALUES ( 'RS' , 'SERBIA' );
INSERT INTO enum_country (id,country) VALUES ( 'ME' , 'MONTENEGRO' );

1.6 -> 1.7

INSERT INTO enum_country (id,country) VALUES ( 'GG' , 'GUERNSEY' );
INSERT INTO enum_country (id,country) VALUES ( 'IM' , 'ISLE OF MAN' );
INSERT INTO enum_country (id,country) VALUES ( 'JE' , 'JERSEY' );

-- archiving of notifications about undelivered emails
ALTER TABLE mail_archive ADD COLUMN response text;

-- new technical test
INSERT INTO check_test (id, name, severity, description, disabled, script, need_domain) VALUES (0, 'glue', 1, '', False, '', True);

-- new dependecies on new technical test
INSERT INTO check_dependance (addictid, testid) VALUES ( 1, 0);
INSERT INTO check_dependance (addictid, testid) VALUES (10, 0);
INSERT INTO check_dependance (addictid, testid) VALUES (20, 0);
INSERT INTO check_dependance (addictid, testid) VALUES (30, 0);
INSERT INTO check_dependance (addictid, testid) VALUES (40, 0);
INSERT INTO check_dependance (addictid, testid) VALUES (50, 0);
INSERT INTO check_dependance (addictid, testid) VALUES (60, 0);


-- must be done as superuser 
CREATE LANGUAGE 'plpgsql';

--
-- COPY the content of func.sql file here
--
\i func.sql

--
-- COPY the content of state.sql file here
--
\i state.sql

-- The original status table must be dropped
DROP TABLE enum_status;

CREATE TABLE MessageType (
        ID INTEGER PRIMARY KEY,
	name VARCHAR(30) NOT NULL
);
INSERT INTO MessageType VALUES (01, 'credit');
INSERT INTO MessageType VALUES (02, 'techcheck');
INSERT INTO MessageType VALUES (03, 'transfer_contact');
INSERT INTO MessageType VALUES (04, 'transfer_nsset');
INSERT INTO MessageType VALUES (05, 'transfer_domain');
INSERT INTO MessageType VALUES (06, 'delete_contact');
INSERT INTO MessageType VALUES (07, 'delete_nsset');
INSERT INTO MessageType VALUES (08, 'delete_domain');
INSERT INTO MessageType VALUES (09, 'imp_expiration');
INSERT INTO MessageType VALUES (10, 'expiration');
INSERT INTO MessageType VALUES (11, 'imp_validation');
INSERT INTO MessageType VALUES (12, 'validation');
INSERT INTO MessageType VALUES (13, 'outzone');

ALTER TABLE message DROP COLUMN message;
ALTER TABLE message ADD COLUMN msgType INTEGER REFERENCES messagetype (id);

CREATE TABLE poll_credit (
  msgid INTEGER PRIMARY KEY REFERENCES message (id),
  zone INTEGER REFERENCES zone (id),
  credlimit INTEGER NOT NULL,
  credit INTEGER NOT NULL
);

CREATE TABLE poll_credit_zone_limit (
  zone INTEGER NOT NULL REFERENCES zone(id),
  credlimit INTEGER NOT NULL
);

CREATE TABLE poll_eppaction (
  msgid INTEGER PRIMARY KEY REFERENCES message (id),
  objid INTEGER REFERENCES object_history (historyid)
);

CREATE TABLE poll_techcheck (
  msgid INTEGER PRIMARY KEY REFERENCES message (id),
  cnid INTEGER REFERENCES check_nsset (id)
);

CREATE TABLE poll_stateChange (
  msgid INTEGER PRIMARY KEY REFERENCES message (id),
  stateid INTEGER REFERENCES object_state (id)
);

--
-- COPY the content of notify_new.sql file here
--
\i notify_new.sql

--
-- renaming of technical tests
--
UPDATE check_test SET name = 'existence' WHERE name = 'existance';
UPDATE check_test SET name = 'glue_ok' WHERE name = 'glue';
UPDATE check_test SET name = 'notrecursive' WHERE name = 'recursive';
UPDATE check_test SET name = 'notrecursive4all' WHERE name = 'recursive4all';
--
-- change whether test requires domains on stdin or not
--
UPDATE check_test SET need_domain = 't' WHERE name = 'existence';
UPDATE check_test SET need_domain = 't' WHERE name = 'notrecursive';

--
-- update telephone number in mail footer and vcard attachment
--
UPDATE mail_defaults SET value = '+420 222 745 111' WHERE name = 'tel';
UPDATE mail_vcard SET vcard =
'BEGIN:VCARD
VERSION:2.1
N:podpora CZ. NIC, z.s.p.o.
FN:podpora CZ. NIC, z.s.p.o.
ORG:CZ.NIC, z.s.p.o.
TITLE:zákaznická podpora
TEL;WORK;VOICE:+420 222 745 111
TEL;WORK;FAX:+420 222 745 112
ADR;WORK:;;Americká 23;Praha 2;;120 00;Česká republika
URL;WORK:http://www.nic.cz
EMAIL;PREF;INTERNET:podpora@nic.cz
REV:20070403T143928Z
END:VCARD
';
UPDATE mail_defaults SET value = 'podpora@nic.cz' WHERE name = 'emailsupport';

UPDATE mail_templates SET template = '
Výsledek technické kontroly sady nameserverů <?cs var:handle ?>
Result of technical check on NS set <?cs var:handle ?>

Datum kontroly / Date of the check: <?cs var:checkdate ?>
Číslo žádosti / Ticket: <?cs var:ticket ?>
  
<?cs def:printtest(par_test) ?><?cs if:par_test.name == "existance" ?>Následující nameservery v sadě nameserverů nejsou dosažitelné:
Following nameservers in NS set are not reachable:
<?cs each:ns = par_test.ns ?>    <?cs var:ns ?>
<?cs /each ?><?cs /if ?><?cs if:par_test.name == "autonomous" ?>Sada nameserverů neobsahuje minimálně dva nameservery v různých
autonomních systémech.
In NS set are no two nameservers in different autonomous systems.
  
<?cs /if ?><?cs if:par_test.name == "presence" ?><?cs each:ns = par_test.ns ?>Nameserver <?cs var:ns ?> neobsahuje záznam pro domény:
Nameserver <?cs var:ns ?> does not contain record for domains:
<?cs each:fqdn = ns.fqdn ?>    <?cs var:fqdn ?>
<?cs /each ?><?cs if:ns.overfull ?>    ...
<?cs /if ?><?cs /each ?><?cs /if ?><?cs if:par_test.name == "authoritative" ?><?cs each:ns = par_test.ns ?>Nameserver <?cs var:ns ?> není autoritativní pro domény:
Nameserver <?cs var:ns ?> is not authoritative for domains:
<?cs each:fqdn = ns.fqdn ?>    <?cs var:fqdn ?>
<?cs /each ?><?cs if:ns.overfull ?>    ...
<?cs /if ?><?cs /each ?><?cs /if ?><?cs if:par_test.name == "heterogenous" ?>Všechny nameservery v sadě nameserverů používají stejnou implementaci
DNS serveru.
All nameservers in NS set use the same implementation of DNS server.

<?cs /if ?><?cs if:par_test.name == "recursive" ?>Následující nameservery v sadě nameserverů jsou rekurzivní:
Following nameservers in NS set are recursive:
<?cs each:ns = par_test.ns ?>    <?cs var:ns ?>
<?cs /each ?><?cs /if ?><?cs if:par_test.name == "recursive4all" ?>Následující nameservery v sadě nameserverů zodpověděli rekurzivně dotaz:
Following nameservers in NS set answered recursively a query:
<?cs each:ns = par_test.ns ?>    <?cs var:ns ?>
<?cs /each ?><?cs /if ?><?cs /def ?>
=== Chyby / Errors ==================================================

<?cs each:item = tests ?><?cs if:item.type == "error" ?><?cs call:printtest(item) ?><?cs /if ?><?cs /each ?>
=== Varování / Warnings =============================================

<?cs each:item = tests ?><?cs if:item.type == "warning" ?><?cs call:printtest(item) ?><?cs /if ?><?cs /each ?>
=== Upozornění / Notice =============================================

<?cs each:item = tests ?><?cs if:item.type == "notice" ?><?cs call:printtest(item) ?><?cs /if ?><?cs /each ?>
=====================================================================


                                             S pozdravem
                                             podpora <?cs var:defaults.company ?>
' WHERE id = 16;

UPDATE mail_defaults SET value = 'http://whois.nic.cz' WHERE name = 'whoispage';

UPDATE mail_templates SET template =
'English version of the e-mail is entered below the Czech version

Zaslání autorizační informace

Vážený zákazníku,

   na základě Vaší žádosti podané prostřednictvím webového formuláře
na stránkách sdružení dne <?cs var:reqdate ?>, které
bylo přiděleno identifikační číslo <?cs var:reqid ?>, Vám zasíláme požadované
heslo, příslušející <?cs if:type == #3 ?>k doméně<?cs elif:type == #1 ?>ke kontaktu s identifikátorem<?cs elif:type == #2 ?>k sadě nameserverů s identifikátorem<?cs /if ?> <?cs var:handle ?>.

   Heslo je: <?cs var:authinfo ?>

   V případě, že jste tuto žádost nepodali, oznamte prosím tuto
skutečnost na adresu <?cs var:defaults.emailsupport ?>.

                                             S pozdravem
                                             podpora <?cs var:defaults.company ?>



Sending authorization information

Dear customer,

   Based on your request submitted via the web form on the association
pages on <?cs var:reqdate ?>, which received
the identification number <?cs var:reqid ?>, we are sending you the requested
password that belongs to the <?cs if:type == #3 ?>domain name<?cs elif:type == #1 ?>contact with identifier<?cs elif:type == #2 ?>NS set with identifier<?cs /if ?> <?cs var:handle ?>.

   The password is: <?cs var:authinfo ?>

   If you did not submit the aforementioned request, please, notify us about
this fact at the following address <?cs var:defaults.emailsupport ?>.


                                             Yours sincerely
                                             support <?cs var:defaults.company ?>
'  WHERE id = 1;

INSERT INTO enum_filetype (id, name) VALUES (5, 'expiration warning letter');

--
-- New logic in domain dependant technical tests requires change to db schema
--
ALTER TABLE check_test DROP COLUMN need_domain;
ALTER TABLE check_test ADD COLUMN need_domain SMALLINT NOT NULL DEFAULT 0;
UPDATE check_test SET need_domain = 1 WHERE
	name = 'presence' OR
	name = 'authoritative';
UPDATE check_test SET need_domain = 2 WHERE
	name = 'existence' OR
	name = 'glue_ok' OR
	name = 'notrecursive';

1-7 ->

CREATE INDEX action_clientid_idx ON action (clientid);
CREATE INDEX action_response_idx ON action (response);
CREATE INDEX action_startdate_idx ON action (startdate);
CREATE INDEX action_action_idx ON action (action);
CREATE INDEX history_action_idx ON history (action);
CREATE INDEX login_registrarid_idx ON login (registrarid);

CREATE INDEX invoice_object_registry_objectid_idx 
       ON invoice_object_registry (objectid);
CREATE INDEX invoice_object_registry_price_map_invoiceid_idx 
       ON invoice_object_registry_price_map (invoiceid);
CREATE INDEX invoice_credit_payment_map_invoiceid_idx 
       ON invoice_credit_payment_map (invoiceid);
CREATE INDEX invoice_credit_payment_map_ainvoiceid_idx 
       ON invoice_credit_payment_map (ainvoiceid);