--
-- Name: atna_instruction_instance; Type: TABLE; Schema: public; Owner: gazelle
--

CREATE TABLE public.atna_instruction_instance (
    id integer NOT NULL,
    testing_session_id integer NOT NULL,
    content text,
    language character varying(255),
    section VARCHAR(64)
);

ALTER TABLE public.atna_instruction_instance OWNER TO gazelle;

--
-- Name: atna_instruction_instance_id_seq; Type: SEQUENCE; Schema: public; Owner: gazelle
--

CREATE SEQUENCE public.atna_instruction_instance_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1;

ALTER TABLE public.atna_instruction_instance_id_seq OWNER TO gazelle;

--
-- Name: atna_instruction_instance atna_quest_instruction_pkey; Type: CONSTRAINT; Schema: public; Owner: gazelle
--

ALTER TABLE ONLY public.atna_instruction_instance
    ADD CONSTRAINT atna_instruction_instance_pkey PRIMARY KEY (id);

--
-- Name: atna_instruction_instance uk_nwlr4p68kn6gynd9g29pxp2lf; Type: CONSTRAINT; Schema: public; Owner: gazelle
--

ALTER TABLE ONLY public.atna_instruction_instance
    ADD CONSTRAINT uk_nwlr4p68kn6gynd9g29pxp2lf UNIQUE (testing_session_id, section, language);

--
-- Name: atna_instruction_instance fk95bebe4a73b1fccb; Type: FK CONSTRAINT; Schema: public; Owner: gazelle
--

ALTER TABLE ONLY public.atna_instruction_instance
    ADD CONSTRAINT fk95bebe4a73b1fccb FOREIGN KEY (testing_session_id) REFERENCES public.atna_testing_session(id);

-- ATNA Instruction
-- Create a new column for migrating enum string values
ALTER TABLE atna_quest_instruction ADD COLUMN new_section VARCHAR(64);
-- Migrate ordinal values to String in the new column
UPDATE atna_quest_instruction SET new_section='INBOUND_NETWORK_COMMUNICATION' WHERE section=0;
UPDATE atna_quest_instruction SET new_section='OUTBOUND_NETWORK_COMMUNICATION' WHERE section=1;
UPDATE atna_quest_instruction SET new_section='AUTHENTICATION_PROCESS' WHERE section=2;
UPDATE atna_quest_instruction SET new_section='AUDIT_MESSAGES' WHERE section=3;
UPDATE atna_quest_instruction SET new_section='ACCESS_PHI' WHERE section=4;
UPDATE atna_quest_instruction SET new_section='TLS_TESTS' WHERE section=5;
UPDATE atna_quest_instruction SET new_section='INTRODUCTION' WHERE section=6;
-- Drop the old column
ALTER TABLE atna_quest_instruction DROP COLUMN section ;
-- Rename the new column to match the entity declaration
ALTER TABLE atna_quest_instruction RENAME COLUMN new_section TO section ;

DO $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT distinct testing_session_id
          FROM atna_questionnaire
          ORDER BY testing_session_id
    LOOP
   insert into atna_instruction_instance(id, testing_session_id, content, language, section)
     select nextval('atna_instruction_instance_id_seq'), rec.testing_session_id, content, language, section from atna_quest_instruction;
    END LOOP;
END $$