#this account table is just temporary
#may go into the sfGuard plugin instead
Account:
    actAs: [Timestampable]
    tableName: account
    columns:
        id:
            type: integer
            primary: true
            autoincrement: true
        first_name: string(255)
        last_name: string(255)
        email: string(255)
        password: string(255)

Customer:
    actAs: [Timestampable, SoftDelete]
    tableName: customer
    columns:
        customer_id:
            type: integer
            primary: true
            autoincrement: true
        name: { type: string(100), notnull: true }
        city: string(100)
        zipcode: int
        address: string(100)
        telephone: string(20)
        fax: string(20)
        homepage: string(100)
        #sales: integer
        information: string(500)
        contact_person: integer
        business_id: integer
    relations:
        Business: {onDelete: CASCADE, local: business_id, foreign: business_id}
        Person: {onDelete: CASCADE, local: contact_person, foreign: person_id}

#health, news, sports, cars, and so on
#just to show what customer the customer is doing, as business
Business:
    actAs: [Timestampable, SoftDelete]
    tableName: business
    columns:
        business_id:
            type: integer
            primary: true
            autoincrement: true
        name: { type: string(20), notnull: true }

Person:
    actAs: [Timestampable, SoftDelete]
    tableName: person
    columns:
        person_id:
            type: integer
            primary: true
            autoincrement: true
        first_name: { type: string(100), notnull: true }
        last_name:  { type: string(100), notnull: true }
        telephone: string(20)
        fax: string(20)
        email: string(100)
        note: string(500)
        category: integer
    relations:
        PersonCategory: {onDelete: CASCADE, local: category, foreign: person_category_id}

PersonCategory:
    actAs: [SoftDelete]
    tableName: person_category
    columns:
        person_category_id:
            type: integer
            primary: true
            autoincrement: true
        name: { type: string(100), notnull: true }

#a customer may have several emails for several purposes
#to list those with a description, could be nice (?)
CustomerEmail:
    actAs: [Timestampable, SoftDelete]
    tableName: customer_email
    columns:
        customer_email_id:
            type: integer
            primary: true
            autoincrement: true
        email: { type: string(100), notnull: true }
        description: string(100)

#this may be an anti-pattern
CustomerPerson:
    actAs: [Timestampable, SoftDelete]
    tableName: customer_person
    columns:
        customer_id:
            type: integer
            primary: true
            autoincrement: false
            notnull: true
        person_id:
            type: integer
            primary: true
            autoincrement: false
            notnull: true
    relations:
        Customer: {onDelete: CASCADE, local: customer_id, foreign: customer_id}
        Person: {onDelete: CASCADE, local: person_id, foreign: person_id}

#then this will too be an anti-pattern
CustomerEmails:
    actAs: [Timestampable, SoftDelete]
    tableName: customer_emails
    columns:
        customer_id:
            type: integer
            primary: true
            autoincrement: false
            notnull: true
        customer_email_id:
            type: integer
            primary: true
            autoincrement: false
            notnull: true
    relations:
        Customer:  {onDelete: CASCADE, local: customer_id, foreign: customer_id}
        CustomerEmail: {onDelete: CASCADE, local: customer_email_id, foreign: customer_email_id}