-- eTailorz Admin Panel — full schema + seed data
-- Import this directly in phpMyAdmin (Import tab) after creating an empty database.

-- ============================================
-- 1. USERS TABLE (Laravel default + our new columns)
-- ============================================
CREATE TABLE IF NOT EXISTS `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `email` VARCHAR(255) NOT NULL,
  `email_verified_at` TIMESTAMP NULL DEFAULT NULL,
  `password` VARCHAR(255) NOT NULL,
  `type_id` TINYINT UNSIGNED NOT NULL DEFAULT 3,
  `shop_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `remember_token` VARCHAR(100) NULL DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `users_email_unique` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 2. SHOPS TABLE
-- ============================================
CREATE TABLE IF NOT EXISTS `shops` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(255) NOT NULL,
  `owner_name` VARCHAR(255) NULL DEFAULT NULL,
  `email` VARCHAR(255) NULL DEFAULT NULL,
  `phone` VARCHAR(20) NULL DEFAULT NULL,
  `address` VARCHAR(255) NULL DEFAULT NULL,
  `city` VARCHAR(100) NULL DEFAULT NULL,
  `gst_number` VARCHAR(20) NULL DEFAULT NULL,
  `logo` VARCHAR(255) NULL DEFAULT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- ============================================
-- 3. ORDERS TABLE (minimal, for dashboard stats)
-- ============================================
CREATE TABLE IF NOT EXISTS `orders` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `shop_id` BIGINT UNSIGNED NOT NULL,
  `customer_id` BIGINT UNSIGNED NULL DEFAULT NULL,
  `stage` ENUM('received','cutting','stitching','finishing','ready','delivered') NOT NULL DEFAULT 'received',
  `total_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `balance_amount` DECIMAL(10,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP NULL DEFAULT NULL,
  `updated_at` TIMESTAMP NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `orders_shop_id_foreign` (`shop_id`),
  CONSTRAINT `orders_shop_id_foreign` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- Add the shop_id foreign key on users now that shops exists
ALTER TABLE `users`
  ADD CONSTRAINT `users_shop_id_foreign` FOREIGN KEY (`shop_id`) REFERENCES `shops` (`id`) ON DELETE SET NULL;

-- ============================================
-- 4. SEED DATA
-- ============================================

-- One test shop
INSERT INTO `shops` (`id`, `name`, `owner_name`, `city`, `is_active`, `created_at`, `updated_at`)
VALUES (1, 'Madurai Main', 'Sample Owner', 'Madurai', 1, NOW(), NOW());

-- Three test logins — all use password: password123
INSERT INTO `users` (`name`, `email`, `password`, `type_id`, `shop_id`, `is_active`, `created_at`, `updated_at`)
VALUES
('Super Admin', 'superadmin@etailorz.com', '$2b$10$G7nlr09W2u9W6zTjTUnWfeasMqwhpOuRRhZpSLIKSN5VByaj4bsPO', 1, NULL, 1, NOW(), NOW()),
('Madurai Shop Owner', 'owner@maduraishop.com', '$2b$10$G7nlr09W2u9W6zTjTUnWfeasMqwhpOuRRhZpSLIKSN5VByaj4bsPO', 2, 1, 1, NOW(), NOW()),
('Staff Member', 'staff@maduraishop.com', '$2b$10$G7nlr09W2u9W6zTjTUnWfeasMqwhpOuRRhZpSLIKSN5VByaj4bsPO', 3, 1, 1, NOW(), NOW());