CREATE TABLE IF NOT EXISTS "migrations"(
  "id" integer primary key autoincrement not null,
  "migration" varchar not null,
  "batch" integer not null
);
CREATE TABLE IF NOT EXISTS "users"(
  "id" integer primary key autoincrement not null,
  "name" varchar not null,
  "email" varchar not null,
  "email_verified_at" datetime,
  "password" varchar not null,
  "role" varchar not null default 'sub_user',
  "is_active" tinyint(1) not null default '1',
  "avatar_path" varchar,
  "remember_token" varchar,
  "created_at" datetime,
  "updated_at" datetime
);
CREATE UNIQUE INDEX "users_email_unique" on "users"("email");
CREATE TABLE IF NOT EXISTS "password_reset_tokens"(
  "email" varchar not null,
  "token" varchar not null,
  "created_at" datetime,
  primary key("email")
);
CREATE TABLE IF NOT EXISTS "sessions"(
  "id" varchar not null,
  "user_id" integer,
  "ip_address" varchar,
  "user_agent" text,
  "payload" text not null,
  "last_activity" integer not null,
  primary key("id")
);
CREATE INDEX "sessions_user_id_index" on "sessions"("user_id");
CREATE INDEX "sessions_last_activity_index" on "sessions"("last_activity");
CREATE TABLE IF NOT EXISTS "cache"(
  "key" varchar not null,
  "value" text not null,
  "expiration" integer not null,
  primary key("key")
);
CREATE TABLE IF NOT EXISTS "cache_locks"(
  "key" varchar not null,
  "owner" varchar not null,
  "expiration" integer not null,
  primary key("key")
);
CREATE TABLE IF NOT EXISTS "jobs"(
  "id" integer primary key autoincrement not null,
  "queue" varchar not null,
  "payload" text not null,
  "attempts" integer not null,
  "reserved_at" integer,
  "available_at" integer not null,
  "created_at" integer not null
);
CREATE INDEX "jobs_queue_index" on "jobs"("queue");
CREATE TABLE IF NOT EXISTS "job_batches"(
  "id" varchar not null,
  "name" varchar not null,
  "total_jobs" integer not null,
  "pending_jobs" integer not null,
  "failed_jobs" integer not null,
  "failed_job_ids" text not null,
  "options" text,
  "cancelled_at" integer,
  "created_at" integer not null,
  "finished_at" integer,
  primary key("id")
);
CREATE TABLE IF NOT EXISTS "failed_jobs"(
  "id" integer primary key autoincrement not null,
  "uuid" varchar not null,
  "connection" text not null,
  "queue" text not null,
  "payload" text not null,
  "exception" text not null,
  "failed_at" datetime not null default CURRENT_TIMESTAMP
);
CREATE UNIQUE INDEX "failed_jobs_uuid_unique" on "failed_jobs"("uuid");
CREATE TABLE IF NOT EXISTS "landing_pages"(
  "id" integer primary key autoincrement not null,
  "sub_user_id" integer not null,
  "slug" varchar not null,
  "title" varchar not null,
  "tagline" varchar,
  "description" text,
  "hero_image_path" varchar,
  "gallery_images" text,
  "cta_button_label" varchar not null default 'Pay to Book',
  "cta_button_subtext" varchar,
  "is_published" tinyint(1) not null default '1',
  "requires_review_approval" tinyint(1) not null default '1',
  "meta" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("sub_user_id") references "users"("id") on delete cascade
);
CREATE UNIQUE INDEX "landing_pages_slug_unique" on "landing_pages"("slug");
CREATE TABLE IF NOT EXISTS "landing_domains"(
  "id" integer primary key autoincrement not null,
  "landing_page_id" integer not null,
  "host" varchar,
  "path" varchar,
  "is_primary" tinyint(1) not null default '0',
  "is_active" tinyint(1) not null default '1',
  "starts_at" datetime,
  "ends_at" datetime,
  "meta" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("landing_page_id") references "landing_pages"("id") on delete cascade
);
CREATE UNIQUE INDEX "landing_domains_landing_page_id_host_path_unique" on "landing_domains"(
  "landing_page_id",
  "host",
  "path"
);
CREATE TABLE IF NOT EXISTS "informations"(
  "id" integer primary key autoincrement not null,
  "landing_page_id" integer not null,
  "sub_user_id" integer not null,
  "visitor_name" varchar,
  "visitor_email" varchar not null,
  "country" varchar,
  "postal_code" varchar,
  "amount" numeric,
  "currency" varchar not null default 'USD',
  "payment_status" varchar not null default 'pending',
  "payment_reference" varchar,
  "card_brand" varchar,
  "card_last_four" varchar,
  "paid_at" datetime,
  "payload" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("landing_page_id") references "landing_pages"("id") on delete cascade,
  foreign key("sub_user_id") references "users"("id") on delete cascade
);
CREATE TABLE IF NOT EXISTS "reviews"(
  "id" integer primary key autoincrement not null,
  "landing_page_id" integer not null,
  "sub_user_id" integer not null,
  "author_name" varchar not null,
  "author_email" varchar,
  "rating" integer not null default '5',
  "content" text not null,
  "approved_by_admin" tinyint(1) not null default '0',
  "approved_by_sub_user" tinyint(1) not null default '0',
  "approved_at" datetime,
  "submitted_at" datetime,
  "meta" text,
  "created_at" datetime,
  "updated_at" datetime,
  foreign key("landing_page_id") references "landing_pages"("id") on delete cascade,
  foreign key("sub_user_id") references "users"("id") on delete cascade
);

INSERT INTO migrations VALUES(1,'0001_01_01_000000_create_users_table',1);
INSERT INTO migrations VALUES(2,'0001_01_01_000001_create_cache_table',1);
INSERT INTO migrations VALUES(3,'0001_01_01_000002_create_jobs_table',1);
INSERT INTO migrations VALUES(4,'2025_10_30_194945_create_landing_pages_table',1);
INSERT INTO migrations VALUES(5,'2025_10_30_195003_create_landing_domains_table',1);
INSERT INTO migrations VALUES(6,'2025_10_30_195020_create_informations_table',1);
INSERT INTO migrations VALUES(7,'2025_10_30_195039_create_reviews_table',1);