Migration steps for Analytics Database

-- Create Partition Function for analytics_signature
CREATE PARTITION FUNCTION pf_analytics_signature (datetime)
AS RANGE RIGHT FOR VALUES ('20220101', '20230101', '20240101');

-- Create Partition Scheme for analytics_signature
CREATE PARTITION SCHEME ps_analytics_signature
AS PARTITION pf_analytics_signature ALL TO ([PRIMARY]);

-- Create New Clustered Index for analytics_signature
CREATE CLUSTERED INDEX idx_analytics_signature_partitioned
ON analytics_signature (action_date)
ON ps_analytics_signature (action_date);


-- Create Partition Function for analytics_artifact
CREATE PARTITION FUNCTION pf_analytics_artifact (datetime)
AS RANGE RIGHT FOR VALUES ('20220101', '20230101', '20240101');

-- Create Partition Scheme for analytics_artifact
CREATE PARTITION SCHEME ps_analytics_artifact
AS PARTITION pf_analytics_artifact ALL TO ([PRIMARY]);

-- Create New Clustered Index for analytics_artifact
CREATE CLUSTERED INDEX idx_analytics_artifact_partitioned
ON analytics_artifact (action_date)
ON ps_analytics_artifact (action_date);


-- Create Partition Function for analytics_user
CREATE PARTITION FUNCTION pf_analytics_user (datetime)
AS RANGE RIGHT FOR VALUES ('20220101', '20230101', '20240101');

-- Create Partition Scheme for analytics_user
CREATE PARTITION SCHEME ps_analytics_user
AS PARTITION pf_analytics_user ALL TO ([PRIMARY]);

-- Create New Clustered Index for analytics_user
CREATE CLUSTERED INDEX idx_analytics_user_partitioned
ON analytics_user (login_date)
ON ps_analytics_user (login_date);

-- Drop the existing primary key constraint
ALTER TABLE analytics_signature DROP CONSTRAINT pk_analytics_signature;

-- Add the new primary key constraint that includes id and action_date
ALTER TABLE analytics_signature ADD CONSTRAINT pk_analytics_signature PRIMARY KEY (id, action_date);

-- Create new partitioned tables
CREATE TABLE new_analytics_signature (LIKE analytics_signature INCLUDING ALL) PARTITION BY RANGE (action_date);
CREATE TABLE analytics_signature_2022 PARTITION OF new_analytics_signature FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE analytics_signature_2023 PARTITION OF new_analytics_signature FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- Populate new partitioned tables with data from old table
INSERT INTO new_analytics_signature SELECT * FROM analytics_signature;

-- Create indexes on new partitioned tables
CREATE INDEX idx_analytics_signature_2022 ON analytics_signature_2022 (action_date);
CREATE INDEX idx_analytics_signature_2023 ON analytics_signature_2023 (action_date);

-- Verify row counts between old and new tables
DO $$
DECLARE
    old_count INTEGER;
    new_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO old_count FROM analytics_signature;
    SELECT COUNT(*) INTO new_count FROM new_analytics_signature;
    IF old_count = new_count THEN
        -- Rename or drop old table
        ALTER TABLE analytics_signature RENAME TO old_analytics_signature;
        -- Rename new table
        ALTER TABLE new_analytics_signature RENAME TO analytics_signature;
    END IF;
END $$;

-- Drop the existing primary key constraint
ALTER TABLE analytics_artifact DROP CONSTRAINT pk_analytics_artifact;

-- Add the new primary key constraint that includes id and action_date
ALTER TABLE analytics_artifact ADD CONSTRAINT pk_analytics_artifact PRIMARY KEY (id, action_date);

-- Create new partitioned tables
CREATE TABLE new_analytics_artifact (LIKE analytics_artifact INCLUDING ALL) PARTITION BY RANGE (action_date);
CREATE TABLE analytics_artifact_2022 PARTITION OF new_analytics_artifact FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE analytics_artifact_2023 PARTITION OF new_analytics_artifact FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- Populate new partitioned tables with data from old table
INSERT INTO new_analytics_artifact SELECT * FROM analytics_artifact;

-- Create indexes on new partitioned tables
CREATE INDEX idx_analytics_artifact_2022 ON analytics_artifact_2022 (action_date);
CREATE INDEX idx_analytics_artifact_2023 ON analytics_artifact_2023 (action_date);

-- Verify row counts between old and new tables
DO $$
DECLARE
    old_count INTEGER;
    new_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO old_count FROM analytics_artifact;
    SELECT COUNT(*) INTO new_count FROM new_analytics_artifact;
    IF old_count = new_count THEN
        -- Rename or drop old table
        ALTER TABLE analytics_artifact RENAME TO old_analytics_artifact;
        -- Rename new table
        ALTER TABLE new_analytics_artifact RENAME TO analytics_artifact;
    END IF;
END $$;

-- Drop the existing primary key constraint
ALTER TABLE analytics_user DROP CONSTRAINT pk_analytics_user;

-- Add the new primary key constraint that includes id and login_date
ALTER TABLE analytics_user ADD CONSTRAINT pk_analytics_user PRIMARY KEY (id, login_date);

-- Create new partitioned tables
CREATE TABLE new_analytics_user (LIKE analytics_user INCLUDING ALL) PARTITION BY RANGE (login_date);
CREATE TABLE analytics_user_2022 PARTITION OF new_analytics_user FOR VALUES FROM ('2022-01-01') TO ('2023-01-01');
CREATE TABLE analytics_user_2023 PARTITION OF new_analytics_user FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

-- Populate new partitioned tables with data from old table
INSERT INTO new_analytics_user SELECT * FROM analytics_user;

-- Create indexes on new partitioned tables
CREATE INDEX idx_analytics_user_2022 ON analytics_user_2022 (login_date);
CREATE INDEX idx_analytics_user_2023 ON analytics_user_2023 (login_date);

-- Verify row counts between old and new tables
DO $$
DECLARE
    old_count INTEGER;
    new_count INTEGER;
BEGIN
    SELECT COUNT(*) INTO old_count FROM analytics_user;
    SELECT COUNT(*) INTO new_count FROM new_analytics_user;
    IF old_count = new_count THEN
        -- Rename or drop old table
        ALTER TABLE analytics_user RENAME TO old_analytics_user;
        -- Rename new table
        ALTER TABLE new_analytics_user RENAME TO analytics_user;
    END IF;
END $$;

CREATE TABLE analytics_signature_test (
    "ID" NUMBER(38,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
    -- [rest of the columns in analytics_signature table]]
    CONSTRAINT "PK_ANALYTICS_SIGNATURE_TEST_NEW" PRIMARY KEY ("ID")
) PARTITION BY RANGE (action_date) (
    PARTITION analytics_signature_test_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
    PARTITION analytics_signature_test_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);


-- Populate new partitioned tables with data from old table
INSERT INTO analytics_signature_test SELECT * FROM analytics_signature;

-- Create local indexes on the new partitioned table
CREATE INDEX idx_analytics_signature_test ON analytics_signature_test (action_date) LOCAL;


-- Verify row counts between old and new table partitions
DECLARE
  old_count NUMBER;
  new_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO old_count FROM analytics_signature;
  SELECT COUNT(*) INTO new_count FROM analytics_signature_test;  -- The table itself remains the same, just partitioned
  IF old_count = new_count THEN
    DBMS_OUTPUT.PUT_LINE('Row counts match. Partitioning was successful.');
    -- Rename or drop old table
    ALTER TABLE analytics_signature RENAME TO old_analytics_signature;
    -- Rename new table
    ALTER TABLE analytics_signature_test RENAME TO analytics_signature;
  END IF;
END;



CREATE TABLE analytics_artifact_test (
    "ID" NUMBER(38,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
    -- [rest of the columns in analytics_artifact table]]
    CONSTRAINT "PK_ANALYTICS_ARTIFACT_TEST_NEW" PRIMARY KEY ("ID")
) PARTITION BY RANGE (action_date) (
    PARTITION analytics_artifact_test_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
    PARTITION analytics_artifact_test_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);


-- Populate new partitioned tables with data from old table
INSERT INTO analytics_artifact_test SELECT * FROM analytics_artifact;

-- Create local indexes on the new partitioned table
CREATE INDEX idx_analytics_artifact_test ON analytics_artifact_test (action_date) LOCAL;


-- Verify row counts between old and new table partitions
DECLARE
  old_count NUMBER;
  new_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO old_count FROM analytics_artifact;
  SELECT COUNT(*) INTO new_count FROM analytics_artifact_test;  -- The table itself remains the same, just partitioned
  IF old_count = new_count THEN
    DBMS_OUTPUT.PUT_LINE('Row counts match. Partitioning was successful.');
    -- Rename or drop old table
    ALTER TABLE analytics_artifact RENAME TO old_analytics_artifact;
    -- Rename new table
    ALTER TABLE analytics_artifact_test RENAME TO analytics_artifact;
  END IF;
END;



CREATE TABLE analytics_user_test (
    "ID" NUMBER(38,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
    -- [rest of the columns in analytics_user table]]
    CONSTRAINT "PK_ANALYTICS_USER_TEST_NEW" PRIMARY KEY ("ID")
) PARTITION BY RANGE (action_date) (
    PARTITION analytics_user_test_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
    PARTITION analytics_user_test_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);


-- Populate new partitioned tables with data from old table
INSERT INTO analytics_user_test SELECT * FROM analytics_user;

-- Create local indexes on the new partitioned table
CREATE INDEX idx_analytics_user_test ON analytics_user_test (action_date) LOCAL;


-- Verify row counts between old and new table partitions
DECLARE
  old_count NUMBER;
  new_count NUMBER;
BEGIN
  SELECT COUNT(*) INTO old_count FROM analytics_user;
  SELECT COUNT(*) INTO new_count FROM analytics_user_test;  -- The table itself remains the same, just partitioned
  IF old_count = new_count THEN
    DBMS_OUTPUT.PUT_LINE('Row counts match. Partitioning was successful.');
    -- Rename or drop old table
    ALTER TABLE analytics_user RENAME TO old_analytics_user;
    -- Rename new table
    ALTER TABLE analytics_user_test RENAME TO analytics_user;
  END IF;
END;

-- Aligned with ps_analytics_artifact partition scheme
CREATE NONCLUSTERED INDEX idx_analytics_artifact_1
ON analytics_artifact (action, action_date)
INCLUDE (artifact_id, action_time, reason)
ON ps_analytics_artifact (action_date);

-- Aligned with ps_analytics_signature partition scheme
CREATE NONCLUSTERED INDEX idx_analytics_signature_1
ON analytics_signature (artifact_id, action, action_date)
INCLUDE (signature_type)
ON ps_analytics_signature (action_date);

CREATE NONCLUSTERED INDEX idx_analytics_signature_2
ON analytics_signature (artifact_id, action_date)
INCLUDE (username)
ON ps_analytics_signature (action_date);

-- Aligned with ps_analytics_user partition scheme
CREATE NONCLUSTERED INDEX idx_analytics_user_1
ON analytics_user (login_date)
INCLUDE (artifact_id, channel)
ON ps_analytics_user (login_date);

CREATE NONCLUSTERED INDEX idx_analytics_user_2
ON analytics_user (login_date)
INCLUDE (username, user_domain)
ON ps_analytics_user (login_date);

DO $$
DECLARE
   table_name text;
BEGIN
   -- Create indexes for analytics_artifact partitions
   FOR table_name IN (SELECT inhrelid::regclass::text
                       FROM pg_inherits
                       WHERE inhparent = 'analytics_artifact'::regclass)
   LOOP
      EXECUTE 'CREATE INDEX ' || table_name || '_idx1 ON ' || table_name || ' (action, action_date) INCLUDE (artifact_id, action_time, reason)';
   END LOOP;

   -- Create indexes for analytics_signature partitions
   FOR table_name IN (SELECT inhrelid::regclass::text
                       FROM pg_inherits
                       WHERE inhparent = 'analytics_signature'::regclass)
   LOOP
      EXECUTE 'CREATE INDEX ' || table_name || '_idx1 ON ' || table_name || ' (artifact_id, action, action_date) INCLUDE (signature_type)';
      EXECUTE 'CREATE INDEX ' || table_name || '_idx2 ON ' || table_name || ' (artifact_id, action_date) INCLUDE (username)';
   END LOOP;

   -- Create indexes for analytics_user partitions
   FOR table_name IN (SELECT inhrelid::regclass::text
                       FROM pg_inherits
                       WHERE inhparent = 'analytics_user'::regclass)
   LOOP
      EXECUTE 'CREATE INDEX ' || table_name || '_idx1 ON ' || table_name || ' (login_date) INCLUDE (artifact_id, channel)';
      EXECUTE 'CREATE INDEX ' || table_name || '_idx2 ON ' || table_name || ' (login_date) INCLUDE (username, user_domain)';
   END LOOP;

END $$;

CREATE INDEX idx_analytics_artifact_1
ON analytics_artifact (action_date, action, artifact_id, action_time, reason) LOCAL;

CREATE INDEX idx_analytics_signature_1
ON analytics_signature (action_date, action, artifact_id, signature_type) LOCAL;

CREATE INDEX idx_analytics_signature_2
ON analytics_signature (action_date, artifact_id, username) LOCAL;

CREATE INDEX idx_analytics_user_1
ON analytics_user (login_date, artifact_id, channel) LOCAL;

CREATE INDEX idx_analytics_user_2
ON analytics_user (login_date, username, user_domain) LOCAL;