<?xml version="1.0" encoding="UTF-8"?>
<databaseChangeLog
    xmlns="http://www.liquibase.org/xml/ns/dbchangelog"
    xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog
        http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-4.20.xsd">

  <!-- 
    CONSOLIDATED BILLABLE_USAGE_REMITTANCE TABLE CREATION SCRIPT
    
    This script consolidates all changes made to the billable_usage_remittance table
    from the original creation (2022-05-23) through the latest changes (2025-04-29).
    
    Running this single script will create the table in its final state, equivalent 
    to running all the individual migration scripts in chronological order.

  <changeSet id="XXX-001" author="AUTHOR">
    <comment>Create billable_usage_remittance table with all consolidated changes</comment>
    <createTable tableName="billable_usage_remittance">
      <column name="account_number" type="VARCHAR(32)">
        <constraints nullable="true"/>
      </column>
      <column name="org_id" type="VARCHAR(32)">
        <constraints nullable="false"/>
      </column>
      <column name="product_id" type="VARCHAR(32)">
        <constraints nullable="false"/>
      </column>
      <column name="metric_id" type="VARCHAR(255)">
        <constraints nullable="false"/>
      </column>
      <column name="accumulation_period" type="VARCHAR(255)">
        <constraints nullable="false"/>
      </column>
      <column name="sla" type="VARCHAR(255)">
        <constraints nullable="false"/>
      </column>
      <column name="usage" type="VARCHAR(255)">
        <constraints nullable="false"/>
      </column>
      <column name="billing_provider" type="VARCHAR(255)">
        <constraints nullable="false"/>
      </column>
      <column name="billing_account_id" type="VARCHAR(255)">
        <constraints nullable="false"/>
      </column>
      
      <column name="remitted_value" type="double precision">
        <constraints nullable="false"/>
      </column>
      <column name="remittance_date" type="TIMESTAMP WITH TIME ZONE">
        <constraints nullable="false"/>
      </column>
      
      <column name="billing_factor" type="double precision" defaultValueNumeric="1.0">
        <constraints nullable="false"/>
      </column>
      
      <column name="version" type="INT" defaultValueNumeric="1">
        <constraints nullable="false"/>
      </column>
      
      <column name="retry_after" type="TIMESTAMP WITH TIME ZONE">
        <constraints nullable="true"/>
      </column>
      
      <column name="tally_id" type="UUID">
        <constraints nullable="true"/>
      </column>
      
      <column name="uuid" type="UUID">
        <constraints nullable="false"/>
      </column>
      
      <column name="billed_on" type="TIMESTAMP WITH TIME ZONE">
        <constraints nullable="true"/>
      </column>
      <column name="error_code" type="VARCHAR(255)">
        <constraints nullable="true"/>
      </column>
      <column name="status" type="VARCHAR(255)">
        <constraints nullable="true"/>
      </column>
      
      <column name="updated_at" type="timestamp">
        <constraints nullable="true"/>
      </column>
    </createTable>
    
    <addPrimaryKey constraintName="billable_usage_remittance_pkey"
                   tableName="billable_usage_remittance"
                   columnNames="uuid"/>
    
    <addUniqueConstraint columnNames="org_id, product_id, metric_id, accumulation_period, sla, usage, billing_provider, billing_account_id, remittance_date"
                         constraintName="billable_usage_remittance_unique" 
                         tableName="billable_usage_remittance"/>
    
    <createIndex tableName="billable_usage_remittance" indexName="billable_usage_retry_after_idx">
      <column name="retry_after"/>
    </createIndex>
    
  </changeSet>
</databaseChangeLog>