AccountType:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
      id:         { type: string(1), primary: true}
      name:       { type: string(25) }
      globally_vis: { type: boolean }

Account:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    name:               { type: string(75), notnull:true }
    nickname:           { type: string(40) }
    site_id:            { type: string(2), notnull: true, default: 'BD'}
    bill_address:       { type: string(100) }
    bill_address2:      { type: string(100) }
    bill_city:          { type: string(50) }
    bill_zone_id:       { type: integer(4) }
    bill_postcode:      { type: string(20) }
    bill_country_id:    { type: string(2), default: 'US' }
    ship_address:       { type: string(100) }
    ship_address2:      { type: string(100) }
    ship_city:          { type: string(50) }
    ship_zone_id:       { type: integer(4) }
    ship_postcode:      { type: string(20) }
    ship_country_id:    { type: string(2), default: 'US' }
    phone:              { type: string(20) }
    phone_alt:          { type: string(20) }
    fax:                { type: string(20) }
    email:              { type: string(100) }
    website:            { type: string(100) }
    quickbooks_listid:  { type: string(25) }
    employee_id:        { type: integer(4) }
    account_type_id:    { type: string(1) }
    offers_install:     { type: boolean, default: 0 }
    roundPOFootage:     { type: boolean, default: 0 } # does this vendor round to nearest whole footage on PO?
    no_employees:       { type: integer(3) }
    no_locations:       { type: integer(3) }
    campaign_id:        { type: int(4) }
    annual_revenue:     { type: string(10)}
    description:        { type: clob(65532) }
  relations:
    AccountType:    { local: account_type_id, foreign: id }
    #EmployeeID {}
    BillZone:       { class: Zone, local: bill_zone_id, foreign: id}
    BillCountry:    { class: Country, local: bill_country_id, foreign: id }
    Campaign:       { local: campaign_id, foreign: id }
    Employee:       { class: sfGuardUser, local: employee_id, foreign: id }
    ShipZone:       { class: Zone, local: ship_zone_id, foreign: id }
    ShipCountry:    { class: Country, local: ship_country_id, foreign: id }
    Site:           { local: site_id, foreign: id }
    Warehouse:      { class:Warehouse, refClass: AccountWarehouse, local: account_id, foreign: warehouse_id, foreignAlias: Accounts }

AccountNote:
  connection: doctrine
  inheritance:
      extends: Note
      type: column_aggregation
      keyField: parent_type
      keyValue: account

AccountWarehouse:
  connection: doctrine
  actAs: [ SoftDelete ]
  options:
    symfony:
      form: false
      filter: false
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    account_id:     { type: integer(4) }
    warehouse_id:   { type: integer(4) }
  relations:
    Account:        { local: account_id, foreign: id }
    Warehouse:      { local: warehouse_id, foreign: id }

AddressBook:
  connection: doctrine
  options:
    symfony:
      form: false
      filter: false
  columns:
      id:             { type: integer(4), primary: true, autoincrement: true }
      contact_id:     { type: integer(4), primary: true }
  relations:
      Contact:        { local: contact_id, foreign: id }
        
Warehouse:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:                   { type: integer(4), primary: true, autoincrement: true }
    name:                 { type: string(100), notnull: true }
    nickname:             { type: string(50) }
    address:              { type: string(100) }
    address2:             { type: string(100) }
    city:                 { type: string(50), notnull: true }
    zone_id:              { type: integer(4), notnull: true }
    postcode:             { type: string(20), notnull: true }
    country_id:           { type: string(2), notnull: true, default: 'US' }
    contact:              { type: string(50) }
    email:                { type: string(100) }
    breaks_pallets:       { type: boolean, default: true }
    break_fee:            { type: decimal(6) }
    break_fee_notes:      { type: string(255) }
    rush_fee:             { type: decimal(6) }
    pickup_after:         { type: string(20) } #time
    pickup_before:        { type: string(20) } #time
    phone:                { type: string(20) }
    phone_alt:            { type: string(20) }
    fax:                  { type: string(20) }
    fax_alt:              { type: string(20) }
    customer_can_pickup:  { type: boolean }
    appointment_required: { type: boolean }
    hr_notice_req:        { type: string(2) }
  relations:
    Account:    { class: Account, refClass: AccountWarehouse, local: warehouse_id, foreign: account_id, foreignAlias: Warehouses }
    Country:   { local: country_id, foreign: id }
    Zone:       { local: zone_id, foreign: id }

## Contacts    
Address:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    first_name:         { type: string(75) }
    last_name:          { type: string(75) }
    company:            { type: string(150) }
    address:            { type: string(100) }
    address2:           { type: string(100) }
    city:               { type: string(50), notnull: true }
    zone_id:            { type: integer(4), notnull: true }
    postcode:           { type: string(20), notnull: true }
    country_id:         { type: string(2), notnull: true, default: 'US' }
    contact_id:         { type: integer(4) } #, notnull: true }
    location_type_id:   { type: integer(1), notnull: true, default: '1' }
    phone_id:           { type: integer(5) }
    phone_alt_id:       { type: integer(5) }
    in_address_book:    { type: boolean }
    edit_locked:        { type: boolean, default: false } #is this address locked from edits bc
                                                          #it's used in previous orders?
  relations:    
    Phone:          { class: Phone, local: phone_id, foreign: id }
    PhoneAlt:       { class: Phone, local: phone_alt_id, foreign: id }
    #Contact:        { local: contact_id, foreign: id }
    Contact:        { foreignAlias: Address, type: one }
    Country:        { local: country_id, foreign: id }
    LocationType:   { local: location_type_id, foreign: id}
    Zone:           { local: zone_id, foreign: id }


      
      
Contact:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:                     { type: integer(4), primary: true, autoincrement: true }
    account_id:             { type: integer(4) }
    first_name:             { type: string(50), notnull: true }
    last_name:              { type: string(50), notnull: true }
    email:                  { type: string(100) }
    title:                  { type: string(50) }
    opt_in_email:           { type: boolean, default: 1 }
    opt_in_fax:             { type: boolean, default: 0 }
    opt_in_phone:           { type: boolean, default: 1 }
    blacklist:              { type: boolean, default: 0 }
    in_email_autocomplete:  { type: boolean, default: 0 }
    default_address_id:     { type: integer(4) }
    sf_guard_user_id:       { type: integer(4) }
    employee_id:            { type: integer(4) }
    quickbooks_listid:      { type: string(25) }
    site_id:                { type: string(2), default: 'BD' }
    #reports_to:            { type: integer(4) }
    campaign_id:            { type: integer(4) }
    is_repeat:              { type: boolean }
    prefers_phone:          { type: boolean }
  relations:
    Account:            { local: account_id, foreign: id, foreignAlias: Contacts }
    Campaign:           { local: campaign_id, foreign: id, foreignAlias: Campaign }
    sfGuardUser:        { local: sf_guard_user_id, foreign: id, foreignAlias: Profile, type: one, foreignType: one, class: sfGuardUser, onDelete: cascade }          
    Employee:           { class: sfGuardUser, local: employee_id, foreign: id }
    DefaultAddress:     { class: Address, local: default_address_id, foreign: id, foreignType: one, alias: DefaultAddress, foreignAlias: ContactMain }
    #Phone:              { local: id, foreign: contact_id, type: many, foreignType: one } #, type: many, foreignType: one
    Site:               { local: site_id, foreign: id }
    #Supervisor:     { class: Contact, local: reports_to, foreign: id, foreignAlias: Supervisor }
    #RecipientEmailMessages: { class: Email, local: recipient_id, foreign: message_id,  refClass: EmailRecipient }
    #RecipientToMessages:    { class: Email, local: recipient_id, foreign: message_id, refClass: ToRecipient }
    #RecipientCcMessages:    { class: Email, local: recipient_id, foreign: message_id, refClass: CcRecipient }
    #RecipientBccMessages:   { class: Email, local: recipient_id, foreign: message_id, refClass: BccRecipient }
      
ContactNote:
  connection: doctrine
  inheritance:
    extends: Note
    type: column_aggregation
    keyField: parent_type
    keyValue: contact

Phone:
  connection: doctrine
  actAs:  [ SoftDelete ]
  columns:
    id:          { type: integer(5), primary: true, autoincrement: true }
    contact_id:  { type: integer(4) }
    number:      { type: string(20) }
    type:        { type: enum, values: [Home, Work, Cell, Fax] }  
  relations:
    Contact: { foreignAlias: Phone }
    
## Geography
Country:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:             { type: string(2), primary: true }
    name:           { type: string(255), notnull: true, unique: false }
    iso_code_3:     { type: string(3), notnull: true, unique: true }
    iso_num:        { type: string(3), notnull: true, unique: true }
    address_format: { type: string(255), notnull: false }

GeoZone:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:          { type: integer(4), primary: true, autoincrement:true }
    name:        { type: string(32) }
    description: { type: string(150) }

Zone:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    name:           { type: string(255), notnull: true, unique: false }
    code:           { type: string(32), notnull: true, unique: false }
    country_id:     { type: string(2), notnull: true, unique: false }
  relations:
      Country:        { local: country_id, foreign: id }

ZoneGeoZone:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    country_id:     { type: string(2), primary: true }
    zone_id:        { type: integer(4), primary: true }
    geo_zone_id:    { type: integer(4), primary: true }
  relations:
    Country:        { local: country_id, foreign: id }
    Zone:           { local: zone_id, foreign: id }
    GeoZone:        { local: geo_zone_id, foreign: id }

## Divider
EmployeeProfile:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:               { type: integer(4), primary: true, autoincrement: true}
    contact_id:       { type: integer(4) }
    sf_guard_user_id: { type: integer(4) }
    site_id:          { type: string(2) }
    public_name:      { type: string(50) }
    title:            { type: string(50) }
    email:            { type: string(60) }
    smtp_s:           { type: string(60) }
    smtp_u:           { type: string(60) }
    smtp_p:           { type: string(60) }
    click2call:       { type: string(125)}
    signature:        { type: clob(65532) }
    phone_toll_free:  { type: string(20) }
    phone_alt:        { type: string(20) }
  indexes:
    contact_site:
      fields: [contact_id, site_id]
      type:   unique
  relations:
    Contact:        { local: contact_id, foreign: id }
    Site:           { local: site_id, foreign: id,  foreignAlias: Site }
    sfGuardUser:    { class: sfGuardUser, local: sf_guard_user_id, foreign: id }



LocationType:
  connection: doctrine
  columns:
    id:         { type: integer(1), primary: true, autoincrement: true }
    name:       { type: string(50) }
    fq_id:      { type: string(10) }
    list_order: { type: integer(2) }

Note:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
      message:     { type: clob(65532) }
      parent_type: { type: string(20) }   
      parent_id:   { type: integer(4) }

Newsletter:
  connection: doctrine
  columns:
    id:           { type: integer(3), primary: true, autoincrement: true }
    name:         { type: string(50) }
    description:  { type: string(255) }
    site_id:      { type: string(2) }
  relations:
    Site:         { local: site_id, foreign: id }

NewsletterCampaign:
  connection: doctrine
  actAs: [ Timestampable ]
  columns:  
    name:           { type: string(100) }
    reference_code: { type: string(50)  }
    site_id:        { type: string(2)   }
    newsletter_id:  { type: integer(3)  }
    opens:          { type: integer(4)  }
    clicks:         { type: integer(4)  }
    sent:           { type: integer(4)  }
    criteria:       { type: string(255) }
    template_special_id:     { type: integer(4) }
  relations:
    Newsletter:       { local: newsletter_id, foreign: id }
    TemplateSpecial:  { local: template_special_id, foreign: id}
    Site:             { local: site_id, foreign: id }
    
NewsletterSubscriber:
  connection: doctrine
  actAs: [ Timestampable ]
  columns:
    contact_id:     { type: integer(4) }
    newsletter_id:  { type: integer(3) }
  relations:
    Contact:    { local: contact_id, foreign: id }
    Newsletter: { local: newsletter_id, foreign: id }

NewsletterCampaignSubscriber:
  connection: doctrine
  columns:
    newsletter_campaign_id:   { type: integer }
    newsletter_subscriber_id: { type: integer }
  relations:
    NewsletterSubscriber: { local: newsletter_campaign_id, foreign: id }
    NewsletterCampaign: { local: newsletter_subscriber_id, foreign: id }
    
#### Products Section
AttributeSet:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
      id:             { type: integer(4), primary: true, autoincrement: true }
      name:           { type: string(255), notnull: true }

AttributeSetProductAttribute:
  connection: doctrine
  actAs: [ SoftDelete ]
  options:
    symfony:
      form: false
      filter: false
  columns:
      id:                   { type: integer(1), primary: true, autoincrement: true }
      attribute_set_id:     { type: integer(4) }
      product_attribute_id: { type: integer(4) }
  relations:        
      AttributeSet:      { local: attribute_set_id,     foreign: id }
      ProductAttribute:  { local: product_attribute_id, foreign: id }

Product:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    name:               { type: string(255), notnull: true }
    name_invoice:       { type: string(255) }
    name_po:            { type: string(255) }
    attribute_set_id:   { type: integer(4) }
    quickbooks_listid:  { type: string(25) }
    short_description:  { type: clob(65532) }
    description:        { type: clob(65532) }
    sku:                { type: string(25), notnull:true }
    manu_sku:           { type: string(25) }
    units:              { type: string(4), values: [sf, each, lf, bf] }
    package_type:       { type: enum, values: [Carton, Bundle]}
    sold_by_package:    { type: boolean, default: true }
    weight_per_unit:    { type: decimal(8) }
    weight_per_package: { type: decimal(10) }
    weight_per_pallet:  { type: decimal(12) }
    units_per_package:  { type: decimal(10) }
    units_per_pallet:   { type: decimal(10) }
    units_min_order:    { type: integer(4) }
    package_ship_height: { type: integer(2) }
    package_ship_width:  { type: integer(2) }
    package_ship_length: { type: integer(2) }
    pallet_ship_height: { type: integer(2) }
    pallet_ship_width:  { type: integer(2) }
    pallet_ship_length: { type: integer(2) }
    meta_title:         { type: string(150) }
    meta_keywords:      { type: string(255) }
    meta_description:   { type: string(255) } 
    account_id:         { type: integer(4) }
    product_line_id:    { type: integer(4), notnull:true }
    price_full_retail:  { type: decimal(10) }
    cost:               { type: decimal(10) }
    tax_class_id:       { type: integer(1), default: '1' }
    image_thumb:        { type: string(255) }
    image_primary:      { type: string(255) }
    photogallery_id:    { type: integer(4) }
    freight_class:      { type: varchar(3) }
    freight_nmfc:       { type: varchar(15) }
    manage_inventory:   { type: boolean, default: false }
    times_viewed:       { type: integer(4) }
    times_quoted:       { type: integer(4) }
    product_url:        { type: varchar(255) }
    url_slug:           { type: varchar(255) }
    call_for_price:     { type: boolean }
    samples_available:  { type: boolean, default: false }
    price_match:        { type: boolean }
    lead_time:          { type: varchar(15)}
  relations:
    AttributeSet:   { local: attribute_set_id, foreign: id }
    Account:        { local: account_id, foreign: id }
    Category:       { local: product_id, foreign: productcategory_id, refClass: ProductCategoryProduct }
    ProductLine:    { local: product_line_id, foreign: id}
    TaxClass:       { local: tax_class_id, foreign: id }
    ProductPrice:   { local: id, foreign: product_id }

ProductAttribute:
  connection: doctrine
  actAs: [ Sortable, SoftDelete ]
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    name:           { type: string(100), notnull: true }
    label:          { type: string(100) } #update
    help:           { type: string(255) }
    input_type:     { type: enum, values: [Dropdown, Text Field, Text Area, Date, Price, MultiSelect] } #update
    public:        { type: boolean, default: true }
    required:       { type: boolean }
    searchable:     { type: boolean }
    comparable:     { type: boolean }
    layered_use:    { type: boolean }
    list_order:     { type: integer(2) }
  relations:       
    AttributeSet:      { refClass: AttributeSetProductAttribute, local: product_attribute_id, foreign: attribute_set_id }

ProductAttributeOption:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    attribute_id:   { type: integer(4) }
    val:            { type: string(100) }
    details:        { type: clob(65532) }
  relations:
    ProductAttribute: { local: attribute_id, foreign: id }

ProductAttributeValue:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    product_id:     { type: integer(4) }
    attribute_id:   { type: integer(4) }
    val:             { type: string(150) }
  relations:
    Product:          { local: product_id, foreign: id }  
    ProductAttribute: { local: attribute_id, foreign: id }  

ProductCategoryProduct:
  connection: doctrine
  actAs: [ SoftDelete ]
  options:
    symfony:
      form: false
      filter: false
  columns:
    product_id:             { type: integer(4), primary: true }
    productcategory_id:     { type: integer(4), primary: true }
  relations:
    Product:            { local: product_id, foreign: id }
    ProductCategory:    { local: productcategory_id, foreign: id }

ProductCategory:
  connection: doctrine
  actAs:
    NestedSet:
      hasManyRoots: true, 
      rootColumnName: root_id
    SoftDelete: ~
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    name:               { type: string(150) }
    url_slug:           { type: string(255), unique: true }
    subcategory_layout: { type: enum, values: ['imgAbove', 'imgRight']}
    thumbnail:          { type: string(255) }
    short_description:  { type: string(255) }
    long_description:   { type: clob(65532) }
    page_title:         { type: string(100) }
    meta_keywords:      { type: string(255) }
    meta_desc:          { type: string(255) }
    in_navigation:      { type: boolean, default: true }
  relations:
    Product:            { refClass: ProductCategoryProduct, local: productcategory_id, foreign: product_id}

ProductLine:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    name:               { type: string(255) }
    description:        { type: clob(65532) }
    warranty:           { type: clob(65532) }
    installation:        { type: clob(65532) }       
    account_id:         { type: integer(4) }
  relations:
    Account:        { local: account_id, foreign: id }

  
ProductPrice:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete]
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    account_type_id:    { type: string(1) }
    product_id:         { type: integer(4) }
    site_id:            { type: string(2) }
    min_qty:            { type: decimal(10) }
    price:              { type: decimal(10) }
    special_price:      { type: decimal(10) }
    special_from_date:  { type: date }
    special_to_date:    { type: date }
    visible:            { type: boolean }
  relations:
    AccountType:    { local: account_type_id, foreign: id }
    Product:        { local: product_id, foreign: id }
    Site:           { local: site_id, foreign: id }

#WORK IN Progress
ProductCost:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete]
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    product_id:         { type: integer(4) }
    # Range: between x and y, this cost,
    # unit:  per box or sq ft,
    # bulk:  per 20 cartons or 1280.7 sq ft etc
    # mixed: in case we want to make things really confusing - combine unit and bulk
    method:             { type: enum, values: ['Range', 'Unit', 'Bulk','Mixed']}
    min_qty:            { type: decimal(10) }
    max_qty:            { type: decimal(10) }
    price:              { type: decimal(10) }
  relations:
    Product:        { local: product_id, foreign: id }

#### Quote Section

Quote:
  connection: doctrine
  tableName: quotes
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:                     { type: integer(4), primary: true, autoincrement: true }
    contact_id:             { type: integer(4) }    
    bill_address_id:        { type: int(4) }
    ship_address_id:        { type: int(4) }
    installation_area:      { type: enum, values: ['Residential', 'Commercial'] }
    installation_subfloor:  { type: enum, values: ['Concrete', 'Plywood', 'Other'] }        
    date_needed:            { type: date }
    urgency:                { type: enum, values: ['High', 'Medium', 'Low'] }
    site_id:                { type: string(2) }
    campaign_id:            { type: integer(4) }
    quote_status_id:        { type: integer(1) } # active/ordered/cancelled  
    quote_quality_id:       { type: integer(1) } # far out, current, just browsing      
    rep_classification:     { type: enum, values: ['Black', 'Blue', 'Green', 'Orange', 'Purple', 'Red', 'White', 'Yellow'] }
    notes_internal:         { type: clob(65532) }
    notes_list:             { type: string(100) }
    notes_from_user:        { type: string(255) }
    employee_id:            { type: integer(4) }
    employee_previous_id:   { type: integer(4) }
    e_followup_on:          { type: date }
    p_followup_on:          { type: date }
    dont_follow_up_before:  { type: date }
    unread:                 { type: boolean }
    installation_needed:    { type: boolean }
    on_autopilot:           { type: boolean }
    can_checkout:           { type: boolean } 
    # TODO: refactor this receive offers out...this should only be in
    # contacts but we want it searchable in the quotes form 
    receive_offers:         { type: boolean }
    last_template_id:       { type: integer(4) }
    #### THESE SHOULD BE TRACKED IN QUOTE MESSAGES INSTEAD WITH DATE/TIME SENT AND CODE ####
    #last_template:          { type: integer(4) }
    #last_offer:             { type: integer(4) } 
    #last_followup_id:       { type: integer(4) }
    #### END SECTION THESE SHOULD BE TRACKED IN QUOTE MESSAGES INSTEAD ####
  relations:
    Campaign:           { local: campaign_id, foreign: id }
    Contact:            { local: contact_id, foreign: id }
    Site:               { local: site_id, foreign: id }
    QuoteStatus:        { local: quote_status_id, foreign: id }
    QuoteQuality:       { local: quote_quality_id, foreign: id }
    sfGuardUser:        { class: sfGuardUser, local: employee_id, foreign: id }
    sfGuardUserPrev:    { class: sfGuardUser, local: employee_previous_id, foreign: id }
    BillAddress:        { class: Address, local: bill_address_id, foreign: id }
    ShipAddress:        { class: Address, local: ship_address_id, foreign: id }
    #QuoteItem:          { local: id, foreign: quote_id, type: many, foreignType: one }
    Template:           { local: last_template_id, foreign: id}
    cascade:            [delete]

QuoteSample:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete]
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    quote_id:           { type: integer(4) }
    product_id:         { type: integer(4) }
    product_id_2:       { type: integer(4) }
    tracking_no:        { type: string(25) }
  relations:
    Quote:              { local: quote_id, foreign: id }
    Product:            { local: product_id, foreign: id }
    Product2:           { local: product_id_2, foreign: id }

QuoteStatus:
  connection: doctrine
  actAs: [ Sortable, SoftDelete ]
  columns:
    id:         { type: integer(1), primary: true, autoincrement: true }
    name:       { type: string(50) }

QuoteQuality:
  connection: doctrine
  actAs: [ Sortable, SoftDelete ]
  columns:
    id:         { type: integer(1), primary: true, autoincrement: true }
    name:       { type: string(50) }

QuoteSavedSearch:
  connection: doctrine
  actAs: [ Sortable ]
  columns:
    id:         { type: integer(4), primary: true, autoincrement: true }
    name:       { type: string(30) }
    user_id:    { type: integer(4) }
    params:     { type: string(255) }
  relations:
    sfGuardUser:    { local: user_id, foreign: id }

QuoteNote:
  inheritance:
    extends: Note
    type: column_aggregation
    keyField: parent_type
    keyValue: quote

## Order Tables
Order:
  connection: doctrine
  tableName: orders
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:                     { type: integer(4), primary: true, autoincrement: true }
    contact_id:             { type: integer(4) }
    quote_id:               { type: integer(4) }
    order_status_id:        { type: integer(1) }
    ship_email:             { type: string(100) }        
    ship_address_id:        { type: int(4) }
    bill_email:             { type: string(100) }             
    bill_address_id:        { type: int(4) }
    note_dashboard:         { type: string(150) } # for the notes that shan keeps regarding certain orders in her sheet
    employee_id:            { type: integer(4) }
    site_id:                { type: string(2) }
    campaign_id:            { type: integer(4) }
    quickbooks_listid:      { type: string(25) }
    invoice_num:            { type: string(5) }
    invoice_amt:            { type: decimal(14) }
    payment:                { type: enum, values: ['Pending', 'Partial', 'Full', 'Overpayment'], default: 'Pending' }
    balance_due_date:       { type: date }
    signed_invoice:         { type: boolean }
    signed_receipt:         { type: boolean }
    thanked:                { type: boolean }
    surveyed:               { type: boolean }
    send_survey:            { type: boolean, default: true }
    unread:                 { type: boolean }
    payment_term_id:        { type: integer(1) }
    payment_method_id:      { type: integer(1) }
    commission_amt:         { type: decimal(6) }
    commission_pd:          { type: boolean }
  relations:
    Contact:            { local: contact_id, foreign: id }
    Campaign:           { local: campaign_id, foreign: id }
    Quote:              { local: quote_id, foreign: id }
    Site:               { local: site_id, foreign: id }
    OrderStatus:        { local: order_status_id, foreign: id }
    PaymentTerm:        { local: payment_term_id, foreign: id }
    PaymentMethod:      { local: payment_method_id, foreign: id }
    sfGuardUser:        { class: sfGuardUser, local: employee_id, foreign: id }
    ShipAddress:        { class: Address, local: ship_address_id, foreign: id }
    BillAddress:        { class: Address, local: bill_address_id, foreign: id }     

OrderTerms:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    term_id:    { type: integer }
    order_id:   { type: integer(4) }
  relations:
    Term:     { local: term_id, foreign: id }
    Order:    { local: order_id, foreign: id }
      
OrderTransaction:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }  
    order_id:           { type: integer(4) }
    payment_method_id:  { type: integer(1) }
    amount:             { type: decimal(14) }
    ip_address:         { type: string(16) }
    result:             { type: string(75) }
    result_code:        { type: string(4) }
    result_subcode:     { type: string(4) }
    avs_code:           { type: string(1) }
    trans_num:          { type: string(20) }
    authcode:           { type: string(15) }
    trans_type:         { type: enum, values: ['AUTH_CAPTURE', 'CREDIT', 'AUTH_ONLY', 'PRIOR_AUTH_CAPTURE', 'CAPTURE_ONLY',  'VOID'] }
  relations:  
    Order:              { local: order_id, foreign: id }
    PaymentMethod:      { local:payment_method_id, foreign: id }


OrderStatus:
  connection: doctrine
  actAs: [ Sortable, SoftDelete ]
  columns:
    id:         { type: integer(1), primary: true, autoincrement: true }
    name:       { type: string(50) }

# Terms of Sale
#####################
Term:
  connection: doctrine
  actAs: [ Sortable, SoftDelete ]
  columns:
    name:               { type: string(50) }
    description:        { type: clob(65532) }
    quickbooks_listid:  { type: string(25) }

TermSet:
  connection: doctrine
  columns:
    name:   { type: string(50) }

TermSetTerm:
  connection: doctrine
  actAs: [ SoftDelete ]
  options:
    symfony:
      form: false
      filter: false
  columns:
    term_id:     { type: integer }
    term_set_id: { type: integer }
  relations:        
    TermSet:    { local: term_set_id,     foreign: id }
    Term:       { local: term_id, foreign: id }
      
# claims
#####################
Claim:
  connection: doctrine
  actAs: [ SoftDelete, Timestampable ]
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    order_shipment_id:  { type: integer(4) }
    type:               { type: enum, values: ['Shipping', 'Product'] }
    status:             { type: enum, values: ['Active', 'Resolved'], default: 'Active' }
    description:        { type: clob(65532) }
    result:             { type: enum, values: ['Pending', 'Approved', 'Rejected', 'Refiled'], default:'Pending'}
    filed:              { type: boolean, default: false }
  relations:
    OrderShipment:      { local: order_shipment_id, foreign: id }

ClaimFile:
  connection: doctrine
  actAs: [ SoftDelete, Timestampable]
  options:
    symfony:
      filter: false
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    claim_id:           { type: integer(4) }
    file_location:      { type: string(150), default: 'claim'  }
    file_name:          { type: string(100) }
    file_size:          { type: string(15) }
    file_type:          { type: string(15) }
  relations:
    Claim:              { local: claim_id, foreign: id }
    
# Signed document tables
OrderDocument:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    order_id:       { type: integer(4) }
    file_location:  { type: string(100), default: 'signed' }
    file_name:      { type: string(100) }
    file_size:      { type: string(15) }
    file_type:      { type: string(15) }
    document:       { type: enum, values: ['Unknown', 'Signed Invoice', 'Signed Receipt', 'Signed Inv+Rec', 'Vendor Release', 'Vendor Invoice', 'Vendor Sales Auth', 'Other']}
    source:         { type: enum, values: ['Email', 'Upload'] }
  relations:
    Order:                { local: order_id, foreign: id }
    OrderDocumentDetail:  { local: id, foreign: order_document_id, cascade: [delete] }

OrderDocumentDetail:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  options:
    symfony:
      filter: false  
  columns:
    id:                   { type: integer(4), primary: true, autoincrement: true }
    order_document_id:    { type: integer(4) }
    sender:               { type: string(100) }
    recipient:            { type: string(150) }
    recipientcc:          { type: string(150) }
    date:                 { type: datetime }
    subject:              { type: string(255) }
    headers:              { type: clob(65532) }
    body_text:            { type: clob(65532) }
    body_html:            { type: clob(65532) }
  relations:
    OrderDocument:        { local: order_document_id, foreign: id }    

# Some order/payment things required by quickbooks
PaymentTerm:
  connection: doctrine
  actAs: [ SoftDelete ]
  options:
    symfony:
      filter: false
  columns:
    id:             { type: integer(1), primary: true, autoincrement: true }
    name:           { type: string(50) }
    quickbooks_id:  { type: string(25) }

PaymentMethod:
  connection: doctrine
  actAs: [ SoftDelete ]
  options:
    symfony:
      filter: false
  columns:
    id:             { type: integer(1), primary: true, autoincrement: true }
    name:           { type: string(50) }
    quickbooks_id:  { type: string(25) }

## Shipments
Shipment:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  options:
    symfony:
      filter: false
      form:   false
  columns:
    id:                       { type: integer(4), primary: true, autoincrement: true }
    #address_id:               { type: integer(4) }
    accountwarehouse_id:      { type: integer(4) }
    delivery_date:            { type: date }
    customer_charge:          { type: decimal(10) }
    handling_fee:             { type: decimal(5)}
    our_cost:                 { type: decimal(10) }
    ship_carrier_id:          { type: integer(4) } # this would be if we directly book with FQ, UPS, Fedex, etc
    ship_method_id:           { type: integer }
    ship_weight:              { type: decimal(10) }
    ship_pallets:             { type: integer(1) }
    ship_dim_l:               { type: integer(2) }
    ship_dim_w:               { type: integer(2) }
    ship_dim_h:               { type: integer(2) }
    fq_quoteid:               { type: string(14) }
    fq_optionid:              { type: string(2) }
    ship_to_alternate:        { type: boolean }
  relations:    
    #Address:            { local: address_id, foreign: id }
    Carrier:          { local: ship_carrier_id, foreign: id, class: ShipCarrier } 
    ShipMethod:       { local: ship_method_id, foreign: id }
    AccountWarehouse: { local: accountwarehouse_id, foreign: id, class: AccountWarehouse }


QuoteShipment:
  connection: doctrine
  inheritance:
    extends: Shipment
    type: concrete
  columns:
    quote_id:                 { type: integer(4) }
    transit_time:             { type: integer(2) }
    need_box_truck:           { type: boolean }
  relations:
    Quote:              { local: quote_id, foreign: id }
    QuoteItem:          { local: id, foreign: quote_shipment_id, class: QuoteItem, type: many, foreignType: one, cascade: [delete] }
    
OrderShipment:
  connection: doctrine
  inheritance:
    extends: Shipment
    type: concrete
  columns:
    order_id:                 { type: integer(4) }    
    vendor_ref_no:            { type: string(15) }
    po_no:                    { type: string(6) }
    release_no:               { type: string(25) }
    tracking_no:              { type: string(25) }
    bol_no:                   { type: string(25) }
    ship_brokered_carrier_id: { type: integer(4) } # say we booked with estes via fq, this would be estes
    pickup_date:              { type: date }
    customer_notified:        { type: boolean }
    dates_are_approx:         { type: boolean } 
    on_hold:                  { type: boolean }
    note:                     { type: string(40) }
    ship_ready_date:          { type: date }
    ship_description:         { type: string(255) }
    ship_cod:                 { type: boolean }
    ship_blind:               { type: boolean }
    ship_call_ahead:          { type: boolean, default: true }
    need_box_truck:           { type: boolean }
  relations:
    Order:            { local: order_id, foreign: id }
    OrderItem:        { local: id, foreign: order_shipment_id, class: OrderItem, type: many, foreignType: one, cascade: [delete] }
    BrokeredCarrier:  { local: ship_brokered_carrier_id, foreign: id, class: ShipBrokeredCarrier }

## Shipment Items
QuoteItem:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    quote_shipment_id:  { type: integer(4) }
    #quote_id:  { type: integer(4) }
    product_id:         { type: integer(4) }
    quantity_bulk:      { type: decimal(6) }
    quantity:           { type: decimal(14) }
    price:              { type: decimal(10) }
    cost:               { type: decimal(10) }
  relations:
    QuoteShipment:      { local: quote_shipment_id, foreign: id }
    #Quote:              { local: quote_id, foreign: id }
    Product:            { local: product_id, foreign: id }
    
OrderItem:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:                 { type: integer(4), primary: true, autoincrement: true }
    order_shipment_id:  { type: integer(4) }
    product_id:         { type: integer(4) }
    quantity_bulk:      { type: decimal(6) }
    quantity:           { type: decimal(14) }
    price:              { type: decimal(10) }
    cost:               { type: decimal(10) }
  relations:
    OrderShipment:      { local: order_shipment_id, foreign: id }
    Product:            { local: product_id, foreign: id }   

## Shipping Carrier Stuff        
ShipBrokeredCarrier:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    name:           { type: string(50) }
    scac:           { type: string(5) }
    fq_id:          { type: string(5) }
    url_primary:    { type: string(150) }
    url_tracking:   { type: string(255) }

ShipCarrier:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:   
    id:             { type: integer(4), primary: true, autoincrement: true }                              
    name:           { type: string(50) }
    type:           { type: enum, values: ['Freight', 'Parcel']}
    url_primary:    { type: string(150) }
    url_tracking:   { type: string(255) }

ShipMethod:
  connection: doctrine
  columns:
    name:             { type: string(50) }
    ship_carrier_id:  { type: integer(4) }
    enabled:          { type: boolean, default: true }
  relations:
    ShipCarrier:      { local: ship_carrier_id, foreign: id }

ProductAttributeSetShipMethod:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    attribute_set_id: { type: integer(4) }
    ship_method_id:   { type: integer }
  relations:
    AttributeSet: { local: attribute_set_id, foreign: id }
    ShipMethod:   { local: ship_method_id, foreign: id }    

#### Section Divider   
Campaign:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:         { type: integer(4), primary: true, autoincrement: true }
    name:       { type: string(50) }
    details:    { type: clob(65532) }

Site:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:                 { type: string(2), notnull: true, primary:true, unique: true}
    name:               { type: string(255), notnull: true, unique: false }
    url:                { type: string(255), notnull: true, unique: false }
    catalog_root_id:    { type: integer(4) }
  relations:
    ProductCategory:    { local: catalog_root_id, foreign: id }

Testimonial:
  connection: doctrine
  actAs: [ SoftDelete, Timestampable ]
  columns:
    order_id:       { type: integer(4) }
    testimonial:    { type: clob(65532) }
  relations:
    Order:          { local: order_id, foreign: id }

TestimonialPhoto:
  connection: doctrine
  columns:
    testimonial_id:   { type: integer }
    file_name_thumb:  { type: string(100) }
    file_name:        { type: string(100) }
    file_size:        { type: string(15) }
    file_type:        { type: string(15) }
  relations:
    Testimonial:    { local: testimonial_id, foreign: id}
     
## Specials
## #####################
Special:
  connection: doctrine
  actAs: [ SoftDelete]
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    name:           { type: string(50) }
    site_id:        { type: string(2) }
    warehouse_id:   { type: integer(4) }
    product_id:     { type: integer(4), primary: true }
    quantity:       { type: decimal(10) }
    date_start:     { type: datetime }
    date_end:       { type: datetime }
  relations:
    Warehouse:  { local: warehouse_id, foreign: id }
    Site:       { local: site_id, foreign: id }
    Product:    { local: product_id, foreign: id }
    #ProductPrice table


## Taxes
## ####################################################
TaxClass:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:             { type: integer(1), primary: true, autoincrement: true }
    name:           { type: string(255), notnull: true, unique: false }

## Templates, FAQ, Knowledgebase
Glossary:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:         { type: integer(4), primary: true, autoincrement: true }
    letter:     { type: string(1) }
    word:       { type: string(150) }
    definition: { type: clob(65532) }

Template:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:                     { type: integer(4), primary: true, autoincrement: true }
    name:                   { type: string(150) }
    name_short:             { type: string(15) }
    reference:              { type: string(50) }
    version:                { type: integer(3) }    # this is for version 1, version 2, Version 3
    email_subject:          { type: string(150) }
    body:                   { type: clob(65532) }
    template_location:      { type: string(255) }
    sequence:               { type: integer(1) }    # works to tell order of follow ups for auto_pilot
    wait_before_next:       { type: integer(4) }    # hours to wait before sending next follow up
    template_category_id:   { type: integer(4) }
    site_id:                { type: string(2) }
    vars:                   { type: string(255) }
    is_public:              { type: boolean }
    is_advanced:            { type: boolean }
  relations:
    TemplateCategory:   { local: template_category_id, foreign: id}
    Site:               { local: site_id, foreign: id }

TemplateCategory:
  connection: doctrine
  actAs:
      NestedSet:
          hasManyRoots: true, 
          rootColumnName: root_id
      Sortable: ~
      SoftDelete: ~
  columns:
      id:             { type: integer(4), primary: true, autoincrement: true }
      name:           { type: string(150) }
      is_public:      { type: boolean }

# Template for specials that can be emailed
TemplateSpecial:
  connection: doctrine
  actAs: [ Timestampable, SoftDelete]
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    name:           { type: string(50) }
    version:        { type: integer(3) } 
    site_id:        { type: string(2) }
    email_subject:  { type: string(150) }
    body:           { type: clob(65532) }
    date_start:     { type: date }
    date_end:       { type: date }
    template_special_category_id: { type: integer(4) }
  relations:
    Site:                     { local: site_id, foreign: id }
    TemplateSpecialCategory:  { local: template_special_category_id, foreign: id}

TemplateSpecialCategory:
  connection: doctrine
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    name:           { type: string(50) }
        
## Messages
## #####################################################
Voicemail:
  connection: doctrine
  actAs:    [ SoftDelete, Timestampable ]
  columns:  
    id:             { type: integer(4), primary: true, autoincrement: true }
    contact_id:     { type: integer(4) }
    employee_id:    { type: integer(4) }
    quote_id:       { type: integer(4) }
    phone_num:      { type: string(20) }
    subject:        { type: string(100) }
    body:           { type: clob(65532) }
    file_location:  { type: string(150), default: 'voicemail'  }
    file_name:      { type: string(100) }
    file_size:      { type: string(15) }
    file_type:      { type: string(15), default: 'audio/mpeg' }
    need_followup:  { type: boolean, default: '1' }
    unassigned:     { type: boolean, default: '1' }
  relations:
    Contact:        { local: contact_id, foreign: id }
    Quote:          { local: quote_id, foreign: id }
    Employee:       { class: sfGuardUser, local: employee_id, foreign: id }   
     
EmailRules:             # allows lets send POs to x, y, and z for Acme, Inc vendor, among other things.
  connection: doctrine
  actAs: [ SoftDelete, Timestampable ]
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    name:           { type: string(50) }
    action_type:    { type: enum, values: ['Purchase Order', 'Inventory Check', 'Invoice', 'Shipment Tracking'] }
    account_id:     { type: integer(4) }
    email_to:       { type: string(255) }
    email_cc:       { type: string(255) }
    email_bcc:      { type: string(255) }
  relations:
      Account:            { local: account_id, foreign: id, foreignAlias: Contacts }

PaypalIPN:
  connection: doctrine
  actAs: [ SoftDelete, Timestampable ]
  options:
    symfony:
      filter: false  
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    first_name:     { type: string(75) }
    last_name:      { type: string(75) }
    email:          { type: string(100) }
    address:        { type: string(150) }
    city:           { type: string(50), notnull: true }
    state:          { type: string(2), notnull: true }
    postcode:       { type: string(20), notnull: true }
    country_id:     { type: string(2), notnull: true, default: 'US' }
    is_shipped:     { type: boolean, default: '0' }
    options:        { type: string(255) }
    item_number:    { type: string(50) }
    item_name:      { type: string(150) }
    txn_id:         { type: string(25) }
    shipped_via:    { type: string(25) }
    tracking_num:   { type: string(25); unique: true; }
    shipped_on:     { type: date }
  indexes:
    tracking:
      fields: [first_name, last_name, postcode, tracking_num]
      type:   unique
      
## TODO: Properly define Inventory
## #####################################################
Inventory:
  connection: doctrine
  columns:
    id:             { type: integer(4), primary: true, autoincrement: true }
    lot_no:         { type: string(25) }
    warehouse_id:   { type: integer(4) }
    product_id:     { type: integer(4) }
    track_qty:      { type: boolean }
    quantity:       { type: decimal(6) }
    status:         { type: enum, values: ['Available', 'On Hold',  'Incoming']}
    status_detail:  { type: string(25) }  # For instance, incoming: 3-6wks
  relations:
    Warehouse:      { local: warehouse_id, foreign: id }
    Product:        { local: product_id, foreign: id }


## Email
## #####################################################

#EmailMessage:       # Consider creating a parent email class that is inherited so that this works in other applications
#  actAs: [ Timestampable, SoftDelete ]
#  columns:
#    id:             { type: integer, primary: true, autoincrement: true}
#    from_contact_id:        { type: integer(4) }
#    from_emp_profile_id:    { type: integer(4) }
#    mime_message_id:        { type: string(50) }
#    subject:                { type: string(150), notnull: true }
#    headers:                { type: clob(65532) }
#    body_html:              { type: clob(65532), notnull: true }
#    quote_id:               { type: integer(4) }
#    template_id:            { type: integer(4) }
#    is_read:                { type: boolean }
#  relations:
#    From:             { class: Contact, local: from_contact_id, foreign: id, foreignAlias: EmailMessages }
#    To:               { class: Contact, local: message_id, foreign: recipient_id, refClass: ToRecipient }
#    Recipients:       { class: Contact, local: message_id, foreign: recipient_id, refClass: EmailRecipient }
#    Cc:               { class: Contact, local: message_id, foreign: recipient_id, refClass: CcRecipient }
#    Bcc:              { class: Contact, local: message_id, foreign: recipient_id, refClass: BccRecipient }
#    EmployeeProfile:  { local: from_emp_profile_id, foreign: id}
#    Quote:            { local: quote_id, foreign: id }        
#    Template:         { local: template_id, foreign: id}
  
#EmailRecipient:
#  tableName: email_recipients
#  columns:
#    id:         { type: integer, primary: true, autoincrement: true }
#    message_id: { type: integer }
#    type:       { type: string(5) }
#    recipient_id: { type: integer(4) }
#  relations:
#    Contact:    { local: recipient_id, foreign: id }

#ToRecipient:
#  inheritance:
#    extends: EmailRecipient
#    type: column_aggregation
#    keyField: type
#    keyValue: to 

#CcRecipient:
#  inheritance:
#    extends: EmailRecipient
#    type: column_aggregation
#    keyField: type
#    keyValue: cc

#BccRecipient:
#  inheritance:
#    extends: EmailRecipient
#    type: column_aggregation
#    keyField: type
#    keyValue: bcc


# for Doctrine spool
MailMessage:
  connection: doctrine
  actAs: { Timestampable: ~ }
  columns:
    message:  { type: blob, notnull: true }
    priority: { type: integer }

## New Email
## #####################################################

  
Email:       # Consider creating a parent email class that is inherited so that this works in other applications
  connection: doctrine
  actAs: [ Timestampable, SoftDelete ]
  columns:
    id:                     { type: integer, primary: true, autoincrement: true}
    from_contact_id:        { type: integer(4) }
    from_emp_profile_id:    { type: integer(4) }
    mime_message_id:        { type: string(50) }
    subject:                { type: string(150), notnull: true }
    headers:                { type: clob(65532) }
    body_html:              { type: clob(65532), notnull: true }
    quote_id:               { type: integer(4) }
    template_id:            { type: integer(4) }
    is_read:                { type: boolean }
  relations:
    From:             { class: Contact, local: from_contact_id, foreign: id, foreignAlias: EmailMessages }
    Recipients:       { class: EmailRecipient, local: id, foreign:  message_id, refClass: EmailRecipient }
    To:               { class: EmailRecipient, local: id, foreign: message_id, refClass: ToRecipient }
    Cc:               { class: EmailRecipient, local: id, foreign:  message_id, refClass: CcRecipient }
    Bcc:              { class: EmailRecipient, local: id, foreign:  message_id, refClass: BccRecipient }
    EmployeeProfile:  { local: from_emp_profile_id, foreign: id}
    Quote:            { local: quote_id, foreign: id }        
    Template:         { local: template_id, foreign: id}


    
EmailRecipient:
  connection: doctrine
  tableName: email_recipients
  columns:
    id:         { type: integer, primary: true, autoincrement: true }
    message_id: { type: integer }
    type:       { type: string(5) }
    recipient:  { type: string(200) }
#  relations:
#    Email:      { local: message_id, foreign: id }

ToRecipient:
  inheritance:
    extends: EmailRecipient
    type: column_aggregation
    keyField: type
    keyValue: to 

CcRecipient:
  inheritance:
    extends: EmailRecipient
    type: column_aggregation
    keyField: type
    keyValue: cc

BccRecipient:
  inheritance:
    extends: EmailRecipient
    type: column_aggregation
    keyField: type
    keyValue: bcc

## Image gallery
################

Gallery:
  connection: doctrine
  actAs: [ SoftDelete ]
  columns:
    id:           { type: integer(4), primary: true, autoincrement: true }
    parent_id:    { type: integer(4) }
    title:        { type: string(150) }
    description:  { type: string(255) }
    details:      { type: string(255) }
    thumbnail:    { type: string(255) }
    main_image:   { type: string(255) }
    url_slug:     { type: string(150) }
    sort_order:   { type: integer(2) }
    is_published: { type: boolean }
  relations:
    Gallery:    { local: parent_id, foreign: id }

GalleryImage:
  connection: doctrine
  columns:
    id:           { type: integer(4), primary: true, autoincrement: true }
    gallery_id:   { type: integer(4) }
    filename:     { type: string(100) }
    caption:      { type: string(255) }
    alt_text:     { type: string(150) }
  relations:
    Gallery:      { local: gallery_id, foreign: id }
    
    
    
# Legacy
################
LAccount:
  connection: legacy
  tableName: account
  columns:
    account_id:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    account_type_id:
      type: string(1)
      fixed: true
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    company_name:
      type: string(75)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    phone_fax:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    phone_office:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    phone_alt:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    website:
      type: string(75)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    email1:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    email2:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    no_employees:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    offers_install:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    locations:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    default_bill_location_id:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    default_ship_location_id:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    annual_revenue:
      type: string(9)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    description:
      type: string()
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    source:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    date_created:
      type: timestamp(25)
      fixed: false
      unsigned: false
      primary: false
      default: '0000-00-00 00:00:00'
      notnull: true
      autoincrement: false
    date_modified:
      type: timestamp(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    restrict_level:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    authorized:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    is_active:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '1'
      notnull: true
      autoincrement: false
    employee_id:
      type: string(4)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    quickbooks_listid:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    install_price:
      type: string(5)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
  relations:
    LEmployee : { local: employee_id, foreign: employee_id }
LAddress:
  connection: legacy
  tableName: address
  columns:
    address_id:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    type:
      type: enum(1)
      fixed: false
      unsigned: false
      values:
        - B
        - S
        - O
      primary: false
      default: B
      notnull: true
      autoincrement: false
    reference_name:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    address:
      type: string(80)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    address2:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    city:
      type: string(60)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    state:
      type: string(2)
      fixed: true
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    zipcode:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    is_active:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '1'
      notnull: true
      autoincrement: false
    contact_id:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
      
LContact:
  connection: legacy
  tableName: contact
  columns:
    contact_id:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: true
    account_id:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    email:
      type: string(50)
      default: ''
      notnull: true
    password:
      type: string(60)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    first_name:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    last_name:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    greeting:
      type: string(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    title:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    department:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    birthdate:
      type: date(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    preferred_contact:
      type: string(17)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    default_address:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    opt_in_email:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '1'
      notnull: true
      autoincrement: false
    opt_in_fax:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '1'
      notnull: true
      autoincrement: false
    opt_in_phone:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '1'
      notnull: true
      autoincrement: false
    opt_in_mail:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '1'
      notnull: true
      autoincrement: false
    phone_office:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    phone_home:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    phone_mobile:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    fax:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    email2:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    assistant_name:
      type: string(100)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    assistant_phone:
      type: string(15)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    is_main_contact:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    date_created:
      type: timestamp(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    date_modified:
      type: timestamp(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    date_last_login:
      type: timestamp(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    authorized:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    is_active:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '1'
      notnull: true
      autoincrement: false
    modify_acct_info:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    employee_id:
      type: string(4)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    quickbooks_listid:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    is_repeat:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    temp_mailchimped:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
  relations:
    LAccount: { local: account_id, foreign: account_id }
    LAddress: { local: default_address, foreign: address_id }
    LEmployee : { local: employee_id, foreign: employee_id }
    
LEmployee:
  connection: legacy
  tableName: employee
  columns:
    employee_id:
      type: string(4)
      fixed: false
      unsigned: false
      primary: true
      autoincrement: false
    access_level:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    username:
      type: string(15)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    password:
      type: string(60)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    greeting:
      type: string(4)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    first_name:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    last_name:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    title:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    department:
      type: string(50)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    email_bd:
      type: string(60)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    email_home:
      type: string(60)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    signature_bd:
      type: string()
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    phone_office:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    phone_home:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    phone_mobile:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    extension:
      type: string(5)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    c2c:
      type: string(255)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false
    fax:
      type: string(25)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    address:
      type: string(150)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    address2:
      type: string(100)
      fixed: false
      unsigned: false
      primary: false
      notnull: false
      autoincrement: false
    city:
      type: string(60)
      fixed: false
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    state:
      type: string(2)
      fixed: true
      unsigned: false
      primary: false
      default: ''
      notnull: true
      autoincrement: false
    zipcode:
      type: integer(4)
      fixed: false
      unsigned: false
      primary: false
      default: '0'
      notnull: true
      autoincrement: false
    is_active:
      type: integer(1)
      fixed: false
      unsigned: false
      primary: false
      default: '1'
      notnull: true
      autoincrement: false
    quickbooks_listid:
      type: string(20)
      fixed: false
      unsigned: false
      primary: false
      notnull: true
      autoincrement: false      
LOrders:
  connection: legacy
  tableName: orders
  options:
    symfony:
      form: true
  columns:
    order_id:
      type: integer(4)
      primary: true
      autoincrement: true
    quote_id:
      type: string(35)
      notnull: true
    date_created:
      type: timestamp(25)
    date_modified:
      type: timestamp(25)
    source:
      type: string(20)
    payment_terms:
      type: string(50)
    ship_company:
      type: string(150)
    ship_first:
      type: string(50)
      default: ''
      notnull: true
    ship_last:
      type: string(50)
      default: ''
      notnull: true
    ship_email:
      type: string(60)
    ship_addr:
      type: string(80)
    ship_addr2:
      type: string(50)
    ship_city:
      type: string(60)
    ship_state:
      type: string(2)
      fixed: true
    ship_zipcode:
      type: string(5)
    ship_req_date:
      type: date(25)
      notnull: true
    ship_req_details:
      type: string(20)
    ship_phone:
      type: string(20)
    ship_phone_alt:
      type: string(20)
    ship_type:
      type: string(15)
      notnull: true
    bol:
      type: string(25)
      notnull: true
    pro:
      type: string(25)
      notnull: true
    bill_company:
      type: string(150)
    bill_first:
      type: string(50)
      default: ''
      notnull: true
    bill_last:
      type: string(50)
      default: ''
      notnull: true
    bill_email:
      type: string(60)
      default: ''
      notnull: true
    bill_addr:
      type: string(80)
    bill_addr2:
      type: string(50)
    bill_city:
      type: string(60)
    bill_state:
      type: string(2)
      fixed: true
    bill_zipcode:
      type: string(5)
    bill_phone:
      type: string(20)
    installation_needed:
      type: integer(1)
      default: '0'
    status_order_id:
      type: integer(4)
    status_payment_id:
      type: integer(4)
    last_msg_by:
      type: string(4)
    user_comments:
      type: string()
    is_active:
      type: integer(1)
      default: '1'
    contact_id:
      type: integer(4)
    employee_id:
      type: string(4)
    company_handling:
      type: string(3)
      fixed: true
      default: BD
    inv_num:
      type: string(8)
    inv_amt:
      type: float(9)
      default: '0.00'
    quickbooks_listid:
      type: string(50)
    quickbooks_txnid:
      type: string(25)
    quickbooks_editsequence:
      type: string(25)
    quickbooks_po_ref:
      type: string(25)
    status_signed_inv:
      type: integer(1)
      default: '0'
    status_signed_receipt:
      type: integer(1)
      default: '0'
  relations:
    #LQuote: { local: quote_id, foreign: quote_id } # cause build error on stage and quote already references orders with a relation
    LEmployee : { local: employee_id, foreign: employee_id }

LTempAcc:
  connection: legacy
  tableName: temp_acc
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    species:
      type: string(50)
    for_floor_height:
      type: string(10)
    finish:
      type: string(25)
    finish_notes:
      type: string(50)
    manu_match:
      type: string(25)
    temp_acc_profile_id:
      type: integer(1)
    moi_sku:
      type: string(20)
    quickbooks_listid:
      type: string(30)
    quickbooks_parent:
      type: string(20)
    price:
      type: 'decimal(6, 2)'
  relations:
    LTempAccProfile:      { local: temp_acc_profile_id, foreign: id }
          
LTempAccOrders:
  connection: legacy
  tableName: temp_acc_orders
  columns:
    id:        {  type: integer(4), primary: true, autoincrement: true }
    order_id:
      type: integer(4)
    temp_acc_id:
      type: integer(4)
    qty:
      type: integer(4)
  relations:
    LTempAcc:      { local: temp_acc_id, foreign: id }

LTempAccProfile:
  connection: legacy
  tableName: temp_acc_profile
  columns:
    id: { type: integer(1), primary: true, autoincrement: false }
    thickness:
      type: string(8)
    width:
      type: string(8)
    length:
      type: string(8)
    type:
      type: string(30)
    image_url:
      type: string()
    description:
      type: string(75)
    qb_name_part:
      type: string(20)
      
LTempQbPo:
  connection: legacy
  tableName: temp_qb_po
  columns:
    id: { type: type: integer(4), primary: true, autoincrement: true }
    quickbooks_listid:
      type: string(25)
    po_num:
      type: string(10)
    inv_num:
      type: string(10)
    type:
      type: string(4)    
      fixed: true
    ordered:
      type: boolean
      default: 0
  relations:
    LOrders:        { local: inv_num, foreign: inv_num }
    LTempQbPoLine:  { local: inv_num, foreign: qb_inv_num, type:many }

LTempQbRepsales:
  connection: legacy
  tableName: temp_qb_repsales
  columns:
    timeperiod:
      type: date
      primary: true
    name:
      type: string(25)
      primary: true
    amount:
      type: 'decimal(9,2)'

LTempQbPoLine:
  connection: legacy
  tableName: temp_qb_po_line
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    qb_inv_num:
      type: string(10)
      notnull: true
    qb_prod_id:
      type: string(25)
      notnull: true
    qb_po_listid:
      type: string(25)
      notnull: true
    qty:
      type: 'decimal(10, 2)'
      notnull: true
    description:
      type: string(255)
      notnull: true
    type:
      type: string(4)
      fixed: true
      notnull: true
  relations:
    LTempQbPo:  { local: qb_inv_num, foreign: inv_num }
    LTempAcc:  { local: qb_prod_id, foreign: quickbooks_listid }

LQuote:
  connection: legacy
  tableName: quote
  columns:
    quote_id:
      type: string(35)
      unsigned: false
      primary: true
    date_created:
      type: timestamp(25)
    date_modified:
      type: timestamp(25)
    date_needed:
      type: date(25)
      default: '0000-00-00'
      notnull: true
    urgency:
      type: enum(1)
      values:
        - '1'
        - '2'
        - '3'
      default: '2'
    source:
      type: string(20)
    payment_terms:
      type: string(50)
    reference_name:
      type: string(50)
      default: ''
      notnull: true
    preferred_contact:
      type: string(30)
      default: ''
      notnull: true
    ship_first:
      type: string(50)
      default: ''
      notnull: true
    ship_last:
      type: string(50)
      default: ''
      notnull: true
    ship_email:
      type: string(60)
      default: ''
      notnull: true
    ship_addr:
      type: string(80)
    ship_addr2:
      type: string(50)
    ship_city:
      type: string(60)
    ship_state:
      type: string(2)
      fixed: true
    ship_zipcode:
      type: string(5)
    ship_country:
      type: string(2)
      fixed: true
      notnull: true
    ship_request_deliv:
      type: string(20)
    ship_phone:
      type: string(20)
    ship_phone_alt:
      type: string(20)
    ship_type:
      type: string(15)
      notnull: true
    bill_first:
      type: string(50)
      default: ''
      notnull: true
    bill_last:
      type: string(50)
      default: ''
      notnull: true
    bill_email:
      type: string(60)
      default: ''
      notnull: true
    bill_addr:
      type: string(80)
    bill_addr2:
      type: string(50)
    bill_city:
      type: string(60)
    bill_state:
      type: string(2)
      fixed: true
    bill_zipcode:
      type: string(5)
    install_area:
      type: enum(11)
      values:
        - Residential
        - Commercial
    install_subfloor:
      type: enum(8)
      values:
        - ''
        - Concrete
        - Plywood
        - Other
    installation_needed:
      type: integer(1)
      default: '0'
    status_id:
      type: integer(4)
    quote_stage:
      type: string(25)
      notnull: true
    last_msg_by:
      type: string(4)
    rep_class_id:
      type: string(3)
      fixed: true
      default: ''
      notnull: true
    last_template:
      type: string(50)
    last_offer:
      type: string(50)
    user_comments:
      type: string()
    is_active:
      type: integer(1)
      default: '1'
      notnull: false
    contact_id:
      type: integer(4)
      default: '0'
      notnull: true
    employee_id:
      type: string(4)
      default: ''
      notnull: true
    employee_id_prev:
      type: string(4)
      default: ''
      notnull: true
    company_handling:
      type: string(3)
      fixed: true
      default: BD
      notnull: true
    note:
      type: string(75)
      default: ''
      notnull: true
    last_followup:
      type: string(25)
      default: ''
      notnull: true
    followup_on:
      type: date(25)
      notnull: true
    mailchimped:
      type: boolean
      notnull: false
    customer_cancelled:
      type: boolean
      notnull: false
    utm_source:
      type: string(35)
    utm_medium:
      type: string(35)
    utm_term:
      type: string(35)
    utm_content:
      type: string(35)
    utm_campaign:
      type: string(35)
  relations:
    LOrder: { local: quote_id, foreign: quote_id }
    LContact: { local: contact_id, foreign: contact_id}
    LEmployee : { local: employee_id, foreign: employee_id }
      
LTempQbProd:
  connection: legacy
  tableName: temp_qb_prod
  columns:
    id:
      type: integer(4)
      primary: true
      autoincrement: true
    qb_listid:
      type: string(50)
      notnull: true
    description:
      type: string(255)