/*
  # Initial Schema Setup for SpendCheck

  1. New Tables
    - `companies`
      - `id` (uuid, primary key)
      - `created_at` (timestamp)
      - `email` (text, unique)
      - `company_name` (text)
      - `post_code` (text)
      - `business_type` (text)
    
    - `suppliers`
      - `id` (uuid, primary key)
      - `created_at` (timestamp)
      - `name` (text)
      - `supplier_code` (text)
      
    - `invoices`
      - `id` (uuid, primary key)
      - `created_at` (timestamp)
      - `company_id` (uuid, foreign key)
      - `supplier_id` (uuid, foreign key)
      - `invoice_number` (text)
      - `invoice_date` (date)
      - `supplier_order_number` (text)
      - `customer_po_number` (text)
      - `scan_date` (timestamp)
      
    - `invoice_items`
      - `id` (uuid, primary key)
      - `created_at` (timestamp)
      - `invoice_id` (uuid, foreign key)
      - `product_code` (text)
      - `product_description` (text)
      - `unit_price` (decimal)
      - `quantity` (decimal)
      - `product_match_id` (uuid)

  2. Security
    - Enable RLS on all tables
    - Add policies for authenticated users to access their own data
*/

-- Companies table
CREATE TABLE companies (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at timestamptz DEFAULT now(),
  email text UNIQUE NOT NULL,
  company_name text NOT NULL,
  post_code text NOT NULL,
  business_type text NOT NULL
);

ALTER TABLE companies ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read own company data"
  ON companies
  FOR SELECT
  TO authenticated
  USING (auth.uid() IN (
    SELECT id FROM auth.users WHERE email = companies.email
  ));

-- Suppliers table
CREATE TABLE suppliers (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at timestamptz DEFAULT now(),
  name text NOT NULL,
  supplier_code text
);

ALTER TABLE suppliers ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read all suppliers"
  ON suppliers
  FOR SELECT
  TO authenticated
  USING (true);

-- Invoices table
CREATE TABLE invoices (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at timestamptz DEFAULT now(),
  company_id uuid REFERENCES companies(id),
  supplier_id uuid REFERENCES suppliers(id),
  invoice_number text NOT NULL,
  invoice_date date NOT NULL,
  supplier_order_number text,
  customer_po_number text,
  scan_date timestamptz DEFAULT now()
);

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read own invoices"
  ON invoices
  FOR SELECT
  TO authenticated
  USING (
    company_id IN (
      SELECT id FROM companies 
      WHERE email = (SELECT email FROM auth.users WHERE id = auth.uid())
    )
  );

CREATE POLICY "Users can insert own invoices"
  ON invoices
  FOR INSERT
  TO authenticated
  WITH CHECK (
    company_id IN (
      SELECT id FROM companies 
      WHERE email = (SELECT email FROM auth.users WHERE id = auth.uid())
    )
  );

-- Invoice items table
CREATE TABLE invoice_items (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at timestamptz DEFAULT now(),
  invoice_id uuid REFERENCES invoices(id),
  product_code text NOT NULL,
  product_description text NOT NULL,
  unit_price decimal NOT NULL,
  quantity decimal NOT NULL,
  product_match_id uuid
);

ALTER TABLE invoice_items ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read own invoice items"
  ON invoice_items
  FOR SELECT
  TO authenticated
  USING (
    invoice_id IN (
      SELECT id FROM invoices 
      WHERE company_id IN (
        SELECT id FROM companies 
        WHERE email = (SELECT email FROM auth.users WHERE id = auth.uid())
      )
    )
  );

CREATE POLICY "Users can insert own invoice items"
  ON invoice_items
  FOR INSERT
  TO authenticated
  WITH CHECK (
    invoice_id IN (
      SELECT id FROM invoices 
      WHERE company_id IN (
        SELECT id FROM companies 
        WHERE email = (SELECT email FROM auth.users WHERE id = auth.uid())
      )
    )
  );

-- Indexes
CREATE INDEX ON companies (email);
CREATE INDEX ON invoices (company_id);
CREATE INDEX ON invoices (supplier_id);
CREATE INDEX ON invoice_items (invoice_id);
CREATE INDEX ON invoice_items (product_match_id);