SET NAMES utf8mb4;
CREATE TABLE enable_plans (id VARCHAR(40) PRIMARY KEY,name VARCHAR(120) NOT NULL,description VARCHAR(255) NOT NULL,amount BIGINT UNSIGNED NOT NULL,currency CHAR(3) NOT NULL DEFAULT 'ZAR',duration_days SMALLINT UNSIGNED NOT NULL,benefits JSON NOT NULL,enabled BOOLEAN NOT NULL DEFAULT FALSE,CHECK(amount>=0),CHECK(duration_days>0)) ENGINE=InnoDB;
CREATE TABLE enable_orders (id CHAR(32) PRIMARY KEY,user_id VARCHAR(128) NOT NULL,plan_id VARCHAR(40) NOT NULL,amount BIGINT UNSIGNED NOT NULL,currency CHAR(3) NOT NULL,duration_days SMALLINT UNSIGNED NOT NULL,idempotency_key VARCHAR(128) NOT NULL UNIQUE,qpay_intent_id VARCHAR(64) UNIQUE,checkout_url VARCHAR(2048),status ENUM('created','pending','paid','failed','expired','cancelled') NOT NULL DEFAULT 'created',created_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),paid_at DATETIME(6),FOREIGN KEY(plan_id) REFERENCES enable_plans(id),INDEX(user_id,created_at)) ENGINE=InnoDB;
CREATE TABLE enable_subscriptions (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,user_id VARCHAR(128) NOT NULL,plan_id VARCHAR(40) NOT NULL,order_id CHAR(32) NOT NULL UNIQUE,starts_at DATETIME(6) NOT NULL,ends_at DATETIME(6) NOT NULL,FOREIGN KEY(plan_id) REFERENCES enable_plans(id),FOREIGN KEY(order_id) REFERENCES enable_orders(id),INDEX(user_id,ends_at)) ENGINE=InnoDB;
CREATE TABLE enable_webhook_events (event_id VARCHAR(160) PRIMARY KEY,payload_hash CHAR(64) NOT NULL,processed_at DATETIME(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6)) ENGINE=InnoDB;
-- User requested TEST plans. USD sandbox only; duration is an assumed 30 days.
INSERT INTO enable_plans VALUES
('plan_1','Plan 1','Free test access',0,'USD',30,JSON_ARRAY('Test learning access'),1),
('plan_2','Plan 2','One dollar test payment',100,'USD',30,JSON_ARRAY('Test learning access'),1),
('plan_3','Plan 3','Two dollar test payment',200,'USD',30,JSON_ARRAY('Test learning access'),1);