CREATE TABLE `charges` (
  `id` int NOT NULL,
  `invoice_id` int NOT NULL DEFAULT '0',
  `line_id` int NOT NULL DEFAULT '0',
  `invoice_date` date DEFAULT NULL,
  `direction` enum('Inbound','Outbound') DEFAULT NULL,
  `message_date` datetime DEFAULT NULL,
  `message_time` time DEFAULT NULL,
  `message_id` varchar(50) DEFAULT NULL,
  `source` varchar(20) DEFAULT NULL,
  `destination` varchar(20) DEFAULT NULL,
  `message_type` varchar(50) DEFAULT NULL,
  `brand_id` int DEFAULT '0',
  `campaign_id` int DEFAULT '0',
  `campaign_class` char(1) DEFAULT NULL,
  `disposition_code` int DEFAULT NULL,
  `status_description` varchar(100) DEFAULT NULL,
  `error_code` varchar(50) DEFAULT NULL,
  `message_count` int DEFAULT NULL,
  `message_length` int DEFAULT NULL,
  `transit_rate` decimal(10,8) DEFAULT NULL,
  `transit_charge` decimal(10,8) DEFAULT NULL,
  `termination_rate` decimal(10,8) DEFAULT NULL,
  `termination_charge` decimal(10,8) DEFAULT NULL,
  `account_id` int DEFAULT NULL,
  `operator_id` int DEFAULT NULL,
  `region_id` int DEFAULT NULL,
  `country_id` int DEFAULT NULL,
  `class_id` int DEFAULT NULL,
  `gateway_id` int DEFAULT NULL,
  `is_sinch` int NOT NULL DEFAULT '1',
  `is_infobip` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `charges_invoices` (
  `id` int NOT NULL,
  `invoice_date` date NOT NULL,
  `is_sinch` int NOT NULL DEFAULT '1',
  `is_infobip` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `charge_invoice_summary` (
  `id` int UNSIGNED NOT NULL,
  `invoice_id` int UNSIGNED NOT NULL,
  `total_incoming` int UNSIGNED DEFAULT '0',
  `total_outgoing` int UNSIGNED DEFAULT '0',
  `total_transit_charge` decimal(12,6) DEFAULT '0.000000',
  `total_termination_charge` decimal(12,6) DEFAULT '0.000000',
  `total_charge` decimal(12,6) DEFAULT '0.000000',
  `is_sinch` int NOT NULL DEFAULT '1',
  `is_infobip` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE `charge_line_summary` (
  `id` int UNSIGNED NOT NULL,
  `invoice_id` int UNSIGNED NOT NULL,
  `line_id` int UNSIGNED NOT NULL,
  `account_id` int UNSIGNED NOT NULL,
  `total_incoming` int UNSIGNED DEFAULT '0',
  `total_outgoing` int UNSIGNED DEFAULT '0',
  `total_transit_charge` decimal(12,6) DEFAULT NULL,
  `total_termination_charge` decimal(12,6) DEFAULT NULL,
  `total_charge` decimal(12,6) DEFAULT '0.000000',
  `is_sinch` int NOT NULL DEFAULT '1',
  `is_infobip` int NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


ALTER TABLE `charges`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `charges_invoices`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `charge_invoice_summary`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `invoice_id` (`invoice_id`);

ALTER TABLE `charge_line_summary`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `invoice_id` (`invoice_id`,`line_id`);


ALTER TABLE `charges`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `charges_invoices`
  MODIFY `id` int NOT NULL AUTO_INCREMENT;

ALTER TABLE `charge_invoice_summary`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;

ALTER TABLE `charge_line_summary`
  MODIFY `id` int UNSIGNED NOT NULL AUTO_INCREMENT;



  CREATE TABLE infobip_charges (
  id bigint NOT NULL,
  invoice_id int NOT NULL,
  line_id int NOT NULL,
  message_id varchar(50) NOT NULL,
  bulk_id varchar(50) DEFAULT NULL,
  message_date datetime(3) NOT NULL,
  source varchar(20) NOT NULL,
  destination varchar(20) NOT NULL,
  status_group varchar(50) DEFAULT NULL,
  status varchar(50) DEFAULT NULL,
  price_per_message decimal(10,6) DEFAULT NULL,
  currency char(3) DEFAULT NULL,
  message_count int DEFAULT '1',
  total_price decimal(12,6) DEFAULT NULL,
  channel varchar(20) DEFAULT 'SMS',
  mccmnc varchar(10) DEFAULT NULL,
  operator varchar(100) DEFAULT NULL,
  error_description varchar(255) DEFAULT NULL,
  text text,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE infobip_invoices (
  id int NOT NULL,
  invoice_date date NOT NULL,
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE infobip_invoice_summary (
  id int NOT NULL,
  invoice_id int NOT NULL,
  total_outgoing int UNSIGNED DEFAULT '0',
  total_charge decimal(14,6) DEFAULT '0.000000',
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

CREATE TABLE infobip_line_summary (
  id int NOT NULL,
  invoice_id int NOT NULL,
  line_id int NOT NULL,
  account_id int DEFAULT NULL,
  total_outgoing int UNSIGNED DEFAULT '0',
  total_charge decimal(12,6) DEFAULT '0.000000',
  created_at timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;


ALTER TABLE infobip_charges
  ADD PRIMARY KEY (id),
  ADD KEY idx_invoice_line (invoice_id,line_id),
  ADD KEY idx_invoice_date (invoice_id,message_date),
  ADD KEY idx_source_dest (source,destination);

ALTER TABLE infobip_invoices
  ADD PRIMARY KEY (id),
  ADD UNIQUE KEY uniq_invoice_date (invoice_date);

ALTER TABLE infobip_invoice_summary
  ADD PRIMARY KEY (id),
  ADD UNIQUE KEY uniq_invoice (invoice_id);

ALTER TABLE infobip_line_summary
  ADD PRIMARY KEY (id),
  ADD UNIQUE KEY uniq_invoice_line (invoice_id,line_id),
  ADD KEY idx_invoice (invoice_id);


ALTER TABLE infobip_charges
  MODIFY id bigint NOT NULL AUTO_INCREMENT;

ALTER TABLE infobip_invoices
  MODIFY id int NOT NULL AUTO_INCREMENT;

ALTER TABLE infobip_invoice_summary
  MODIFY id int NOT NULL AUTO_INCREMENT;

ALTER TABLE infobip_line_summary
  MODIFY id int NOT NULL AUTO_INCREMENT;