BEGIN;

-- ====================================================
-- 0. RENAME OLD TABLES (if they exist)
-- ====================================================
DO $$
BEGIN
    IF to_regclass('public.master_key') IS NOT NULL THEN
        EXECUTE 'ALTER TABLE master_key RENAME TO old_master_key';
    END IF;
    IF to_regclass('public.license') IS NOT NULL THEN
        EXECUTE 'ALTER TABLE license RENAME TO old_license';
    END IF;
    IF to_regclass('public.product') IS NOT NULL THEN
        EXECUTE 'ALTER TABLE product RENAME TO old_product';
    END IF;
    IF to_regclass('public.subscription') IS NOT NULL THEN
        EXECUTE 'ALTER TABLE subscription RENAME TO old_subscription';
    END IF;
    IF to_regclass('public.subscription_products') IS NOT NULL THEN
        EXECUTE 'ALTER TABLE subscription_products RENAME TO old_subscription_products';
    END IF;
    IF to_regclass('public.subscription_order') IS NOT NULL THEN
        EXECUTE 'ALTER TABLE subscription_order RENAME TO old_subscription_order';
    END IF;
    IF to_regclass('public.licensed_instance') IS NOT NULL THEN
        EXECUTE 'ALTER TABLE licensed_instance RENAME TO old_licensed_instance';
    END IF;
    IF to_regclass('public.activity_counter') IS NOT NULL THEN
        EXECUTE 'ALTER TABLE activity_counter RENAME TO old_activity_counter';
    END IF;
    IF to_regclass('public.named_object') IS NOT NULL THEN
        EXECUTE 'ALTER TABLE named_object RENAME TO old_named_object';
    END IF;
    IF to_regclass('public.latest_activations_status') IS NOT NULL THEN
        EXECUTE 'ALTER TABLE latest_activations_status RENAME TO old_latest_activations_status';
    END IF;
	IF to_regclass('public.license_unit') IS NOT NULL THEN
        EXECUTE 'ALTER TABLE license_unit RENAME TO old_license_unit';
    END IF;
END $$;

-- ====================================================
-- 1. CREATE NEW TABLES
-- ====================================================

CREATE TABLE tenant (
    tenant_id VARCHAR(40) PRIMARY KEY
);

CREATE TABLE online_license (
    tenant_id VARCHAR(40) PRIMARY KEY,
    master_key VARCHAR(40),
    FOREIGN KEY (tenant_id) REFERENCES tenant(tenant_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE offline_license (
    tenant_id VARCHAR(40) PRIMARY KEY,
    license TEXT,
    FOREIGN KEY (tenant_id) REFERENCES tenant(tenant_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE product (
    id INT PRIMARY KEY,
    license_type VARCHAR(40),
    name VARCHAR(110) NOT NULL
);

CREATE TABLE subscription (
    id INT PRIMARY KEY,
    start_date TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    end_date TIMESTAMP WITHOUT TIME ZONE
);

CREATE TABLE subscription_products (
    subscription_id INT,
    product_id INT,
    PRIMARY KEY (subscription_id, product_id),
    FOREIGN KEY (subscription_id) REFERENCES subscription(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES product(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE subscription_order (
    subscription_id INT,
    order_id INT,
    PRIMARY KEY (subscription_id, order_id),
    FOREIGN KEY (subscription_id) REFERENCES subscription(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE subscription_tenant (
                                     subscription_id INT,
                                     tenant_id VARCHAR(40),
                                     PRIMARY KEY (subscription_id, tenant_id),
                                     FOREIGN KEY (subscription_id) REFERENCES subscription(id)
                                         ON DELETE CASCADE
                                         ON UPDATE CASCADE,
                                     FOREIGN KEY (tenant_id) REFERENCES tenant(tenant_id)
                                         ON DELETE CASCADE
                                         ON UPDATE CASCADE
);

CREATE TABLE licensed_instance (
    id BIGSERIAL PRIMARY KEY,
    tenant_id VARCHAR(40),
    product_id INT,
    logged_instance VARCHAR(120) NOT NULL,
    FOREIGN KEY (tenant_id) REFERENCES tenant(tenant_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES product(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE named_object (
    tenant_id VARCHAR(40),
    product_id INT,
    logged_instance VARCHAR(120) NOT NULL,
    PRIMARY KEY (tenant_id, product_id),
    FOREIGN KEY (tenant_id) REFERENCES tenant(tenant_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES product(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE activity_counter (
    tenant_id VARCHAR(40),
    last_updated TIME WITHOUT TIME ZONE NOT NULL,
    product_id INT,
    activity_start_date TIME WITHOUT TIME ZONE,
    counter BIGINT NOT NULL,
    PRIMARY KEY (tenant_id, product_id),
    FOREIGN KEY (tenant_id) REFERENCES tenant(tenant_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES product(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

CREATE TABLE license_status (
    tenant_id VARCHAR(40),
    product_id INT,
    total_activations INT NOT NULL,
    current_activations INT NOT NULL,
    remaining_activations INT NOT NULL,
    status VARCHAR(30) NOT NULL,
    PRIMARY KEY (tenant_id, product_id),
    FOREIGN KEY (tenant_id) REFERENCES tenant(tenant_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (product_id) REFERENCES product(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- ====================================================
-- 2. DATA MIGRATION
-- ====================================================

-- 2.1 Tenant table
INSERT INTO tenant (tenant_id)
VALUES ('expertflow')
ON CONFLICT (tenant_id) DO NOTHING;

-- 2.2 Online License
INSERT INTO online_license (tenant_id, master_key)
SELECT 'expertflow', master_key
FROM old_master_key
WHERE EXISTS (
    SELECT 1 FROM old_master_key
);

-- 2.3 Offline License
INSERT INTO offline_license (tenant_id, license)
SELECT 'expertflow', license
FROM old_license
WHERE EXISTS (
    SELECT 1 FROM old_license
);

-- 2.4 Product
INSERT INTO product (id, license_type, name)
SELECT uuid, license_type, name
FROM old_product;

-- 2.5 Subscription
INSERT INTO subscription (id, start_date, end_date)
SELECT uuid, start_date, end_date
FROM old_subscription;

-- 2.6 Subscription Products
INSERT INTO subscription_products (subscription_id, product_id)
SELECT subscription_id, product_id
FROM old_subscription_products;

-- 2.7 Subscription Order
INSERT INTO subscription_order (subscription_id, order_id)
SELECT subscription_id, order_id
FROM old_subscription_order;

-- 2.8 Subscription Tenant (all linked to expertflow)
INSERT INTO subscription_tenant (subscription_id, tenant_id)
SELECT DISTINCT sp.subscription_id, 'expertflow'
FROM old_subscription_products sp;

COMMIT;