

CREATE TABLE IF NOT EXISTS `subscription_plans` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `store_id` INT DEFAULT NULL,
  `name` VARCHAR(100) NOT NULL,
  `billing_interval` ENUM('monthly','quarterly','yearly') NOT NULL,
  `is_active` TINYINT(1) NOT NULL DEFAULT 1,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_subscription_plans_store_id` (`store_id`),
  KEY `idx_subscription_plans_is_active` (`is_active`),
  KEY `idx_subscription_plans_billing_interval` (`billing_interval`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `customer_subscriptions` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `store_id` INT DEFAULT NULL,
  `customer_id` INT NOT NULL,
  `item_id` INT NOT NULL,
  `subscription_plan_id` INT DEFAULT NULL,
  `status` ENUM('active','paused','cancelled') NOT NULL DEFAULT 'active',
  `start_date` DATE NOT NULL,
  `current_period_end` DATE NOT NULL,
  `next_renewal_date` DATE DEFAULT NULL,
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `auto_renew` TINYINT(1) NOT NULL DEFAULT 0,
  `notes` TEXT DEFAULT NULL,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_customer_subscriptions_store_id` (`store_id`),
  KEY `idx_customer_subscriptions_customer_id` (`customer_id`),
  KEY `idx_customer_subscriptions_item_id` (`item_id`),
  KEY `idx_customer_subscriptions_plan_id` (`subscription_plan_id`),
  KEY `idx_customer_subscriptions_status` (`status`),
  KEY `idx_customer_subscriptions_period_end` (`current_period_end`),
  KEY `idx_customer_subscriptions_renewal_date` (`next_renewal_date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE IF NOT EXISTS `customer_subscription_invoices` (
  `id` INT NOT NULL AUTO_INCREMENT,
  `subscription_id` INT NOT NULL,
  `sale_id` INT NOT NULL,
  `period_start` DATE NOT NULL,
  `period_end` DATE NOT NULL,
  `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00,
  `created_at` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uq_subscription_sale` (`subscription_id`, `sale_id`),
  KEY `idx_customer_subscription_invoices_subscription_id` (`subscription_id`),
  KEY `idx_customer_subscription_invoices_sale_id` (`sale_id`),
  KEY `idx_customer_subscription_invoices_period_end` (`period_end`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

INSERT INTO rights (name, description, category)
SELECT 'Subscriptions', 'View and manage customer subscriptions', 'Accounts'
WHERE NOT EXISTS (
    SELECT 1 FROM rights WHERE LOWER(name) = LOWER('Subscriptions')
);

INSERT INTO role_rights (role_id, right_id)
SELECT 1, r.id
FROM rights r
WHERE LOWER(r.name) = LOWER('Subscriptions')
  AND NOT EXISTS (
      SELECT 1
      FROM role_rights rr
      WHERE rr.role_id = 1
        AND rr.right_id = r.id
  );





CREATE TABLE IF NOT EXISTS `production_cost_lines` (
  `id` int NOT NULL AUTO_INCREMENT,
  `production_item_id` int NOT NULL COMMENT 'FK to production_items.id',
  `cost_type` enum('labor','overhead','subcontract','other') NOT NULL DEFAULT 'other',
  `description` varchar(255) DEFAULT NULL,
  `quantity` decimal(12,4) DEFAULT NULL COMMENT 'e.g. hours; optional',
  `unit_rate` decimal(12,4) DEFAULT NULL COMMENT 'optional if using amount only',
  `amount` decimal(12,2) NOT NULL DEFAULT 0.00,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `idx_production_cost_lines_item` (`production_item_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;






CREATE TABLE IF NOT EXISTS `bank_access` (
  `user_id` int NOT NULL,
  `bank_account_id` int NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL COMMENT 'User who assigned access',
  PRIMARY KEY (`user_id`,`bank_account_id`),
  KEY `idx_bank_access_bank` (`bank_account_id`),
  KEY `idx_bank_access_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;



ALTER TABLE `users`
  ADD COLUMN `cash_access` tinyint(1) NOT NULL DEFAULT 1
  COMMENT '1=view cash balance/ledger, 0=hidden; POS cash sales still allowed';




ALTER TABLE `sale_returns`
    MODIFY COLUMN `sale_id` int NULL DEFAULT NULL;

-- Step 2: Add return_source only if missing (safe to re-run)
SET @col_exists = (
    SELECT COUNT(*)
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = DATABASE()
      AND TABLE_NAME = 'sale_returns'
      AND COLUMN_NAME = 'return_source'
);

SET @sql_add_return_source = IF(
    @col_exists > 0,
    'SELECT ''return_source already exists'' AS info',
    'ALTER TABLE `sale_returns` ADD COLUMN `return_source` varchar(20) NOT NULL DEFAULT ''invoice'' AFTER `sale_id`'
);

PREPARE stmt_return_source FROM @sql_add_return_source;
EXECUTE stmt_return_source;
DEALLOCATE PREPARE stmt_return_source;






INSERT INTO `settings` (
    `store_id`,
    `category_id`,
    `name`,
    `code`,
    `type`,
    `value`,
    `default_value`,
    `description`,
    `is_active`
)
SELECT
    NULL,
    sc.id,
    'Display shift summary before shift close',
    'pos_display_shift_summary_before_close',
    'boolean',
    '1',
    '1',
    'When enabled, the cashier reviews expected cash and discrepancy before confirming shift close. When disabled, the shift closes after entering closing cash and the full summary is shown afterward.',
    1
FROM `setting_categories` sc
WHERE sc.code = 'pos'
  AND NOT EXISTS (
      SELECT 1 FROM `settings` s WHERE s.code = 'pos_display_shift_summary_before_close' LIMIT 1
  )
LIMIT 1;








CREATE TABLE IF NOT EXISTS `branch_access` (
  `user_id` int NOT NULL,
  `branch_id` int NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `created_by` int DEFAULT NULL COMMENT 'User who assigned access',
  PRIMARY KEY (`user_id`,`branch_id`),
  KEY `idx_branch_access_branch` (`branch_id`),
  KEY `idx_branch_access_user` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- Backfill existing users with access to this installation's branch.
SET @install_branch := (
    SELECT bi.id
    FROM branch_info bi
    WHERE bi.is_active = 1
      AND TRIM(IFNULL(bi.branch_code, '')) <> ''
      AND TRIM(IFNULL(bi.branch_code, '')) = (
          SELECT TRIM(IFNULL(db_prefix, ''))
          FROM sync_settings
          ORDER BY id DESC
          LIMIT 1
      )
    LIMIT 1
);

SET @install_branch := IFNULL(@install_branch, (
    SELECT id FROM branch_info WHERE is_active = 1 AND is_default = 1 ORDER BY id ASC LIMIT 1
));

SET @install_branch := IFNULL(@install_branch, (
    SELECT id FROM branch_info WHERE is_active = 1 ORDER BY id ASC LIMIT 1
));

SET @install_branch := IFNULL(@install_branch, 1);

INSERT IGNORE INTO branch_access (user_id, branch_id)
SELECT u.id, @install_branch
FROM users u;


