USE internationaliser;
/**
  * translations into various languages.
  * <><><><><><><><><><><><><><><><><><>
  * This is the core table, and the most voluminoous one.
  * All the others are just housekeeping for this one.
  * Each record handles translation of one key into one locale language/country/variant.
  */
DROP TABLE IF EXISTS translations;
CREATE TABLE translations (
  /**
   * compactly encodes which bundle this translation belongs to. encode, project, ResourceBundle, locale
   */
   uniqueBundleID INTEGER NOT NULL,
   /**
    * the key the Java source program uses to tie together all the translations bundlename.getString( "key" )
    * This is case sensitive. You may use any combination of upper and lower case.
    * You can use any convention you like including adding punctuation characters like . and _
    * to create a hierarchy e.g. SEARCH.RESULTS.WEB.FOUND_DOCUMENTS
    * In the projects table, you can configure a project-wide REGEX
    * that determines if a translationKey is valid.
    * Dots in the key indicate levels of hierarchy.
    */
   translationKey VARCHAR( 100 ) NOT NULL,
   /**
    * the translated value of the string into the bundle's language and country.
    */
   translation VARCHAR( 20000 ),
  /**
    * programmer who created, or last modified the translation key
    */
   programmerInitials CHAR ( 4 ),
   /**
    * translator who last changed this translation, or its stage
    */
   translatorInitials CHAR ( 4 ),
    /**
    * proofreader who last changed this translation, or its stage.
    */
   proofreaderInitials CHAR ( 4 ),
   /**
    * when this record was last updated, either by a new comment or a new translation
    */
   lastChanged TIMESTAMP NOT NULL,
   /**
    * stage codes, upper case
    * U = untranslated
    * ? = unsure of translation
    * T = translated
    * P = proofread  (only proofreader/admin can set P or C status)
    * C = complete (proofread in context of the actual program making sure it fits in the box, looks good etc.)
    */
   stage ENUM( 'U','?','T','P', 'C' )  NOT NULL,
   /**
    * how important is it to get this translation done quickly? 1 .. 5.
    * 1=top priority  5=low priority
    */
   priority SMALLINT,
   /**
    * used by a separate  billing program to track the fact a given translation has been paid.
    * This prevents paying translater twice for the same translation.
    * We don't track who was paid or when. That is the function of a separate billing program.
    * use dateTranslotePaid not equal NULL rather that having a separate field.
    * wasTranslatorPaid BOOLEAN  NOT NULL,
    */
   /**
    * initials of translator that was paid for doing this translation.
    */
   paidTranslator CHAR ( 4 ),
   /**
    * date the proofreader was paid, actually the date the billing program extracted the information
    * about this translation to generate the payments.  NULL means not yet paid.
    */
   dateTranslatorPaid DATE,
   /**
    * initials of proofreader who was paid for doing this translation
    */
   paidProofreader CHAR ( 4 ),
   /**
    * date the proofreader was paid, actually the date the billing program extracted the information
    * about this translation generate the payments. NULL means not yet billed.
    */
   dateProofreaderPaid DATE,
   /**
    * date the customer was billed, actually the date the billing program extracted the information
    * about this translation to generate the billing.  NULL means not yet billed.
    */
   dateCustomerBilled DATE,
   /**
    * automatically generated used to help in paying translators or billing translations.
    * Counts non-blank characters. The internationaliser creates this field but does nothing
    * with it itself.
    * CALCULATED AS NEEDED
    * characterCount INTEGER  NOT NULL,
    */
  /**
    * automatically generated used to help in paying translators or billing translations.
    * Counts words. The internationaliser creates this field but does nothing
    * with it itself.
    * CALCULATED AS NEEDED
    * wordCount INTEGER  NOT NULL,
    */
   /**
    * we find records by bundle/translationKey
    */
   PRIMARY KEY( uniqueBundleID, translationKey ),
   FOREIGN KEY( uniqueBundleID ) REFERENCES bundles( uniqueBundleID )
   );