-- Table: migrations
CREATE TABLE "migrations" ("id" integer primary key autoincrement not null, "migration" varchar not null, "batch" integer not null);

INSERT INTO migrations (id, migration, batch) VALUES ('1', '2024_01_01_000001_create_users_table', '1');
INSERT INTO migrations (id, migration, batch) VALUES ('2', '2024_01_01_000002_create_products_table', '1');
INSERT INTO migrations (id, migration, batch) VALUES ('3', '2024_01_01_000003_create_product_images_table', '1');
INSERT INTO migrations (id, migration, batch) VALUES ('4', '2024_01_01_000004_create_pairings_table', '1');
INSERT INTO migrations (id, migration, batch) VALUES ('5', '2024_01_01_000005_create_orders_table', '1');
INSERT INTO migrations (id, migration, batch) VALUES ('6', '2024_01_01_000006_create_order_items_table', '1');
INSERT INTO migrations (id, migration, batch) VALUES ('7', '2024_01_01_000007_create_payments_table', '1');
INSERT INTO migrations (id, migration, batch) VALUES ('8', '2024_01_01_000008_create_shipping_records_table', '1');
INSERT INTO migrations (id, migration, batch) VALUES ('9', '2024_01_01_000009_create_settings_table', '1');
INSERT INTO migrations (id, migration, batch) VALUES ('10', '2024_01_01_000010_create_notifications_table', '1');
INSERT INTO migrations (id, migration, batch) VALUES ('11', '2024_01_01_000011_create_product_requests_table', '1');


-- Table: notifications
CREATE TABLE "notifications" ("id" integer primary key autoincrement not null, "user_id" integer not null, "title" varchar not null, "message" text not null, "type" varchar not null default 'general', "is_read" tinyint(1) not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade);



-- Table: order_items
CREATE TABLE "order_items" ("id" integer primary key autoincrement not null, "order_id" integer not null, "product_id" integer not null, "quantity" integer not null, "unit_price" numeric not null, "shipping_cost" numeric not null default '0', "total" numeric not null, "created_at" datetime, "updated_at" datetime, foreign key("order_id") references "orders"("id") on delete cascade, foreign key("product_id") references "products"("id") on delete cascade);



-- Table: orders
CREATE TABLE "orders" ("id" integer primary key autoincrement not null, "user_id" integer not null, "order_number" varchar not null, "subtotal" numeric not null, "shipping_cost" numeric not null default '0', "total" numeric not null, "paid_amount" numeric not null default '0', "payment_status" varchar check ("payment_status" in ('pending', 'partial', 'paid')) not null default 'pending', "order_status" varchar check ("order_status" in ('pending', 'processing', 'shipped', 'delivered', 'cancelled')) not null default 'pending', "notes" text, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade);



-- Table: pairings
CREATE TABLE "pairings" ("id" integer primary key autoincrement not null, "user_id" integer not null, "product_id" integer not null, "quantity" integer not null, "unit_price" numeric not null, "total_price" numeric not null, "status" varchar check ("status" in ('active', 'converted', 'cancelled')) not null default 'active', "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade, foreign key("product_id") references "products"("id") on delete cascade);



-- Table: payments
CREATE TABLE "payments" ("id" integer primary key autoincrement not null, "user_id" integer not null, "order_id" integer not null, "payment_method" varchar check ("payment_method" in ('bank_transfer')) not null, "reference_number" varchar, "proof_image" varchar, "amount" numeric not null, "status" varchar check ("status" in ('pending', 'confirmed', 'rejected')) not null default 'pending', "admin_notes" text, "confirmed_by" integer, "confirmed_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade, foreign key("order_id") references "orders"("id") on delete cascade, foreign key("confirmed_by") references "users"("id") on delete set null);



-- Table: product_images
CREATE TABLE "product_images" ("id" integer primary key autoincrement not null, "product_id" integer not null, "image_path" varchar not null, "sort_order" integer not null default '0', "created_at" datetime, "updated_at" datetime, foreign key("product_id") references "products"("id") on delete cascade);

INSERT INTO product_images (id, product_id, image_path, sort_order, created_at, updated_at) VALUES ('1', '1', 'products/placeholder-1.jpg', '0', '2026-04-13 19:17:04', '2026-04-13 19:17:04');
INSERT INTO product_images (id, product_id, image_path, sort_order, created_at, updated_at) VALUES ('2', '2', 'products/placeholder-2.jpg', '0', '2026-04-13 19:17:04', '2026-04-13 19:17:04');
INSERT INTO product_images (id, product_id, image_path, sort_order, created_at, updated_at) VALUES ('3', '3', 'products/placeholder-3.jpg', '0', '2026-04-13 19:17:04', '2026-04-13 19:17:04');
INSERT INTO product_images (id, product_id, image_path, sort_order, created_at, updated_at) VALUES ('4', '4', 'products/placeholder-4.jpg', '0', '2026-04-13 19:17:04', '2026-04-13 19:17:04');
INSERT INTO product_images (id, product_id, image_path, sort_order, created_at, updated_at) VALUES ('5', '5', 'products/placeholder-5.jpg', '0', '2026-04-13 19:17:04', '2026-04-13 19:17:04');


-- Table: product_requests
CREATE TABLE "product_requests" ("id" integer primary key autoincrement not null, "user_id" integer not null, "product_name" varchar not null, "description" text, "image_path" varchar, "status" varchar check ("status" in ('pending', 'fulfilled', 'rejected')) not null default 'pending', "created_at" datetime, "updated_at" datetime, foreign key("user_id") references "users"("id") on delete cascade);



-- Table: products
CREATE TABLE "products" ("id" integer primary key autoincrement not null, "name" varchar not null, "description" text, "base_cost" numeric not null, "markup_price" numeric not null, "shipping_type" varchar check ("shipping_type" in ('AIR', 'SEA')) not null default 'AIR', "estimated_days" integer not null, "pairing_deadline" datetime not null, "min_quantity" integer not null default '1', "total_paired" integer not null default '0', "status" varchar check ("status" in ('active', 'paired', 'ordered', 'shipped', 'delivered', 'closed')) not null default 'active', "total_shipping_cost" numeric, "shipping_per_item" numeric, "created_at" datetime, "updated_at" datetime);

INSERT INTO products (id, name, description, base_cost, markup_price, shipping_type, estimated_days, pairing_deadline, min_quantity, total_paired, status, total_shipping_cost, shipping_per_item, created_at, updated_at) VALUES ('1', 'iPhone 15 Pro Max', 'Latest Apple flagship smartphone with A17 Pro chip, titanium design, and advanced camera system.', '650000', '750000', 'AIR', '14', '2026-04-20 19:17:04', '1', '0', 'active', NULL, NULL, '2026-04-13 19:17:04', '2026-04-13 19:17:04');
INSERT INTO products (id, name, description, base_cost, markup_price, shipping_type, estimated_days, pairing_deadline, min_quantity, total_paired, status, total_shipping_cost, shipping_per_item, created_at, updated_at) VALUES ('2', 'Samsung Galaxy S24 Ultra', 'Premium Android flagship with S Pen, 200MP camera, and AI features.', '550000', '650000', 'AIR', '14', '2026-04-23 19:17:04', '1', '0', 'active', NULL, NULL, '2026-04-13 19:17:04', '2026-04-13 19:17:04');
INSERT INTO products (id, name, description, base_cost, markup_price, shipping_type, estimated_days, pairing_deadline, min_quantity, total_paired, status, total_shipping_cost, shipping_per_item, created_at, updated_at) VALUES ('3', 'MacBook Air M3', 'Ultra-thin laptop with M3 chip, perfect for work and creativity.', '680000', '780000', 'AIR', '14', '2026-04-18 19:17:04', '1', '0', 'active', NULL, NULL, '2026-04-13 19:17:04', '2026-04-13 19:17:04');
INSERT INTO products (id, name, description, base_cost, markup_price, shipping_type, estimated_days, pairing_deadline, min_quantity, total_paired, status, total_shipping_cost, shipping_per_item, created_at, updated_at) VALUES ('4', 'Sony PlayStation 5', 'Next-gen gaming console with 4K gaming, Ray Tracing, and DualSense controller.', '350000', '420000', 'SEA', '70', '2026-04-27 19:17:04', '1', '0', 'active', NULL, NULL, '2026-04-13 19:17:04', '2026-04-13 19:17:04');
INSERT INTO products (id, name, description, base_cost, markup_price, shipping_type, estimated_days, pairing_deadline, min_quantity, total_paired, status, total_shipping_cost, shipping_per_item, created_at, updated_at) VALUES ('5', 'AirPods Pro 2nd Gen', 'Active Noise Cancellation, Adaptive Audio, and personalized spatial audio.', '95000', '125000', 'AIR', '14', '2026-04-16 19:17:04', '2', '0', 'active', NULL, NULL, '2026-04-13 19:17:04', '2026-04-13 19:17:04');


-- Table: settings
CREATE TABLE "settings" ("id" integer primary key autoincrement not null, "key" varchar not null, "value" text, "created_at" datetime, "updated_at" datetime);

INSERT INTO settings (id, key, value, created_at, updated_at) VALUES ('1', 'bank_name', 'First Bank of Nigeria', '2026-04-13 19:17:04', '2026-04-13 19:17:04');
INSERT INTO settings (id, key, value, created_at, updated_at) VALUES ('2', 'account_number', '1234567890', '2026-04-13 19:17:04', '2026-04-13 19:17:04');
INSERT INTO settings (id, key, value, created_at, updated_at) VALUES ('3', 'account_name', 'PreOrder System Ltd', '2026-04-13 19:17:04', '2026-04-13 19:17:04');


-- Table: shipping_records
CREATE TABLE "shipping_records" ("id" integer primary key autoincrement not null, "product_id" integer not null, "order_id" integer, "status" varchar check ("status" in ('ordered', 'shipped_from_origin', 'in_transit', 'arrived_nigeria', 'ready_for_pickup', 'delivered')) not null default 'ordered', "tracking_notes" text, "status_updated_at" datetime, "created_at" datetime, "updated_at" datetime, foreign key("product_id") references "products"("id") on delete cascade, foreign key("order_id") references "orders"("id") on delete set null);



-- Table: users
CREATE TABLE "users" ("id" integer primary key autoincrement not null, "name" varchar not null, "email" varchar not null, "email_verified_at" datetime, "password" varchar not null, "phone" varchar, "address" varchar, "role" varchar check ("role" in ('customer', 'admin')) not null default 'customer', "wallet_balance" numeric not null default '0', "remember_token" varchar, "created_at" datetime, "updated_at" datetime);

INSERT INTO users (id, name, email, email_verified_at, password, phone, address, role, wallet_balance, remember_token, created_at, updated_at) VALUES ('1', 'Admin', 'admin@preorder.com', NULL, '$2y$12$VhLWl7TS13dC4TPXJQm5UuXMSwKKUQhTGy.OVkoDfmbYc2M970M0m', '+2348000000000', NULL, 'admin', '0', NULL, '2026-04-13 19:17:03', '2026-04-13 19:17:03');
INSERT INTO users (id, name, email, email_verified_at, password, phone, address, role, wallet_balance, remember_token, created_at, updated_at) VALUES ('2', 'John Doe', 'john@example.com', NULL, '$2y$12$4J4jNGI0eqRj8y/rqFu52.ISHC/kLeiFeGp0pJaMnSifoxN0ziL6i', '+2348012345678', NULL, 'customer', '0', NULL, '2026-04-13 19:17:03', '2026-04-13 19:17:03');
INSERT INTO users (id, name, email, email_verified_at, password, phone, address, role, wallet_balance, remember_token, created_at, updated_at) VALUES ('3', 'Jane Smith', 'jane@example.com', NULL, '$2y$12$7n3aLmalSpbFRif14i9HOO3JFkA9ORUehPO4PBBbGOyP5ANjKUnQa', '+2348012345679', NULL, 'customer', '0', NULL, '2026-04-13 19:17:04', '2026-04-13 19:17:04');


-- Done!