-- ================================================================
--  FarmOS — MySQL Database Schema
--  Compatible with: phpMyAdmin / MySQL 5.7+ / MariaDB 10.3+
-- ================================================================

CREATE DATABASE IF NOT EXISTS farmos CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
USE farmos;

-- ----------------------------------------------------------------
-- TABLE: users
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS users (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  uid         VARCHAR(64) UNIQUE NOT NULL,
  username    VARCHAR(100) UNIQUE NOT NULL,       -- login username
  name        VARCHAR(255) NOT NULL,              -- display name
  password    VARCHAR(255) NOT NULL,              -- bcrypt hashed
  role        ENUM('admin','owner','manager') NOT NULL DEFAULT 'owner',
  client_id   INT DEFAULT NULL,
  client_name VARCHAR(255) DEFAULT NULL,
  last_login  DATETIME DEFAULT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------------------------------
-- TABLE: clients
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS clients (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  name        VARCHAR(255) NOT NULL,
  loc         VARCHAR(255) DEFAULT '',
  owner       VARCHAR(255) NOT NULL,
  owner_email VARCHAR(255) DEFAULT '',
  owner_uid   VARCHAR(64)  DEFAULT NULL,
  phone       VARCHAR(50)  DEFAULT '',
  plan        ENUM('trial','basic','pro','enterprise') DEFAULT 'trial',
  status      ENUM('active','trial','inactive') DEFAULT 'trial',
  notes       TEXT DEFAULT NULL,
  stat_revenue   DECIMAL(15,2) DEFAULT 0,
  stat_expenses  DECIMAL(15,2) DEFAULT 0,
  stat_tasks     INT DEFAULT 0,
  stat_workers   INT DEFAULT 0,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP,
  updated_at  DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------------------------------
-- TABLE: managers
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS managers (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  client_id   INT NOT NULL,
  user_uid    VARCHAR(64) NOT NULL,
  name        VARCHAR(255) NOT NULL,
  email       VARCHAR(255) DEFAULT '',            -- stores username here
  added_at    DATETIME DEFAULT CURRENT_TIMESTAMP,
  last_login  DATETIME DEFAULT NULL,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------
-- TABLE: farmdata
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS farmdata (
  id            INT AUTO_INCREMENT PRIMARY KEY,
  client_id     INT UNIQUE NOT NULL,
  tasks         LONGTEXT DEFAULT '[]',
  rems          LONGTEXT DEFAULT '[]',
  expenses      LONGTEXT DEFAULT '[]',
  revenues      LONGTEXT DEFAULT '[]',
  workers       LONGTEXT DEFAULT '[]',
  wlogs         LONGTEXT DEFAULT '[]',
  assets        LONGTEXT DEFAULT '[]',
  inv           LONGTEXT DEFAULT '[]',
  yields        LONGTEXT DEFAULT '[]',
  sops          LONGTEXT DEFAULT '[]',
  notes         LONGTEXT DEFAULT '[]',
  bsv           LONGTEXT DEFAULT '{}',
  year_archives LONGTEXT DEFAULT '{}',
  nid           LONGTEXT DEFAULT '{}',
  updated_at    DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  updated_by    VARCHAR(255) DEFAULT NULL,
  FOREIGN KEY (client_id) REFERENCES clients(id) ON DELETE CASCADE
) ENGINE=InnoDB;

-- ----------------------------------------------------------------
-- TABLE: activity
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS activity (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  type        VARCHAR(20) DEFAULT 'info',
  text        TEXT NOT NULL,
  time_str    VARCHAR(100) DEFAULT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------------------------------
-- TABLE: recovery
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS recovery (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  email       VARCHAR(255) NOT NULL,
  time_str    VARCHAR(100) DEFAULT NULL,
  status      ENUM('pending','done') DEFAULT 'pending',
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ----------------------------------------------------------------
-- TABLE: sessions
-- ----------------------------------------------------------------
CREATE TABLE IF NOT EXISTS sessions (
  id          INT AUTO_INCREMENT PRIMARY KEY,
  token       VARCHAR(128) UNIQUE NOT NULL,
  user_uid    VARCHAR(64) NOT NULL,
  expires_at  DATETIME NOT NULL,
  created_at  DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;

-- ================================================================
-- ALL USERS WITH ORIGINAL USERNAMES AND PASSWORDS
-- ================================================================

-- Admin (username: admin / password: farmos2024)
INSERT IGNORE INTO users (uid, username, name, password, role) VALUES (
  'admin-001',
  'admin',
  'FarmOS Admin',
  '$2y$10$92IXUNpkjO0rOQ5byMi.Ye4oKoEa3Ro9llC/.og/at2.uheWG/igi',
  'admin'
);

-- Clients
INSERT IGNORE INTO clients (id, name, loc, owner, owner_email, owner_uid, plan, status) VALUES
(1, 'Bharath Areca Farm',   'Pollachi, TN', 'Bharath', 'bharath', 'usr-bharath', 'pro',   'active'),
(2, 'Rajan Coconut Estate', 'Pollachi, TN', 'Rajan',   'rajan',   'usr-rajan',   'basic', 'active');

-- Farm Users
-- bharath / farm123 / owner of client 1
INSERT IGNORE INTO users (uid, username, name, password, role, client_id, client_name) VALUES
('usr-bharath', 'bharath', 'Bharath',
 '$2y$10$N9qo8uLOickgx2ZMRZoMyeIjZAgcfl7p922He7Sn3sMR1LWCqGpWS',
 'owner', 1, 'Bharath Areca Farm');

-- murugan / mgr123 / manager of client 1
INSERT IGNORE INTO users (uid, username, name, password, role, client_id, client_name) VALUES
('usr-murugan', 'murugan', 'Murugan',
 '$2y$10$hgd.8LHRbyZivNNgJoAcpuXmHHYPpPChvHmXHN9YuDIV5RWOlCCJi',
 'manager', 1, 'Bharath Areca Farm');

-- selvam / mgr456 / manager of client 1
INSERT IGNORE INTO users (uid, username, name, password, role, client_id, client_name) VALUES
('usr-selvam', 'selvam', 'Selvam',
 '$2y$10$Zn4d6NjmXnCiXvXWuSiqUeVAHJqwOb7aLAExAp3czMVhVBh.eHyUi',
 'manager', 1, 'Bharath Areca Farm');

-- rajan / raj123 / owner of client 2
INSERT IGNORE INTO users (uid, username, name, password, role, client_id, client_name) VALUES
('usr-rajan', 'rajan', 'Rajan',
 '$2y$10$8tHNEagOakgijC2n8H2v9.bHIZkMc/5yFVALfJmOfE9HrAUQIf4oS',
 'owner', 2, 'Rajan Coconut Estate');

-- anbu / anbu456 / manager of client 2
INSERT IGNORE INTO users (uid, username, name, password, role, client_id, client_name) VALUES
('usr-anbu', 'anbu', 'Anbu',
 '$2y$10$Kb2SdMxqPVBf3Pi4YoJl6OiVkw6mmN0mMRbJ0F2TKYm3Q3JkZ4r8i',
 'manager', 2, 'Rajan Coconut Estate');

-- Managers table entries
INSERT IGNORE INTO managers (client_id, user_uid, name, email) VALUES
(1, 'usr-murugan', 'Murugan', 'murugan'),
(1, 'usr-selvam',  'Selvam',  'selvam'),
(2, 'usr-anbu',    'Anbu',    'anbu');

-- Empty farmdata for each client
INSERT IGNORE INTO farmdata (client_id) VALUES (1);
INSERT IGNORE INTO farmdata (client_id) VALUES (2);

-- ================================================================
-- LOGIN SUMMARY
-- Username       Password      Role
-- admin          farmos2024    Admin
-- bharath        farm123       Owner (Bharath Areca Farm)
-- murugan        mgr123        Manager (Bharath Areca Farm)
-- selvam         mgr456        Manager (Bharath Areca Farm)
-- rajan          raj123        Owner (Rajan Coconut Estate)
-- anbu           anbu456       Manager (Rajan Coconut Estate)
-- ================================================================