CREATE TABLE bill_data_$mid
(
	id int(10) NOT NULL auto_increment, 
    cid int(10) NOT NULL,
    type int(11) NOT NULL,
    account_id int(11) NOT NULL,
    number int(11) NOT NULL,
    number_in_month int(11) NOT NULL,
    number_in_year int(11) NOT NULL,
    format_number varchar(15) NOT NULL,
    uid int(11) NOT NULL,
    yy int(11) NOT NULL,
    mm int(11) NOT NULL,
    create_dt date,
    pay_dt date,
    status tinyint(2) NOT NULL,
    unload_status int(11) NOT NULL, 
    summ decimal(12,2) NOT NULL, 
    xml MEDIUMBLOB NOT NULL, 
    PRIMARY KEY id (id), 
    KEY cid (cid), 
    KEY create_dt (create_dt),
    KEY yy(yy),
    KEY mm(mm)
);

CREATE TABLE bill_invoice_data_$mid
(
	id int(10) NOT NULL auto_increment,
    cid int(10) NOT NULL,
    type int(11) NOT NULL,
    number int(11) NOT NULL,
    number_in_month int(11) NOT NULL,
    number_in_year int(11) NOT NULL,
    format_number varchar(15) NOT NULL,
    yy int(11) NOT NULL,
    mm int(11) NOT NULL,
    create_dt date,
    unload_status int(11) NOT NULL, 
    summ decimal(12,2) NOT NULL,
    show_ready TINYINT NOT NULL, 
    xml MEDIUMBLOB NOT NULL, 
    PRIMARY KEY id (id), 
    KEY cid (cid), 
    KEY create_dt (create_dt),
    KEY yy (yy),
    KEY mm (mm)
);

CREATE TABLE bill_attribute_$mid 
(
	id int(10) NOT NULL auto_increment,
	cid int(10) NOT NULL, 
    name varchar(255) NOT NULL, 
	value varchar(255) NOT NULL,
    start date, 
    end date,
	PRIMARY KEY id (id), 
    KEY cid (cid)
);

CREATE TABLE bill_account_$mid
(
	id int(11) NOT NULL auto_increment,
    title varchar(255) NOT NULL,
    bank_title varchar(255) NOT NULL,
    account varchar(30) NOT NULL,
    bik varchar(30) NOT NULL,
    corr_account varchar(30) NOT NULL,
    pid int(11) NOT NULL,
    gr bigint(20) NOT NULL,
    comment text, 
    PRIMARY KEY id(id)
);

CREATE TABLE bill_contract_doc_type_$mid
(
	id int(11) NOT NULL auto_increment,
    cid int(11) NOT NULL default '0',
    doc_type int(11) NOT NULL default '0',
    PRIMARY KEY  (id),
    KEY cid (cid)
);

CREATE TABLE bill_doc_type_$mid
(
	id int(11) NOT NULL auto_increment,
    type tinyint(4) NOT NULL default '0',
    title varchar(200) NOT NULL default '',
    pos_list varchar(100) NOT NULL default '',
    comment varchar(250) NOT NULL default '',
    PRIMARY KEY (id) 
);

ALTER TABLE bill_invoice_data_$mid ADD show_ready TINYINT NOT NULL AFTER summ;

ALTER TABLE bill_data_$mid ADD `type` INT NOT NULL DEFAULT '0';
ALTER TABLE bill_invoice_data_$mid ADD `type` INT NOT NULL DEFAULT '0';
ALTER TABLE bill_doc_type_$mid ADD pattern VARCHAR(200)  NOT NULL AFTER title;

ALTER TABLE bill_doc_type_$mid ADD opt TINYINT NOT NULL AFTER pos_list;

ALTER TABLE bill_data_$mid change xml xml MEDIUMBLOB NOT NULL;
ALTER TABLE bill_invoice_data_$mid change xml xml MEDIUMBLOB NOT NULL;

ALTER TABLE bill_data_$mid MODIFY summ DECIMAL(12,2) NOT NULL;
ALTER TABLE bill_invoice_data_$mid MODIFY summ DECIMAL(12,2) NOT NULL;
ALTER TABLE bill_doc_type_$mid ADD setup TEXT;
ALTER TABLE `bill_data_$mid` ADD COLUMN `payment_id` INTEGER  NOT NULL;
ALTER TABLE bill_doc_type_$mid MODIFY COLUMN pos_list TEXT NOT NULL;

ALTER TABLE bill_doc_type_$mid ADD COLUMN npid INTEGER DEFAULT 1;
UPDATE bill_doc_type_$mid SET npid=2 WHERE type=2;
ALTER TABLE bill_data_$mid ADD COLUMN npid INTEGER DEFAULT 1;
ALTER TABLE bill_invoice_data_$mid ADD COLUMN npid INTEGER DEFAULT 2;

-- #BLOCK#
CREATE TABLE bill_numeration_pool_$mid
(
  id int(11) NOT NULL AUTO_INCREMENT,
  title varchar(50) DEFAULT NULL,
  pattern varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
);
INSERT INTO bill_numeration_pool_$mid(title, pattern) VALUES('По умолчанию (счета)', '');
INSERT INTO bill_numeration_pool_$mid(title, pattern) VALUES('По умолчанию (счета-фактуры)', '');
-- #ENDB#

ALTER TABLE bill_data_$mid MODIFY format_number VARCHAR(30) NOT NULL;
ALTER TABLE bill_invoice_data_$mid MODIFY format_number VARCHAR(30) NOT NULL;

ALTER TABLE bill_data_$mid	ADD COLUMN pay_uid int(11) NULL;


-- Пусть события идут всегда в конце
-- #BLOCK#
DELETE FROM script_event_type WHERE mid=$mid;

SET @mid=$mid, @old_event_id=1, @new_event_id='ru.bitel.bgbilling.modules.bill.server.events.BillCreatedEvent', @event_title='Создан счет';
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES (@mid, @new_event_id, 1, @event_title);
INSERT INTO script_function_event_type(fid, mid, event_id) SELECT sfet1.fid, sfet1.mid, @new_event_id FROM script_function_event_type AS sfet1 LEFT JOIN script_function_event_type AS sfet2 ON sfet1.fid=sfet2.fid AND sfet1.mid=sfet2.mid AND sfet2.event_id=@new_event_id WHERE sfet1.mid=@mid AND sfet1.event_id=@old_event_id AND sfet2.fid IS NULL;

SET @mid=$mid, @old_event_id=2, @new_event_id='ru.bitel.bgbilling.modules.bill.server.events.InvoiceCreatedEvent', @event_title='Создан счет-фактура';
INSERT INTO script_event_type (mid, event_id, event_mode, title) VALUES (@mid, @new_event_id, 1, @event_title);
INSERT INTO script_function_event_type(fid, mid, event_id) SELECT sfet1.fid, sfet1.mid, @new_event_id FROM script_function_event_type AS sfet1 LEFT JOIN script_function_event_type AS sfet2 ON sfet1.fid=sfet2.fid AND sfet1.mid=sfet2.mid AND sfet2.event_id=@new_event_id WHERE sfet1.mid=@mid AND sfet1.event_id=@old_event_id AND sfet2.fid IS NULL;
-- #ENDB#

