DROP TABLE IF EXISTS `{#}billing_actions`;
CREATE TABLE `{#}billing_actions` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `controller` varchar(32) DEFAULT NULL COMMENT 'Controller name',
  `name` varchar(64) DEFAULT NULL COMMENT 'Action name',
  `title` varchar(255) DEFAULT NULL COMMENT 'Title',
  `prices` text DEFAULT NULL COMMENT 'YAML with prices by group',
  PRIMARY KEY (`id`),
  KEY `controller` (`controller`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Paid actions';

INSERT INTO `{#}billing_actions` (`controller`, `name`, `title`, `prices`) VALUES
('content', 'pages_add', 'Pages: adding', '---\n3: 0\n4: 0\n5: 0\n6: 0\n');

DROP TABLE IF EXISTS `{#}billing_holds`;
CREATE TABLE `{#}billing_holds` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `target` varchar(100) DEFAULT NULL COMMENT 'Operation identifier',
  `user_id` int(11) UNSIGNED NOT NULL COMMENT 'User ID',
  `amount` decimal(10,2) UNSIGNED NOT NULL DEFAULT 0.00 COMMENT 'Amount',
  `payload` text DEFAULT NULL COMMENT 'JSON with operation parameters',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`,`target`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Pending balances';

DROP TABLE IF EXISTS `{#}billing_log`;
CREATE TABLE `{#}billing_log` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `type` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Operation type: 1 - income, 0 - payment',
  `action_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'Paid action ID',
  `date_created` timestamp NULL DEFAULT current_timestamp() COMMENT 'Operation creation date',
  `date_done` timestamp NULL DEFAULT NULL COMMENT 'Operation completion date',
  `amount` decimal(11,2) DEFAULT NULL COMMENT 'Amount in internal currency',
  `summ` decimal(11,2) DEFAULT NULL COMMENT 'Amount in real currency',
  `user_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'User ID',
  `sender_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'User ID of the sender (for transfers)',
  `status` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Operation status: 0 - created, 1 - completed',
  `description` varchar(512) DEFAULT NULL COMMENT 'Operation description',
  `url` varchar(255) DEFAULT NULL COMMENT 'URL of the related purchase',
  `ref_link_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'Referral entry ID from billing_refs table',
  `plan_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'Tariff plan ID',
  `plan_period` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Tariff plan duration',
  `system_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'Payment system ID',
  PRIMARY KEY (`id`),
  KEY `type` (`type`),
  KEY `action_id` (`action_id`),
  KEY `date_created` (`date_created`),
  KEY `user_id` (`user_id`),
  KEY `sender_id` (`sender_id`),
  KEY `status` (`status`),
  KEY `ref_link_id` (`ref_link_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='List of all transactions';

DROP TABLE IF EXISTS `{#}billing_outs`;
CREATE TABLE `{#}billing_outs` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `date_created` timestamp NULL DEFAULT current_timestamp() COMMENT 'Creation date',
  `date_done` timestamp NULL DEFAULT NULL COMMENT 'Completion date',
  `user_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'ID of the user who submitted the request',
  `amount` decimal(11,2) UNSIGNED DEFAULT NULL COMMENT 'Amount in internal currency',
  `summ` decimal(11,2) UNSIGNED DEFAULT NULL COMMENT 'Amount in real currency',
  `system` varchar(64) DEFAULT NULL COMMENT 'Destination of the withdrawal',
  `purse` varchar(32) DEFAULT NULL COMMENT 'Wallet/account number',
  `status` tinyint(1) UNSIGNED DEFAULT 0 COMMENT 'Request status',
  `code` varchar(32) DEFAULT NULL COMMENT 'Withdrawal confirmation code',
  `done_code` varchar(32) DEFAULT NULL COMMENT 'Withdrawal completion code',
  PRIMARY KEY (`id`),
  KEY `date_created` (`date_created`),
  KEY `user_id` (`user_id`),
  KEY `code` (`code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Withdrawal requests';

DROP TABLE IF EXISTS `{#}billing_paid_fields`;
CREATE TABLE `{#}billing_paid_fields` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ctype_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'Content type ID',
  `field` varchar(20) DEFAULT NULL COMMENT 'System name of the field',
  `price_field` varchar(20) DEFAULT NULL COMMENT 'Name of the field containing the price',
  `prices` text DEFAULT NULL COMMENT 'YAML with prices by user group',
  `is_to_author` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Payment goes to the author',
  `is_notify_author` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Notify the author about the purchase',
  `notify_email` varchar(100) DEFAULT NULL COMMENT 'Email for field purchase notifications',
  `btn_titles` text DEFAULT NULL COMMENT 'JSON with button titles',
  PRIMARY KEY (`id`),
  KEY `ctype_id` (`ctype_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Field sales';

DROP TABLE IF EXISTS `{#}billing_paid_fields_log`;
CREATE TABLE `{#}billing_paid_fields_log` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `field_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'ID of the purchasable field from billing_paid_fields',
  `item_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'ID of the content item',
  `user_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'ID of the user who purchased the field',
  `date_sold` timestamp NULL DEFAULT current_timestamp() COMMENT 'Date of purchase',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`item_id`,`field_id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Field sale transactions';

DROP TABLE IF EXISTS `{#}billing_payouts`;
CREATE TABLE `{#}billing_payouts` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `is_enabled` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Enabled?',
  `title` varchar(128) DEFAULT NULL COMMENT 'Payout title',
  `groups` text DEFAULT NULL COMMENT 'User groups eligible for the payout',
  `user_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'User ID for the payout',
  `is_topup_balance` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Top up the balance up to the specified amount',
  `is_passed` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Restrict by time since registration',
  `is_rating` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Restrict by user rating',
  `is_karma` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Restrict by user reputation',
  `is_field` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Restrict by profile field value',
  `passed_days` int(11) UNSIGNED DEFAULT NULL COMMENT 'Minimum days since registration',
  `rating` int(11) DEFAULT NULL COMMENT 'Minimum rating',
  `karma` int(11) DEFAULT NULL COMMENT 'Minimum reputation',
  `field` varchar(64) DEFAULT NULL COMMENT 'Profile field name',
  `field_value` varchar(128) DEFAULT NULL COMMENT 'Required value of the profile field',
  `amount` decimal(11,2) DEFAULT NULL COMMENT 'Fixed payout amount',
  `field_amount` varchar(64) DEFAULT NULL COMMENT 'Payout amount based on this profile field value',
  `period` int(11) UNSIGNED DEFAULT NULL COMMENT 'Payout frequency (in days)',
  `date_last` timestamp NULL DEFAULT NULL COMMENT 'Date of last payout',
  PRIMARY KEY (`id`),
  KEY `is_enabled` (`is_enabled`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Scheduled payouts';

DROP TABLE IF EXISTS `{#}billing_plans`;
CREATE TABLE `{#}billing_plans` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `title` varchar(128) DEFAULT NULL COMMENT 'Plan name',
  `description` text DEFAULT NULL COMMENT 'Plan description',
  `is_enabled` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Enabled?',
  `is_real_price` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Purchase with real currency only',
  `max_out` decimal(11,2) UNSIGNED DEFAULT NULL COMMENT 'Maximum withdrawal amount',
  `groups` text DEFAULT NULL COMMENT 'YAML with target user groups',
  `prices` text DEFAULT NULL COMMENT 'YAML with prices by group',
  `users` int(11) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Number of users subscribed to the plan',
  `ordering` int(11) UNSIGNED DEFAULT NULL COMMENT 'Display order in list',
  PRIMARY KEY (`id`),
  KEY `is_enabled` (`is_enabled`),
  KEY `ordering` (`ordering`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Subscription plans';

DROP TABLE IF EXISTS `{#}billing_plans_log`;
CREATE TABLE `{#}billing_plans_log` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'ID of the user who purchased the subscription',
  `plan_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'ID of the subscription plan',
  `old_groups` text DEFAULT NULL COMMENT 'YAML of the user’s groups before the subscription',
  `date_until` timestamp NULL DEFAULT NULL COMMENT 'Subscription expiration date',
  `is_paused` tinyint(1) UNSIGNED NOT NULL DEFAULT 0 COMMENT 'Inactive plan?',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `date_until` (`date_until`),
  KEY `plan_id` (`plan_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Active subscriptions';

DROP TABLE IF EXISTS `{#}billing_refs`;
CREATE TABLE `{#}billing_refs` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `user_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'ID of the user who registered',
  `ref_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'ID of the user whose referral link was used',
  `level` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Referral level',
  `date_reg` timestamp NULL DEFAULT current_timestamp() COMMENT 'Registration date',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`),
  KEY `ref_id` (`ref_id`),
  KEY `level` (`level`),
  KEY `date_reg` (`date_reg`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Referrals';

DROP TABLE IF EXISTS `{#}billing_systems`;
CREATE TABLE `{#}billing_systems` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(32) DEFAULT NULL COMMENT 'Internal system name of the payment system',
  `title` varchar(64) DEFAULT NULL COMMENT 'Display name of the payment system',
  `payment_url` varchar(255) DEFAULT NULL COMMENT 'External URL of the payment form',
  `rate` decimal(8,4) UNSIGNED DEFAULT 1.0000 COMMENT 'Exchange rate of the payment system currency',
  `options` text DEFAULT NULL COMMENT 'YAML with configuration options',
  `is_enabled` tinyint(1) UNSIGNED DEFAULT NULL COMMENT 'Enabled?',
  `ordering` int(11) UNSIGNED DEFAULT NULL COMMENT 'Sort order in the list',
  PRIMARY KEY (`id`),
  KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Payment systems';

INSERT INTO `{#}billing_systems` (`id`, `name`, `title`, `payment_url`, `rate`, `options`, `is_enabled`, `ordering`) VALUES
(2, 'wmz', 'Webmoney WMZ', 'https://merchant.webmoney.ru/lmi/payment.asp', 1.0000, '---\npurse: \"\"\nsecret_key: \"\"\ntest_mode: \"0\"\n', NULL, 2),
(3, 'robokassa', 'E-money, cards, terminals (Robokassa)', 'billing/prepare/robokassa', 1.0000, '---\nmerchant_login: \"\"\npassword1: \"\"\npassword2: \"\"\nfiscal_on: null\nfiscal_sno: osn\nfiscal_method: full_payment\nfiscal_object: service\nfiscal_name: \"\"\nfiscal_tax: none\n', NULL, 6),
(4, 'interkassa', 'E-money, cards, terminals (Interkassa)', 'https://sci.interkassa.com', 1.0000, '---\nik_co_id: \"\"\nik_secret_key: \"\"\n', NULL, 7),
(8, 'w1', 'E-money, cards, terminals (W1)', 'https://wl.walletone.com/checkout/checkout/Index', 1.0000, '---\nmerchant_id: \"\"\nkey: \"\"\ncurrency_id: \"\"\n', NULL, 8),
(9, 'test', 'Test (instant top-up)', 'billing/prepare/test', 0.1000, NULL, 1, 1),
(10, 'yandex', 'ЮMoney', 'https://yoomoney.ru/quickpay/confirm.xml', 1.0000, '---\nreceiver: \"\"\nsecret_key: \"\"\n', 0, 12),
(11, 'yakassa', 'ЮKassa', 'billing/prepare/yakassa', 1.0000, '---\nshop_id: \"\"\nkey: \"\"\n', NULL, 13),
(14, 'paypal', 'PayPal', 'https://api.sandbox.paypal.com', 1.0000, '---\naccount: \"\"\ncurrency: USD\nclient_id: \"\"\nsecret: \"\"\n', 0, 10),
(16, 'payeer', 'PAYEER', 'https://payeer.com/merchant/', 1.0000, '---\nshop_id: \"\"\nsecret_key: \"\"\nsig_key: \"\"\ncurr: USD\n', NULL, 16);

DROP TABLE IF EXISTS `{#}billing_terms`;
CREATE TABLE `{#}billing_terms` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ctype_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'Content type ID',
  `prices` text DEFAULT NULL COMMENT 'YAML with prices by group',
  PRIMARY KEY (`id`),
  KEY `ctype_id` (`ctype_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Prices for one day publication';

DROP TABLE IF EXISTS `{#}billing_transfers`;
CREATE TABLE `{#}billing_transfers` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `from_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'User ID, from whom the transfer is from',
  `to_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'User ID to whom the transfer is to',
  `amount` decimal(11,2) UNSIGNED DEFAULT NULL COMMENT 'Transfer amount',
  `description` varchar(255) DEFAULT NULL COMMENT 'Operation description',
  `code` varchar(32) DEFAULT NULL COMMENT 'Transfer Confirmation Code',
  `status` tinyint(1) DEFAULT 0 COMMENT 'Transfer status',
  PRIMARY KEY (`id`),
  UNIQUE KEY `code` (`code`) USING BTREE,
  KEY `from_id` (`from_id`),
  KEY `to_id` (`to_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Transfers between users';

DROP TABLE IF EXISTS `{#}billing_vip_fields`;
CREATE TABLE `{#}billing_vip_fields` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `ctype_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'Content type ID',
  `field` varchar(40) DEFAULT NULL COMMENT 'System field name',
  `prices` text DEFAULT NULL COMMENT 'YAML with prices by group',
  `description` varchar(255) DEFAULT NULL COMMENT 'Description for transaction history',
  PRIMARY KEY (`id`),
  KEY `ctype_id` (`ctype_id`),
  KEY `field` (`field`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Fields for sale when filling out';

DROP TABLE IF EXISTS `{#}billing_vip_fields_log`;
CREATE TABLE `{#}billing_vip_fields_log` (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `field_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'Field ID from the billing_vip_fields table',
  `item_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'Content type ID',
  `user_id` int(11) UNSIGNED DEFAULT NULL COMMENT 'User ID of the user who purchased the field filling',
  `date_sold` timestamp NULL DEFAULT NULL COMMENT 'The date of sale',
  PRIMARY KEY (`id`),
  KEY `user_id` (`user_id`,`item_id`,`field_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Purchases to fill out fields';

ALTER TABLE  `{#}users` ADD  `balance` decimal(12,2) DEFAULT 0.00 AFTER  `email`;
ALTER TABLE  `{#}users` ADD  `plan_id` int(11) UNSIGNED DEFAULT NULL AFTER  `balance`;
ALTER TABLE  `{#}users` ADD INDEX (`balance`);
ALTER TABLE  `{#}users` ADD INDEX (`plan_id`);

INSERT INTO  `{#}users_tabs` (`title` ,`controller` ,`name` ,`is_active` ,`ordering`)
VALUES ('Balance',  'billing',  'balance',  '1',  '100');

INSERT INTO `{#}scheduler_tasks` (`title`, `controller`, `hook`, `period`, `date_last_run`, `is_active`, `is_new`) VALUES
('Tracking the end of subscriptions', 'billing', 'relegation', 60, NULL, 1, 1),
('Scheduled payments', 'billing', 'payouts', 720, NULL, 1, 1);

INSERT INTO `{#}controllers` (`title`, `name`, `slug`, `is_enabled`, `options`, `author`, `url`, `version`, `is_backend`, `is_external`, `files`, `addon_id`) VALUES
('Billing', 'billing', NULL, 1, '---\ncurrency_title: points\ncurrency: point|points|points\ncurrency_real: usd\nmin_pack: 0\nreg_bonus: 0\nin_mode: enabled\nprices:\n  - \n    amount: 1\n    price: 1\nis_plans: 1\nplan_remind_days: 3\nis_transfers: 1\nis_transfers_mail: null\nis_transfers_notify: null\nrtp_groups: [ ]\nis_rtp: null\nrtp_rate: 0.5\nis_ptr: 1\nptr_rate: 1\nis_out: null\nout_groups: [ ]\nis_out_mail: 1\nout_period_days: 0\nout_min: 1\nout_rate: 0.5\nout_systems: |\n  Stripe\r\n  PayPal\nout_email: \"\"\nis_refs: 1\nref_days: 100\nref_url: \"\"\nref_terms: \"\"\nref_bonus: 0\nref_mode: all\nref_type: linear\nref_scale: 2\nref_levels:\n  - \n    percent: 1\ncur_real_symb: $\nis_refs_as_invite: 1\npay_field_html: \'<a class=\"btn btn-primary billing-buy-field\" href=\"{url}\">{solid%coins} {title}</a>\'\nlimit_log: 15\nlimit_out: 15\nlimit_refs: 15\nbtn_titles:\n  guest: \'Buying from {price}\'\n  user: \'Buy for {price}\'\n', 'InstantCMS Team', 'https://instantcms.io', '2.1.0', 1, NULL, NULL, NULL);

INSERT INTO `{#}events` (`event`, `listener`, `ordering`, `is_enabled`) VALUES
('admin_users_filter', 'billing', 244, 1),
('content_add', 'billing', 245, 1),
('content_after_add_approve', 'billing', 246, 1),
('content_after_update_approve', 'billing', 247, 1),
('content_before_item', 'billing', 249, 1),
('content_edit', 'billing', 250, 1),
('content_validate', 'billing', 251, 1),
('cron_payouts', 'billing', 252, 1),
('cron_relegation', 'billing', 253, 1),
('ctype_after_add', 'billing', 254, 1),
('ctype_after_delete', 'billing', 255, 1),
('grid_admin_users', 'billing', 257, 1),
('menu_billing', 'billing', 258, 1),
('user_delete', 'billing', 259, 1),
('user_profile_buttons', 'billing', 260, 1),
('user_registered', 'billing', 261, 1),
('user_tab_info', 'billing', 262, 1),
('user_tab_show', 'billing', 263, 1),
('engine_start', 'billing', 264, 1),
('content_after_add', 'billing', 265, 1),
('content_after_update', 'billing', 266, 1),
('content_after_delete', 'billing', 267, 1),
('moderation_cancel', 'billing', 268, 1),
('moderation_rework', 'billing', 269, 1);