-- name: CreateDocuments :one
INSERT INTO documents (
    name,
    description,
    documentusage,
    iscrop,
    isglobal
 ) VALUES (
    $1,
    $2,
    $3,
    $4,
    $5
 ) RETURNING *;


-- name: CreateDocumentCountries :one
INSERT INTO document_countries (
    country_id,
    document_id
 ) VALUES (
    $1,
    $2
 ) RETURNING *;

-- name: CreateDocumentCrops :one
INSERT INTO document_crops (
    crop_id,
    document_id
 ) VALUES (
    $1,
    $2
 ) RETURNING *;

-- name: GetDocument :one
SELECT * FROM documents
WHERE id = $1 LIMIT 1;

-- name: ListDocuments :many
SELECT * FROM documents;

-- name: ListCropDocuments :many
SELECT  documents.id, crops.cropname, documents.name,documents.description,
documents.documentusage,documents.iscrop,documents.isglobal
from    document_crops
JOIN    documents on documents.id = document_crops.document_id
JOIN    crops     on crops.id     = document_crops.crop_id
where document_crops.crop_id      = $1;

-- name: AllDocumentDetails :many
SELECT name,description,documentusage,iscrop,isglobal,crops.cropname, countries.countryname FROM documents
INNER JOIN document_crops on document_crops.document_id = documents.id
INNER JOIN document_countries on document_countries.document_id = documents.id
INNER JOIN countries on countries.id = document_countries.country_id
INNER JOIN crops     on crops.id     = document_crops.crop_id
WHERE documents.id = $1;


-- name: DeleteDocument :exec
DELETE FROM documents WHERE id= $1;


-- name: CheckDocumentIDReceptionDocs :one
SELECT EXISTS (
    select 1 from reception_documents
    where document_id = $1
    AND   receptionform_id = $2
);

-- name: UpdateReceptionDocumentsBasedDocumentID :one
UPDATE reception_documents
  set
    document = $2
WHERE receptionform_id = $1
AND   document_id  = $3
RETURNING *;
