#
# Hi, you probably don't want to use this class/view for anything.
# This MySql view, when used with a where clause, like this...
#
# ItemView.where(...)
#
# ...causes MySQL to use a derived table. The where(...) clause is essentially
# running against a temp table with *no* indexing. This makes selecting
# against the items_view table prohibitively slow.
#
# In general, it's much faster to just select against the Item table
# and join with Order and Person
#
class ItemView < ActiveRecord::Base
  self.table_name = 'items_view'

  has_many :items,  :foreign_key => 'order_id'
  belongs_to :person, :foreign_key => 'person_id'
  belongs_to :item
  belongs_to :organization
  belongs_to :order
  has_many :shows,  :foreign_key => 'show_id'

  set_watch_for :created_at, :local_to => :self, :as => :organization
  set_watch_for :datetime, :local_to => :self, :as => :organization

  default_scope order('created_at desc')

  def self.sales_export_filename_for(organization)
    "exports/Artfully-Sales-Export-#{organization.id}.csv"
  end

  def self.donations_export_filename_for(organization)
    "exports/Artfully-Donations-Export-#{organization.id}.csv"
  end

  #load {item_id => product_type_name} once to avoid 3n query call for each item
  def self.product_type_name(item_id, organization_id)
    if @hash.nil?
      sql = "SELECT items.id,  " +
        "CASE WHEN items.product_type = 'Ticket' THEN ticket_types.name " +
        "WHEN items.product_type = 'Membership' THEN membership_types.name " +
        "WHEN items.product_type = 'Pass' THEN pass_types.name " +
        "END AS product_type_name " +
        "FROM items " +
        "LEFT OUTER JOIN orders ON orders.id = items.order_id " +
        "LEFT OUTER JOIN tickets ON tickets.id = items.product_id AND items.product_type = 'Ticket' " +
        "LEFT OUTER JOIN memberships ON memberships.id = items.product_id AND items.product_type = 'Membership' " +
        "LEFT OUTER JOIN passes ON passes.id = items.product_id AND items.product_type = 'Pass' " +
        "LEFT OUTER JOIN ticket_types ON ticket_types.id = tickets.ticket_type_id " +
        "LEFT OUTER JOIN membership_types ON membership_types.id = memberships.membership_type_id " +
        "LEFT OUTER JOIN pass_types ON pass_types.id = passes.pass_type_id " +
        "WHERE product_type IN('Ticket', 'Pass', 'Membership') AND orders.organization_id = #{organization_id}"
      @hash  = Hash[ActiveRecord::Base.connection.select_all(sql).map(&:values)]
    end
    @hash[item_id]
  end


  comma :donation do
    created_at_local_to_organization("Date")

    payment_method("Payment Method")
    price("Deductible Amount")                { |cents| ((cents || 0) / 100.00) }
    nongift_amount("Non-Deductible Amount")   { |cents| ((cents || 0) / 100.00) }
    special_instructions("Special Instructions")
    notes("Notes")
    creator("Creator")

    person("Email")           { |person| person.email }
    person("Salutation")      { |person| person.salutation }
    person("First Name")      { |person| person.first_name }
    person("Middle Name")     { |person| person.middle_name }
    person("Last Name")       { |person| person.last_name }
    person("Suffix")          { |person| person.suffix }
    person("Nickname")        { |person| person.nickname }
    person("Listing Name")    { |person| person.listing_name }
    person("Maiden Name")     { |person| person.maiden_name }
    person("Title")           { |person| person.title }
    person("Type")            { |person| person.type }
    person("Subtype")         { |person| person.subtype }
    person("Company Name")    { |person| person.company_name }

    ('A'..'C').each_with_index do |letter, i|
      person("Address#{letter} Type")        { |person| person.addresses[i] && person.addresses[i].kind }
      person("Address#{letter} Address 1")   { |person| person.addresses[i] && person.addresses[i].address1 }
      person("Address#{letter} Address 2")   { |person| person.addresses[i] && person.addresses[i].address2 }
      person("Address#{letter} City")        { |person| person.addresses[i] && person.addresses[i].city }
      person("Address#{letter} State")       { |person| person.addresses[i] && person.addresses[i].state }
      person("Address#{letter} Zip")         { |person| person.addresses[i] && person.addresses[i].zip }
      person("Address#{letter} Country")     { |person| person.addresses[i] && person.addresses[i].country }
    end

    (1..3).each do |i|
      person("Phone#{i} type")          { |person| person.phones[i-1] && person.phones[i-1].kind }
      person("Phone#{i} number")        { |person| person.phones[i-1] && person.phones[i-1].number }
    end

    person("Website")         { |person| person.website }
    person("Twitter Handle")  { |person| person.twitter_handle }
    person("Facebook URL")    { |person| person.facebook_url }
    person("Linked In Url")   { |person| person.linked_in_url }
    person("Tags")            { |person| person.tags.join("|") }
    person("Do Not Email")    { |person| person.do_not_email }
    person("Do Not Call")     { |person| person.do_not_call }
    person("Household Name")  { |person| person.household && person.household.name }
  end

  comma :ticket_sale do
    created_at_local_to_organization("Date of Purchase")

    event_name("Performance Title")
    datetime_local_to_organization("Performance Date-Time")
    payment_method("Payment Method")
    price("Ticket Price") { |cents| number_to_currency(cents.to_f/100) }
    item("Ticket Type") { |item| item.product.ticket_type && item.product.ticket_type.name  }
    special_instructions("Special Instructions")
    notes("Notes")
    creator("Creator")

    person("Email")           { |person| person.email }
    person("Salutation")      { |person| person.salutation }
    person("First Name")      { |person| person.first_name }
    person("Middle Name")     { |person| person.middle_name }
    person("Last Name")       { |person| person.last_name }
    person("Suffix")          { |person| person.suffix }
    person("Nickname")        { |person| person.nickname }
    person("Listing Name")    { |person| person.listing_name }
    person("Maiden Name")     { |person| person.maiden_name }
    person("Title")           { |person| person.title }
    person("Type")            { |person| person.type }
    person("Subtype")         { |person| person.subtype }
    person("Company Name")    { |person| person.company_name }

    ('A'..'C').each_with_index do |letter, i|
      person("Address#{letter} Type")        { |person| person.addresses[i] && person.addresses[i].kind }
      person("Address#{letter} Address 1")   { |person| person.addresses[i] && person.addresses[i].address1 }
      person("Address#{letter} Address 2")   { |person| person.addresses[i] && person.addresses[i].address2 }
      person("Address#{letter} City")        { |person| person.addresses[i] && person.addresses[i].city }
      person("Address#{letter} State")       { |person| person.addresses[i] && person.addresses[i].state }
      person("Address#{letter} Zip")         { |person| person.addresses[i] && person.addresses[i].zip }
      person("Address#{letter} Country")     { |person| person.addresses[i] && person.addresses[i].country }
    end

    (1..3).each do |i|
      person("Phone#{i} type")          { |person| person.phones[i-1] && person.phones[i-1].kind }
      person("Phone#{i} number")        { |person| person.phones[i-1] && person.phones[i-1].number }
    end

    person("Website")         { |person| person.website }
    person("Twitter Handle")  { |person| person.twitter_handle }
    person("Facebook URL")    { |person| person.facebook_url }
    person("Linked In Url")   { |person| person.linked_in_url }
    person("Tags")            { |person| person.tags.join("|") }
    person("Do Not Email")    { |person| person.do_not_email }
    person("Do Not Call")     { |person| person.do_not_call }
    person("Household Name")  { |person| person.household && person.household.name }
  end

  comma :membership_sale do
    created_at_local_to_organization("Date of Purchase")

    item('Membership Type') { |item| item.product.membership_type.name }
    payment_method("Payment Method")
    price("Price") { |cents| number_to_currency(cents.to_f/100) }
    special_instructions("Special Instructions")
    notes("Notes")

    person("Email")           { |person| person.email }
    person("Salutation")      { |person| person.salutation }
    person("First Name")      { |person| person.first_name }
    person("Middle Name")     { |person| person.middle_name }
    person("Last Name")       { |person| person.last_name }
    person("Suffix")          { |person| person.suffix }
    person("Nickname")        { |person| person.nickname }
    person("Listing Name")    { |person| person.listing_name }
    person("Maiden Name")     { |person| person.maiden_name }
    person("Title")           { |person| person.title }
    person("Type")            { |person| person.type }
    person("Subtype")         { |person| person.subtype }
    person("Company Name")    { |person| person.company_name }

    ('A'..'C').each_with_index do |letter, i|
      person("Address#{letter} Type")        { |person| person.addresses[i] && person.addresses[i].kind }
      person("Address#{letter} Address 1")   { |person| person.addresses[i] && person.addresses[i].address1 }
      person("Address#{letter} Address 2")   { |person| person.addresses[i] && person.addresses[i].address2 }
      person("Address#{letter} City")        { |person| person.addresses[i] && person.addresses[i].city }
      person("Address#{letter} State")       { |person| person.addresses[i] && person.addresses[i].state }
      person("Address#{letter} Zip")         { |person| person.addresses[i] && person.addresses[i].zip }
      person("Address#{letter} Country")     { |person| person.addresses[i] && person.addresses[i].country }
    end

    (1..3).each do |i|
      person("Phone#{i} type")          { |person| person.phones[i-1] && person.phones[i-1].kind }
      person("Phone#{i} number")        { |person| person.phones[i-1] && person.phones[i-1].number }
    end

    person("Website")         { |person| person.website }
    person("Twitter Handle")  { |person| person.twitter_handle }
    person("Facebook URL")    { |person| person.facebook_url }
    person("Linked In Url")   { |person| person.linked_in_url }
    person("Tags")            { |person| person.tags.join("|") }
    person("Do Not Email")    { |person| person.do_not_email }
    person("Do Not Call")     { |person| person.do_not_call }
  end

  comma :pass_sale do
    created_at_local_to_organization("Date of Purchase")

    item('Pass Type') { |item| item.product.pass_type.name }
    payment_method("Payment Method")
    price("Price") { |cents| number_to_currency(cents.to_f/100) }
    special_instructions("Special Instructions")
    notes("Notes")

    person("Email")           { |person| person.email }
    person("Salutation")      { |person| person.salutation }
    person("First Name")      { |person| person.first_name }
    person("Middle Name")     { |person| person.middle_name }
    person("Last Name")       { |person| person.last_name }
    person("Suffix")          { |person| person.suffix }
    person("Nickname")        { |person| person.nickname }
    person("Listing Name")    { |person| person.listing_name }
    person("Maiden Name")     { |person| person.maiden_name }
    person("Title")           { |person| person.title }
    person("Type")            { |person| person.type }
    person("Subtype")         { |person| person.subtype }
    person("Company Name")    { |person| person.company_name }

    ('A'..'C').each_with_index do |letter, i|
      person("Address#{letter} Type")        { |person| person.addresses[i] && person.addresses[i].kind }
      person("Address#{letter} Address 1")   { |person| person.addresses[i] && person.addresses[i].address1 }
      person("Address#{letter} Address 2")   { |person| person.addresses[i] && person.addresses[i].address2 }
      person("Address#{letter} City")        { |person| person.addresses[i] && person.addresses[i].city }
      person("Address#{letter} State")       { |person| person.addresses[i] && person.addresses[i].state }
      person("Address#{letter} Zip")         { |person| person.addresses[i] && person.addresses[i].zip }
      person("Address#{letter} Country")     { |person| person.addresses[i] && person.addresses[i].country }
    end

    (1..3).each do |i|
      person("Phone#{i} type")          { |person| person.phones[i-1] && person.phones[i-1].kind }
      person("Phone#{i} number")        { |person| person.phones[i-1] && person.phones[i-1].number }
    end

    person("Website")         { |person| person.website }
    person("Twitter Handle")  { |person| person.twitter_handle }
    person("Facebook URL")    { |person| person.facebook_url }
    person("Linked In Url")   { |person| person.linked_in_url }
    person("Tags")            { |person| person.tags.join("|") }
    person("Do Not Email")    { |person| person.do_not_email }
    person("Do Not Call")     { |person| person.do_not_call }
  end

  comma :all_sales do
    created_at_local_to_organization("Date of Purchase")
    order_id("Order Id")
    product_type("Item")
    item_id("Detail") { |item_id| ItemView.product_type_name(item_id, self.instance_variable_get("@instance").organization_id)  }
    payment_method("Payment Method")
    price("Price") { |cents| number_to_currency(cents.to_f/100) }
    special_instructions("Special Instructions")
    notes("Notes")
    creator("Creator")

    person("Email")           { |person| person.email }
    person("Salutation")      { |person| person.salutation }
    person("First Name")      { |person| person.first_name }
    person("Middle Name")     { |person| person.middle_name }
    person("Last Name")       { |person| person.last_name }
    person("Suffix")          { |person| person.suffix }
    person("Nickname")        { |person| person.nickname }
    person("Listing Name")    { |person| person.listing_name }
    person("Title")           { |person| person.title }
    person("Type")            { |person| person.type }
    person("Subtype")         { |person| person.subtype }
    person("Company Name")    { |person| person.company_name }

    ('A'..'C').each_with_index do |letter, i|
      person("Address#{letter} Type")        { |person| person.addresses[i] && person.addresses[i].kind }
      person("Address#{letter} Address 1")   { |person| person.addresses[i] && person.addresses[i].address1 }
      person("Address#{letter} Address 2")   { |person| person.addresses[i] && person.addresses[i].address2 }
      person("Address#{letter} City")        { |person| person.addresses[i] && person.addresses[i].city }
      person("Address#{letter} State")       { |person| person.addresses[i] && person.addresses[i].state }
      person("Address#{letter} Zip")         { |person| person.addresses[i] && person.addresses[i].zip }
      person("Address#{letter} Country")     { |person| person.addresses[i] && person.addresses[i].country }
    end

    (1..3).each do |i|
      person("Phone#{i} type")          { |person| person.phones[i-1] && person.phones[i-1].kind }
      person("Phone#{i} number")        { |person| person.phones[i-1] && person.phones[i-1].number }
    end

    person("Website")         { |person| person.website }
    person("Twitter Handle")  { |person| person.twitter_handle }
    person("Facebook URL")    { |person| person.facebook_url }
    person("Linked In Url")   { |person| person.linked_in_url }
    person("Tags")            { |person| person.tags.join("|") }
    person("Do Not Email")    { |person| person.do_not_email }
    person("Do Not Call")     { |person| person.do_not_call }
    person("Household Name")  { |person| person.household && person.household.name }
  end
end