-- ENUMs
CREATE TYPE net_enum AS ENUM ('A','B','C','D','E','F','G','UNKNOWN');

CREATE TYPE js_enum AS ENUM (
    'ERROR','ABORTED','IN_PROGRESS','FINISHED',
    'FINISHED_WITH_WARNINGS','FINISHED_WITHOUT_COMMIT'
);

CREATE TYPE modus_enum AS ENUM ('Zyklus','Statik','Bewegung');


-- Parent-Tabelle (unpartitioniert bleibt so)
CREATE TABLE log_tab (
    id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    js js_enum NOT NULL,
    start_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    end_time TIMESTAMP NULL,
    modus modus_enum NOT NULL,
    z_options VARCHAR(45) NOT NULL,
    job_options VARCHAR(45) NOT NULL,
    execution_time TIME NOT NULL DEFAULT '00:00:00'
);


-- Partitionierte Tabelle
CREATE TABLE origtab_log (
    id BIGINT GENERATED ALWAYS AS IDENTITY,
    e_id BIGINT NOT NULL,
    net net_enum NOT NULL DEFAULT 'UNKNOWN',
    zyk CHAR(2) NOT NULL,
    origtab VARCHAR(45) NOT NULL,
    update_timestamp TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- WICHTIG: Partition Key im PK!
    PRIMARY KEY (id, update_timestamp),

    CONSTRAINT id_key_origtab_fraud
        FOREIGN KEY (e_id)
        REFERENCES log_tab (id)
)
PARTITION BY RANGE (update_timestamp);


--------------------------------------------------------------------------------
-- Beispiel-Partitionen anlegen (täglich)
--CREATE TABLE origtab_log_p20260301 PARTITION OF origtab_log FOR VALUES FROM ('2026-03-01') TO ('2026-03-02');
--CREATE TABLE origtab_log_p20260302 PARTITION OF origtab_log FOR VALUES FROM ('2026-03-02') TO ('2026-03-03');
--CREATE TABLE origtab_log_p20260303 PARTITION OF origtab_log FOR VALUES FROM ('2026-03-03') TO ('2026-03-04');

-- Default-Partition (sehr wichtig!)
-- CREATE TABLE origtab_log_default PARTITION OF origtab_log DEFAULT;

-- eine Beispiel-Partition abtrennen und löschen
--ALTER TABLE public.origtab_log DETACH PARTITION public.origtab_log_p20260301;
--DROP TABLE IF EXISTS origtab_log_p20260301 CASCADE;


-- Extension installieren
CREATE EXTENSION pg_cron; 
CREATE EXTENSION IF NOT EXISTS pg_partman;
CREATE SCHEMA partman;
        
        
-- Tabelle registrieren - legt fehlende Partitionen für die Zukunft an
SELECT public.create_parent(
        p_parent_table := 'public.origtab_log'::text,
        p_control      := 'update_timestamp'::text,
        p_type         := 'range'::text,
        p_interval     := '1 day'::text,
        p_premake      := 7::int   -- 7 Tage im Voraus
);

-- Retention: markiert alte Partitionen zum löschen
UPDATE public.part_config
SET retention = '30 days',
        retention_keep_table = false
WHERE parent_table = 'public.origtab_log';


-- Automatisieren mit pg_cron
SELECT cron.schedule(
        'partman maintenance',
        '0 1 * * *',  -- täglich um 01:00
        $$SELECT public.run_maintenance();$$
);

-- Wartung laufen lassen
-- löscht die markierten Partitionen und legt die fehlenden Partitionen an
SELECT public.run_maintenance();

--------------------------------------------------------------------------------
-- Indizes (auf Parent!)
CREATE INDEX bc_key       ON origtab_log (net, zyk, origtab);
CREATE INDEX e_id_key_idx ON origtab_log (e_id);


--------------------------------------------------------------------------------

-- Partitionen anzeigen
SELECT inhrelid::regclass AS partition FROM pg_inherits WHERE inhparent = 'origtab_log'::regclass;