DROP TABLE IF EXISTS subscription;

-- DROP SEQUENCE IF EXISTS subscription_subscription_id_seq;
-- CREATE SEQUENCE subscription_subscription_id_seq;
-- SELECT setval('subscription_seq', 0, true); -- last inserted id by sample data

CREATE TABLE "subscription" (
  "subscription_id" int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY, -- SERIAL PRIMARY KEY
  "order_id" INT NOT NULL,
  "order_product_id" INT NOT NULL,
  "site_id" INT NOT NULL,
  "user_id" INT NOT NULL,
  "payment_address_id" INT NOT NULL,
  "payment_method" text NOT NULL,
  "shipping_address_id" INT NOT NULL,
  "shipping_method" text NOT NULL,
  "product_id" INT NOT NULL,
  "quantity" INT NOT NULL,
  "subscription_plan_id" INT NOT NULL,
  "price" decimal(10,4) NOT NULL,
  "period" TEXT CHECK( period IN ('day','week','month','year') ) NOT NULL DEFAULT 'month',
  "cycle" smallint NOT NULL,
  "length" smallint NOT NULL,
  "left" smallint NOT NULL,
  "trial_price" decimal(10,4) NOT NULL,
  "trial_period" TEXT CHECK( trial_period IN ('day','week','month','year') ) NOT NULL DEFAULT 'month',
  "trial_cycle" smallint NOT NULL,
  "trial_length" smallint NOT NULL,
  "trial_left" smallint NOT NULL,
  "trial_status" smallint NOT NULL,
  "date_next" timestamp(0) NOT NULL,
  "subscription_status_id" INT NOT NULL,
  "notes" text NOT NULL,
  "created_at" timestamp(0) NOT NULL DEFAULT now(),
  "updated_at" timestamp(0) NOT NULL DEFAULT now()
);

CREATE INDEX "subscription_order_id" ON "subscription" ("order_id");
CREATE INDEX "subscription_user_id" ON "subscription" ("user_id");
CREATE INDEX "subscription_product_id" ON "subscription" ("product_id");
CREATE INDEX "subscription_subscription_plan_id" ON "subscription" ("subscription_plan_id");
CREATE INDEX "subscription_subscription_status_id" ON "subscription" ("subscription_status_id");
-- SELECT setval('subscription_subscription_id_seq', 0, true); -- last inserted id by sample data
