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;

-- #BLOCK#
DELETE FROM script_event_type WHERE mid=$mid;
INSERT INTO script_event_type (mid, event_id, title) VALUES ($mid, 1, ' ');
INSERT INTO script_event_type (mid, event_id, title) VALUES ($mid, 2, ' -');
-- #ENDB#

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;
ALTER TABLE `bill_data_$mid` ADD INDEX `number`(`number`), ADD INDEX `number_in_month`(`number_in_month`), ADD INDEX `number_in_year`(`number_in_year`), ADD INDEX `npid`(`npid`);
ALTER TABLE `bill_invoice_data_$mid` ADD INDEX `number`(`number`), ADD INDEX `number_in_month`(`number_in_month`), ADD INDEX `number_in_year`(`number_in_year`), ADD INDEX `npid`(`npid`);