CREATE TABLE IF NOT EXISTS subscriptions (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT(20) UNSIGNED NOT NULL,
    package_id BIGINT(20) UNSIGNED NOT NULL,
    subscription_key VARCHAR(191) NOT NULL,
    status ENUM('active','trial','suspended','expired','cancelled') NOT NULL DEFAULT 'active',
    billing_period ENUM('monthly','yearly','lifetime','custom') NOT NULL DEFAULT 'monthly',
    starts_at DATETIME NULL,
    expires_at DATETIME NULL,
    grace_ends_at DATETIME NULL,
    trial_ends_at DATETIME NULL,
    suspended_at DATETIME NULL,
    cancelled_at DATETIME NULL,
    created_by BIGINT(20) UNSIGNED NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_subscriptions_subscription_key (subscription_key),
    KEY idx_subscriptions_tenant_id (tenant_id),
    KEY idx_subscriptions_package_id (package_id),
    KEY idx_subscriptions_status (status),
    KEY idx_subscriptions_expires_at (expires_at),
    CONSTRAINT fk_subscriptions_tenant_id FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_subscriptions_package_id FOREIGN KEY (package_id) REFERENCES packages (id) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS subscription_history (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT(20) UNSIGNED NOT NULL,
    old_package_id BIGINT(20) UNSIGNED NULL,
    new_package_id BIGINT(20) UNSIGNED NULL,
    action_type VARCHAR(100) NOT NULL,
    old_status VARCHAR(50) NULL,
    new_status VARCHAR(50) NULL,
    note TEXT NULL,
    created_by BIGINT(20) UNSIGNED NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    KEY idx_subscription_history_tenant_id (tenant_id),
    KEY idx_subscription_history_old_package_id (old_package_id),
    KEY idx_subscription_history_new_package_id (new_package_id),
    KEY idx_subscription_history_action_type (action_type),
    CONSTRAINT fk_subscription_history_tenant_id FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_subscription_history_old_package_id FOREIGN KEY (old_package_id) REFERENCES packages (id) ON UPDATE CASCADE ON DELETE SET NULL,
    CONSTRAINT fk_subscription_history_new_package_id FOREIGN KEY (new_package_id) REFERENCES packages (id) ON UPDATE CASCADE ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE IF NOT EXISTS invoices_foundation (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    tenant_id BIGINT(20) UNSIGNED NOT NULL,
    subscription_id INT UNSIGNED NOT NULL,
    invoice_no VARCHAR(191) NOT NULL,
    amount DECIMAL(12,2) UNSIGNED NOT NULL DEFAULT 0.00,
    currency VARCHAR(10) NOT NULL DEFAULT 'TRY',
    invoice_status ENUM('pending','paid','cancelled','expired') NOT NULL DEFAULT 'pending',
    billing_period ENUM('monthly','yearly','lifetime','custom') NOT NULL DEFAULT 'monthly',
    issued_at DATETIME NULL,
    due_at DATETIME NULL,
    paid_at DATETIME NULL,
    notes TEXT NULL,
    created_at DATETIME NOT NULL,
    PRIMARY KEY (id),
    UNIQUE KEY uk_invoices_foundation_invoice_no (invoice_no),
    KEY idx_invoices_foundation_tenant_id (tenant_id),
    KEY idx_invoices_foundation_subscription_id (subscription_id),
    KEY idx_invoices_foundation_status (invoice_status),
    CONSTRAINT fk_invoices_foundation_tenant_id FOREIGN KEY (tenant_id) REFERENCES tenants (id) ON UPDATE CASCADE ON DELETE CASCADE,
    CONSTRAINT fk_invoices_foundation_subscription_id FOREIGN KEY (subscription_id) REFERENCES subscriptions (id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;