;;; Holy yeah

#lang racket

(require (for-syntax racket/syntax))
(require racket/format)


(provide (all-from-out racket)
    CREATE
    INSERT
    SELECT
    DELETE
    EXPORT
    ;;; EVAL
    ;;; PARSE-FUNC
    ;;; PARSE-NAME
    ;;; PARSE-NAMES-SELECT
    ;;; PARSE-FUNC-SELECT
    ;;; SHOW
    ;;; convert-contents
    ;;; natural-join
    ;;; natural-join-relations
    ;;; relation-contents
    ;;; write-contents
    ;;; triple-name
    ;;; triple-value
)

(struct triple (name type value)
    #:mutable
    #:transparent
)

(struct tuple (name type)
    #:mutable
    #:transparent
)

(struct relation (headers contents)
    #:mutable
    #:transparent
)

;------------------------------------------- Some utility functions ---------------------------------------
(define/contract (triple-contain-name? trpl name)
    (triple? symbol? . -> . boolean?)
    (equal? (triple-name trpl) name)
)

(define/contract (tuple-contain-name? attr name)
    (tuple? symbol? . -> . boolean?)
    (equal? (tuple-name attr) name)
)

(define (pretty-print-f str)
    (define b (~s str
    #:align 'center
    #:width 15
    #:pad-string " "))
    (display b)
)

(define (write-headers hdrs)
    (define (match-type t)
        (cond   [(equal? t integer?) 'integer]
                [(equal? t string?) 'string]
                [else 'other]
        )
    )

    (define (write-headers-name hds)
        (cond   [(empty? hds) (printf "~n")]
            [else (
                let ()
                ;;; (pretty-print-f (string->symbol (tuple-name (car hds))))
                (pretty-print-f (tuple-name (car hds)))
                (printf "|")
                (write-headers-name (cdr hds))
            )]
        )
    )

    (define (write-headers-type hds)
        (cond   [(empty? hds) (printf "~n")]
            [else (
                let ()
                (pretty-print-f (match-type (tuple-type (car hds))))
                (printf "|")
                (write-headers-type (cdr hds))
            )]
        )
    )
    (printf "|")
    (write-headers-name hdrs)
    (printf "|")
    (write-headers-type hdrs)
)

(define (write-contents cnts)
    (for-each
        (lambda (cnt)
            (for-each
                (lambda (trpl)
                    (printf "|")
                    (pretty-print-f (triple-value trpl))
                )
                cnt
            )
            (printf "|")
            (printf "~n")
        )
        cnts
    )
)

(define (SHOW rel)
    (define headers (relation-headers rel))
    (define contents (relation-contents rel))

    (define n-cols (length headers))
    (define n-chars (+ (* n-cols 16) 1))





    (display (make-string n-chars #\-)) (newline)

    (write-headers headers)
    (display (make-string n-chars #\-)) (newline)
    (write-contents contents)
    (display (make-string n-chars #\-)) (newline)
)

; content -> get value by name
(define (content-get-value-by-name content name)

    (define found-triple
        (filter (lambda (trpl) (equal? (triple-name trpl) name)) content)
    )

    (if (empty? found-triple) (error "Not have the column ~a" name) (triple-value (car found-triple)))
)

; Two have same col names
(define (union-two-relations rel1 rel2)
    (define headers (relation-headers rel1))
    (define union-contents (remove-duplicates (append (relation-contents rel1) (relation-contents rel2)) equal?))
    (relation headers union-contents)
)

(define (intersect-two-relations rel1 rel2)
    (define headers (relation-headers rel1))
    (define intersect-contents (
        filter (lambda (cnt) (member cnt (relation-contents rel2))) (relation-contents rel1)
    ))
    (relation headers intersect-contents)
)

(define (union-two-lists-contents contents1 contents2)
    (remove-duplicates (append contents1 contents2) equal?)
)

(define (intersect-two-lists-contents contents1 contents2)
    (filter (lambda (cnt) (member cnt contents2)) contents1)
)

;------------------------------------------- END ---------------------------------------

(define-syntax (print-hehe stx)
    (define (conv expression)
        (with-syntax ([e expression])
            (symbol->string (quote e)))
    )

    (syntax-case stx ()
        [(_ expr)
            (with-syntax
                (
                )

                #'(begin
                    (write expr
                    )
                    (newline)
                )
            )
        ]
    )
)

(define (make-empty-relation raw-headers)

    (define (match-type t)
        (cond   [(equal? t 'integer) integer?]
                [(equal? t 'string) string?]
                [else (error "Wrong type...")]
        )
    )

    (define (looop hdrs)
        (cond   [(empty? hdrs) '()]
                [else (
                    let ()
                    (define h (car hdrs))
                    (define name (car h))
                    (define type (match-type (car (cdr h))))

                    ;;; (write name) (newline)
                    ;;; (write type) (newline)

                    (cons (tuple name type) (looop (cdr hdrs)))
                )]
        )
    )

    (define headers (looop raw-headers))
    (relation headers '())
)

(define-syntax (CREATE stx)
    (define (conv expression)
        (with-syntax ([e expression])
            #'(symbol->string (quote e)))
    )

    (define (conv-header expression)
        (with-syntax ([e expression])
            #'(quote e)
        )
    )

    (syntax-case stx ()
        [
            (_ relname (raw-header ...))

            (let
                ()

                (with-syntax
                    (
                        [
                            (converted-header ...)
                            (map conv-header
                                (syntax->list #' (raw-header ...))
                            )
                        ]
                    )

                    #'(begin
                            ;;; (define headers (list converted-header ...))
                            (define relname (make-empty-relation (list converted-header ...)))
                    )
                )
            )
        ]
    )
)

;;; contents are raw (just list of list)
(define (insert-raw rel raw-contents)
    (define headers (relation-headers rel))

    (define syntax-ok #t)

    (define (loop-triples h b)
        (cond   [(empty? h) '()]
                [else (
                    let ()
                    (define name (tuple-name (car h)))
                    (define value (car b))
                    (define type (tuple-type (car h)))

                    (if (type value)
                        (cons (triple name type value) (loop-triples (cdr h) (cdr b)))
                        (begin
                            (set! syntax-ok #f)
                            '()
                        )
                    )
                )]
        )
    )

    (define (loop-rows h bs)
        (cond   [(empty? bs) '()]
                [else (
                    let ()
                    (if syntax-ok

                        (let ()
                            (define b (car bs))
                            (if (= (length h) (length b))
                                (cons (loop-triples h b) (loop-rows h (cdr bs)))
                                (begin
                                    ;;; (write "raw fail 2") (newline)
                                    (set! syntax-ok #f)
                                    '()
                                )
                            )
                        )

                        '()
                    )
                )]
        )
    )

    (cond
        [(empty? raw-contents) (void)]

        [(list? (car raw-contents)) (
            let ()
            (define contents (loop-rows headers raw-contents))
            (if syntax-ok
                (let ([appended-contents (remove-duplicates (append (relation-contents rel) contents))])
                    (set-relation-contents! rel appended-contents)
                )

                (begin
                    (display "Error input - no rows inserted ...") (newline)
                )
            )
        )]

        [else (
            let ()
            (define contents (loop-rows headers (list raw-contents)))
            (if syntax-ok
                (let ([appended-contents (remove-duplicates (append (relation-contents rel) contents))])
                    (set-relation-contents! rel appended-contents)
                )

                (begin
                    (display "Error input - no rows inserted ...") (newline)
                )
            )
        )]
    )
)

;;; contents are formatted - in the same form as contents of relations
(define (insert rel contents)
    (define headers (relation-headers rel))

    (define syntax-ok #t)

    (define (loop-triples h b)
        (cond   [(empty? h) '()]
                [else (
                    let ()
                    (define trpl (car b))
                    (define name (tuple-name (car h)))
                    (define type (tuple-type (car h)))
                    (define value (triple-value trpl))
                    (define name-t (triple-name trpl))
                    (define type-t (triple-type trpl))

                    (if (and (and (equal? name name-t) (equal? type type-t)) (type value))
                        (cons (triple name type value) (loop-triples (cdr h) (cdr b)))
                        (begin
                            (write "bt fail 1") (newline)
                            (write "b") (newline)
                            (write trpl) (newline)
                            (set! syntax-ok #f)
                            '()
                        )
                    )
                )]
        )
    )

    (define (loop-rows h bs)
        (cond   [(empty? bs) '()]
                [else (
                    let ()
                    (if syntax-ok

                        (let ()
                            (define b (car bs))
                            (if (= (length h) (length b))
                                (cons (loop-triples h b) (loop-rows h (cdr bs)))
                                (begin
                                    (write "bt fail 2") (newline)
                                    (set! syntax-ok #f)
                                    '()
                                )
                            )
                        )

                        '()
                    )
                )]
        )
    )

    (cond
        [(empty? contents) (void)]
        [else (
            let ()
            (define return-contents (loop-rows headers contents))
            (if syntax-ok
                (let ()
                    (define appended-contents (remove-duplicates (append (relation-contents rel) return-contents) equal?))
                    (set-relation-contents! rel appended-contents)
                )

                (begin
                    (display "Error input - no rows inserted...") (newline)
                )
            )
        )]
    )
)

(define-syntax (INSERT stx)

    (define (conv-content expression)
        (with-syntax ([e expression])
            #'(quote e))
    )

    (syntax-case stx (INTO VALUES SELECT)
        [(_ INTO relname VALUES raw-content ...)

            (with-syntax
                (
                    [
                        (converted-content ...)
                        (map conv-content
                            (syntax->list #' (raw-content ...))
                        )
                    ]
                )

                #'(begin
                    (let
                        (
                            [converted-contents (list converted-content ...)]
                        )

                        (insert-raw relname converted-contents)
                    )
                )
            )
        ]

        [(_ INTO relname SELECT x ...)

            (with-syntax
                (
                    [contents
                        #'(SELECT x ...)
                    ]
                )

                ;;; #'(begin
                ;;;         (SHOW relname) (newline)
                ;;;         (write-contents contents) (newline)
                ;;;         (insert-raw relname contents)
                ;;; )

                #'(insert-raw relname contents)
            )
        ]
    )
)

;;; relation and predicate to filter
(define (where contents pred)
    (filter
        pred
        contents
    )
)

(define-syntax (get-real-op stx)
    (syntax-case stx ()
        [(_ op value1 value2)
            (with-syntax
                (
                    [real-op
                        (cond   [(equal? (symbol->string (syntax->datum #'op)) "!=")
                                    #'(not (equal? value1 value2))
                                ]

                                [(equal? (symbol->string (syntax->datum #'op)) "=")
                                    #'(equal? value1 value2)
                                ]

                                [else #'(op value1 value2)]
                        )
                    ]
                )

                #'(begin
                    real-op
                )
            )
        ]
    )
)

(define-syntax (WHEREH stx)
    (syntax-case stx (= != > < >= <= AND OR)

        [(_ contents (expr1 AND expr2))
            (with-syntax
                (
                    [get-expr1
                        #'(WHEREH contents expr1)
                    ]

                    [get-expr2
                        #'(WHEREH contents expr2)
                    ]
                )

                #'(begin
                    (let ([c1 get-expr1] [c2 get-expr2])
                        (intersect-two-lists-contents c1 c2)
                    )
                )
            )
        ]

        [(_ contents (expr1 OR expr2))
            (with-syntax
                (
                    [get-expr1
                        #'(WHEREH contents expr1)
                    ]

                    [get-expr2
                        #'(WHEREH contents expr2)
                    ]
                )

                #'(begin
                    (let ([c1 get-expr1] [c2 get-expr2])
                        (union-two-lists-contents c1 c2)
                    )
                )
            )
        ]

        [(_ contents expr1 AND expr2 ...)
            (with-syntax
                (
                    [get-expr1
                        #'(WHEREH contents expr1)
                    ]

                    [get-expr2
                        #'(WHEREH contents expr2 ...)
                    ]
                )

                #'(begin
                    (let ([c1 get-expr1] [c2 get-expr2])
                        (intersect-two-lists-contents c1 c2)
                    )
                )
            )
        ]

        [(_ contents expr1 OR expr2 ...)
            (with-syntax
                (
                    [get-expr1
                        #'(WHEREH contents expr1)
                    ]

                    [get-expr2
                        #'(WHEREH contents expr2 ...)
                    ]
                )

                #'(begin
                    (let ([c1 get-expr1] [c2 get-expr2])
                        (union-two-lists-contents c1 c2)
                    )
                )
            )
        ]

        [(_ contents (expr1 op expr2))
            (with-syntax
                (
                    [compare
                        #'(begin
                            (lambda (cnt)
                                (let ([value1 (EVAL cnt expr1)] [value2 (EVAL cnt expr2)])
                                    (get-real-op op value1 value2)
                                )
                            )
                        )
                    ]
                )

                #'(begin
                    (where contents compare)
                )
            )
        ]
    )
)

(define-syntax (EVAL stx)
    (syntax-case stx ()
        [(_ content (e1 (e2 e3)))
            #'(e1 (EVAL content (e2 e3)))
        ]

        [(_ content (e1 e2))
            #'(e1 (EVAL content e2))
        ]

        [(_ content e)
            (with-syntax
                (
                    [value
                        (cond   [(symbol? (syntax->datum #'e))
                                    #'(content-get-value-by-name content 'e)
                                ]

                                [else
                                    #'e
                                ]
                        )
                    ]
                )

                #'value
            )
        ]
    )
)

;;; stx here is for example (a (b (c X))) -> '(c b a)
(define-syntax (PARSE-FUNC stx)
    (syntax-case stx ()
        [(_ (e1 (e2 e3)))
            (with-syntax
                (
                    [rest
                        #'(PARSE-FUNC (e2 e3))
                    ]
                )

                #'(flatten (list rest e1))
            )
        ]

        [(_ (e1 e2))
            #'(list e1)
        ]
    )
)

;;; stx here is for example (a (b (c X))) -> 'X
(define-syntax (PARSE-NAME stx)
    (syntax-case stx ()
        [(_ (_ (e2 e3)))
            #'(PARSE-NAME (e2 e3))
        ]

        [(_ (_ e2))
            #'(quote e2)
        ]
    )
)

(define-syntax (FROMH stx)
    (syntax-case stx ()
        [(_ (rel))
            #'(relation-contents rel)
        ]

        [(_ (rel1 rel2))
            (with-syntax
                (
                    [c1
                        #'(FROMH (rel1))
                    ]

                    [c2
                        #'(FROMH (rel2))
                    ]
                )

                #'(natural-join c1 c2)
            )
        ]

        [(_ (rel1 rel2 rel3 ...))
            (with-syntax
                (
                    [c1
                        #'(FROMH (rel1 rel2))
                    ]

                    [c2
                        #'(FROMH (rel3 ...))
                    ]
                )

                #'(natural-join c1 c2)
            )
        ]
    )
)

;;; a b c MAX(d) -> ('a 'b 'c 'd)
(define-syntax (PARSE-NAMES-SELECT stx)
    (syntax-case stx (MIN MAX MED)

        [(_ MIN(a))
            #'(list 'a)
        ]

        [(_ MIN(a) b ...)
            #'(flatten (list 'a (PARSE-NAMES-SELECT b ...)))
        ]

        [(_ MAX(a))
            #'(list 'a)
        ]

        [(_ MAX(a) b ...)
            #'(flatten (list 'a (PARSE-NAMES-SELECT b ...)))
        ]

        [(_ MED(a))
            #'(list 'a)
        ]

        [(_ MED(a) b ...)
            #'(flatten (list 'a (PARSE-NAMES-SELECT b ...)))
        ]

        [(_ a)
            #'(list 'a)
        ]

        [(_ a b ...)
            #'(flatten (list 'a (PARSE-NAMES-SELECT b ...)))
        ]
    )
)

;;; a b c MAX(d) -> ('MAX 'd)
(define-syntax (PARSE-FUNC-SELECT stx)
    (syntax-case stx (MIN MAX MED)

        [(_ MIN(a))
            #'(list 'MIN 'a)
        ]

        [(_ MIN(a) b ...)
            #'(list 'MIN 'a)
        ]

        [(_ MAX(a))
            #'(list 'MAX 'a)
        ]

        [(_ MAX(a) b ...)
            #'(list 'MAX 'a)
        ]

        [(_ MED(a))
            #'(list 'MED 'a)
        ]

        [(_ MED(a) b ...)
            #'(list 'MED 'a)
        ]

        [(_ a)
            #'(list 'NAH 'a)
        ]

        [(_ a b ...)
            #'(PARSE-FUNC-SELECT b ...)
        ]
    )
)

(define-syntax (SELECT stx)

    (define (conv expression)
        (with-syntax ([e expression])
            #'(quote e))
    )

    (syntax-case stx (FROM WHERE *)

        [(_ * FROM (f ...) WHERE w ...)
            (with-syntax*
                (
                    [f-contents
                        #'(FROMH (f ...))
                    ]

                    [w-contents
                        #'(WHEREH f-contents w ...)
                    ]
                )

                #'(convert-contents (select '() w-contents #t))

            )
        ]

        [(_ * FROM (f ...))
            (with-syntax
                (
                    [f-contents
                        #'(FROMH (f ...))
                    ]
                )

                #'(convert-contents (select '() f-contents #t))

            )
        ]

        [(_ (s ...) FROM (f ...) WHERE w ...)
            (with-syntax*
                (
                    [f-contents
                        #'(FROMH (f ...))
                    ]

                    [w-contents
                        #'(WHEREH f-contents w ...)
                    ]

                    [names
                        #'(PARSE-NAMES-SELECT s ...)
                    ]

                    [spec
                        #'(PARSE-FUNC-SELECT s ...)
                    ]
                )

                #'(convert-contents (select-with-func names w-contents #f spec))

            )
        ]

        [(_ (s ...) FROM (f ...))
            (with-syntax*
                (
                    [f-contents
                        #'(FROMH (f ...))
                    ]

                    [names
                        #'(PARSE-NAMES-SELECT s ...)
                    ]

                    [spec
                        #'(PARSE-FUNC-SELECT s ...)
                    ]
                )

                #'(convert-contents (select-with-func names f-contents #f spec))
            )
        ]
    )
)

(define-syntax (SELECT-CONTENTS stx)
    (syntax-case stx (FROM WHERE *)

        [(_ * FROM (f ...) WHERE w ...)
            (with-syntax*
                (
                    [f-contents
                        #'(FROMH (f ...))
                    ]

                    [w-contents
                        #'(WHEREH f-contents w ...)
                    ]
                )

                #'(select '() w-contents #t)
            )
        ]
    )
)

(define (delete rel contents)
    (define original-contents (relation-contents rel))
    (define next-contents (remove* contents original-contents equal?))
    (set-relation-contents! rel next-contents)
)

(define-syntax (DELETE stx)
    (syntax-case stx (FROM WHERE *)
        [(_ FROM relname WHERE x ...)
            (with-syntax
                (
                    [selected-contents
                        #'(SELECT-CONTENTS * FROM (relname) WHERE x ...)
                    ]
                )

                #'(delete relname selected-contents)
            )
        ]
    )
)

;;; filename is string
(define (export relname filename)

    ;;; a row is a content
    ;;; loop through a row - triples
    (define (print-row row out-port)
        (define (print r o-p first?)
            (cond   [(empty? r)
                        (void)
                    ]
                    [else (
                        let ([value (triple-value (car r))])
                        (if first?
                            (begin
                                (write value o-p)
                                (print (cdr r) o-p #f)
                            )

                            (begin
                                (display "," o-p)
                                (write value o-p)
                                (print (cdr r) o-p #f)
                            )
                        )
                    )]
            )
        )
        (print row out-port #t)
    )

    ;;; loop through contents - list of rows
    (define (print-contents contents out-port)
        (define (print cnts o-p first?)
            (cond   [(empty? cnts)
                        (void)
                    ]

                    [else
                        (if first?
                            (begin
                                (print-row (car cnts) o-p)
                                (print (cdr cnts) o-p #f)
                            )

                            (begin
                                (display "\n" o-p)
                                (print-row (car cnts) o-p)
                                (print (cdr cnts) o-p #f)
                            )
                        )
                    ]
            )
        )

        (print contents out-port #t)
    )

    (define out-port (open-output-file filename #:mode 'text #:exists 'replace))
    (print-contents (relation-contents relname) out-port)
    (close-output-port out-port)
)

(define-syntax (EXPORT stx)
    (syntax-case stx ()
        [(_ relname filename)
            #'(export relname (symbol->string (quote filename)))
        ]
    )
)

;;; execute a list of functions on the initial variable var
;;; execute one by one function consecutively.
(define (exec funcs var)
    (cond   [(empty? funcs)
                var
            ]
            [else (
                let* ([f (car funcs)] [v (f var)])
                (exec (cdr funcs) v)
            )]
    )
)

;;; return contents (not headers) based on col-names of rel
(define (select col-names contents is-full?)
    (define (filter-each-content content col-names)
        (cond   [(empty? col-names) '()]
                [else (
                    let ()
                    (define name (car col-names))
                    (define filtered-content
                        (filter (lambda (x) (triple-contain-name? x name)) content
                        )
                    )
                    (if (empty? filtered-content) (error "Not have column " name) (cons (car filtered-content) (filter-each-content content (cdr col-names))))
                )]
        )
    )

    (cond   [(equal? is-full? #t)
                contents
            ]
            [else
                (map
                    (lambda (cntnt) (filter-each-content cntnt col-names))
                    contents
                )
            ]
    )
)

;;; return just ONE row
(define (select-with-func col-names contents is-full? spec)
    ;;; sort the contents by the column name
    (define (sort-contents contents name)
        (sort contents
            (lambda (cnt1 cnt2)
                (let (
                    [value1 (content-get-value-by-name cnt1 name)]
                    [value2 (content-get-value-by-name cnt2 name)]
                    )

                    (< value1 value2)
                )
            )
        )
    )

    (define (traverse-n sorted-contents n)
        (cond   [(equal? n 0)
                    (car sorted-contents)
                ]

                [else
                    (traverse-n (cdr sorted-contents) (- n 1))
                ]
        )
    )

    (define (max sorted-contents)
        (define l (length sorted-contents))
        (define n (- l 1))
        (traverse-n sorted-contents n)
    )

    (define (min sorted-contents)
        (define n 0)
        (traverse-n sorted-contents n)
    )

    (define (med sorted-contents)
        (define l (length sorted-contents))
        (define n
            (cond   [(equal? (modulo l 2) 0)
                        (- (/ l 2) 1)
                    ]

                    [else
                        (- (/ (+ l 1) 2) 1)
                    ]
            )
        )

        (traverse-n sorted-contents n)
    )

    (define selected-contents (select col-names contents is-full?))

    (cond   [(equal? (car spec) 'NAH)
                selected-contents
            ]
            [else (
                let ()
                (define func-literal (first spec))
                (define sort-name (last spec))
                (define sorted-contents (sort-contents selected-contents sort-name))
                (cond   [(equal? func-literal 'MAX)
                            (max sorted-contents)
                        ]

                        [(equal? func-literal 'MIN)
                            (min sorted-contents)
                        ]

                        [(equal? func-literal 'MED)
                            (med sorted-contents)
                        ]

                        [else
                            (error "Cannot find the correct func... " func-literal)
                        ]
                )
            )]
    )
)

(define (natural-join contents1 contents2)

    (define (construct-headers c)
        (cond   [(empty? c) '()]
                [else (
                    let ()
                    (define name (triple-name (car c)))
                    (define type (triple-type (car c)))

                    (cons (tuple name type) (construct-headers (cdr c)))
                )]
        )
    )

    ;;; get common column names of those contents by looping via the first content of each list
    (define (get-common-names hds1 hds2)
        (filter-map (lambda (x) (and (member x hds2) (tuple-name x))) hds1)
    )

    ;;; get the rest of the names after remove common names
    (define (remove-common-names names headers)
        (filter-map (lambda (x) (and (not (member (tuple-name x) names)) (tuple-name x))) headers)
    )

    ;;; o - origin
    ;;; f - filtered - contains only common names
    ;;; d - diff - contains the rest
    (define (carte-prod o-cont1 f-cont1 f-cont2 d-cont2)

        ;;; loop through each row of contents 2
        (define (loop-rel-1 o-row1 f-row1 f-cont2 d-cont2)
            (cond   [(empty? f-cont2) '()]
                    [else (
                        let ()
                        (define f-row2 (car f-cont2))
                        (define d-row2 (car d-cont2))

                        (define merged-row
                            (if (equal? f-row1 f-row2)
                                (append o-row1 d-row2)
                                '()
                            )
                        )

                        (cons merged-row (loop-rel-1 o-row1 f-row1 (cdr f-cont2) (cdr d-cont2)))
                    )]
            )
        )

        ;;; loop through each row of contents 1
        (define (loop-rel o-cont1 f-cont1 f-cont2 d-cont2)
            (cond   [(empty? f-cont1) '()]
                    [else (
                        let ()
                        (define o-row1 (car o-cont1))
                        (define f-row1 (car f-cont1))

                        (define merged-cont (loop-rel-1 o-row1 f-row1 f-cont2 d-cont2))

                        (append merged-cont (loop-rel (cdr o-cont1) (cdr f-cont1) f-cont2 d-cont2))
                    )]
            )
        )

        (loop-rel o-cont1 f-cont1 f-cont2 d-cont2)
    )

    (define headers1 (construct-headers (car contents1)))
    (define headers2 (construct-headers (car contents2)))

    (define common-names (get-common-names headers1 headers2))
    (define diff-names-rel2 (remove-common-names common-names headers2))

    (define o-cont1 contents1)
    (define o-cont2 contents2)

    (define f-cont1 (select common-names o-cont1 #f))
    (define f-cont2 (select common-names o-cont2 #f))

    (define d-cont2 (select diff-names-rel2 o-cont2 #f))

    (remove* (list '())
        (carte-prod o-cont1 f-cont1 f-cont2 d-cont2)
    )
)
;;; intersect

(define (natural-join-relations rel1 rel2)

    ;;; get common column names of rel1 and rel2 (headers)
    (define (get-common-names rel1 rel2)
        (define headers1 (relation-headers rel1))
        (define headers2 (relation-headers rel2))

        (filter-map (lambda (x) (and (member x headers2) (tuple-name x))) headers1)
    )

    ;;; get the rest of the names after remove common names
    (define (remove-common-names names rel)
        (filter-map (lambda (x) (and (not (member (tuple-name x) names)) (tuple-name x))) (relation-headers rel))
    )

    ;;; o - origin
    ;;; f - filtered - contains only common names
    ;;; d - diff - contains the rest
    (define (carte-prod o-cont1 f-cont1 f-cont2 d-cont2)

        ;;; loop through each row of contents 2
        (define (loop-rel-1 o-row1 f-row1 f-cont2 d-cont2)
            (cond   [(empty? f-cont2) '()]
                    [else (
                        let ()
                        (define f-row2 (car f-cont2))
                        (define d-row2 (car d-cont2))

                        (define merged-row
                            (if (equal? f-row1 f-row2)
                                (append o-row1 d-row2)
                                '()
                            )
                        )

                        (cons merged-row (loop-rel-1 o-row1 f-row1 (cdr f-cont2) (cdr d-cont2)))
                    )]
            )
        )

        ;;; loop through each row of contents 1
        (define (loop-rel o-cont1 f-cont1 f-cont2 d-cont2)
            (cond   [(empty? f-cont1) '()]
                    [else (
                        let ()
                        (define o-row1 (car o-cont1))
                        (define f-row1 (car f-cont1))

                        (define merged-cont (loop-rel-1 o-row1 f-row1 f-cont2 d-cont2))

                        (append merged-cont (loop-rel (cdr o-cont1) (cdr f-cont1) f-cont2 d-cont2))
                    )]
            )
        )

        (loop-rel o-cont1 f-cont1 f-cont2 d-cont2)
    )

    (define common-names (get-common-names rel1 rel2))
    (define diff-names-rel2 (remove-common-names common-names rel2))

    (define o-cont1 (relation-contents rel1))
    (define o-cont2 (relation-contents rel2))

    (define f-cont1 (select common-names o-cont1 #f))
    (define f-cont2 (select common-names o-cont2 #f))

    (define d-cont2 (select diff-names-rel2 o-cont2 #f))

    (remove* (list '())
        (carte-prod o-cont1 f-cont1 f-cont2 d-cont2)
    )
)

(define (extract-headers content)
    (define (loop trpls)
        (cond   [(empty? trpls) '()]
                [else (
                    let ()
                    (define trpl (car trpls))
                    (define tpl (tuple (triple-name trpl) (triple-type trpl)))
                    (cons tpl (loop (cdr trpls)))
                )]
        )
    )

    (loop content)
)

(define (convert-contents contents)
    (define (loop-row cnt)
        (cond   [(empty? cnt) '()]
                [else
                    (cons (triple-value (car cnt)) (loop-row (cdr cnt)))
                ]
        )
    )

    (define (loop-contents cnts)
        (cond   [(empty? cnts) '()]
                [else
                    (cons (loop-row (car cnts)) (loop-contents (cdr cnts)))
                ]
        )
    )

    (cond   [(empty? contents) '()]
            [(list? (car contents))
                (loop-contents contents)
            ]
            [else
                (loop-row contents)
            ]
    )
)