-- SQL Schema Definition

CREATE TABLE IF NOT EXISTS demog (
    personID INTEGER PRIMARY KEY AUTOINCREMENT,
    regID INTEGER NOT NULL,
    LastName VARCHAR(30),
    FirstName VARCHAR(30),
    MiddleName VARCHAR(25),
    Gender VARCHAR(10) CHECK (Gender IN ('Male', 'Female', 'Other')),
    DOB DATE,
    SSN VARCHAR(9) DEFAULT '' NULL,
    notes VARCHAR(255),
    display VARCHAR(3) CHECK (display IN ('yes', 'no')) DEFAULT 'no'
);

-- Create an index on regID if needed
CREATE INDEX IF NOT EXISTS regID_idx ON demog (regID);

-- Insert new record only if no edit is in progress
INSERT INTO demog (
    regID,
    LastName,
    FirstName,
    MiddleName,
    Gender,
    DOB,
    SSN,
    notes,
    display
)
SELECT
    :regID,
    :LastName,
    :FirstName,
    :MiddleName,
    :Gender,
    :DOB,
    :SSN,
    :notes,
    :display
WHERE
    :regID       IS NOT NULL AND
    :DOB         IS NOT NULL AND
    :LastName    IS NOT NULL AND
    :FirstName   IS NOT NULL AND
    $edit        IS NULL;  -- Only insert if no edit is in progress

-- Update the existing record only if editing
UPDATE 
    demog
SET 
    regID =       :regID,
    LastName =    :LastName,
    FirstName =   :FirstName,
    MiddleName =  :MiddleName,
    Gender =      :Gender,
    DOB =         :DOB,
    SSN =         :SSN,
    notes =       :notes,
    display =     :display
WHERE 
    personID = $edit   AND
    :regID            IS NOT NULL AND
    :DOB              IS NOT NULL AND
    :LastName         IS NOT NULL AND
    :FirstName        IS NOT NULL;

-- Delete the record
DELETE FROM demog WHERE personID = $delete;

-- Form for adding or editing
SELECT
    'form'            AS component,
    'Add/edit patient:'  AS title;
SELECT
    (SELECT regID FROM demog WHERE personID = $edit)  AS value,
    'regID'         AS name,
    'number'        AS type;
SELECT
    (SELECT LastName FROM demog WHERE personID = $edit)  AS value,
    'LastName'      AS name;
SELECT
    (SELECT FirstName FROM demog WHERE personID = $edit)  AS value,
    'FirstName'     AS name;
SELECT 
    (SELECT MiddleName FROM demog WHERE personID = $edit)  AS value,
    'MiddleName'    AS name;
SELECT 
    (SELECT Gender FROM demog WHERE personID = $edit)  AS value,
    'Gender'        AS name;
SELECT 
    (SELECT SSN FROM demog WHERE personID = $edit)  AS value,
    'SSN'           AS name;
SELECT 
    (SELECT notes FROM demog WHERE personID = $edit)  AS value,
    'notes'         AS name;
SELECT
    (SELECT DOB FROM demog WHERE personID = $edit)  AS value,
    'DOB'           AS name,
    'date'          AS type,
    '2010-01-01'    AS max;
SELECT
    (SELECT display FROM demog WHERE personID = $edit)  AS value,
    'display'       AS name,
    'select'        AS type,
    '[{"label": "no", "value": "no"}, {"label": "yes", "value": "yes"}]' AS options,
    'no'            AS value;

-- Button for adding new entry
SELECT 'button' as component, 'center' as justify;
SELECT '?add=1' as link, 'Add New' as title;

-- Table to display demog records
SELECT
    'table'         AS component,
    'Demog:'        AS title,
    'Edit'          AS markdown, 
    'Remove'        AS markdown, 
    TRUE            AS sort,
    TRUE            AS search;
SELECT
    personID        AS personID,
    regID           AS regID,
    LastName        AS LastName,
    FirstName       AS FirstName,
    MiddleName      AS MiddleName,
    Gender          AS Gender,
    DOB             AS Date_Of_Birth,
    SSN             AS SSN,
    notes           AS notes,
    display         AS display,
    '[Edit](?edit=' || personID || ')' AS Edit,
    '[X](?delete=' || personID || ')' AS Remove
FROM demog;